Dynamic amount of vars in UDF

Question

I am writing a UDF trying to create a list out of a unsorted "matrix"/array of strings and numerics. I just want to list the non-numerics. So far I have solved all my problems. But now I am struggling at "implementing" a dynamic amount of input vars. I want to be able to mark several separate matrices/arrays.

To explain what happens so far:

The UDF is called "LIST" and it has 3 inputvars: (SearchRange As Range, ExceptionRange As Range, OnlyStrings As Boolean)

SearchRange is the Range I am collecting my data, ExceptionRange is to make exception what val shall be ignored (I had to implement it because otherwise I always get the same string back.) and OnlyStrings is, as the name says, a Boolean to decide if you want to consider numerics or not in the evaluation process.

An example for a Function call:=LIST($C$2:$N$73;$C$75:C75;TRUE)

Here's the Code of my UDF:

Public Function LIST(SearchRange As Range, ExceptionRange As Range, OnlyStrings As Boolean)

    'Assign value to LIST as default val
    LIST = "Nothing found."
    'If an error occurs express the error with its val
    On Error GoTo ERRORHANDLING
    Dim codeoferror As String
    codeoferror = "01"
    '"Consts"
    Dim FstRow As Integer, FstCol As Integer, _
        LstRow As Integer, LstCol As Integer
    'Loop Vars
    Dim CurRow As Integer, CurCol As Integer, i As Integer, j As Integer
    'Initiate Arr
    Dim ExcArr() As String
    ReDim ExcArr(1 To 1) As String
    ExcArr(1) = ""

    'Create Array with all Vals of ExceptionRange
    codeoferror = "02"
    For i = ExceptionRange.Row To (ExceptionRange.Row + _
            ExceptionRange.Rows.Count - 1)
        For j = ExceptionRange.Column To (ExceptionRange.Column + _
                ExceptionRange.Columns.Count - 1)
            ReDim Preserve ExcArr(1 To UBound(ExcArr) + 1)
            ExcArr(UBound(ExcArr)) = Cells(i, j)
        Next j
    Next i

    'Assigning Vals to "Consts"
    codeoferror = "03"
    FstRow = SearchRange.Row
    FstCol = SearchRange.Column
    LstRow = SearchRange.Row + SearchRange.Rows.Count - 1
    LstCol = SearchRange.Column + SearchRange.Columns.Count - 1

    'Going through SearchRange searching for Non-Numerics
    For CurRow = FstRow To LstRow
        For CurCol = FstCol To LstCol
            If IsNumeric(Cells(CurRow, CurCol)) <> OnlyStrings Then
                'Jump to "ISINARRAY" (to replace an additional function)
                GoTo ISITINARRAY
ISINARRAY:
            End If
        Next CurCol
    Next CurRow
    GoTo FUNCTIONEND

    'As a replacement for an additional Func
    codeoferror = "04"
ISITINARRAY:
    For i = LBound(ExcArr) To UBound(ExcArr)
        If ExcArr(i) = Cells(CurRow, CurCol) Then GoTo ISINARRAY
    Next i
    LIST = Cells(CurRow, CurCol)
    GoTo FUNCTIONEND

    'Errorhandling
ERRORHANDLING:
    LIST = "ERROR VBA" & codeoferror

    'End of Function (and its Marker)
FUNCTIONEND:
End Function

I know that the GOTOs are pretty bad. It has to work and so far it works.

So, if I want to consider more than just one array as input for my SearchRange, how can I do that dynamically?

ANSWER:

So, after a break I finally managed to get the UDF I am looking for. The ParamArray was really a huge help. Now I made it neccesary, that the List is in one column. So I was able to replace the ExceptionRange by a new val called "Heading" - which stands for the heading of my list. This leads to the following Function call in a cell:

 =LIST2("benötigt" ;TRUE           ;$C$2:$N$73;$A$2:$A$73)  
'=LIST2(*_Heading_*;*_OnlyStrings_*;*_SearchRange_*      )

Here's my code:

