Figure 20.14

The properties of a UDT are available as they are in a custom object.

Option Expllclt

Public Type Style

StyleNmig jta Etring Price i- Single UnitaSold la Long UnitaOnHand la Long

End Type

PUB11= Type Store Name A3 Stclng Styles () As Style

End Type_

Mm ayStyle As Scyle myStylt.|

üS1 StyieName uS" UnitsOnHand UnitsSo Id

The first UDT consists of properties for each product style.

Option Explicit Public Type Style

StyleName As String Price As Single UnitsSold As Long UnitsOnHand As Long End Type

The second UDT consists of the store name and an array whose type is the first UDT.

Public Type Store Name As String Styles() As Style End Type

After the UDTs are established, the main program is written. Only a variable of the second UDT type, Store, is needed because that type contains the first type, Style (see Figure 20.15). But all the properties of the UDTs are easily available. And with the use of the UDT, the various variables are easy to remember—they're only a "." away:

Sub Main()

Dim FinalRow As Integer, ThisRow As Integer, ThisStore As Integer

Dim CurrRow As Integer, TotalDollarsSold As Integer, TotalUnitsSold As Integer

Dim TotalDollarsOnHand As Integer, TotalUnitsOnHand As Integer

Dim ThisStyle As Integer

Dim StoreName As String

ReDim Stores(0 To 0) As Store 1 The UDT is declared FinalRow = Range("A65536").End(xlUp).Row

' The following For loop fills both arrays. The outer array is filled with the 1 store name and an array consisting of product details. ' To accomplish this, the store name is tracked and when it changes, 'the outer array is expanded.

0 0

Post a comment