How do I pass an array into an Excel VBA function

Question

I am trying to clean up some of my VBA code by removing redundancies and making more reusable functions. One place I am trying to do this is a function where I take an array of row numbers (e.g. [3, 20, 45]) and a column integer (e.g. 5 meaning E) and turn it into a string: (e.g. "E3, E20, E45").

Original Code

Here is my original VBA code that I am trying to transfer:

Dim customRange As String
customRange = ""
For cc = 0 To UBound(resultArr)
    If cc = UBound(resultArr) Then
        customRange = customRange & "E" & resultArr(cc)
    Else
        customRange = customRange & "E" & resultArr(cc) & ", "
    End If
Next cc

(keep in mind, I wasn't trying to convert the column number to a letter here yet)

First Attempt

And this was working fine. Now that I am trying to clean up my code by creating functions for redundant code, I am getting some errors. I am fairly new to VBA in general so this could be fairly obvious. This was my first attempt:

The Call:

customRange = BuildRangeStr(resultArr, 5)

The Function:

Function BuildRangeStr(ByRef custRowArr As Variant, ByVal custCol As Integer) As String
    Dim aa As Integer
    'convert custCol to column letter
    If custCol <= 26 Then
        column_letter = Chr(64 + custCol)
    Else
        column_letter = Chr(Int((custCol - 1) / 26) + 64) & Chr(((custCol - 1) Mod 26) + 65)
    End If
    
    BuildRangeStr = ""
    'build string
    For aa = 0 To UBound(custRowArr)
        If aa = UBound(custRowArr) Then
            Set BuildRangeStr = BuildRangeStr & "E" & custRowArr(aa)
        Else
            Set BuildRangeStr = BuildRangeStr & "E" & custRowArr(aa) & ", "
        End If
    Next aa
End Function

Second Attempt

With this first attempt I had some errors with passing in the Array. So, I went to creating a Variant of the Array as passing that in for my second attempt:

The Call:

Dim MyVar As Variant
MyVar = resultArr()
customRange = BuildRangeStr(MyVar, 5)

The Function:

Function BuildRangeStr(ByVal custRowArr As Variant, ByVal custCol As Integer) As String
    Dim aa As Integer
    Dim tempStr As String
    'convert custCol to column letter
    If custCol <= 26 Then
        column_letter = Chr(64 + custCol)
    Else
        column_letter = Chr(Int((custCol - 1) / 26) + 64) & Chr(((custCol - 1) Mod 26) + 65)
    End If
    
    tempStr = ""
    'build string
    For aa = 0 To UBound(custRowArr)
        If aa = UBound(custRowArr) Then
            tempStr = tempStr & "E" & custRowArr(aa)
        Else
            tempStr = tempStr & "E" & custRowArr(aa) & ", "
        End If
    Next aa
    Set BuildRangeStr = tempStr
End Function

Current Error

I am now getting an error "Object Required".

Ideal Solution

Ideally, I would like to not have to create a variant for my Array to increase simplicity in my code, but I understand that may be required for best use-case.

Really, all I need is to have my function run the same way the code did before, but without cluttering my code with redundant snippets.

If anyone has any ideas I would really appreciate it!

Answer 1

While not an answer per se to the body of your question the answer to the question line is that it is passed like any other variable. I would question why you are trying to achieve your aim like this though. A quicker way to output a string from an array is using the join function and this can easily be adapted to the purpose you want.

Sub Test()
    Dim arr() As Variant
    arr = Array(2, 5, 10)
    Debug.Print BuildRange(arr, 27)
End Sub

Function BuildRange(ByRef arr() As Variant, Col As Long) As String
    Dim Prefix As String
    Prefix = IIf(Col < 27, Chr(Col + 64), Chr(Int((Col - 1) / 26) + 64) & Chr(((Col - 1) Mod 26) + 65))
    BuildRange = Prefix & Join(arr, ", " & Prefix)
End Function