VBA - 循环自定义类型

Question

首先我想说的是，我不是一个大的 Visual Basic 人。但是，我必须以 VBA for Excel 的形式使用它。我遇到了困难。我创建了一个自定义类，stores，如下

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

salesData 和随后的 salesDataItem 如下:

  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

我如何填充数据的简短示例

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")

现在要将数据实际写入 Excel 工作表，我需要获取与每个项目关联的列和行。

我试过这个没有成功

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

因为我无法遍历非集合选项。 VBA 处理集合和数组的方式与我习惯的完全不同。我意识到为什么会出现错误，但我不确定在 VBA 中，解决此问题的最佳方法是什么。任何指导将不胜感激。

谢谢!

Answer 1

这是使用 Class 对象的选项。

我创建了两个类模块 cSalesDataItem 和 cStores 。在 cSalesDatItem 中，我为它提供了 col 、 startRow 、 Value 的属性，另外还有 ID ，它是项目名称(例如，“voids”、“dcTrans”等)

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

cStores 类看起来像这样。 Initialize 事件拆分 id 字符串并将每个 id 的新 cSalesDataItem 对象添加到 DailySales 集合中:

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

创建、分配和读取 cStores.dailysales 集合的示例:

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