Subscript out of range VBA error when setting an array equal to a range

Question

Currently, I have a range of strings that I would like to be able to enter into an array. However, I'm not sure that it's working and every time I try to do anything with the array, I get a subscript out of range error. I tried just doing a Debug.Print to see if values are going into the array or not but that resulted in the same error. Here's what I have so far..

UsedRow = ActiveWorkbook.Sheets(3).UsedRange.Rows.Count
Dim ProjectCounter As Long
Dim ArrRange As Range

'This determines the number of entries in the array (I know this part works)
i = UsedRow
ProjectCounter = 0
Do While Cells(i, 1).FormulaR1C1 <> vbNullString
    ProjectCounter = ProjectCounter + 1
    i = i - 1
Loop

'Array should have dimensions that match the number of projects
Dim ProjectArray() As Variant
ReDim ProjectArray(ProjectCounter - 1)

'Set range for array to cover
Set ArrRange = ActiveWorkbook.Sheets(3).Range("A" & UsedRow - ProjectCounter & ":A" & UsedRow)

'Populate array with projects
ProjectArray = ArrRange

For i = LBound(ProjectArray) To UBound(ProjectArray)
Debug.Print ProjectArray(i)
Next

Is this the right way to set up an array? And if not, what am I doing incorrectly? Thanks.

Maybe try `ReDim ProjectArray(ProjectCounter - 1, 1)`? Ranges map back to two-dimensional VBA arrays. You can also try to remove the `ReDim` statement altogether — Ioannis, Jun 30 '15 at 16:24
I've tried removing the ReDim before and it results in an error. I think because you can't have Dim ProjectArray(someVariable), it has to be a constant. Unfortunetly ReDim ProjectArray(ProjectCounter - 1, 1) did not work either. — Alex Cavanaugh, Jun 30 '15 at 16:30
Strange that it does not work [without `ReDim`](http://www.cpearson.com/excel/ArraysAndRanges.aspx).. I think the length of the array is ProjectCounter (not ProjectCounter - 1) so indeed that wouldn't work either. By the way, removing `ReDim` Would make it `Dim ProjectArray() As Variant`, no need for a constant — Ioannis, Jun 30 '15 at 16:35
You shouldn't need to redim at all, the array will automatically adjust in size to account for the range which you pass into the array. — luke_t, Jun 30 '15 at 16:37

John Coleman · Answer 1 · 2016-01-22T17:28:56.840

I have long considered the way 1-dimensional ranges are copied as 2-dimensional arrays to be one of the most annoying things about VBA. One way to fix the resulting subscript out of range error is to, rather then remembering to include a pointless subscript, first fix the array itself so that if the array is conceptually 1-dimensional then your code can treat it as such. The following sub modifies the sort of array that you get when you assign a range of values to a variant. It takes no action if it is genuinely 2 dimensional:

Sub FixArray(valArray As Variant) 'As Variant
'This sub takes a pseudo 2-dimenional 1-based variant array
'And makes it 1-dimensional

    Dim fixedArray As Variant
    Dim columnVector As Boolean
    Dim i As Long, m As Long, n As Long
    On Error GoTo err_handler

    m = UBound(valArray, 1)
    n = UBound(valArray, 2) 'will throw an error if already 1-dimensional
    If m > 1 And n > 1 Then Exit Sub 'can't be fixed without losing data
    If m > 1 Then
        columnVector = True
    Else
        columnVector = False
        m = n
    End If
    ReDim fixedArray(1 To m)
    For i = 1 To m
        If columnVector Then
            fixedArray(i) = valArray(i, 1)
        Else
            fixedArray(i) = valArray(1, i)
        End If
    Next i
    valArray = fixedArray
err_handler:
    'no action - nothing to fix
End Sub

A test sub (run in debug mode with the locals window open and see how v changes from 2 dimensional to 1 dimensional):

Sub test()
    Dim v As Variant
    v = Range("A1:A3").Value
    FixArray v
    Debug.Print "here" 'convenient breakpoint
End Sub

@loannis - Weird. But -- nice to know. Thanks for pointing it out! — John Coleman, Jun 30 '15 at 19:17
Indeed perhaps nice to use for own consumption, but since it fails with large arrays I wouldn't distribute such code -- would do as you show above. — Ioannis, Jun 30 '15 at 19:40

score 0 · Accepted Answer · answered Jun 30 '15 at 16:43

0

You can read an array into a preset range without redim. Declare the variant without the parentheses.

Dim ProjectArray as Variant
ProjectArray = ArrRange

You get the error because your array has 2 dimensions. You need to

for I = 1 to ubound(ProjectArray)
   debug.print ProjectArray(I,1)
next I

And LBound will always be 1 when you do it this way.

answered Jun 30 '15 at 16:43

Ron Rosenfeld

53,870
7
28
60

Shouldn't my array only have one dimension? What makes you say that it has two? – Alex Cavanaugh Jun 30 '15 at 16:45
@AlexCavanaugh I tried to show you how to fill an array with a range. If you do it the way I demonstrated, it will have two dimensions -- Dimension 1 for the rows, Dimension 2 for the columns. I've not filled an array the way you are trying to, as it seems cumbersome and inefficient. But to check the dimensions of your array doing it your way, examine the array in the locals window. – Ron Rosenfeld Jun 30 '15 at 16:49

Subscript out of range VBA error when setting an array equal to a range

2 Answers2

Linked