Using a UDF in Excel to update the worksheet

What is a UDF in Excel?

A UDF (User Defined Function) is a custom function that you can create using VBA (Visual Basic for Applications) and use in your worksheets like any regular Excel function. A UDF can take data, perform a calculation, and return a value or an array of values. You can use a UDF when the built-in Excel functions are not enough for your needs or when you want to simplify complex formulas.

How to create a UDF in Excel?

To create a UDF in Excel, you need to use the VBA editor. You can access the VBA editor by pressing Alt + F11 or by clicking the Visual Basic button on the Developer tab. In the VBA editor, you need to insert a new module by right-clicking on the project name in the Project Explorer window and selecting Insert > Module. In the module, you can write your UDF code using the Function keyword, followed by the name of your function, the arguments (if any), and the return type (optional). For example, the following code creates a simple UDF that adds two numbers and returns the result:

Function AddTwoNumbers(num1 As Double, num2 As Double) As Double
    AddTwoNumbers = num1 + num2
End Function

How to use a UDF in Excel?

To use a UDF in Excel, you can enter it in a cell like any regular Excel function, preceded by an equal sign. You can also use the Insert Function dialog box to browse and select your UDF from the User Defined category. For example, to use the AddTwoNumbers UDF, you can enter the following formula in a cell:

=AddTwoNumbers(10, 20)

This will return 30 in the cell.

How to update the worksheet from a UDF in Excel?

Normally, a UDF in Excel cannot change the environment of Excel, such as inserting, deleting, or formatting cells, changing another cell’s value, or moving, renaming, deleting, or adding sheets to a workbook. This is because a UDF is designed to only return a value or an array of values, not to perform any actions on the worksheet. However, there are some workarounds that can allow you to update the worksheet from a UDF in Excel. One of them is to use the Evaluate method of the Application object, which can execute a VBA expression or a worksheet formula and return the result. By using the Evaluate method, you can call a subroutine from a UDF that can change the worksheet. For example, the following code creates a UDF that changes the color of a cell based on its value:

Sub ChangeColor(cell As Range)
    If cell.Value > 10 Then
        cell.Interior.Color = vbRed
    Else
        cell.Interior.Color = vbYellow
    End If
End Sub

Function SetColor(src As Range, dest As Range) As String
    dest.Parent.Evaluate "ChangeColor(" & dest.Address(False, False) & ")"
    SetColor = "Changed color!"
End Function

The ChangeColor subroutine takes a cell as an argument and changes its interior color to red if its value is greater than 10, or to yellow otherwise. The SetColor UDF takes two ranges as arguments: the source range and the destination range. The UDF uses the Evaluate method to call the ChangeColor subroutine and pass the destination range as the argument. The UDF then returns a string “Changed color!” as the result. To use the SetColor UDF, you can enter the following formula in a cell:

=SetColor(A1, B1)

This will change the color of cell B1 based on the value of cell A1 and return “Changed color!” in the cell where the formula is entered.

Example scenario

To illustrate how to use a UDF in Excel to update the worksheet, let’s consider an example scenario. Suppose you have a worksheet that contains the sales data of a company for the year 2023. The worksheet has four columns: Month, Sales, Target, and Bonus. The Bonus column is empty and you want to use a UDF to calculate and fill it based on the following criteria:

• If the sales are equal to or greater than the target, the bonus is 10% of the sales.
• If the sales are less than the target but greater than 80% of the target, the bonus is 5% of the sales.
• If the sales are less than 80% of the target, the bonus is 0.

You also want to use the UDF to format the Bonus column as currency and highlight the cells that have a bonus of 0 with a red fill color. The following screenshot shows the worksheet before using the UDF:

To create and use the UDF, you can follow these steps:

1. Open the VBA editor by pressing Alt + F11 or clicking the Visual Basic button on the Developer tab.
2. Insert a new module by right-clicking on the project name in the Project Explorer window and selecting Insert > Module.
3. In the module, write the following code to create the UDF:

Sub CalculateBonus(cell As Range, sales As Double, target As Double)
    Dim bonus As Double
    If sales >= target Then
        bonus = sales * 0.1
    ElseIf sales >= target * 0.8 Then
        bonus = sales * 0.05
    Else
        bonus = 0
    End If
    cell.Value = bonus
    cell.NumberFormat = "$#,##0.00"
    If bonus = 0 Then
        cell.Interior.Color = vbRed
    End If
End Sub

Function FillBonus(src As Range, dest As Range) As String
    Dim sales As Double
    Dim target As Double
    sales = src.Cells(1).Value
    target = src.Cells(2).Value
    dest.Parent.Evaluate "CalculateBonus(" & dest.Address(False, False) & "," & sales & "," & target & ")"
    FillBonus = "Filled bonus!"
End Function

The CalculateBonus subroutine takes three arguments: a cell, a sales value, and a target value. It calculates the bonus based on the criteria and assigns it to the cell. It also formats the cell as currency and changes its fill color to red if the bonus is 0. The FillBonus UDF takes two ranges as arguments: the source range and the destination range. The source range contains the sales and target values, and the destination range is the cell where the bonus will be calculated and filled. The UDF uses the Evaluate method to call the CalculateBonus subroutine and pass the arguments. The UDF then returns a string “Filled bonus!” as the result.

4. Save the module and close the VBA editor.
5. In the worksheet, select the cell D2 and enter the following formula:

=FillBonus(B2:C2, D2)

This will use the UDF to calculate and fill the bonus for January based on the sales and target values in B2 and C2.

6. Copy the formula down to the rest of the cells in column D. This will use the UDF to calculate and fill the bonus for the other months.

The following screenshot shows the worksheet after using the UDF:

Other approaches

Using a UDF in Excel to update the worksheet is one of the possible approaches to solve the problem, but it is not the only one. There are other ways to achieve the same result, such as:

• Using built-in Excel functions, such as IF, AND, OR, and conditional formatting. This may require longer and more complex formulas, but it does not require any VBA code.
• Using VBA macros, such as Worksheet_Change or Workbook_Open events. This may allow more flexibility and control over the worksheet, but it may also require more coding and testing.
• Using Excel add-ins, such as AbleBits Ultimate Suite or Kutools for Excel. These are third-party tools that provide additional functions and features for Excel, but they may also require installation and licensing.