In Excel VBA, functions and subroutines are essential tools for automating tasks and processing data. Let’s explore the key differences between these two constructs and see how they can be used effectively to streamline your VBA code.

Function

A function in Excel VBA is a versatile tool that performs a task and returns a value. It’s perfect for situations where you need a specific result. Here’s how to create a function:

1. Open the Visual Basic Editor by clicking Insert > Module.
2. Define your function, giving it a name and specifying its arguments and return type. For example:

Function Area(x As Double, y As Double) As Double
    Area = x * y
End Function

1. You can now use this function in your code to calculate values. For example:

Dim z As Double
z = Area(3, 5) + 2
MsgBox z

Result: When you click the command button, it displays the result “17” in a message box.

Subroutine (Sub)

A subroutine in Excel VBA is designed for performing actions without returning a value. Here’s how to create a subroutine:

1. Open the Visual Basic Editor by clicking Insert > Module.
2. Define your subroutine, specifying its arguments. For example:

Sub Area(x As Double, y As Double)
    MsgBox x * y
End Sub

1. You can call this subroutine from your code to perform actions. For example:

Area 3, 5

Result: Click the command button to see a message box displaying the result “15”. Subroutines don’t return values.

User Defined Function (UDF)

User Defined Functions (UDFs) are custom functions you can create in Excel VBA. They are particularly useful when built-in Excel functions aren’t sufficient. Let’s create a UDF called SUMEVENNUMBERS to sum even numbers in a given range.

1. Open the Visual Basic Editor by clicking Insert > Module.
2. Define the UDF, specifying its arguments. For example:

Function SUMEVENNUMBERS(rng As Range) As Double

1. Within the UDF, declare variables and set up a loop to check each cell in the provided range.

Dim cell As Range
For Each cell In rng

1. Calculate the sum of even numbers and the count of even numbers in the range.

    If cell.Value Mod 2 = 0 Then
        SUMEVENNUMBERS = SUMEVENNUMBERS + cell.Value
    End If
Next cell

1. End the UDF.

End Function

1. You can now use this custom function in Excel, just like any other function, to find the sum of even numbers in a range.

Custom Average Function

Let’s create another UDF, a custom average function that calculates the average of values in a range, excluding outliers.

1. Open the Visual Basic Editor by clicking Insert > Module.
2. Define the UDF, specifying its arguments. For example:

Function CUSTOMAVERAGE(rng As Range, lower As Integer, upper As Integer) As Double

1. Declare variables and set up a loop to check each cell in the provided range.

Dim cell As Range, total As Integer, count As Integer
For Each cell In rng

1. Calculate the average by excluding values outside the specified range.

    If cell.Value >= lower And cell.Value <= upper Then
        total = total + cell.Value
        count = count + 1
    End If
Next cell

1. Calculate the custom average and end the UDF.

CUSTOMAVERAGE = total / count
End Function

1. You can use this custom average function to calculate averages, excluding specified outliers.

Volatile Functions

By default, UDFs in Excel VBA are not volatile. They recalculate only when one of their arguments changes. If you need a function to recalculate every time any cell on the worksheet is recalculated, you can make it volatile.

To make a UDF volatile, you can use the “Application.Volatile“ statement within the UDF.

ByRef and ByVal

In Excel VBA, you can pass arguments to a procedure (function or sub) by reference (ByRef) or by value (ByVal). By default, VBA passes arguments by reference.

Here’s an example to illustrate the difference between ByRef and ByVal:

Dim x As Integer
x = 10

MsgBox Triple(x)
MsgBox x

In the module:

Function Triple(ByRef x As Integer) As Integer
    x = x * 3
    Triple = x
End Function

Result: The second “MsgBox" displays a value of 30 because the original value of “x"was changed within the function.

To pass arguments by value, modify the function like this:

Function Triple(ByVal x As Integer) As Integer
    x = x * 3
    Triple = x
End Function

Result: The second “MsgBox" displays a value of 10 because the original value of “x" remains unchanged.

Understanding how to use functions, subroutines, UDFs, and arguments passed by reference or by value is crucial for efficient VBA programming in Excel.