excel - Not able to create array from 1 cell range in VBA

Question

I have code that reads ranges and converts them to arrays for processing. It unfortunately fails when the range only has one cell.

To boil down the problem, consider the following ranges (r1, r2) with respectively 1 and 2 cells, that I want to convert to arrays a1 and a2, respectively:

Sub ranges_to_arrays()

    Dim r1 As Range, r2 as Range
    Dim a1() As Variant, a2() as Variant

    Set r2 = Worksheets("test").Range("A1:A2")
    a2 = r2 ' Creates Variant(1 to 2, 1 to 1)

    Set r1 = Worksheets("test").Range("A1")
    a1 = r1 'Fails with a type mismatch

End Sub

How can I ensure that an array will be created even if the range has only one element?

Answer 1

You need to check how many cells there are in your Range you are trying to convert to array, use If r2.Cells.Count > 1 Then.

Code

Option Explicit

Sub ranges_to_arrays()

    Dim r1 As Range, r2 As Range
    Dim a1() As Variant, a2() As Variant

    Set r2 = Worksheets("test").Range("A1:A2")
    If r2.Cells.Count > 1 Then
        a2 = r2 ' Creates Variant(1 to 2, 1 to 1)
    Else
        ReDim a2(0 To r2.Cells.Count - 1) ' redim array size to 1 (only 1 cell in range)
        a2(0) = r2
    End If

    Set r1 = Worksheets("test").Range("A1")
    If r1.Cells.Count > 1 Then
        a1 = r1 'Fails with a type mismatch
    Else
        ReDim a1(0 To r1.Cells.Count - 1) ' redim array size to 1 (only 1 cell in range)
        a1(0) = r1
    End If

End Sub