I'm iterating through a list of possible values that reside in a named range on a worksheet. Since accessing ranges is slow, I wanted an easy way to convert them to an array and came up with the function below that does so. Is there a better way of setting my code up?
Public Function ConvertRangeIntoArray(ByVal convertRange As Range) As String()
Dim temporaryVariant As Variant
Dim is2DArray As Boolean
Select Case True
Case convertRange.Rows.Count = 1
temporaryVariant = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(convertRange))
Case convertRange.columns.Count = 1
temporaryVariant = Application.WorksheetFunction.Transpose(convertRange)
Case Else
temporaryVariant = convertRange
is2DArray = True
End Select
Dim returnValue() As String
Dim rw As Long
Dim cl As Long
If is2DArray Then
ReDim returnValue(LBound(temporaryVariant) To UBound(temporaryVariant), LBound(temporaryVariant, 2) To UBound(temporaryVariant, 2))
For rw = LBound(temporaryVariant) To UBound(temporaryVariant)
For cl = LBound(temporaryVariant, 2) To UBound(temporaryVariant, 2)
returnValue(rw, cl) = CStr(temporaryVariant(rw, cl))
Next
Next
Else
ReDim returnValue(LBound(temporaryVariant) To UBound(temporaryVariant))
For cl = LBound(temporaryVariant) To UBound(temporaryVariant)
returnValue(cl) = CStr(temporaryVariant(cl))
Next
End If
ConvertRangeIntoArray = returnValue
End Function
WorksheetFunction.Transpose
? \$\endgroup\$