hb / Excel Tutorial Create UDF

Last update: 2024-03-19

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.

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:

- The function names must not collide with the names of the predefined functions
- 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
- 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'

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).

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:

- Syntax of the function
- Evaluation of the parameter list
- Performing the search
- Processing of the return value

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.

= 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.

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.

There are several possible structures for parameter lists in VBA.

- Mandatory parameters only

e. g. Function Name (p1 As long, p2 As String) As Double - 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) - 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)

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.

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:

- 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. - arg(1) is an array consisting of n rows and one column.

Again, lkArr has the same dimensions as arg(1). - 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:

Sharing knowledge is the future of mankind