I have a sub (Sub1) that declare's a dynamic array as variant, activates a sheet then calls another sub (Sub2) that checks the size of a range in the activated sheet, ReDim's the array in two dimensions (first is range size, second is 2) and populates the array with values in the activated sheet. The first time this works just fine and the array values are correct.
Execution goes back to Sub1 which activates a second sheet and again calls Sub2. Sub2 checks the size of the next range, adds this to the size of the first range (the number is correct) and I think it should ReDim the array (ReDim Preserve) to the new larger first dimension size. However when I step through the code, before ReDim is executed the second time I get error '<= Subscript out of range'.
I can't understand what I'm doing wrong.
Any thoughts are appreciated.
Here's an excerpt of the VBA:
Sub GetNameTkr()
Dim NameTkrArray() As Variant
Dim NumRows As Integer
NumRows = 0
Workbooks("Stock Tickers.xlsm").Activate
Application.Goto (ActiveWorkbook.Sheets("NASDAQ").Range("A1"))
DataIntoArray NameTkrArray, NumRows
Application.Goto (ActiveWorkbook.Sheets("NYSE").Range("A1"))
DataIntoArray NameTkrArray, NumRows
End Sub
Sub DataIntoArray(NameTkrArray As Variant, NumRows)
Dim ArrayRowCounter As Integer
ArrayRowCounter = 1
NumRows = NumRows + Application.WorksheetFunction.CountA(Range("A:A"))
NumRows = NumRows - 1
ReDim Preserve NameTkrArray(NumRows, 2) As Variant 'THIS IS WHERE IT FAILS
Do Until ActiveCell.Offset(ArrayRowCounter, 0) = ""
NameTkrArray(ArrayRowCounter, 1) = ActiveCell.Offset(ArrayRowCounter, 1).Value 'puts corp name in array(x,1)
NameTkrArray(ArrayRowCounter, 2) = ActiveCell.Offset(ArrayRowCounter, 0).Value 'puts corp tkr in array(x,2)
ArrayRowCounter = ArrayRowCounter + 1
Loop
End Sub
Execution goes back to Sub1 which activates a second sheet and again calls Sub2. Sub2 checks the size of the next range, adds this to the size of the first range (the number is correct) and I think it should ReDim the array (ReDim Preserve) to the new larger first dimension size. However when I step through the code, before ReDim is executed the second time I get error '<= Subscript out of range'.
I can't understand what I'm doing wrong.
Any thoughts are appreciated.
Here's an excerpt of the VBA:
Sub GetNameTkr()
Dim NameTkrArray() As Variant
Dim NumRows As Integer
NumRows = 0
Workbooks("Stock Tickers.xlsm").Activate
Application.Goto (ActiveWorkbook.Sheets("NASDAQ").Range("A1"))
DataIntoArray NameTkrArray, NumRows
Application.Goto (ActiveWorkbook.Sheets("NYSE").Range("A1"))
DataIntoArray NameTkrArray, NumRows
End Sub
Sub DataIntoArray(NameTkrArray As Variant, NumRows)
Dim ArrayRowCounter As Integer
ArrayRowCounter = 1
NumRows = NumRows + Application.WorksheetFunction.CountA(Range("A:A"))
NumRows = NumRows - 1
ReDim Preserve NameTkrArray(NumRows, 2) As Variant 'THIS IS WHERE IT FAILS
Do Until ActiveCell.Offset(ArrayRowCounter, 0) = ""
NameTkrArray(ArrayRowCounter, 1) = ActiveCell.Offset(ArrayRowCounter, 1).Value 'puts corp name in array(x,1)
NameTkrArray(ArrayRowCounter, 2) = ActiveCell.Offset(ArrayRowCounter, 0).Value 'puts corp tkr in array(x,2)
ArrayRowCounter = ArrayRowCounter + 1
Loop
End Sub