VBA UDF for LO Calc that takes several arrays as arguments or produces array

Question

I have trouble finding easy examples how to write simple UDF's (user defined functions) for LO Calc, that uses arrays as arguments or gives arrays as output e.g. array functions.

I need simple example UDF that takes array and gives single number as output, like count() function.

I need simple example where UDF that is array function, that takes two arrays and produces array that is Cartesian product of two arrays.

It would be nice if to have a comment for every step.

score 1 · Accepted Answer · answered Jun 14 '21 at 11:59

The simplest UDF for the first case looks like this:

Function MyCount1(aSourceData As Variant) As Long 
Dim i As Long, j As Long    ' Loop variables for two dimensions of an array '
Dim iResult As Long     ' Temporary variable for counting non-empty values (counter) '
    iResult = 0         ' Init counter '
    For i = LBound(aSourceData,1) To UBound(aSourceData,1)      ' Loop row by row (first dimension) '
        For j = LBound(aSourceData,2) To UBound(aSourceData,2)  ' Loop cell by cell in current row (second dimension) '
            If Not IsEmpty(aSourceData(i,j)) Then iResult = iResult + 1 ' If the cell contains any value (not empty) - increase the counter '
        Next j
    Next i
    MyCount1 = iResult  ' Set counter as result of UDF '
End Function

This will work correctly if you pass a range of cells or an array of values to the function as a parameter (=MYCOUNT1(A2:C10) or =MYCOUNT1({1;2;3;4;5;;7;8}))

However, if you leave the parameter list empty (=MYCOUNT1()), the program will stop with an error

If you specify not a range, but only one cell as a parameter(=MYCOUNT1(B5)), then the program will stop with another error

To avoid such errors, you can use this technique

Function MyCount2(Optional aSourceData As Variant) As Variant
Rem Here param aSourceData is optional, can be skipped
Dim i As Long, j As Long, iResult As Long   ' Same as previous
    On Error GoTo wrongData ' On any error jump to the end of function
    iResult = 0
    For i = LBound(aSourceData,1) To UBound(aSourceData,1)
        For j = LBound(aSourceData,2) To UBound(aSourceData,2)
            If Not IsEmpty(aSourceData(i,j)) Then   ' Skip empty cells `
                If IsNumeric(aSourceData(i,j)) Then iResult = iResult + 1   ' Count numeric values only (skip texts) '
            EndIf 
        Next j
    Next i
    MyCount2 = iResult
    Exit Function 
wrongData:
    MyCount2 = "Wrong param"
End Function

For the function to return either a counter value (number) or message text (string), the function type must be specified As Variant.

This solution is also not good enough. For example, a user may want to process arrays with more than two dimensions, but the program cannot do this. To cover all use cases, the program will have to be greatly complicated.

To return the result of the UDF as an array, form it inside the function in any convenient way and return the result As Variant. Keep in mind that the one-dimensional array will be a string - its values will appear in the table cells from left to right:

Function MyCartesian(Optional aSourceA As Variant, Optional aSourceB As Variant) As Variant
Dim iA As Long, jA As Long  ' Loop by first array
Dim iB As Long, jB As Long  ' Loop by second array
Dim aResult As Variant, iLen As Long    ' Result array and count rows (height of array)
    On Error GoTo wrongData ' On any error jump to the end of function
    iLen = (UBound(aSourceA,1)-LBound(aSourceA,1)+1) * _
        (UBound(aSourceA,2)-LBound(aSourceA,2)+1) * _
        (UBound(aSourceB,1)-LBound(aSourceB,1)+1) * _
        (UBound(aSourceB,2)-LBound(aSourceB,2)+1)
Rem Create result array
    ReDim aResult(1 To iLen, 1 To 2)
    iLen = 0
Rem Loop by each element of first array:
    For iA = LBound(aSourceA,1) To UBound(aSourceA,1)   
        For jA = LBound(aSourceA,2) To UBound(aSourceA,2)
Rem Loop by each element of second array:
            For iB = LBound(aSourceB,1) To UBound(aSourceB,1)
                For jB = LBound(aSourceB,2) To UBound(aSourceB,2)
Rem Fill result array
                    iLen = iLen + 1
                    aResult(iLen, 1) = aSourceA(iA,jA)
                    aResult(iLen, 2) = aSourceB(iB,jB)
                Next jB
            Next iB
        Next jA
    Next iA
    MyCartesian = aResult
    Exit Function 
wrongData:
    MyCartesian = "Wrong param"
End Function

Enter something like =MYCARTESIAN(A2:C10; F2:G6) in a cell, press Ctrl+Shift+Enter and get an array

VBA UDF for LO Calc that takes several arrays as arguments or produces array

1 Answers1