VBA Array - subscript out of range

Question

I'm hoping to create a VBA array with 5 rows and 2 columns

varData = [{0, 0; 0, 0; 0, 0; 0, 0; 0, 0}]

and change the values according to a case

select Case sth
                Case "1"
                    varData(0, 0) = varData(0, 0) + Cells(I, 1).Value
                Case "2"
                    varData(0, 1) = varData(0, 1) + Cells(I, 1).Value
                Case "3"
                    varData(0, 2) = varData(0, 2) + Cells(I, 1).Value
                Case "4"
                    varData(0, 3) = varData(0, 3) + Cells(I, 1).Value
                Case "5"
                    varData(0, 4) = varData(0, 4) + Cells(I, 1).Value
                Case Else
                    '?
            End Select

I'm getting an error on this line:

varData(0, 2) = varData(0, 2) + Cells(I, 1).Value

saying "subscript out of range"

I also tried changing the array to

varData = [{0, 0, 0, 0, 0; 0, 0, 0, 0, 0}]

Please help me understand how I'm out of range

What if you write a line of code that prints varData(0,2) ? Does that fail? — nicomp
– nicomp, Commented Jul 22, 2019 at 20:09
@nicomp I = 12, the first relevant line of data. When removed, the line with varData(0, 0) is the one with the error — Laura
– Laura, Commented Jul 22, 2019 at 20:15
@nicomp MsgBox (varData(0, 0)) produces the same error: subscript out of range — Laura
– Laura, Commented Jul 22, 2019 at 20:17

Scott Craner · Accepted Answer · 2019-07-22 20:13:20Z

3

You are using a shorthand of Evaluate and as such you are loading the array through the same mechanism as the sheet and as such the base is 1 not 0.

So you want to change the references as such:

       select Case sth
            Case "1"
                varData(1, 1) = varData(0, 0) + Cells(I, 1).Value
            Case "2"
                varData(1, 2) = varData(0, 1) + Cells(I, 1).Value
            Case "3"
                varData(1, 3) = varData(0, 2) + Cells(I, 1).Value
            Case "4"
                varData(1, 4) = varData(0, 3) + Cells(I, 1).Value
            Case "5"
                varData(1, 5) = varData(0, 4) + Cells(I, 1).Value
            Case Else
                '?
        End Select

answered Jul 22, 2019 at 20:13

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

Collectives™ on Stack Overflow

VBA Array - subscript out of range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related