Modify an Arbitrary Number of Variables Within a Subroutine

Question

So I have an application where I'm looking up data from a bunch of different tables and entering the data into a bunch of variables to be used elsewhere. I wrote a function to look up and return a row (as a Range) from a specified table given an entry in the first column:

Public Function RowLookup(table As Range, entry As String) As Variant
    Dim rowNum As Variant
    rowNum = Application.Match(entry, table.Columns.item(1), 0)

    If IsError(rowNum) Then
        RowLookup = CVErr(xlErrValue)
    Else
        RowLookup = table.Rows(rowNum)(1)
    End If
End Function

However, now I've ended up with a ton of code that looks like this:

tempRow = RowLookup(Range("Table1"), var1)
If IsError(tempRow) Then
    var2 = ""
    var3 = ""
    var4 = ""
    var5 = ""
    Debug.Print "Error looking up data"
Else
    var2 = tempRow(1, 2)
    var3 = tempRow(1, 3)
    var4 = tempRow(1, 4)
    var5 = tempRow(1, 6)
End If

Any ideas on how to work this boilerplate code into the function? I want it to take an arbitrarily long list of (colIndex, variable) pairs and then set each variable based on columnIndex. Using a Collection doesn't seem to work as I can only change the entry in the Collection, not the original variable.

https://stackoverflow.com/questions/28326429/vba-pass-array-by-reference-and-modify-contents might meet your needs though - pass in two arrays, one for column indexes and another with the corresponding variables you want to populate/clear. Have your function return a Boolean to indicate match/no match. — Tim Williams, Feb 08 '20 at 23:12
Was trying something similar myself. Problem is that creating my own array and modifying that only changes the array, not the original variables. Solution by below seems to work, because I guess ParamArray creates an array of variables by reference? Very confusing. — RTKurek, Feb 09 '20 at 18:53

chris neilsen · Accepted Answer · 2020-02-09T00:28:35.020

You could pass the Vars and Table Index's as a ParamArray

I'd rework RowLookup as a Sub. Param Arrayexpects pairs of Variables, and Table Column Numbers.

An Odd number of passed parameters cauase an error.
A Column Index > number of columns in Table is ignored
Vars don't have to be Variants (they will be type cast, so could throw errors)

Public Sub RowLookup(table As Range, entry As Variant, ParamArray Vars() As Variant)
    Dim rowNum As Variant
    Dim i As Long
    Dim TableData As Variant
    Dim TableColumns As Long

    If WorksheetFunction.IsEven(UBound(Vars)) Then
        For i = 0 To UBound(Vars) - 1 Step 2
            Vars(i) = CVErr(xlErrNA)
        Next
    Else
        rowNum = Application.Match(entry, table.Columns(1), 0)

        If IsError(rowNum) Then
            For i = 0 To UBound(Vars) - 1 Step 2
                Vars(i) = CVErr(xlErrValue)
            Next
        Else
            TableData = table.Value
            TableColumns = UBound(TableData, 2)
            For i = 0 To UBound(Vars) - 1 Step 2
                If Vars(i + 1) >= 1 And Vars(i + 1) <= TableColumns Then
                    Vars(i) = TableData(rowNum, Vars(i + 1))
                End If
            Next
        End If
    End If
End Sub

and call it like this

Sub Demo()
    Dim SomeLongVar As Long
    Dim SomeStringVar As String
    Dim var3(1 To 2) As Variant
    Dim var4 As Variant
    Dim var5 As Variant

    var3(2) = 5 ' Column Index

    RowLookup Range("Table1"), "x", SomeLongVar, 2, SomeStringVar, 3, var3(1), var3(2)

    Debug.Print SomeLongVar, SomeStringVar, var3(1)
End Sub

Modify an Arbitrary Number of Variables Within a Subroutine

1 Answers1