Excel VBA Format Number
VBA comes with an inbuilt function for Formatting numbers. The name of that inbuilt Function is “Format Number”. This function formats the specific number in a format which is required by the user.
Before we go ahead, let us first understand what is number formatting. Number Formatting basically means the specific number should be shown in a particular way. For example, if you want to see a specific number in terms of two decimal point (150.55) or you want to see the same number in terms of no decimal point (150). These are just examples.
Categories of Excel Format Number
Basically, we can categorize these number formatting in four categories.
- Decimal points
- Negative numbers in brackets
- Leading digits
- Group digits
We are going to learn all the formattings in VBA with the help of examples. But first, let us take a look at the syntax of VBA function for Format number.
So as you can see the syntax of VBA function for format number in the above screenshot, let us break that function and understand every part of the function.
- Expression: As you can see the first part of the function is an expression which is nothing but the number we want to Format.
- Num digit after the decimal: The second part of the function asks for the number of digits of the number you want to see after the decimal point.
- Leading Digit: This is useful when the number is between -1 and 1 which means when the number is more than -1 and less than 1. For example 0.25.
So if you want to see the value zero before the decimal point, you need to insert argument in the function as TRUE or -1. The result will be 0.55.
But if you insert the value in the argument as FALSE or 0, the result will .55.
- Use parent for Negative Numbers: This part is used for Negative numbers. If you want to see the negative numbers in a bracket then you should give an argument as TRUE or -1 and the result will be (156). But when you provide the argument as FALSE or 0, the result will be -156.
- Group Digits: This is the last part of the function which is used for the thousand separators. So if you want to see the numbers with a thousand separator you should provide the argument as TRUE or -1 and the result will be 52,000. But if you provide the same argument as FALSE or 0, the result will be like 52000.
How to Format Number Using VBA Code?
Now as you understand the different forms of Number Formatting and the VBA inbuilt function of Number format, we can go ahead and see how we can use this with the help of examples.
Before going to the examples, follow the below basic steps if you are new to VBA macro. You can insert the below codes in the VBA module.
Step 1: Create a Macro name which is the first line in the code as you can see in the screenshot.
Step 2: Declare one of the variables as String because the result given by the VBA Format number function is String.
Code:
Sub Format_Number_Example1() Dim MyNum As String End Sub
Now we can go ahead with our examples.
Decimal Points – Example #1
Suppose we are working with the number 56456 and you want to see two numbers after the decimal point.
Step 1: In the code, you can add the VBA inbuilt function as below.
Mynum = Formatnumber(
Step 2: As you know the first step of the function is the expression which is nothing but the number you want to format. 56456 in our case.
Mynum = Formatnumber(56456,
Step 3: The next step is Numdigitafterdecimal which is nothing but the number of digits you want to see after the decimal point which is 2 in our case.
Code:
Sub Format_Number_Example1() Dim MyNum As String MyNum = FormatNumber(56456, 2) MsgBox MyNum End Sub
Step 4: The next step is to show the number in a message box. VBA has an inbuilt function to show the value in a message box which is MSGBOX.
So you just need to insert the below code after inserting the function of Format number for MyNum.
Code:
Sub Format_Number_Example1() Dim MyNum As String MyNum = FormatNumber(56456, 2) MsgBox MyNum End Sub
As you can see the last line in the code is End Sub which means VBA code for the macro ends after that.
Step 6: As the coding for the format number ends, you can run the macro and you can see the below result after running the macro.
Thousand Separator – Example #2
Now suppose with the same example, we need to show the same number 56456 with thousand separators. In that case, we need to give the last argument as “vbTrue” as shown in the below screenshot.
Code:
Sub Format_Number_Example2()
Dim MyNum As String
MyNum = FormatNumber(56456, 2, , , vbTrue)
MsgBox MyNum
End Sub
After running the macro, you will be able to see the below result.
If you select “vbFalse” as the last argument, the number will not be shown as thousand separator.
Show Negative number in Bracket – Example #3
Suppose the same number 56456 in the above example is negative and you need to show the negative numbers in a bracket then the fourth argument in the function should be “vbTrue” as shown in the below screenshot.
Code:
Sub Format_Number_Example3() Dim MyNum As String MyNum = FormatNumber(-56456, 2, , vbTrue, vbTrue) MsgBox MyNum End Sub
As you can see the Fourth & Fifth Argument in the code are “vbTrue” which means the formatting for Negative number (fourth argument) & formatting for thousand separators (Fifth argument) both are True. So you will see the negative number within a bracket and with Thousand Separators as shown in the below screenshot.
Formatting for Number between -1 and 1 – Example #4
Suppose the number is 0.567 which is between -1 & 1 then you have two options for formatting the number. First option is to show Zero before the decimal point or not to show zero before the decimal point.
If you choose the option to show zero before the decimal point then you should provide the third argument in the function as “vbTrue” as shown in the below screenshot.
Code:
Sub Format_Number_Example4() Dim MyNum As String MyNum = FormatNumber(0.567, 2, vbTrue, vbTrue, vbTrue) MsgBox MyNum End Sub
You will be able to see the below result after running the macro code.
As you can see the result is showing only two digits after the decimal point because we have provided only 2 as the second argument in the function.
If you select the third argument as “vbFalse” as shown in the below screenshot.
Code:
Sub Format_Number_Example5() Dim MyNum As String MyNum = FormatNumber(0.567, 2, vbFalse, vbTrue, vbTrue) MsgBox MyNum End Sub
Then you will not see the zero before the decimal point as shown in the below screenshot.
Things to Remember
- VBA FormatNumber Function has 5 arguments so make sure you provide the right arguments in the order of the function. We have to follow the order of the function, there is no other way out.
- If you keep any one of the argument as Blank then it will automatically consider the VBA default option.
- Be careful with vbTrue and vbFalse argument for last three arguments for number formatting.
- For the first argument which is the expression, you can also provide the cell reference in the excel sheet instead of any number.
Recommended Articles
This is a guide to VBA Format Number. Here we discuss how to Format Numbers in Excel Using VBA Code along with practical examples and downloadable excel template. You can also go through our other suggested articles-
