Populate dynamic multi-dimensional, mult-type arrays array Excel VBA

Question

I am trying to use excel 2010 VBA to populate an array containing three arrays. The first is a string type array and the other two are integer type arrays. The relevant portion of the macro is below.

Option Explicit
Option Base 1
Private Type T_small

    myStr() As String
    y() As Integer
    z() As Integer
End Type

Sub ColorByPoint()

On Error GoTo ErrHandler

Dim I As Integer, SCCount As Integer, PCCount As Integer, CLCount As Integer
Dim N As Integer, M As Integer, K As Integer, P As Integer
Dim x() As String, y() As Integer, z() As Integer
Dim pvtItM  As Variant
Dim xName As String, str As String
Dim xlRowField As Range
Dim PC As ChartObjects
Dim WS As Sheet3
Dim SC As SeriesCollection
Dim MyObj As Object
Dim PvTbl As Object
Dim CelVal As Integer
Dim rng As Variant, lbl As Variant, vlu As Variant
Dim ItemField1 As PivotItem, ItemField2 As PivotItem
Dim ValueField As PivotField
Dim dField As PivotCell
Dim oPi As PivotItem
Dim acolRng As Range
Dim arowRng As Range
Dim myStr() As String
Dim iData() As T_small
Dim xSSN() As String

Set WS = Application.ActiveWorkbook.ActiveSheet

Set MyObj = Worksheets("Pivot1").ChartObjects("MyChart").Chart
Set PvTbl = Worksheets("Pivot1").PivotTables("PivotTable1")
Set rng = PvTbl.PivotFields("SSN").PivotItems
Set lbl = PvTbl.DataFields
M = 1

SCCount = MyObj.SeriesCollection.Count          'Series count
PCCount = PvTbl.TableRange1.Rows.Count          'Rows Count
CLCount = PvTbl.TableRange1.Columns.Count    'Columns Count


Set acolRng = PvTbl.ColumnRange
Set arowRng = PvTbl.RowRange
Worksheets("Pivot1").Activate

 P = PCCount

    ReDim Preserve myStr(P)
    ReDim Preserve y(P)
    ReDim Preserve z(P)
    ReDim Preserve iData(P)
For N = 2 To PCCount
    ReDim Preserve iData((iData(2).myStr(2)), (iData(N).y(N)),(iData(N).z(N)))
Next N


For I = 2 To PvTbl.TableRange1.Rows.Count Step 1
    For K = 2 To PvTbl.TableRange1.Columns.Count Step 1
        M = K
        N = K

        iData(I).myStr(I) = PvTbl.Cells("myStr" & I, "K").Value
        iData(I).y(I) = PvTbl.Cells("I", "M").Value
        iData(I).z(I) = PvTbl.Cells("I", "N").Value
    Next K
Next I

The problem is that the line

ReDim Preserve iData((iData(2).myStr(2)), (iData(N).y(N)), (iData(N).z(N)))

continues to give me a "Run Time error 9 Subscript out of range" error. I've tried everything I can think of to get past this including using "N"'s instead of the "2" indexes throughout, adding and removing parentheses, etc.

What causes the runtime error?

Two things: 1) You should probably ReDim your arrays once before using ReDim Preserve on them, and 2) ReDim Preserve can only change the size of the last dimension. One (or both) of these two is causing your problem. — RBarryYoung
I am having trouble working out your dynamic arrays, but they look a bit over-complex. Is there a reason why you cannot use a variant array redimmed as iData(3,PCCOUNT)? Then you could always use Redim preserve if it needs to get larger. — Charles Williams

Jason Faulkner Jason Faulkner · Accepted Answer · 2015-01-14T19:40:36

The problem is you are accessing the array indexes of your T_small properties. You never define (or change) the bounds of iData(x).myStr; rather you only define the bounds of myStr, which is not part of your iData array.

In other words, the of bounds error comes from trying to access iData(x).myStr(x) because iData(x).myStr has no bounds defined.

This should work:

' Now that the iData bounds have been defined, update the property bounds.
ReDim Preserve iData(N).myStr(myStr(N))
ReDim Preserve iData(N).y(y(N))
ReDim Preserve iData(N).z(z(N))

Note that I am having a bit of difficulty following exactly what your code is trying to accomplish, so the above only addresses the specific error you are getting.

Populate dynamic multi-dimensional, mult-type arrays array Excel VBA

1 Answers