Converting an Excel range into an array

Question

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

Answer 1

Kudos for explicit Public and the ByVal parameter. I wouldn't make it return a String() array though.

You can't know that every value in the source Range can be converted to a String.

returnValue(cl) = CStr(temporaryVariant(cl))

This instruction will make your function fail if the convertRange contains any Error value. You need to first verify whether IsError(temporaryVariant(cl)) before you can attempt any safe type conversion.

But then, what would you do with a value that can't be converted? Remove it from the results? Return an empty string?

What if the range is all numeric values, that the calling code needs to add up; there's some serious overhead going on with iterating both dimensions of a 2D array to convert everything to a String, and then doing the same thing again to convert strings back to Double for proper calculations.

IMO that's one of the cases where you're better off just returning a Variant. That significantly cuts down the implementation:

Public Function ToArray(ByVal target As Range) As Variant
    Select Case True
        Case target.Rows.Count = 1
            'horizontal 1D range
            ToArray = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(target)).Value
        Case target.columns.Count = 1
            'vertical 1D range
            ToArray = Application.WorksheetFunction.Transpose(target).Value
        Case Else
            '2D array: let Excel to the conversion itself
            ToArray = target.Value
    End Select
End Function

Note I renamed the function to a more straightforward ToArray which lets the argument speak for itself, and renamed convertRange to the more idiomatic (it comes up a lot in worksheet event handlers) target (it's also a noun, which is more appropriate than something that looks like a verb for naming a variable).

The caller now has access to the actual types - with everything it means: if a value's type IsError, then the caller needs to deal with it accordingly. If a value IsNumeric, then the caller can perform arithmetic operations on it without incurring implicit type conversions (aside from unwrapping the value from the Variant). If the value IsDate, then the caller can work with dates without ever having to deal with how VBA might be representing that date value in a String.

And performance-wise, it's now as fast as it's going to get (pretty much instant), regardless of the size of the returned array.

I'm not too fond of Select Case True. Case conditions are still going to be evaluated sequentially until one matches, so at the end of the day it's not much different from a more conventional construct:

Public Function ToArray(ByVal target As Range) As Variant
    If target.Rows.Count = 1 Then
        'horizontal 1D range
        ToArray = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(target.Value))
    ElseIf target.columns.Count = 1 Then
        'vertical 1D range
        ToArray = Application.WorksheetFunction.Transpose(target.Value)
    Else
        '2D array: let Excel to the conversion itself
        ToArray = target.Value
    End If
End Function

...which removes an indentation level, and puts the conditions where we'd normally expect them. We're also explicitly assigning to the .Value of the range, transposed or not, instead of implicitly pulling the ranges' default member.

Answer 2

Take a look at http://www.cpearson.com/excel/ArraysAndRanges.aspx

If you declare your array variable as

Dim temporaryVariant() As Variant

it will automatically pick up the cells as a 2-d array, without needing any funny business to avoid picking up the Range object.

That should make it a lot easier to make your String() later.

You should also cache the LBound() and UBound() results; it's faster (and less code)