How to Format Numbers in Decimal Places Using Excel VBA (6 Methods)

We’ll work with a dataset containing columns for “Rank,” “Chocolate Name,” and “Price ($)”.

Excel VBA Format Number Decimal Places


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.

Excel VBA Format Number Decimal Places

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

Excel VBA Format Number Decimal Places

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

Excel VBA Format Number Decimal Places

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

Excel VBA Format Number Decimal Places

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

Excel VBA Format Number Decimal Places
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.
  • Write 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

Excel VBA Format Number Decimal Places

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

Excel VBA Format Number Decimal Places

The formatted number will appear in the status bar.

  • Press the Run button to display the output.

Read More: Excel VBA: Format Percentage to 2 Decimal Places


Practice Section

For further expertise, you can use the workbook provided.

Excel VBA Format Number Decimal Places


Download Practice Workbook

You can download the practice workbook from here:


 

Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo