VBA formatting issue

Question

I'm formatting a cell with the following format using VBA (€ accounting):

"_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* ""-""?? €_-;_-@_-"

But strangely in Excel the numbers are shown as i.e. 50,05 is 50,05000€.

When I select format I get the following:

_-* #,##000 \€_-;-* #,##000 \€_-;_-* "-"?? \€_-;_-@_-

The commata in the zeros have gone missing. How do I fix this?

Thanks

Answer 1

I find the . strange in the left part.

I would try something like "_-* # ##0,00 €_-;-* # ##0,00 €_-;_-* ""-""?? €_-;_-@_-"

Answer 2

i see some thousands and decimal separator mistake here

"_-* #.##0,00 €_-;-* #.##0,00 €_-;_-* ""-""?? €_-;_-@_-" is for dots as thousand separator and commas as decimal separator

while your example (i.e. 50,05), is pointing to a comma decimal separator

may be your excel adopts the opposite convention

go to Click File > Options, click "Advanced" tab and go to "Editing options" section by the end of which you can check what thousands and decimal separator have been assumed

and then change your code accordingly

or you can use a somewhat "insensitive" code which adopts currently separator conventions:

Selection.NumberFormat = "_-* #" & Application.ThousandsSeparator & "##0" & Application.DecimalSeparator & ",00 €_-;-* #" & Application.ThousandsSeparator & "##0" & Application.DecimalSeparator & "00 €_-;_-* ""-""?? €_-;_-@_-"

or, in a slightly more readable form:

With Application
    Selection.NumberFormat = "_-* #" & .ThousandsSeparator & "##0" & .DecimalSeparator & ",00 €_-;-* #" & .ThousandsSeparator & "##0" & .DecimalSeparator & "00 €_-;_-* ""-""?? €_-;_-@_-"
End With

Answer 3

I have found the mistake. I had to swap the commata and points to:

"_-* #,##0.00 €_-;-* #,##0.00 €_-;_-* ""-""?? €_-;_-@_-"