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 own built-in functions just like how we have more than 500 functions in the worksheet. One of 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 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 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 in 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 computer regional settings.

Examples of Excel VBA FormatNumber Function

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

For this purpose create the macro name and declare one of the variables as a string. The reason why we need to declare 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 expression i.e. what is 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 digits.

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 then 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 the number which is negative 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 along with some practical examples and downloadable excel template. Below are some useful excel articles related to VBA –

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