Utilize Number Formats in Excel VBA

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:

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