In a program I've been working on, I had a function that returned a string array Function rtnStringArray() As String() The array returned would go on to be passed as an argument in another subroutine Sub useSrtArray(ByRef inArray As Variant)
For context, this program pulls data (strings) from cells in an excel sheet. I had a 2nd subroutine that counted the number of cells which had data for a specific column in order to properly allocate enough space in our array. For my recreated example this sub is irrelevant. Just note the excel document I'm working with has 4 relevant cells; A1 to A4, which contain string values.
Here is a short recreation of the problem:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
useArray (makeArray(4)) '4 because A1 to A4 is 4 cells, this is where I'd call the 3rd sub.
End Sub
Function makeArray(ByVal numRows As Integer) As String()
Dim rtnArray() As String
ReDim rtnArray(numRows - 1) As String
Dim i As Integer, rowCount As Integer, currentContent As String
rowCount = 1
i = 0
currentContent = Worksheets("Sheet1").Cells(rowCount, 1).Value
While currentContent <> ""
rtnArray(i) = currentContent
i = i + 1
rowCount = rowCount + 1
currentContent = Worksheets("Sheet1").Cells(rowCount, 1).Value
Wend
makeArray = rtnArray
End Function
Sub useArray(ByRef inArray() As String)
Dim i As Integer
For i = LBound(inArray) To UBound(inArray)
MsgBox "INDEX: " & i & vbLf & "CONTENT: " & inArray(i)
Next i
End Sub
This doesn't work. When you try to run it, you'll get "Compile Error: Type mismatch: Array or user-defined type expected" at useArray (makeArray(4))
To get this work I had to change the header of the sub to Sub useArray(ByRef inArray As Variant)
Can anyone explain?
makeArray(4)String. Only Variant can hold an array in VBA. This definition is only valid withDimor other type definition context where the content of the array is predefined.Array, which does return aVariant. But there are typed arrays in VBA.IntegertoLong. Then, it might be easier to use the 2D array you get fromRange.Value(called on a multicell range), instead of creating a 1D array.