Array loop with 1 entry

Question

My array comes from a range which can have a variable number of entries.

Dim myArray as Variant, elem as Variant 

myArray = hSheet.Range("A1:A" & hSheet.Cells(Rows.Count, 1).End(xlUp).Row)

I'm using this array to loop through it's values and do something.

For Each elem In myArray
    Magic
Next elem

This works pretty well when my array has more than 1 value (range has more than 1 entry). However, when there's only 1 value/entry it gives a "Type Mismatch" error.

I need this to be flexible and work with multiple values, but also with 1 value only. If you have other suggestions beside an array I'm all ears.

user_3219613 · Answer

Because you Dim myArray as Variant the variable myArray can be a also a simple value (not an array). So if there is only on cell like

myArray = Range("A1")

then it is no array but a value and therefore has no elements to loop through.

If you use the VarType function to Debug.Print VarType(myArray) you will see it:

After myArray = Range("A1") it returns eg
VarType = 8 (vbString) or 5 (vbDouble) which means it is a value
After myArray = Range("A1:A5") it returns eg
VarType = 8204 = 8192 + 12 (vbArray + vbVariant)

So you need to distinguish both types and do different things with them:

If VarType(myArray) >= 8192 Then 'is array
    Debug.Print Join(myArray, ";")  'print whole array
Else
    Debug.Print myArray  'print single value
End If

Second solution

Note that with Dim myArray() as Variant you can force the variable to always be an array. But then putting a simple value (single cell) myArray = Range("A1") will fail with a type mismatch error.

So you need to fill the variable myArray differently:

Dim myArray() as Variant 'forced to be an array!

Dim MyRange As Range
Set MyRange = hSheet.Range("A1:A" & hSheet.Cells(Rows.Count, 1).End(xlUp).Row)

If MyRange.Cells.Count > 1 Then 'is array
    myArray = MyRange.Value
Else
    'create an array with only one item
    ReDim myArray(1 To 1, 1 To 1) As Variant
    myArray(1, 1) = MyRange.Value
End If

'now you can always loop even if only one cell was in the range
For Each elem In myArray
    Magic
Next elem

user_380384 · Answer

The statement myArray = hSheet.Range("A1:A" & hSheet.Cells(Rows.Count, 1).End(xlUp).Row) is equivalent to (IMO cleaner)

Dim numRows as Long
numRows = hSheet.Cells(Rows.Count, 1).End(xlUp).Row
Dim myArray as Variant
myArray = hSheet.Range("A1").Resize(numRows,1).Value2

which returns a nice 2d array when numRows>1. The result is the same shape as

ReDim myArray(1 to numRows, 1 to 1)

But when numRows=1 the result is just a value. Not a 2D array, not a 1D array, but a single value. In order to take care of this case, modify the code above as follows:

Dim numRows as Long
numRows = hSheet.Cells(Rows.Count, 1).End(xlUp).Row
Dim myArray as Variant
if numRows>1 Then
    myArray = hSheet.Range("A1").Resize(numRows,1).Value2
Else
    ReDim myArray(1 to 1, 1 to 1)
    myArray(1,1) = hSheet.Range("A1").Resize(numRows,1).Value2
End If

From this point on, myArray is guaranteed to contain a 2D array.

user_7177346 · Answer

In the OP code the nested loops fail when the range evaluates to a single cell. In this manner Excel is performing according to its specification. That doesn't stop the error from being annoying.

In my previous comments I make the observation that Excel does not behave orthogonally in that if a range evaluates to a single row or column you actually get a 2d array i.e one of the dimensions is scoped as 1 to 1.

The code below attempts to reconcile the above observations by allowing a range to return a 2D array even if the range evaluates to a single cell. Additionally, the optional parameter allows for a value to be returned that represents the true size of the range, a single cell returns a single value, a single row or column returns a 1 dimensional array etc.

It should be noted that when the optional variable is False, and a single dimensioned array is returned, you lose the orientation of the original range.

In the test below, the nested loop runs correctly even though the range specifies a single cell.

Public Sub ttest()

    Dim myVar As Variant
    myVar = GetRange(ThisWorkbook.Sheets.Item(1).Range("A1:A1"))
    
    Dim myRow As Long
    For myRow = LBound(myVar, 1) To UBound(myVar, 1)
    
        Dim myCOl As Long
        For myCOl = LBound(myVar, 2) To UBound(myVar, 2)
        
            Debug.Print "Row "; myRow, "Col "; myCOl, " is ", myVar(myRow, myCOl)
            
        Next
        
    Next

End Sub

Public Function GetRange(ByVal ipRange As Excel.Range, Optional ByVal RangeAsTable As Boolean = True) As Variant
    
    If RangeAsTable Then
    
        GetRange = GetRangeAsTable(ipRange)
        
    Else
    
        GetRange = GetRangeAsSized(ipRange)
        
    End If
    
End Function
    
    
Private Function GetRangeAsTable(ByVal ipRange As Excel.Range) As Variant

    Dim myArray As Variant
    
    If ipRange Is Nothing Then
    
        ReDim myArray(1 To 1, 1 To 1)
        GetRangeAsTable = myArray
        Exit Function
        
    End If
    
    Dim xlArray As Variant
    xlArray = ipRange.Value
    
    If Not IsArray(xlArray) Then
    
        ReDim myArray(1 To 1, 1 To 1)
        myArray(1, 1) = xlArray
        GetRangeAsTable = myArray
        Exit Function
        
    End If
    
    GetRangeAsTable = xlArray
    
End Function

Private Function GetRangeAsSized(ByVal ipRange As Excel.Range) As Variant

    Dim xlArray As Variant
    xlArray = ipRange.Value
    
    If Not IsArray(xlArray) Then
    
        GetRangeAsSized = xlArray
        Exit Function
        
    End If
    
    If UBound(xlArray, 1) = 1 Or UBound(xlArray, 2) = 1 Then
    
        GetRangeAsSized = Application.WorksheetFunction.Transpose(xlArray)
        Exit Function
        
    End If
    
    GetRangeAsSized = xlArray

End Function

Probably not the neatest or most efficient code but at least it demonstrates the point, and, more importantly, allows the OP to use a nested loop even if the range is a single cell.

Array loop with 1 entry

3 Answers

Second solution