arrray code in Excel keeps generating error-CodePudding

Where am I going wrong below? Code keeps throwing Subscript out of range error:

Sub test()

Dim arr As Variant    
arr = Range("a1", "a6").Value  
a = LBound(arr)
b = UBound(arr)

For i = a To b
    MsgBox arr(i)
Next
    
End Sub

CodePudding user response：

Arrays pulled from ranges in excel are always two-dimensional which is a pain (compared to say google sheets appscripts where it's just arrays within arrays). A fabulous resource of Excel Arrays is from the late Chip Pearson who posted this site years ago.

Anyway, as Scott said... you just have to do two nested level...

Sub test()
Dim arr As Variant, i As Long, j As Long
arr = Range("A1", "A6").Value
    
    For i = LBound(arr) To UBound(arr)
        For j = LBound(arr, 2) To UBound(arr, 2)
        
            MsgBox arr(i, j)
            
        Next j
    Next i
    
End Sub