Set standard number format with thousand separator, 2 decimals and minus sign for negative numbers using VBA?

Question

I've seen the question asked before on stackoverflow, how to get normal number format with thousand separator and 2 decimals. The answer was to set:

    rng.NumberFormat = "##0.00"

But this is incomplete, because, at least on my computer, I don't get any space separator between millions and thousands. So I have changed that to:

    rng.NumberFormat = "### ### ##0.00"

But this is again incomplete, because for some reason negative numbers were formatted to look like they have a space between the minus sign and the number. See below:

- 12.4

So, there are some things left to do to arrive at Excels "built-in" "format as number" formats. Additionally the formatting that I apply though VBA is described as Custom by Excel.

Is there any way to set the format to be the standard built in format as number with thousand separators, 2 decimals and minus signs for negative numbers?

I'm looking for something like:

    rng.NumberFormat = "Number, 2, minus"

Solution

rng.NumberFormat = "# ##0.00:-# ##0.00"

You put the format for positive numbers before : and the format for negative after. You don't need to put hundreds of # signs in the format, just enough to show what the 1000's separator is.

Comments

[Rory]

Have you tried simply #,##0.00, assuming your regional settings use a space as the thousands separator?

[wqw]

There is no point in putting multiple # in a sequence i.e. #,##0.00 => #,#0.00 and ##0.00 => #0.00 should be the same format.

[vladiz]

How can I make it not to display zeros after the point if the number is whole number. For example 1000 instead of 1000.00 , but if there is numbers after the decimal point they should be displayed like 1000.22

[simon at rcl]

You either have decimals or you don't. Buy you could conditionally change the NumberFormat if the value is an integer/long.