How to Use VBA Function and Excel Formula to Format Numbers Based on Cell Value

In this article, we will learn how to format numbers in Excel cells based on their values using VBA function and Excel formula. Number formatting is the process of changing the appearance of a number without changing its underlying value. For example, we can format a number as currency, percentage, fraction, date, time, etc. Number formatting can make the data more readable and meaningful.

To format numbers based on cell value, we need to use two components: a VBA function and an Excel formula. A VBA function is a custom-made procedure that can perform calculations, manipulate data, or return a value. An Excel formula is an expression that can perform calculations, look up data, or return a value. We can use a VBA function in an Excel formula to create a dynamic number format that depends on the cell value.

Procedures

The general steps to format numbers based on cell value using VBA function and Excel formula are:

1. Create a VBA function that takes a cell value as an argument and returns a number format string based on some criteria. For example, if the cell value is greater than or equal to zero, then return a whole number format without decimals. If the cell value is less than zero, then return a number format with two decimal places.
2. Enter the VBA function in the Visual Basic Editor (VBE) and save the workbook as a macro-enabled file (.xlsm).
3. Use the VBA function in an Excel formula to apply the number format to a range of cells. For example, use the formula =TEXT(A1,MyFormat(A1)) to format the cell A1 with the custom number format returned by the VBA function MyFormat.
4. Copy and paste the formula to other cells as needed.

Example

To illustrate the above procedures, let’s create an example scenario. Suppose we have a table of sales data for different products in different regions. We want to format the sales numbers based on their values. If the sales number is positive, we want to show it as a whole number with a dollar sign. If the sales number is negative, we want to show it as a number with two decimal places and a minus sign. If the sales number is zero, we want to show it as a dash.

Here are the steps to format the sales numbers based on their values using VBA function and Excel formula:

1. Create a VBA function that takes a cell value as an argument and returns a number format string based on the criteria. The VBA function can be written as follows:

Function MyFormat(Value As Double) As String
    'This function returns a custom number format based on the value
    If Value > 0 Then
        'If the value is positive, return a whole number format with a dollar sign
        MyFormat = "$#,##0"
    ElseIf Value < 0 Then
        'If the value is negative, return a number format with two decimal places and a minus sign
        MyFormat = "-#,##0.00"
    Else
        'If the value is zero, return a dash
        MyFormat = "-"
    End If
End Function

2. Enter the VBA function in the VBE and save the workbook as a macro-enabled file. To do this, follow these steps:
   • Press Alt + F11 on your keyboard or go to the Developer tab and click on Visual Basic to open the VBE.
   • In the VBE, click on Insert > Module to insert a new module.
   • In the code window of the module, paste the VBA function code.
   • Save the workbook as a macro-enabled file with the extension .xlsm.
3. Use the VBA function in an Excel formula to apply the number format to the sales numbers. To do this, follow these steps:
   • In the cell D2, enter the formula =TEXT(C2,MyFormat(C2)) to format the sales number in C2 with the custom number format returned by the VBA function MyFormat.
   • Copy and paste the formula to the other cells in column D.
4. The result is a table with the sales numbers formatted based on their values.

Other Approaches

There are other ways to format numbers based on cell value in Excel without using VBA function and Excel formula. Some of them are:

• Using conditional formatting: Conditional formatting is a feature that allows you to apply different formats to cells that meet certain criteria. You can use conditional formatting to apply different number formats to cells based on their values. For example, you can use conditional formatting to apply a currency format to positive values, a percentage format to negative values, and a text format to zero values.
• Using custom number formats: Custom number formats are user-defined formats that can control how a number is displayed in a cell. You can use custom number formats to create a dynamic number format that depends on the cell value. For example, you can use the custom number format "$#,##0;-#,##0.00;-" to format a number as a currency with a dollar sign if it is positive, as a number with two decimal places and a minus sign if it is negative, and as a dash if it is zero.