Excel VBA 将数据写入类模块中的字典

Question

我试图将数据保存在类模块中声明的字典中。我在类模块中使用了字典，因为组的数量和关联的数据点在开始时是未知的。下面的代码可以编译，但是模块和类模块中的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

Answer 1

您的主要问题是您将初始化字典对象与向其加载项目混为一谈(每次调用clsIVDATA.Init时，您都在创建一个新的空字典)。

此外，使用 Array(...) 生成的数组是基于 0 的(除非您为模块指定 Option Base 1)，因此您的 For 循环会出错并产生意外的结果。

这是您的代码，经过重构以解决这些问题和其他一些小问题

类代码

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

模块代码

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