Tag Archives: User Defined Function

Understanding VBA Functions and Subroutines in Excel

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.

Excel VBA Function Result

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.

Excel VBA Sub Result

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

User Defined Function in Excel VBA
  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.
User Defined Function Result

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

Custom Average Function in Excel VBA
  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.
Custom Average Function Check

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.

ByRef Result

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

ByVal Result

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.

Excel VBA Macros: Mastering Arrays for Efficient Data Handling

Arrays are the unsung heroes of Excel VBA, offering a powerful way to manage data efficiently. In this guide, we’ll explore the world of arrays, from one-dimensional structures to dynamic arrays and user-defined functions, unlocking their potential for handling data.

One-dimensional Array

Create one-dimensional arrays effortlessly with these steps:

  1. Open the Visual Basic Editor.
  2. Place a command button on your worksheet and add the following code lines:
Dim Films(1 To 5) As String

Films(1) = "Lord of the Rings"
Films(2) = "Speed"
Films(3) = "Star Wars"
Films(4) = "The Godfather"
Films(5) = "Pulp Fiction"

MsgBox Films(4)

Result: When you click the command button, you’ll see “The Godfather” displayed in a message box.

Element of a One-dimensional Array in Excel VBA

Two-dimensional Array

If you’re dealing with two-dimensional data, follow these steps:

  1. Open the Visual Basic Editor.
  2. Place a command button on your worksheet and add the following code lines:
Dim Films(1 To 5, 1 To 2) As String
Dim i As Integer, j As Integer

For i = 1 To 5
    For j = 1 To 2
        Films(i, j) = Cells(i, j).Value
    Next j
Next i

MsgBox Films(4, 2)

Result: Click the command button to display an element from the array.

Element of a Two-dimensional Array in Excel VBA

Dynamic Array

When you need arrays that can change size dynamically, use the ReDim keyword:

  1. Open the Visual Basic Editor.
  2. Place a command button on your worksheet and add the following code lines:
Dim numbers() As Integer, size As Integer, i As Integer

size = WorksheetFunction.CountA(Worksheets(1).Columns(1))
ReDim numbers(size)

For i = 1 To size
    numbers(i) = Cells(i, 1).Value
Next i

MsgBox numbers(size)

Dynamic Array in Excel VBA

Result: Your dynamic array adapts to the size of your data.

Last Element of the Array

Array Function

Use the Array function to initialize an array efficiently:

  1. Open the Visual Basic Editor.
  2. Place a command button on your worksheet and add the following code lines:
Dim departments As Variant

departments = Array("Sales", "Production", "Logistics")

MsgBox departments(0) ' Default array indexing

Result: You’ll see the first element, “Sales,” in a message box.

Array Function Result

User Defined Function

Create a User Defined Function (UDF) to return month names using the Array function:

  1. Open the Visual Basic Editor and insert a module.
  2. Add the following code:
Function MONTHNAMES()
    MONTHNAMES = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
End Function

Result: You can use the function =MONTHNAMES() to get the month names.

Month Names in Excel VBA

Size of an Array

Determine the size of an array in Excel VBA using UBound and LBound:

  1. Open the Visual Basic Editor.
  2. Place a command button on your worksheet and add the following code lines:
Dim Films(1 To 5, 1 To 2) As String, x As Integer, y As Integer

x = UBound(Films, 1) - LBound(Films, 1) + 1
y = UBound(Films, 2) - LBound(Films, 2) + 1

MsgBox "This array consists of " & x * y & " elements"

Size of an Array in Excel VBA

Result: The message box shows the total number of array elements.

Size of an Array Result

Mastering arrays is essential for effective data handling in Excel VBA. Whether you’re working with one-dimensional or two-dimensional data, dynamic arrays, or creating custom functions, arrays are your key to efficient data management.