VBA Format Number

Format function in VBA is used to format the given values in the desired format, this function has basically two mandatory arguments, one is the input which is taken in the form of a string and the second argument is the type of format we want to use for example if we use Format (.99,” Percent”) this will give us the result as 99%.

Format Number in VBA Excel

VBA stands way ahead from regular excel functions. VBA has many of its built-in functions, just like how we have more than 500 operations in the worksheet. One such formula in VBA is “Format Number.”

Yes, you heard it right. We have a function called “FormatNumber” in VBA. In this article, we will take a complete tour of this function exclusively.

How to Format Numbers with VBA NumberFormat?

As the function name itself says, it will format the given number according to the formatting instructions given by the user.

Number formatting is nothing but adding decimal points, enclosing negative numbers in parenthesis, showing leading zeroes for decimal values, etc. Using VBA FormatNumber function, we can apply the formatting style to the numbers we work with. Below is the syntax of the function

• Expression: This is nothing but the number we need to format.
• Num Digits After Decimal: How many digits you want for decimals position for the right side of the number.
• Include Leading Digit: Leading digit is nothing but digits before the number starts. This is applicable for the values less than 1 but greater than -1.
  • If you want to show zero before the decimal value, you can pass the argument as TRUE or -1, and the result will be. “0.55”
  • If you don’t want to show zero before the decimal value, you can pass the argument as FALSE or 0, and the result will be “.55.”
  • By default, the value will be -2 i.e., regional computer settings.
• Use Parents for Negative Numbers: If you wish to show the negative numbers in parenthesis, you can pass the argument as TRUE or -1, and the result will be. “(255)”
  • If you wish to show the negative numbers without parenthesis, you can pass the argument as FALSE or 0, and the result will be. “-255.”
• Group Digits: Whether you want to add a thousand separator or not. If yes, TRUE or -1 is the argument. If not, FALSE or 0 is the argument. By default, the value is -2 i.e., equal to regional computer settings.

Examples of Excel VBA FormatNumber Function

We will see the practical examples of the Excel VBA Format Number function. We will perform every argument separately.

For this purpose, create the macro name and declare one of the variables as a string. We need to report the variable as a string because the result given by the VBA function FormatNumber is as String only.

Code:

Sub Format_Number_Example1()
  Dim MyNum As String

End Sub

Example #1 – Add Decimal Points in Front of the Number

Step #1 – Assume we have been working with the number 25000, and we need to format it and add decimal points to the right of the number. Assign a value to our variable.

Code:

Sub Format_Number_Example1()
  Dim MyNum As String
  MyNum = FormatNumber(

End Sub

Step #2 – First up is an expression, i.e., the number we need to format, so our number is 25000.

Code:

Sub Format_Number_Example1()
  Dim MyNum As String

  MyNum = FormatNumber(25000,

End Sub

Step #3 – Next is how many digits we need to add, i.e., 2 numbers.

Code:

Sub Format_Number_Example1()
  Dim MyNum As String

  MyNum = FormatNumber(25000, 2)

End Sub

Step #4 – Show the value of the variable in the VBA message box.

Code:

Sub Format_Number_Example1()
  Dim MyNum As String

  MyNum = FormatNumber(25000, 2)
  MsgBox MyNum

End Sub

Step #5 – The result of this macro is like this.

We can see two decimals to the right of the number.

Example #2 – Group Number i.e., Thousand Separator

For the same number, we can add or delete a thousand separators. If we want to show a thousand separators, we need to select vbTrue for the last argument.

Code:

Sub Format_Number_Example1()
  Dim MyNum As String

  MyNum = FormatNumber(25000, 2, , , vbTrue)
  MsgBox MyNum

End Sub

This will throw the result like this.

Now, if select vbFalse, then we will not get a thousand separators.

Code:

Sub Format_Number_Example1()
  Dim MyNum As String

  MyNum = FormatNumber(25000, 2, , , vbFalse)
  MsgBox MyNum

End Sub

The result of this code like this.

If I select vbUseDefault, we get the result as per the system setting. Below is the result of this.

So my system setting has a thousand separators by default.

Example #3 – Enclose Parenthesis for Negative Numbers

If we have a harmful number, we can show the negative number in parenthesis. We need to select vbTrue under “Use Parents for Negative Numbers.”

Code:

Sub Format_Number_Example1()
  Dim MyNum As String

  MyNum = FormatNumber(-25000, 2, , vbTrue)
  MsgBox MyNum

End Sub

Now the result like this.

If we select vbFalse, we will get a negative number with a minus sign.

Code:

Sub Format_Number_Example1()
  Dim MyNum As String

  MyNum = FormatNumber(-25000, 2, , vbFalse)
  MsgBox MyNum

End Sub

Now the result like this.

Recommended Articles

This has been a guide to VBA Format Number Function. Here we learn how to Format Numbers using VBA Format Number in Excel and some practical examples and a downloadable excel template. Below are some useful excel articles related to VBA –

• VBA Set
• Use VLookup in VBA
• Excel Negative Numbers
• VBA ISERROR