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
– Ioannis, Commented Jun 30, 2015 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
– Alex Cavanaugh, Commented Jun 30, 2015 at 16:30
Strange that it does not work without ReDim.. 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
– Ioannis, Commented Jun 30, 2015 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
– luke_t, Commented Jun 30, 2015 at 16:37

John Coleman · Accepted Answer · 2016-01-22 17:28:56Z

1

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

edited Jan 22, 2016 at 17:28

answered Jun 30, 2015 at 17:16

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Coleman Over a year ago

@loannis - Weird. But -- nice to know. Thanks for pointing it out!

Ioannis Over a year ago

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.

Ron Rosenfeld · Accepted Answer · 2015-06-30 16:43:21Z

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, 2015 at 16:43

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

2 Comments

Alex Cavanaugh Over a year ago

Shouldn't my array only have one dimension? What makes you say that it has two?

Ron Rosenfeld Over a year ago

@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.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related