VBA Array Subscript out of Range again?

[Dsewardj]

Alright, I was gaining my sanity but now I feel it slipping yet again. I have populated an array in VBA with a range from a worksheet. Now I am trying to loop through the array and populate a listbox but it is throwing a Subscript Out of Range error. Below is my code again:

Private Sub listBoxMasterTemplate_Click()
Dim x As Integer
Dim lColumn As Double
Dim wksName As String
Dim HeaderArray As Variant
'Determine which Item was selected in ListBox
For x = 0 To listBoxMasterTemplate.ListCount - 1
    If listBoxMasterTemplate.Selected(x) = True Then
        wksName = listBoxMasterTemplate.List(x)
    End If
Next x
'If no item was selected
If wksName = "" Then
    MsgBox "Please select a worksheet."
    Exit Sub
End If
'Determine how many columns are in header and populate array
With wbsMasterTemplate.Sheets(wksName)
    lColumn = .Range("IV1").End(xlToLeft).Column
    HeaderArray = .Range(.Cells(1, 1), .Cells(1, lColumn)).Value
End With
'now populate the Header Listbox with the array
For x = 1 To UBound(HeaderArray)
    [B][COLOR=red]MsgBox HeaderArray(x)[/COLOR][/B]
    'frmOpenSheets.listBoxMasterTemplate.AddItem (HeaderArray(x))
Next
End Sub

The error is occurring in the red text. I don't get it?

 

[Richard Schollar]

Because you have array from a worksheet range, you have generated a 2 dimensional array so you need to give the second dimension you are extracting the value from (you have a horizontal array so write like this):

MsgBox HeaderArray(1,x)

 

[mikerickson]

headerArray is pulled from the Value property of a range, it is a two dimensional array

Dim headerArray(1 to RowCount, 1 to ColumnCount)

Even if RowCount or ColumnCount is 1, there are still two dimensions to headerArray

Two ways to approach this
1) use headerArray(1, x) in place of headerArray(x) throughout.
or
2) convert the 2D Range.Value to a 1D array

HeaderArray = .Range(.Cells(1, 1), .Cells(1, lColumn)).Value
HeaderArray = Application.Transpose(Application.Transpose(headerArray))

 

[Dsewardj]

Ah,

I did not realize that an array populated from a worksheet range was two dimensional. That definitely explains some things. Thanks again Richard.

Thanks Mikerickson, I like the idea of declaring the dimensions when declaring the variable. This is giving me some more ideas of what I can do now.

The more I learn the less I seem to know...

 

[mikerickson]

I'm not suggesting that you run that Dim statement. I used it to illustrate what loading arrays from ranges did to the indices.