Excel VBA 将数据写入类模块中的字典
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27353056/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA writing data to a dictionary in a class module
提问by Zeus
I am trying to save data in a dictionary declared in a class module. I have used a dictionary in a class module because the number of groups and associated data points are unknown at the start. The code below compiles, but the dRATIO.existsstatements in the module andclass module both return false (however on the first pass the debug statement in the class module gives the correct value, errors thereafter), and then Function GetRATIOreturns 999. Any suggestions?
我正在尝试将数据保存在类模块中声明的字典中。我在类模块中使用了字典，因为组的数量和关联的数据点在开始时是未知的。下面的代码可以编译，但是dRATIO.exists模块和类模块中的语句都返回false（但是在第一次通过时，类模块中的调试语句给出了正确的值，之后出现错误），然后Function GetRATIO返回999。有什么建议吗？
'CODE IN A CLASS MODULE CALLED clsIVDATA
Option Explicit
Public dRATIO
Public dIV
'
Sub Init(RATIO As Variant, IV As Variant, KEY As String)
'Dim I As Long
Dim VAL As String
Dim RowKeys
Dim COLKEYS
Set dRATIO = CreateObject("Scripting.Dictionary")
Set dIV = CreateObject("Scripting.Dictionary")
dRATIO.ADD ITEM:=RATIO, KEY:=KEY
dIV.ADD ITEM:=RATIO, KEY:=KEY
Debug.Print dRATIO.Exists("1")
Debug.Print dRATIO.ITEM("1")
End Sub
Function GetRATIO(KEY As String)
If dRATIO.Exists(KEY) Then
GetRATIO = dRATIO(KEY)
Else
GetRATIO = 999 'or raise an error...
End If
End Function
Function NO_VALUES()
NO_VALUES = dRATIO.COUNT
End Function
Function GetIV(KEY As String)
If dIV.Exists(KEY) Then
GetIV = dIV(KEY)
Else
GetIV = 999 'or raise an error...
End If
End Function
'=====================================================
'CODE IN A NORMAL MODULE
Sub tstclass()
Dim RATIO() As Variant
Dim IV() As Variant
Dim I As Integer
Dim dctSKEW As Object
Set dctSKEW = CreateObject("Scripting.Dictionary")
dctSKEW.ADD "APZ4", New clsIVDATA
RATIO = Array(0.879, 0.843, 0.802, 0.756, 0.658)
IV = Array(0.165, 0.156, 0.145, 0.136, 0.125)
For I = 1 To 5
KEY = CStr(I)
dctSKEW("APZ4").Init RATIO(I), IV(I), KEY
Next I
Debug.Print dctSKEW("APZ4").GetRATIO("1")
Debug.Print dctSKEW("APZ4").GetRATIO("2")
Debug.Print dctSKEW("APZ4").NO_VALUES
End Sub
回答by chris neilsen
Your primary issue is you are mixing up Initialising the Dictioary Object with Loading items to it (every time you call clsIVDATA.Inityou are creating a new, empty Dictionary).
您的主要问题是您将初始化字典对象与加载项目混在一起（每次您打电话时，clsIVDATA.Init您都在创建一个新的空字典）。
Also, arrays generated with Array(...)are 0 based (unless you specify Option Base 1for the module), so your Forloop will error and produce unexpected results.
此外，生成的数组Array(...)是基于 0 的（除非您Option Base 1为模块指定），因此您的For循环将出错并产生意外结果。
Here's your code, refactored to address these and a few other minor issues
这是您的代码，经过重构以解决这些问题和其他一些小问题
Class Code
班级代码
Option Explicit
'CODE IN A CLASS MODULE CALLED clsIVDATA
Private dRATIO As Object
Private dIV As Object
'
Private Sub Class_Initialize()
Set dRATIO = CreateObject("Scripting.Dictionary")
Set dIV = CreateObject("Scripting.Dictionary")
End Sub
Sub Init(RATIO As Variant, IV As Variant, KEY As String)
dRATIO.Add Item:=RATIO, KEY:=KEY
dIV.Add Item:=RATIO, KEY:=KEY
End Sub
Function GetRATIO(KEY As String)
If dRATIO.Exists(KEY) Then
GetRATIO = dRATIO(KEY)
Else
GetRATIO = 999 'or raise an error...
End If
End Function
Function NO_VALUES()
NO_VALUES = dRATIO.Count
End Function
Function GetIV(KEY As String)
If dIV.Exists(KEY) Then
GetIV = dIV(KEY)
Else
GetIV = 999 'or raise an error...
End If
End Function
Module Code
模块代码
Option Explicit
'=====================================================
'CODE IN A NORMAL MODULE
Sub tstclass()
Dim RATIO() As Variant, KEY As String
Dim IV() As Variant
Dim I As Long
Dim c As clsIVDATA
Dim dctSKEW As Object
Set dctSKEW = CreateObject("Scripting.Dictionary")
dctSKEW.Add "APZ4", New clsIVDATA
Set c = dctSKEW.Item("APZ4")
RATIO = Array(0.879, 0.843, 0.802, 0.756, 0.658)
IV = Array(0.165, 0.156, 0.145, 0.136, 0.125)
For I = 0 To 4
KEY = CStr(I + 1)
c.Init RATIO(I), IV(I), KEY
Next I
Debug.Print dctSKEW("APZ4").GetRATIO("1")
Debug.Print dctSKEW("APZ4").GetRATIO("2")
Debug.Print dctSKEW("APZ4").NO_VALUES
End Sub
