Excel VBA : format to percentage without percentage sign

Question

To keep the question as simple as possible:

In excel cell A1 has a numeric value: 0.11

I want to format the cell to percentage value but without the % sign. I want: 11 ; not 11%

I am not asking how to do this in regular excel; it must be VBA..

I guess Range("A1").NumberFormat = .... is the way to go; but when using this method, the % sign always show up.

Is there a way to format this without the percentage sign? Note that the value can not be changed, it has to remain 0.11 .

Answer 1

Another workaround would be to display the content of your cell x 100.

Sub Macro1()

    Dim cell As Range
    For Each cell In Range("A:A")   ' Change with your Range
        If cell.Value > 0 Then
            cell.NumberFormat = Str(cell.Value * 100)
        End If
    Next cell

End Sub

This would work but would have to be rerun with every change. For example with the following code in your Worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("A:A")) Is Nothing Then 
       Call Macro1            
   End If

End Sub

Sub Macro1()

        Dim cell As Range
        For Each cell In Range("A:A")   ' Change with your Range
            If cell.Value > 0 Then
                cell.NumberFormat = Str(cell.Value * 100)
            End If
        Next cell

    End Sub