Info

'The inner array for each outer array expands with each new product For ThisRow = 2 To FinalRow

StoreName = Range("A" & ThisRow).Value ' Checks whether this is the first entry in the outer array If LBound(Stores) = 0 Then ThisStore = 1

ReDim Stores(1 To 1) As Store

Stores(1).Name = StoreName

ReDim Stores(1).Styles(0 To 0) As Style

Else

For ThisStore = LBound(Stores) To UBound(Stores)

If Stores(ThisStore).Name = StoreName Then Exit For Next ThisStore

If ThisStore > UBound(Stores) Then

ReDim Preserve Stores(LBound(Stores) To UBound(Stores) + 1) As Store Stores(ThisStore).Name = StoreName ReDim Stores(ThisStore).Styles(0 To 0) As Style End If End If

With Stores(ThisStore)

If LBound(.Styles) = 0 Then

ReDim .Styles(1 To 1) As Style

Else

ReDim Preserve .Styles(LBound(.Styles) To _ UBound(.Styles) + 1) As Style End If

With .Styles(UBound(.Styles))

.StyleName = Range("B" & ThisRow).Value .Price = Range("C" & ThisRow).Value .UnitsSold = Range("D" & ThisRow).Value .UnitsOnHand = Range("E" & ThisRow).Value End With End With Next ThisRow

' Create a report on a new sheet Sheets.Add

Range("A1:E1").Value = Array("Store Name", "Units Sold", _

"Dollars Sold", "Units On Hand", "Dollars On Hand") CurrRow = 2

For ThisStore = LBound(Stores) To UBound(Stores) With Stores(ThisStore) TotalDollarsSold = 0 TotalUnitsSold = 0 TotalDollarsOnHand = 0 TotalUnitsOnHand = 0 ' Go through the array of product styles within the array 1 of stores to summarize information

For ThisStyle = LBound(.Styles) To UBound(.Styles) With .Styles(ThisStyle)

TotalDollarsSold = TotalDollarsSold + .UnitsSold * .Price TotalUnitsSold = TotalUnitsSold + .UnitsSold

TotalDollarsOnHand = TotalDollarsOnHand + .UnitsOnHand * .Price TotalUnitsOnHand = TotalUnitsOnHand + .UnitsOnHand End With Next ThisStyle

Range("A" & CurrRow & ":E" & CurrRow).Value = Array(.Name, TotalUnitsSold, TotalDollarsSold, TotalUnitsOnHand, TotalDollarsOnHand) End With

CurrRow = CurrRow + 1 Next ThisStore

End Sub

Figure 20.15

UDTs can make a potentially confusing multivariable program easier to write.(Note:The results of the program have been combined with the raw data for convenience.)

0 0

Post a comment