At times working with Excel, We have the freedom to choose our number format. We can use Excel VBA to Format number(s) in decimal places.
For more simplification, I am gonna use a Dataset of Best Chocolates around the World containing the columns Rank, Chocolate Name, and Price ($).
6 Ways to Use VBA to Format Number(s) in Decimal Places in Excel
We can choose Format numbers in decimal places in so many ways. But here we are going to discuss only in Excel VBA ways. To use Excel VBA, we have to follow some steps.
Steps:
A new window will open.
- Pick Insert from the new ribbon.
- Next, select Module.
We will have a space for writing VBA Codes.
- Click on the Run button or press F5 to run the Codes.
1. Using VBA to Format Integers Upto Two Decimals
We can use VBA to make a Format where we will have two decimals after integers.Â
Steps:
- Select a dataset for this purpose.
- In order to write code in VBA, open Module according to the previous steps.
- Write the following code.
Sub Format_in_Decimal()
Range("D5:D9").NumberFormat = "#.00"
End Sub
Here, I used the Sub_procedure Format_in_Decimal . I used Range.NumberFormat property in #.00 format to have two decimals after the Integers where I mentioned the Range D5:D9.
- Press Run or F5 to run the code.
We can see the result on the screen.
2. Applying VBA to Format Selected Cell in Decimals
We can also use VBA  to convert a selected cell’s format into decimals.
Steps:
- Select the cell. Here, I selected the D7 cell.
- Open the Module following the previous steps.
- Type the following code in the Module.
Sub Selected_cell_Format_Decimal()
ActiveCell.Select
Selection.Value = Format(ActiveCell, "#.0")
End Sub
Here, I used the Selected_cell_Format_Decimal Sub-Procedure. I also chose the Selection.Value property as well as Format #.0 which defines the results Format.
- Hit the F5/Run button to run the program.
The output will come automatically.
Read More: Excel VBA: Number Format with No Decimal Places
3. Using VBA in Sheet to Format Numbers in Decimal Places
We can format the whole Worksheet number in decimal places using Excel VBA.
Steps:
- Open the Module following the previous steps.
- For this, we can type the following Code:
Sub Formula_Decimal_Places()
Sheets("Module6").Range("D5:D9").NumberFormat = "#,#####0.00000"
End Sub
Here, I used the Formula_Decimal_Places Sub-procedure. I also used Worsheet.Range property where I mentioned the name of my Worksheet, Range, and Format.Â
The results were in front of my eyes as soon as I pressed the Run button.
Read More:Â Excel VBA to Format Number with Leading Zero
4. Using VBA to Format Number in Standard Form
Neglecting all digits after two digits of decimal is a Standard Format.
Steps:
- We can open the Module following the previous steps.
- Type the following Code to have numbers in Standard Form.
Sub Standard_Format()
Range("D5:D9").NumberFormat = Format("6853.8756", "Standard")
MsgBox "We have the value changed in " & Range("D5:D9").NumberFormat
End Sub
I used the Standard_Format Sub-procedure . I also used the Range.NumberFormat property where I mentioned the range D5:D9. Here, I also used MsgBox Function along with a message to show the output in a message box.
- Hit F5.
The Message box will appear along with the message and output.
5. Applying VBA Variable to Format Number in Decimal Places
We can also mention Variables in VBA to decide the Format in decimal places.
Steps:
- In order to open the Module follow the previous steps.
- Now, type the following code for this purpose.
Sub Format_Number_Decimal_Places()
Dim ChosenNum As String
ChosenNum = FormatNumber(6456, 3)
MsgBox "The expected Figure is " & ChosenNum
End Sub
Here, I have used the Sub-procedure named Format_Number_Decimal_Places. I used the Dim Statement where I mentioned the variable and type. In the MsgBox property, I have mentioned the variable with a message to see the output in the Message box.
After the run procedure, We will have the output.
6. Using Application StatusBar Property to Format Number in Decimal
In order to have the desired output in the Status bar, we can use the Application StatusBar property.
Steps:
- We can open the Module following the previous steps.
- Type the following code mentioned below:
Sub Decimal_Places_Format()
Application.StatusBar = Format(9684, "#,##0.00")
End Sub
Here, I used the Decimal_Places_Format Sub-Procedure here. I mentioned the Format in Application StatusBar property to have the output in Status Bar.
Pressing the Run button, we can see the output in Status Bar.
Read More:Â Excel VBA: Format Percentage to 2 Decimal Places
Practice Section
For further expertise, you can practice here.
Download Practice Workbook
Conclusion
We have tried to represent some ways to format numbers in decimal places in Excel VBA as simply as possible. For further interest, you can comment below.
