excel - 将三个变量添加到脚本字典问题VBA

Question

我对 VBA 很陌生(2 个月后)，我正在尝试将三个变量添加到脚本字典中以重新格式化 Excel 表，但遇到了错误。

我尝试通过

添加三个变量

countrydict.Add country, data, time

但是我收到一条错误消息

Run-time error '450':
Wrong number of arguments or invalid property assignment

但是如果我写它就可以了

countrydict.Add country, data 'or 
countrydict.Add country, time




Dim lastrow As Long
Dim iter As Long
Dim diter As Long
Dim countrydict As Object
Dim country As String
Dim data As String
Dim time As String
Dim key As Variant
Dim i As Long

Const StartRow As Byte = 2
lastrow = Range("A" & StartRow).End(xlDown).Row

Set countrydict = CreateObject("Scripting.Dictionary")

Dim diter2 As Long, arr, arr2

With ActiveSheet
For iter = 2 To lastrow
    country = Trim(.Cells(iter, 1).Value) '<<<<<
    data = Trim(.Cells(iter, 2).Value) '<<<<<
    time = Trim(.Cells(iter, 3).Text) '<<<<<
    If countrydict.Exists(country) Then
        If Not InStr(1, countrydict(country), data) > 0 Then

            countrydict(country) = countrydict(country) & _
                                   "|" & data & "/" & time
        End If
    Else
        countrydict.Add country, data, time '<<<<<<<
    End If
Next
    iter = 2
      For Each key In countrydict
    .Cells(iter, 1).Value = key & ":"
    .Cells(iter, 1).Font.Bold = True
    .Cells(iter, 1).Font.ColorIndex = 30
    iter = iter + 1
    arr = Split(countrydict(key), "|")
    For diter = 0 To UBound(arr)
        arr2 = Split(arr(diter), "/")
        .Cells(iter, 1).Value = arr2(0)
        .Cells(iter, 2).Value = arr2(1)
    Next diter
Next key
End With
End Sub

预期结果是以这种格式重新格式化表格

"A"  "B"     "C"
EU  Sales   10:00
EU  Tax     12:00
USA Sales   09:00
USA Tax     10:00

改成这个格式

EU: 
Sales 10:00
Tax   12:00 
USA:
Sales 09:00
Tax   10:00

非常感谢您的帮助。我已经被这个问题困扰好几天了......

Answer 1

另一种可能性是创建一个新类来存储您的数据。将您的数据存储在此类的实例中，然后将此对象传递给您的字典。

通过这种方式，您可以事件扩展类以返回其他内容，例如所有值作为组合字符串等...使用公共(public)属性，您甚至可以设置输入验证等等，但这可能比现在需要。
我将“类”保持在绝对最小值，通常类中的公共(public)变量是不好的，但由于我们只将它用作自定义数据类型，所以这并不重要。

编辑:我对类进行了一些更新以显示更多功能，但我将旧的放在这里作为示例。

标准模块“Module1”:

Option Explicit

Sub fillDict()

    Dim adict As Scripting.Dictionary
    Set adict = New Dictionary

    Dim info As myRegionData
    Dim iter As Long

    For iter = 0 To 10
        Set info = New myRegionData

        info.Region = "someRegion" & iter
        info.data = "someData" & iter
        info.Time = "someTime" & iter

        adict.Add info.Region, info

    Next iter

    Dim returnInfo As myRegionData
    Set returnInfo = adict.Item("someRegion1")

    With returnInfo
        Debug.Print .Region, .data, .Time       'someRegion1   someData1     someTime1
        Debug.Print .fullSentence               'At someTime1 I was in someRegion1 and did someData1
    End With

End Sub

类模块(简单)“myRegionData”:

Option Explicit

Public Region As String
Public data As String
Public Time As String

类模块(扩展)“myRegionData”:

Option Explicit

Private Type TmyRegionData
    'More about this structure:
    'https://rubberduckvba.wordpress.com/2018/04/25/private-this-as-tsomething/
    Region As String
    data As String
    Time As String
End Type
Private this As TmyRegionData


Public Property Get Region() As String
    Region = this.Region
End Property
Public Property Let Region(value As String)
    this.Region = value
End Property

Public Property Get data() As String
    data = this.data
End Property
Public Property Let data(value As String)
    this.data = value
End Property

Public Property Get Time() As String
    Time = this.Time
End Property
Public Property Let Time(value As String)
    this.Time = value
End Property


Public Function getFullSentence() As String
    getFullSentence = "At " & Time & " I was in " & Region & " and did " & data
End Function