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

try replacing `dctSKEW("APZ4").Init RATIO(I), IV(I), KEY` with `dctSKEW.Item("APZ4").Init RATIO(I), IV(I), KEY` — , Dec 08 '14 at 08:29
@vba4all Thanks, but it didn't work. I think the dictionary item gets assigned in the class module not the module so this change won't make a difference. So I think the problem is with the GetRatio or GetIV functions the dRATIO.Exists(KEY) returns false and therefore 999 is returned — Zeus, Dec 08 '14 at 12:34
dRATIO.Add KEY, RATIO and dIV.Add KEY, IV ... KEY then VALUE. http://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure — Daniel Dušek, Dec 08 '14 at 16:01
@dee. Thanks dee, I corrected the code but I still get the same error. On the first pass the debug statement in the class module is correct, but errors thereafter. Is it possible the dictionary values aren't preserved? — Zeus, Dec 09 '14 at 05:35

chris neilsen · Accepted Answer · 2019-08-26T09:57:11.113

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

Thanks Chris, your code works well - I was out of ideas. Why does the line of code dctSKEW.ADD "APZ4", New clsIVDATA run the Private Sub Class_Initialize() routine? Is the sub a predefined routine in excel vba? — Zeus, Dec 09 '14 at 09:33
When an instance of a class is created (in this case by `New clsIVDATA`) its Initialise method is called automatically. This is a fundimental feature of Classes. — chris neilsen, Dec 09 '14 at 09:37
tks again. Simple question...how to i highlight code in a comment? 4 indents doesn't work for me... — Zeus, Dec 09 '14 at 09:40

Excel VBA writing data to a dictionary in a class module

1 Answers1