VBA Format Number

VBA stands way ahead of regular excel functions. It happens because VBA has many built-in functions, just like we have more than 500 operations in the worksheet. For example, one such formula in VBA is "Format Number."

Yes, you heard it right. We have a function called "FormatNumber" in VBA. This article will take a complete tour of this function exclusively.

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

Number formatting is nothing but adding decimal points, enclosing negative numbers in parenthesis, showing leading zeroes for decimal values, etc. Using the 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 do you want for decimals positioned on the right side of the number?
• Include Leading Digit: Leading digit is nothing but digits before the number starts. It is applicable for 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."
  • The value will default 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 parentheses, you can pass the argument as FALSE or 0, and the result will be "-255."
• Group Digits: Whether you want to add a thousand separators 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.

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 a 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 variable's value 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 must select vbTrue for the last argument.

Code:

Sub Format_Number_Example1()
  Dim MyNum As String

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

End Sub

It will throw the result like this.

Now, if we select vbFalse, 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 is like this.

If we select vbUseDefault, we get the result 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

We can show the negative number in parenthesis if we have a harmful number. 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 is 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 is like this.

This article is a guide to VBA Format Number Function. Here, we learn how to format numbers using VBA Format Number in Excel, 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