Excel VBA currency formatting does not work-CodePudding

I have the following code to clear and reset the formatting on a range of cells. firstRow and lastRow are correctly set. But still some "random" cells are not correctly formatted. The only way to find those cells is by looking for a missing "€" sign. The only way I know to fix this error is to double-click the cell. Afterwards the "€" sign appears and the calculations work properly again. Do you know a smarter solution and a way to detect and debug this?

' Amount in EUR
Dim amountInEurRange As Range
Set amountInEurRange = Full.Range(f_amountInEUR & firstRow & ":" & f_amountInEUR & lastRow)
amountInEurRange.ClearFormats
amountInEurRange.Select
With Selection
    .NumberFormat = "#,##0.00 [$€-de-DE]"
    .Value = .Value
End With

CodePudding user response：

Obviously, Excel didn't recognize the second value as number and stored it as text. A text has no numeric value and therefore is not added up in the Sum-function. A text will also not be formatted.

First thing is you need to understand the difference between value and format of a cell. A numeric value is internally stored in a binary format. This value is then displayed in a human readable form. The value 1234 can be displayed as 1234, as 1.234,00, as 1,234.0, as €1,234.0 or 1.23E 03 and so on. Formatting will not change it's internal value.

Second thing is you need to understand how Excel works if you enter some data. You enter the keys 1 8 2 1 , 3 7 and presses Enter. If on your computer (not in Excel!) the regional settings define the decimal character as comma, Excel will see your input as number and convert it to its internal binary value. It then looks at the formatting of the cell and displays it accordingly. If however, your decimal character is a dot, Excel will not see your input as number and therefore store the input as string. A string is stored in a complete different way (put it simple: Every character is one byte).

Of course the reality is more complicated, Excel will also check the formatting when it receives your input. If you format a cell as date, it will try to interpret your input as date, again by checking it against the regional settings. Formatting the cell as text will store the input as text, even if it could be converted to a number.

Now somehow the data in your sheet was entered and Excel didn't interpret is as number. Either someone with different regional settings entered it, or maybe it was imported from a CSV. What happens when you double-click the cell is that Excel handle the content as if you just entered it - and on your PC it can be interpreted as number and therefore is converted. Looks similar or equal, but is a complete different value.

What you could do is to add the following code to your routine, it will try to convert a string into a number

For Each cell In amountInEurRange
    If VarType(cell.Value) = vbString Then
        Dim s As String, v As Variant
        s = cell.Value
        On Error Resume Next
        v = CDbl(Replace(s, ",", ".")) ' In VBA, decimal char is always "."
        On Error GoTo 0
        If VarType(v) = vbDouble Then cell.Value = v
    End If
Next cell