Creating custom functions in VBA for Excel VBA

Creating custom functions in VBA for Excel (also called User Defined Functions or UDFs) allows you to add specific functionality that is not available in Excel’s built-in functions. Here’s a detailed guide to help you understand how to create custom functions in VBA, with step-by-step explanations.

Step 1: Access the VBA Editor

1. Open Excel.
2. Press Alt + F11 to open the VBA editor.
3. In the editor, click Insert in the menu, then select Module to insert a new code module.

Step 2: Write a Simple Custom Function

Here’s an example of a custom function that adds two numbers.

VBA code for the function:

Function AddNumbers(a As Double, b As Double) As Double
    AddNumbers = a + b
End Function

Explanation of the code:

• Function AddNumbers(a As Double, b As Double) As Double:
  • Function declares that you are creating a function.
  • AddNumbers is the name of the function.
  • (a As Double, b As Double) are the function arguments, meaning you will input two numbers (a and b) into this function. As Double indicates that these arguments are floating-point numbers (i.e., decimal numbers).
  • As Double at the end indicates that the return type of the function is also a decimal number (Double).
• AddNumbers = a + b:
  • This line adds a and b and returns the result in the function.
• End Function:
  • This marks the end of the function.

Step 3: Use the Function in Excel

1. After writing your code in the VBA module, you can return to your Excel worksheet.
2. In a cell, type the custom function just like any regular Excel function:

=AddNumbers(5, 3)

This will display the result 8 in the cell.

Step 4: Create a Custom Function with Additional Features

Now, let’s create a custom function that calculates the average of multiple numbers, ignoring any negative values.

VBA code for the function with condition:

Function AveragePositive(ParamArray values() As Variant) As Double
    Dim sum As Double
    Dim count As Integer
    Dim i As Integer   
    sum = 0
    count = 0   
    ' Loop through all values in the array
    For i = LBound(values) To UBound(values)
        If values(i) > 0 Then
            sum = sum + values(i)
            count = count + 1
        End If
    Next i   
    ' If no positive numbers, return 0
    If count > 0 Then
        AveragePositive = sum / count
    Else
        AveragePositive = 0
    End If
End Function

Explanation of the code:

• Function AveragePositive(ParamArray values() As Variant) As Double:
  • ParamArray allows you to pass a variable number of arguments to the function, making it useful for accepting multiple values.
  • values() is an array of type Variant, meaning it can hold different types of data (e.g., integers, decimals, etc.).
• sum and count:
  • These variables are used to store the sum of positive values and the count of positive values, respectively.
• For i = LBound(values) To UBound(values):
  • This loop goes through all the elements in the values array. LBound and UBound give the indices of the first and last elements in the array.
• If values(i) > 0 Then:
  • If the value is positive, it is added to the sum, and the count is incremented.
• AveragePositive = sum / count:
  • If positive numbers are found, the function returns the average of the positive values. Otherwise, it returns 0.

Step 5: Use the Function in Excel

1. Go back to your Excel worksheet and enter the function in a cell with multiple values, for example:

=AveragePositive(5, -3, 2, 8, -1)

This will return the average of the positive values, i.e., (5 + 2 + 8) / 3 = 5.

Step 6: Other Examples of Custom Functions

Here are a few other examples to show the flexibility of VBA:

Function to calculate the square of a number:

Function Square(x As Double) As Double
    Square = x * x
End Function

Function to check if a number is even or odd:

Function IsEven(x As Double) As String
    If x Mod 2 = 0 Then
        IsEven = "Even"
    Else
        IsEven = "Odd"
    End If
End Function

Step 7: Handling Errors in a Custom Function

It’s often useful to handle errors in custom functions. Here’s an example where the function checks if the user enters valid values (non-zero and numeric).

Function Divide(a As Double, b As Double) As Double
    On Error GoTo ErrorHandler
    If b = 0 Then
        Divide = "Error: Division by zero"
        Exit Function
    End If
    Divide = a / b
    Exit Function
ErrorHandler:
    Divide = "Error: Invalid input"
End Function

In this example, if the user tries to divide by zero or enters invalid values, an error message is displayed.

Conclusion

Creating custom functions in VBA for Excel extends the capabilities of Excel and allows you to automate specific calculations. You can use multiple arguments, conditional statements, loops, and even error handling in your functions to make them more robust. These functions can be used in Excel cells just like built-in functions.