How to Use Excel VBA to Format Number in Decimal Places (6 Ways)

At times of 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 ($).

Download Practice Workbook

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. In order to use Excel VBA, we have to follow some steps.

Steps:

• Select Developer from the ribbon.
• Then, choose Visual Basic.

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.

Read More: Excel VBA: Round to 2 Decimal Places (5 Ways)

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: Format Percentage to 2 Decimal Places

3. Using VBA in Sheet to Format Number 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 are in front of my eyes as soon as I pressed the Run button.

Read More: Excel VBA: Format Currency to Two Decimal Places (3 Methods)

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.

Read More: Excel VBA: Number Format with No Decimal Places (6 Methods)

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 following 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.

Read More: Excel VBA: Format Number with Comma (2 Examples)

6. Using Application StatusBar Property to Format Number in Decimal

In order to have 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.

Practice Section

For further expertise, you can practice here.

Conclusion

We have tried to represent some ways to format number in decimal places in Excel VBA as simply as possible. For further interest, you can comment below.

Related Articles

• Excel VBA to Format Textbox Number with UserForm (With Easy Steps)