Dataset Overview
We’ll work with a dataset containing columns for Rank, Chocolate Name, and Price ($).
Method 1 – Formatting Integers Up to Two Decimals
- Go to the Excel ribbon and choose the Developer tab.
- Click on Visual Basic to open the VBA editor.
- In the VBA editor, select Insert from the menu and choose Module.
- Enter the following code in the module:
Sub Format_in_Decimal() Range("D5:D9").NumberFormat = "#.00" End Sub
This code formats the range D5:D9 to display two decimal places after integers.
- Click the Run button or press F5 to execute the code.
The result will be reflected in the specified range.
Method 2 – Formatting a Selected Cell to Decimal Places
- Choose the cell you want to format (e.g., D7).
- Follow the previous steps to open the VBA editor.
- Enter the following code:
Sub Selected_cell_Format_Decimal()
ActiveCell.Select
Selection.Value = Format(ActiveCell, "#.0")
End Sub
This code formats the selected cell to display one decimal place.
- Press F5 or click the Run button to apply the formatting.
Read More: Excel VBA: Number Format with No Decimal Places
Method 3 – Formatting an Entire Worksheet to Decimal Places
- Access the VBA editor as before.
- Use the following code to format the entire range D5:D9 in the Module6 worksheet:
Sub Formula_Decimal_Places()
Sheets("Module6").Range("D5:D9").NumberFormat = "#,#####0.00000"
End Sub
This code sets the format to display up to five decimal places.
- Execute/Run the code to see the results.
Read More: Excel VBA to Format Number with Leading Zero
Method 4 – Formatting Numbers in Standard Form
- Access the VBA editor.
- Use this code to format the range D5:D9 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
The message box will display the formatted output.
- Press F5 to execute the code.
Method 5 – Using VBA Variables to Format Numbers in Decimal Places
In this method, we’ll leverage VBA variables to determine the desired decimal format. Follow these steps:
- Access the VBA editor by following the previous steps.
- Enter the Code:
- Create a new sub-procedure named Format_Number_Decimal_Places.
- Use the Dim statement to declare a variable called ChosenNum as a string.
- Assign the formatted number to ChosenNum using the FormatNumber function:
Sub Format_Number_Decimal_Places()
Dim ChosenNum As String
ChosenNum = FormatNumber(6456, 3)
MsgBox "The expected Figure is " & ChosenNum
End Sub
This code will display the number 6456 with three decimal places in a message box.
- Execute the code to see the output.
Method 6 – Formatting Numbers Using the Application StatusBar Property
To display the formatted number in the status bar, follow these steps:
- Access the VBA editor as before.
- Add the Code:
- Create a sub-procedure called Decimal_Places_Format.
- Use the Application.StatusBar property to set the desired format (e.g., #,##0.00):
Sub Decimal_Places_Format()
Application.StatusBar = Format(9684, "#,##0.00")
End Sub
The formatted number will appear in the status bar.
- Press the Run button to display the output.
Practice Section
For further expertise, you can use the workbook provided.
Download Practice Workbook
You can download the practice workbook from here:
