Excel Tutorial:
Create UDF
using the XLookup2 function as an example

Foreword

In Excel there are predefined functions such as SUM, IF, XLOOKUP that can be used in cell formulas. In addition, it is possible to add your own functions with special tasks, the so-called UDFs (User Defined Functions).

One of the ways to make this happen is to program them using the VBA (Visual Basic for Applications) programming language. This tutorial is about what needs to be considered and what difficulties you have to overcome.

Experience in using VBA is required.

Hint:
A UDF that can be used very profitably when creating tournament schedules is the GROUP_TABLE function.
▸More

This tutorial refers to the UDF named 'XLookup2'. An Excel file with the full VBA code of this UDF (and others) can be downloaded here: ▸Download Excel file

This website only shows an excerpt of the tutorial. The full tutorial can be viewed and downloaded as a (free) PDF - see ▸below.

Introduction

In principle, any function programmed in a module that is not declared as 'Private' can be used as part of a formula within a cell of the worksheet - just like the predefined functions. However, in order for them to return results and not just return the #VALUE error code, some restrictions must be observed.

Restrictions:

1. The function names must not collide with the names of the predefined functions
2. The functions may only calculate and return values; they must not contain any actions such as changes in cell content or changes in cell formatting or other properties of the Excel objects
3. Arrays of a specified data type can neither be passed as a parameter nor returned as a function result; for this you have to use the data type 'Variant'

Small introductory example:  The first Monday of the year

A UDF that takes a year as a parameter and returns the date of the first Monday of the New Year might look like this:

Function FirstMonday(Year As Long) As Date
    Dim NewYearsDay As Date
    Dim dayNr As Long
    NewYearsDay = DateSerial(Year, 1, 1)
    dayNr = NewYearsDay Mod 7     'Saturday = 0
    If dayNr < 3 Then
        FirstMonday = NewYearsDay + 2 - dayNr
    Else
        FirstMonday = NewYearsDay + 9 - dayNr
    End If
End Function

Such a function can be called within the VBA code from other functions or procedures, and it can also be used in a formula on the worksheet, e. g. =FirstMonday($B$4).

Focus of the tutorial:  The function XLOOKUP2

This tutorial focuses on creating a more complex UDF. It's a function called XLOOKUP2. It expands the already diverse possibilities of the Excel function XLOOKUP.

It provides the following three abilities in addition to XLOOKUP:

• Search for multiple search criteria
• Use of wildcards within the search criteria
• Return all matches, not just the first one (optional)

The considerations of the XLOOKUP2 function are divided into four chapters:

1. Syntax of the function
2. Evaluation of the parameter list
3. Performing the search
4. Processing of the return value

1.  Syntax of the function

The first six parameters of the XLOOKUP2 function are the same as those of the XLOOKUP function. After the sixth parameter, further pairs consisting of a search criterion and a search array can follow.

Syntax

= XLookup2 ( lookup_value1, lookup_array1, return_array, [if_not_found],
   [match_mode], [search_mode],
   [lookup_value2], [lookup_array2], [lookup_value3], [lookup_array3], ... )

