VBA - Looping over Custom Types

Question

first off I would like to preface by saying, I am not a big Visual Basic person. However, I do have to work with it in the form of VBA for Excel. I have encountered a difficulty. I created a custom class, stores, as follows

Public Type stores
number As Integer
file As String
dailySales As salesData
End Type

salesData and subsequently salesDataItem are as follows:

  Private Type salesData
custCount As salesDataItem
nightDeposit As salesDataItem
dayDeposit As salesDataItem
inSales As salesDataItem
driveSales As salesDataItem
driveCust As salesDataItem
tax As salesDataItem
overShort As salesDataItem
refunds As salesDataItem
voids As salesDataItem
breakfastSales As salesDataItem
breakfastCustomers As salesDataItem

creditCardTotal As salesDataItem
dcTrans As salesDataItem
dcTotal As salesDataItem
mcTrans As salesDataItem
mcTotal As salesDataItem
visaTrans As salesDataItem
visaTotal As salesDataItem
aeTrans As salesDataItem
aeTotal As salesDataItem
mcDebit As salesDataItem
mcDebitCount As salesDataItem
visaDebit As salesDataItem
visaDebitCount As salesDataItem

gcRedeemed As salesDataItem
gcSold As salesDataItem
End Type

Private Type salesDataItem
col As String
startRow As Integer
value As Double
End Type

A brief example of how I populate the data

Public Function getSalesData(store As stores) As stores
Dim sourceFile As String
Dim salesRange As String
Dim pluRange As String

sourceFile = store.file
Workbooks.Open (sourceFile)

store.dailySales.dayDeposit.col = "C"
store.dailySales.nightDeposit.col = "D"
store.dailySales.dayDeposit.startRow = SALES_START_ROW
store.dailySales.nightDeposit.startRow = SALES_START_ROW
store.dailySales.dayDeposit.value = sumSelective("amount", getRange("deposits"), 11, "1")
store.dailySales.nightDeposit.value = sumSelective("amount", getRange("deposits"), 11, "2")

Now to actually write the data to an Excel sheet, I need to obtain the columns and rows associated with each item.

I tried this with no success

Dim item As salesDataItem
For Each item In store.dailySales
Range(item.col, item.startRow + day).value = item.value
Next item

because I cannot iterate over a non-collection option. VBA handles collections and arrays quite differently then I am accustomed to. I realize why I am getting the error, but I am not sure, in VBA, what the best approach would be to get around this. Any guidance would be very much appreciated.

Thanks!

Answer 1

Try Class module 'clsStores' with

Public number As Integer
Public file As String
Public dailySales As New Collection

and Class module 'clsSalesDataItem' with

Public col As String
Public startRow As Integer
Public value As Double

and your code like this:

Dim SDI As New clsSalesDataItem, store As New clsStores
SDI.col = "C"
SDI.startRow = SALES_START_ROW
SDI.value = sumSelective("amount", getRange("deposits"), 11, "1")
store.dailySales.Add SDI, "dayDeposit"

for a start and come back when you get problems.

Answer 2

Here's the option to use Class objects.

I created two class modules cSalesDataItem and cStores. In the cSalesDatItem I give it the properties for col, startRow, Value and additionally ID which is the item name (e.g., "voids", "dcTrans", etc.)

Option Explicit
Private pID As String
Private pCol As String
Private pStartRow As Long
Private pValue As Double

Public Property Get ID() As String
    ID = pID
End Property
Public Property Let ID(lID As String)
    pID = lID
End Property
Public Property Get col() As String
    col = pCol
End Property
Public Property Let col(lcol As String)
    pCol = lcol
End Property
Public Property Get StartRow() As Long
    StartRow = pStartRow
End Property
Public Property Let StartRow(lStartRow As Long)
    pStartRow = lStartRow
End Property
Public Property Get Value() As Double
    Value = pValue
End Property
Public Property Let Value(lValue As Double)
    pValue = lValue
End Property

The cStores class looks like this. The Initialize event splits the string of ids and adds a new cSalesDataItem object for each id to the DailySales collection:

Option Explicit
Private pNumber As Integer
Private pFile As String
Private pDailySales As Collection

Private Const ids As String = "custCount,nightDeposit,dayDeposit,inSales,driveSales,driveCust,tax,overShort,refunds,voids," & _
    "breakfastSales,breakfastCustomers,creditCardTotal,dcTrans,dcTotal,mcTrans,mcTotal,visaTrans," & _
    "visaTotal,aeTrans,aeTotal,mcDebit,mcDebitCount,visaDebit,visaDebitCount,gcRedeemed,gcSold"


Private Sub Class_Initialize()
    Set pDailySales = New Collection

    Dim itm
    Dim sdItem As cSalesDataItem
    For Each itm In Split(ids, ",")
        Set sdItem = New cSalesDataItem
        sdItem.ID = itm
        pDailySales.Add sdItem, itm
    Next
End Sub
Public Property Get number() As Integer
    number = pNumber
End Property
Public Property Let number(lNumber As Integer)
    pNumber = lNumber
End Property
Public Property Get file() As String
    number = pNumber
End Property
Public Property Let file(lNumber As String)
    pFile = lfile
End Property
Public Property Get dailysales() As Collection
    Set dailysales = pDailySales
End Property
Public Property Let dailysales(lDailySales As Collection)
    Set pDailySales = lDailySales
End Property

Example of creating, assigning to & reading from the cStores.dailysales collection:

Sub example()

Dim store As cStores

'## Initialize a new cStores object
Set store = New cStores

Dim sdItem

'Assign the values
store.dailysales("custCount").Value = 259
store.dailysales("custCount").StartRow = 1
store.dailysales("custCount").col = 9
store.file = "c:\myfile.txt"
store.number = "123456"

'Read the values:
Debug.Print store.dailysales("custCount").Value
Debug.Print store.dailysales("custCount").StartRow
Debug.Print store.dailysales("custCount").col

'Iteration over the cStores object's .DailySales collection:
'Read the values
For Each sdItem In store.dailysales
    Debug.Print sdItem.ID
    Debug.Print sdItem.col
    Debug.Print sdItem.StartRow
    Debug.Print sdItem.Value
Next


End Sub