Excel VBA writing data to a dictionary in a class module

Question

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.exists statements in the module and class 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 GetRATIO returns 999. Any suggestions?

'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

Your primary issue is you are mixing up Initialising the Dictioary Object with Loading items to it (every time you call clsIVDATA.Init you are creating a new, empty Dictionary).

Also, arrays generated with Array(...) are 0 based (unless you specify Option Base 1 for the module), so your For loop will error and produce unexpected results.

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