Parameter	Explanation
lookup_value1	1st search criterion
lookup_array1	Array or range in which the 1st search criterion is searched (one-dimensional - single column or single row)
return_array	Array or range from which found values are returned
if_not_found (optional)	Text returned in place of the #N/A error code if nothing was found
match_mode (optional)	Match type:  0:  Exact match search (default)  1:  Invalid (generates the error #VALUE) -1:  Invalid (generates the error #VALUE)  2:  Search with wildcard symbols, the wildcards *, ?, ~ can be used for the search
search_mode (optional)	Search mode:  1:  Normal search order starting with the first item (default),  the first match found is returned -1:  Reverse search order starting with the last item,  the first match found is returned  2:  Normal search order, all matches are returned -2:  Reverse search order, all matches are returned
lookup_value2 (optional)	2nd search criterion
lookup_array2 (optional)	Array or range in which the 2nd search criterion is searched (one-dimensional - single column or single row)
lookup_value3 (optional)	3rd search criterion
lookup_array3 (optional)	Array or range in which the 3rd search criterion is searched (one-dimensional - single column or single row)
etc.	. . .

Search criteria that are not specified (just a comma) or contain the empty string have no effect on the search.

2.  Evaluation of the parameter list

The first step is to convert the parameter list of the cell function XLOOKUP2 into a parameter list of the VBA function XLookup2. The second step then consists of reading the parameter values into corresponding VBA variables.

2.0  Parameter list in VBA

There are several possible structures for parameter lists in VBA.

1. Mandatory parameters only
   e. g.   Function Name (p1 As long, p2 As String) As Double
2. Mandatory parameters and a fixed number of optional parameters
   e. g.   Function Name (p1 As long, p2 As String, Optional p3 As long = 1, Optional p4 As long = 0)
3. Mandatory parameters and a indefinite number of optional parameters
   e. g.   Function Name (p1 As long, p2 As String, ParamArray arg() As Variant) As String

In all three cases, the number of mandatory parameters can also be zero, so that one can also speak of six different cases.

A mixture of individual optional parameters and the ParamArray is not possible. The ParamArray must be of type Variant.

Since the first parameter can already be omitted in the XLOOKUP2 function, analogous to the XLOOKUP function, we are only left with case 6: the parameter list consists only of the ParamArray.

The function has the following header in VBA:
Function XLookup2(ParamArray arg() As Variant)
The return type is not specified, so it is Variant. It has to be Variant because this function returns different data types: ranges, arrays or single values - depending on the situation.

The first instruction is:
argCnt = UBound(arg)

The number of parameters is stored in the variable argCnt. Since the ParamArray is zero-based, the number 4 is stored here if 5 parameters were specified.

At least 3 parameters must be specified. Therefore, the next line of code ensures that an error code is returned if there are fewer than 3 parameters:
If argCnt < 2 Then EXIT_BY_ERROR

The small auxiliary procedure EXIT_BY_ERROR has the advantage that it can be used in different UDFs. It intentionally generates an error so that further execution of the function is aborted and the error code #VALUE is returned.

Private Sub EXIT_BY_ERROR()
    Dim errorArr() As Long
    errorArr(0) = 1   'exit by #VALUE error
End Sub

Since the Redim statement is missing here, the error 'Index out of range' is generated. Because the XLookup2 function was called as a cell function, no error message appears, but the cell function responds with the error code #VALUE.

The number of search criteria results from the number of parameters. It is determined with the following code:

    If argCnt < 6 Then
        critCnt = 1
    Else
        critCnt = (argCnt - 4) \ 2 + 1   'Explanation of the calculation term in Chap. 2.7
    End If

The different pairs consisting of search criterion and search array should be stored in the two arrays critArr and lookArr. They are therefore dimensioned accordingly at this point:

    ReDim critArr(1 To critCnt)
    ReDim lookArr(1 To critCnt)

2.1  Evaluation of the 1st parameter

Reading the first parameter is easy:

    If IsMissing(arg(0)) Then
        critArr(1) = ""
    Else
        critArr(1) = arg(0)
    End If

The IsMissing function can be used to check whether a parameter was specified or whether only a comma was set. In the second case, the empty string is entered as the first criterion. Criteria consisting of the empty string have no effect on the search.

The various search criteria are stored in the array critArr. Therefore, this first parameter is stored in the critArr(1) variable.

2.2  Evaluation of the 2nd parameter

The second parameter must be a range (object of type RANGE) or an array. This check does the statement
If Not IsArray(arg(1)) Then EXIT_BY_ERROR

The IsArray function returns FALSE even if the address of a single cell or an array that consists of only one element is given as a parameter.

The next statement copies the values of the arg(1) variable to the lkArr variable:
lkArr = arg(1)

This is a critical point in that we must note that arg(1) can be either a range or an array. Of course, a user will usually specify a range as a parameter and only in special cases an array. However, there are still cases where formulas are used for this parameter that either return ranges – such as OFFSET – or often arrays of values.

The variable lkArr is initially an uninitialized variable of type Variant. There are now three cases to consider in the copying process mentioned above:

1. arg(1) is a range.
   In this case, lkArr becomes an array with the same dimensions as arg(1), ie a two-dimensional array with 1 row and n columns or with n rows and 1 column.
2. arg(1) is an array consisting of n rows and one column.
   Again, lkArr has the same dimensions as arg(1).
3. arg(1) is an array consisting of one row and n columns.
   This is the critical case because the lkArr variable now only has one dimension and querying the 2nd dimension with UBound(lkArr, 2) leads to a runtime error.

I don't know why a single-column array has two dimensions while a single-row array has only one dimension. Seems like a Microsoft bug to me.

Since this circumstance has to be taken into account in several places, the small procedure repairArray is used as a workaround.

Private Sub repairArray(ByRef arr As Variant)
    Dim res As Variant
    Dim uBnd As Long
    Dim maxCol As Long
    Dim col As Long
    
    If Not IsObject(arr) Then      'arr is an array, not a range
On Error GoTo UBoundError          'Workaround for a single row array
        uBnd = UBound(arr, 2)
        GoTo GoOn                  'no error
UBoundError:
        maxCol = UBound(arr, 1)
        ReDim res(1 To 1, 1 To maxCol)
        For col = 1 To maxCol
            res(1, col) = arr(col)
        Next
        arr = res
        Resume GoOn
GoOn:
On Error GoTo 0
    End If
End Sub

It checks whether the relevant parameter is the critical 3rd case. If at the instruction
uBnd = UBound(arr, 2)
an error is thrown, the error is caught and the array is converted into a two-dimensional array (1 row and n columns).

Now the dimensions can be determined:

    maxrowLook = UBound(lkArr, 1)
    maxcolLook = UBound(lkArr, 2)

If it is not a one-dimensional array, the XLOOKUP2 function returns the error code:

    If maxrowLook > 1 And maxcolLook > 1 Then EXIT_BY_ERROR

The variable byRow stores whether it is a horizontal search in the row (byRow = TRUE) or vertically in the column (byRow = FALSE):

    byRow = (maxcolLook > 1)

The variable maxInd contains the number of rows or the number of columns - depending on whether the search is horizontal or vertical:

    If byRow Then
        maxInd = maxcolLook
    Else
        maxInd = maxrowLook
    End If

For each search criterion there is an associated search array in the parameter list. Just as the search criteria are stored in an array called critArr, the search arrays are stored in an array called lookArr. The variable lookArr is thus an array of arrays.

In the (rare) case of a horizontal search, the search array is converted from a single-row array to a single-column array (1✕n matrix to n✕1 matrix conversion). This is done by the following code section:

    If byRow Then
        ReDim arr(1 To maxInd, 1 To 1)
        For ind = 1 To maxInd
            arr(ind, 1) = lkArr(1, ind)
        Next
        lookArr(1) = arr
    Else
        lookArr(1) = lkArr
    End If

In this way, the search criteria and search arrays are transferred to the actual search routines in a uniform format.

. . .

 

So far the first insight into the tutorial.
The full tutorial can be viewed or downloaded here: