VBA - Populating Arrays Inside of Structures

[tpotter12]

he title is basically the google search I've been performing; but I've not found an example yet. I really do hope its possible.

I am utilizing a web service to pull down data to excel.

The web service defines some structures that I iterate through to populate the data, send it down to excel, then I iterate through the variants and define using the web service provided structures.

I would like to then send the same object back to the web service.

The parent structure object looks like this:

[VBA]Public Structure FixtureFillData
Public LadderID As Integer
Public YearNumber As Integer
Public FixtureFillSeasons() As FixtureFillSeason
End Structure[/VBA]

The FixtureFillSeason structure looks like this:

[VBA]Public Structure FixtureFillSeason
Public SeasonChar As String
Public LYDoorCtr As Integer
Public FixtureFillMonth1_Percent As Decimal
Public FixtureFillMonth2_Percent As Decimal
Public FixtureFillMonth3_Percent As Decimal
Public FixtureFillMonth4_Percent As Decimal
Public FixtureFillMonth5_Percent As Decimal
Public FixtureFillMonth6_Percent As Decimal
Public WorksheetSalesPlanNumber As Integer
Public ProjectCtdUnitQuantity As Integer
Public StartRegularOnHandQuantity As Integer
Public StartClearanceOnHandQuantity As Integer
Public StartFinalOnHandQuantity As Integer
Public LastModifiedTimeStamp As DateTime
Public LastModifedID As String
Public ClusterInfo() As ClusterData
End Structure[/VBA]

In this example, I want to create many FixtureFillSeason instances and put them into the parent structure - then send the parent structure back to the web service.


[VBA]Public Sub SaveFixtureFill()
'Save all of the fixture fill data
Dim FFObj As New struct_FixtureFillData
FFObj.LadderID = "12453"
FFObj.YearNumber = "2010"

'Set up the three seasons
Dim FFSeason1 As New struct_FixtureFillSeason
FFSeason1.FixtureFillMonth1_Percent = Range("H9")
FFSeason1.FixtureFillMonth2_Percent = Range("H10")
FFSeason1.FixtureFillMonth3_Percent = Range("H11")
FFSeason1.FixtureFillMonth4_Percent = Range("H12")
FFSeason1.FixtureFillMonth5_Percent = Range("H13")
FFSeason1.FixtureFillMonth6_Percent = Range("H14")
FFSeason1.LastModifedID = "LDDRUSR"
FFSeason1.LastModifiedTimeStamp = DateTime.Now
FFSeason1.LYDoorCtr = 45
FFSeason1.ProjectCtdUnitQuantity = 46
FFSeason1.SeasonChar = "1"
FFSeason1.StartClearanceOnHandQuantity = 2353
FFSeason1.StartFinalOnHandQuantity = 3882
FFSeason1.StartRegularOnHandQuantity = 3885
FFSeason1.WorksheetSalesPlanNumber = 3

FFObj.FixtureFillSeasons(0) = FFSeason1
Dim FIXFILLWS As New clsws_WSLadderPlan
FIXFILLWS.wsm_UpdateFixtureFillData (FFObj)

End Sub[/VBA]

I realize there is some initialization needed - but you hopefully this provides enough data to understand what I am trying to do.

[Bob Phillips]

There is no such thing as a Structure in VBA, so you must be thinking of something else, a Type perhaps?

[tpotter12]

Sorry, I should be saying 'class'. The Microsoft Office Web services toolkit creates classes as representations of the structures in the web service.

I need to send a class back to the web service, containing an array, of other classes.

Is this possible?

[Bob Phillips]

You can easily create an array of class instances (which is what I take your statement to mean), but does that array need to be in a class of its own? If so, you would just create a new class with a variant property, and in its initilaize event, build the array.

[tpotter12]

Ok, Here's what I've done:

The FixtureFillData Class (The Parent Class):

[VBA]Public LadderID As Long
Public YearNumber As Long
Public FixtureFillSeasons As Variant

Private Sub Class_Initialize()
ReDim FixtureFillSeasons(2)
End Sub[/VBA]

The FixtureFillSeason Class (The parent class needs to contain an array of these):

[VBA]Public SeasonChar As String
Public LYDoorCtr As Long
Public FixtureFillMonth1_Percent As Variant
Public FixtureFillMonth2_Percent As Variant
Public FixtureFillMonth3_Percent As Variant
Public FixtureFillMonth4_Percent As Variant
Public FixtureFillMonth5_Percent As Variant
Public FixtureFillMonth6_Percent As Variant
Public WorksheetSalesPlanNumber As Long
Public ProjectCtdUnitQuantity As Long
Public StartRegularOnHandQuantity As Long
Public StartClearanceOnHandQuantity As Long
Public StartFinalOnHandQuantity As Long
Public LastModifiedTimeStamp As Date
Public LastModifedID As String
Public ClusterInfo As Variant

Private Sub Class_Initialize()
ReDim ClusterInfo(10)
End Sub
[/VBA]
I have three layers here, but for the purpose of example, I'm just showing the immediate parent and children.

Here is how I am attempting to populate:

[VBA]'Define a new fixture fill object class
Dim FFObj As New struct_FixtureFillData

FFObj.LadderID = "12453"
FFObj.YearNumber = "2010"

'Set up a season
Dim FFSeason1 As New struct_FixtureFillSeason
FFSeason1.FixtureFillMonth1_Percent = Range("H9")
FFSeason1.FixtureFillMonth2_Percent = Range("H10")
FFSeason1.FixtureFillMonth3_Percent = Range("H11")
FFSeason1.FixtureFillMonth4_Percent = Range("H12")
FFSeason1.FixtureFillMonth5_Percent = Range("H13")
FFSeason1.FixtureFillMonth6_Percent = Range("H14")
FFSeason1.LastModifedID = "LDDRUSR"
FFSeason1.LastModifiedTimeStamp = DateTime.Now
FFSeason1.LYDoorCtr = 45
FFSeason1.ProjectCtdUnitQuantity = 46
FFSeason1.SeasonChar = "1"
FFSeason1.StartClearanceOnHandQuantity = 2353
FFSeason1.StartFinalOnHandQuantity = 3882
FFSeason1.StartRegularOnHandQuantity = 3885
FFSeason1.WorksheetSalesPlanNumber = 3
'Add the Season to the fixturefillclass
Set FFObj.FixtureFillSeasons(0) = FFSeason1

'Return the Fixture fill object class
Dim FIXFILLWS As New clsws_WSLadderPlan
FIXFILLWS.wsm_UpdateFixtureFillData (FFObj)
[/VBA]
When I step through and watch FFSeason1 and FFObj, I can see that the arrays are being instantiated; yet nothing is being added to them?