Format Number in VBA Excel
VBA stands way ahead of regular excel functionsExcel FunctionsExcel functions help the users to save time and maintain extensive worksheets. There are 100+ excel functions categorized as financial, logical, text, date and time, Lookup & Reference, Math, Statistical and Information functions.read more. 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.
You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Format Number (wallstreetmojo.com)
How to Format Numbers with VBA NumberFormat?
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 zeroesLeading ZeroesLeading zeros are zeros that are added to figures without altering their mathematical values. This is done to keep a specific format in a sheet or to prevent errors. We may add Leading Zeros in Excel by utilizing the Right, TEXT, and Concatenate Functions.read more 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.
Examples of Excel VBA FormatNumber Function
We will see the practical examples of the Excel VBA FormatVBA FormatThe format function in VBA is used to format the given values into the format that is desired. It can be used to format dates, numbers, and trigonometrical values, among other things.read more Number function. We will perform every argument separately.
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.
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.”
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.
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.
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.
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 boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.
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.
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.
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.”
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.
Sub Format_Number_Example1() Dim MyNum As String
MyNum = FormatNumber(-25000, 2, , vbFalse) MsgBox MyNum
End Sub
Recommended Articles
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 SetUse VLookup in VBAExcel Negative NumbersVBA ISERROR
