Rectangle 27 0

What are .NumberFormat Options In Excel VBA?


General     General
Number      0
Currency    $#,##0.00;[Red]$#,##0.00
Accounting  _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
Date        m/d/yy
Time        [$-F400]h:mm:ss am/pm
Percentage  0.00%
Fraction    # ?/?
Scientific  0.00E+00
Text        @
Special     ;;
Custom      #,##0_);[Red](#,##0)
Sub numberformats()
  Dim rng As Range
  Set rng = Range("A24:A35")
  For Each c In rng
    Debug.Print c.NumberFormat
  Next c
End Sub

(I just picked a random entry for custom)

Note this was done on Excel for Mac 2011 but should be same for Windows

The "s of Accounting need to be escaped, "" to prevent bugs in vba that uses that code.

Note
Rectangle 27 0

What are .NumberFormat Options In Excel VBA?


_ [$-804]* #,##0.00_ ;_ [$-804]* -#,##0.00_ ;_ [$-804]* "-"??_ ;_ @_

If you do these steps for each NumberFormat that you desire, then the world is yours.

In my example, I selected the Chinese (PRC) Currency from the options contained in the "Account Numbers Format" combo box.

So, in this example, the NumberFormat of my Chinese (PRC) Currency cell is as follows:

Step 1: In the user interface, set a cell to the NumberFormat you want to use.

Step 2: Expand the Number Format dropdown and select "More Number Formats...".

Step 3: In the Number tab, in Category, click "Custom".

Thanks to this question (and answers), I discovered an easy way to get at the exact NumberFormat string for virtually any format that Excel has to offer.

The "Sample" section shows the Chinese (PRC) currency formatting that I applied.

The "Type" input box contains the NumberFormat string that you can use programmatically.

Note
Rectangle 27 0

What are .NumberFormat Options In Excel VBA?


  • A custom formatted string, like "$#,##0", to specify exactly what format you're using.

In Excel, you can set a Range.NumberFormat to any string as you would find in the "Custom" format selection. Essentially, you have two choices:

Note