# Utilize Number Formats in Excel VBA

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:

`Range` The range to format
`[FormatCodes]` Predetermined strings which will represent what type of number format to be used.

`[format codes]` Matrix:

Format Name Format Code Example Description
—————– ——————————————————– —————– ——————————————————
General `General` `1234567.89` display the number without specific format
Currency `\$#,##0.00` `\$1,234,567.89` display as general monetary values
Number `0` `1234567.89` format will display the number exactly as it is
Scientific `0.00E+00` `1.23E+06` convert the number to Exponential format
Accounting `_(\$* #,##0.00_);_(\$* (#,##0.00);_(\$* ""-""??_);_(@_)` `\$123.00` display the number used in Accounting
Date `m/d/yy` `11/23/2021` display date and time serial numbers
Time `[\$-F400]h:mm:ss am/pm` `12:00:03 AM` display date and time serial numbers
Percentage `0.00%` `5.23 %` display the value in percentage form
Fraction `# ?/?` `1/4` display the value as fractional form
Special `;;` `000-00-0000` formats used on list and database values

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:

`Range` The cell range to get the formatting type

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
``````