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

Get FREE Advanced Excel Exercises with Solutions!

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 (\$). ## 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.

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

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

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

### 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:

• 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 Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  