Array Inconsitency

Question

I am having a lot of trouble with some array code here - If I run this:

Sub ArrayRunner()

Dim PipeBArray() As Variant

Dim i As Integer

PipeBArray = ThisWorkbook.Sheets(1).Range("A1:A6").Value

MsgBox Str(UBound(PipeBArray)) & Str(LBound(PipeBArray))

For i = LBound(PipeBArray) To UBound(PipeBArray)
    MsgBox PipeBArray(i)
Next i

MsgBox "Done!"
End Sub

Then I get error 9 - subscript out of range on the line in the for loop - and when I watch the variable 'i' it tells me the value of i is one... so this occurs on the first instance of the for loop.

Can anyone help me see what I am doing wrong here?

Thanks everyone.

Joe

Answer 1

also want to add that

Dim Temp As Variant
Dim Dict As New Dictionary

' fill dictionary here

Temp = Dict.Items

Creates a 1D array. I was just having the opposite to the trouble mentioned above.

regards, Joe

Answer 2

When you set a range to an array like that, VBA automatically makes it a 2-dimensional array. So you need to reference it like this:

MsgBox PipeBArray(i, 1) 

rather than:

MsgBox PipeBArray(i)

I would recommend this link for more information.