How to Utilize Number Formats in Excel VBA

1. Implement NumberFormat Property on Numeric Values VBA
2. Get the Type of NumberFormat Used by a VBA Range

Numbers come in different forms in our daily lives. We have dates, prices, percentages, fractions, decimals, etc. They might all appear differently, but they all signify a value.

Same with Excel, we can deal with different formats of numbers. We can use Date and Time datatypes when dealing with time and dates, while we can use Percent when dealing with percentages.

This article will demonstrate the NumberFormat property to help us utilize and adequately deal with numbers throughout our Excel VBA practice.

Implement NumberFormat Property on Numeric Values VBA

This section uses the NumberFormat property to change the number formatting in a cell.

Syntax:

Range.NumberFormat = [FormatCodes]

Parameters:

[format codes] Matrix:

The Sheet1 virtual worksheet will be the referenced values used on all examples below for the example codes below.

Sheet1:

 |    A    |      B      |      C    |
1| 1234.56 | 11/23/2021  | 93249812  |
2| 5       | 0.00004     | 0.25      |

The code block below will output General formatting:

Sub FormatGeneral()
Range("A1").NumberFormat = "General"
Debug.Print Range("A1").Text
End Sub

FormatGeneral Output:

1234.56 

The code block below will output Currency formatting:

Sub FormatCurrency()
Range("C1").NumberFormat = "$#,##0.00"
Debug.Print Range("C1").Text
End Sub

General Output:

$93,249,812.00

The code block below will output Scientific formatting:

Sub FormatScientific()
Range("C1").NumberFormat = "0.00E+00"
Debug.Print Range("C1").Text
End Sub

FormatScientific Output:

9.32E+07

The code block below will output Accounting formatting:

Sub FormatAccounting()
Range("A1").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Debug.Print Range("A1").Text
End Sub

FormatAccounting Output:

 $ 1,234.56 

The code block below will output Date formatting:

Sub FormatDate()
Range("A2").NumberFormat = "m/d/yy"
Debug.Print Range("A2").Text
End Sub

FormatDate Output:

1/5/00

The code block below will output Time formatting:

Sub FormatTime()
Range("B2").NumberFormat = "$-F400]h:mm:ss am/pm"
Debug.Print Range("B2").Text
End Sub

FormatTime Output:

12:00:03 AM

The code block below will output Percentage formatting:

Sub FormatPercentage()
Range("A1").NumberFormat = "0.00%"
Debug.Print Range("A1").Text
End Sub

FormatPercentage Output:

123456.00%

The code block below will output Fraction formatting:

Sub FormatFraction()
Range("C2").NumberFormat = "# ?/?"
Debug.Print Range("C2").Text
End Sub

FormatFraction Output:

1/4

Get the Type of NumberFormat Used by a VBA Range

We returned the formatted value of the specified range on the above examples. We will get the formatting type used in the specified range in this section.

Syntax:

Range.NumberFormat

Parameters:

Still referring to Sheet1, below example code blocks will demonstrate returning the formatting type used by the range specified.

Sub GetFormattingType()
Debug.Print Range("B1").NumberFormat
End Sub

GetFormattingType Output:

m/d/yyyy