Public Function LIST2(ByVal Heading As String, _
                      ByVal OnlyStrings As Boolean, _
                      ParamArray SearchRange() As Variant)

    'LIST2 only works written in one column, else no functionality

    'Assign value to LIST2 as default val
    LIST2 = "Nothing found."
    'If an error occurs express the error with its val
    On Error GoTo ERRORHANDLING
    Dim codeoferror As String
    codeoferror = "01 - error while initiation"
    '"Consts"
    Dim FstRow As Integer, FstCol As Integer, LstRow As Integer, LstCol As Integer
    'Loop Vars
    Dim CurRow As Integer, CurCol As Integer, i As Integer, j As Integer, k As Integer
    'Var for Testing if array
    Dim ArrayTest As Variant
    'Initiate Arr
    Dim ExcArr() As String
    ReDim ExcArr(1 To 1) As String
    ExcArr(1) = ""
    'Cell the UDF is called from
    Dim CurCell As Variant
    'Dim CurCell As Range
    'Set CurCell = Range(Replace(Application.Caller.Address, "$", ""))
    If TypeName(Application.Caller) = "Range" Then
        Set CurCell = Range(Replace(Application.Caller.Address, "$", ""))
    ElseIf TypeName(Application.Caller) = "String" Then
        Set CurCell = Range(Application.Caller)
    Else
        codeoferror = "00 - unexpected error"
        GoTo ERRORHANDLING
    End If

    'Create Array with all Vals of ExceptionRange
    'ExceptionRange is defined as the Range
    ' between the Heading and the current list-position
    codeoferror = "02 - Heading is missing"
    j = CurCell.Column
    i = CurCell.Row
    Do
        i = i - 1
        If Cells(i, j) <> Heading Then
            ReDim Preserve ExcArr(1 To UBound(ExcArr) + 1)
            ExcArr(UBound(ExcArr)) = Cells(i, j)
        Else
            Exit Do
        End If
    Loop

    'Going through SearchRange searching for Non-Numerics
    For k = LBound(SearchRange, 1) To UBound(SearchRange, 1)
        'Assigning Vals to "Consts"
        codeoferror = "03 - Val assignment error"
        FstRow = SearchRange(k).Row
        FstCol = SearchRange(k).Column
        LstRow = SearchRange(k).Row + SearchRange(k).Rows.Count - 1
        LstCol = SearchRange(k).Column + SearchRange(k).Columns.Count - 1
        codeoferror = "04 - SearchRange error"
        For CurRow = FstRow To LstRow
            For CurCol = FstCol To LstCol
                If IsNumeric(Cells(CurRow, CurCol)) <> OnlyStrings Then
                    'Jump to "ISINARRAY" (to replace an additional function)
                    GoTo ISITINARRAY
ISINARRAY:
                End If
            Next CurCol
        Next CurRow
    Next k
    GoTo FUNCTIONEND

    codeoferror = "05"
ISITINARRAY:
    For i = LBound(ExcArr) To UBound(ExcArr)
        If ExcArr(i) = Cells(CurRow, CurCol) Then GoTo ISINARRAY
    Next i
    LIST2 = Cells(CurRow, CurCol)
    GoTo FUNCTIONEND

    'Errorhandling
ERRORHANDLING:
    LIST2 = "ERROR VBA" & codeoferror

    'End of Function (and its Marker)
FUNCTIONEND:
End Function

for whichever element you want a variable amount of inputs define it as a paramarray. alternatively you could have a parameter as a variant and feed it an array. — Zerk, Nov 21 '17 at 11:09
and how do I do that? I call the UDF from my worksheet and that's the way I want i t to be. In a normal Function-Call I'd simply create an array and pass it to the func, but I don't know how I shall write this in a UDF for my sheet bc in my sheet I can't actively create an array - or can I? — Kajkrow, Nov 21 '17 at 11:18

score 2 · Accepted Answer · answered Nov 21 '17 at 11:23

2

As @Zerk said - you'll need to make use of the ParamArray.

ParamArrays can't be used in combination with optional arguments and it has to be the last argument in the list.

This function takes a number and an array.

Public Function MyUDF(SomeNumber As Long, ParamArray MyArray())

    Dim x As Long
    Dim y As String

    For x = LBound(MyArray) To UBound(MyArray)
        y = y & MyArray(x) & ", "
    Next x

    MyUDF = y & SomeNumber

End Function

You can use it in code as follows:

Sub Test()

    MsgBox MyUDF(12, "a", "b", "c")

End Sub

Or as a worksheet function: =MyUDF(12,"a","b","c")

Further reading:
http://www.tushar-mehta.com/publish_train/xl_vba_cases/1005%20ParamArray.shtml

answered Nov 21 '17 at 11:23

Darren Bartrup-Cook

18,362
1
23
45

Thanks a lot! But what if I want to use 2 ParamArrays in 1 FunctionCall? Since "However, since the ParamArray accommodates an arbitrary number of arguments as defined by the caller, it must be the last of the arguments." should be right (according to your source). – Kajkrow Nov 21 '17 at 11:30
No, forget it. I simply have to create one more UDF to assign the ParamArray val to it and bypass it to the UDF I need it in. I'll try that now at first. – Kajkrow Nov 21 '17 at 11:33
I don't think you can have more than one per function - the code wouldn't know where the first ended and the second started. I guess you could pass arrays to the paramarray: `MyUDF(Array(1, 2), Array(3, 5))` for example. – Darren Bartrup-Cook Nov 21 '17 at 11:42

Dynamic amount of vars in UDF

1 Answers1