Dictionary Property in VBA Class

Question

I have been asked to modify an Excel sheet with some arcaic programming. I have decided to rewrite it rather then modify all of the many GOTO statments and static arrays. My background is in C# so it has been a bit of a challenge (note: I am sure the naming convention is bad, I am used to being able to use underscore to define private variables)

I am having trouble inializing an attribute of the type dictionary within a class that I have in a VBA application.

The shortened version of the class looks like this

Private pTerminalCode As String
Private pTerminalName As String
...... other attributes
Private pPayRoll As Dictionary

'Propeties
Public Property Get terminalCode() As String
  terminalCode = pTerminalCode
End Property
Public Property Let terminalCode(Value As String)
  pTerminalCode = Value
End Property
....... more properties
Public Property Get headCount() As Dictionary
  headCount = pHeadCount
End Property
Public Property Let headCount(Value As Dictionary)
  pHeadCount = Value
End Property

When I try to use the following I get the error "Argument not optional" within the Get property of the headCount() attribute.

Private Function PopulateTerminal()
   Dim terminal As clsTerminal
   Set terminal = New clsTerminal

   terminal.terminalCode = "Wil"
   terminal.headCount.Add "Company", 100
 End Function

I assume somewhere I need to inialize the dictionary (i.e. = New Dictionary) however I am strugling with where to place it. In C# I do this in the constructor without issue, not sure what to do here.

Thanks

Answer 1

You can do it in the constructor of the VBA class, like so:-

Public Sub Class_Initialize()
    Set myDictionary = New Dictionary
End Sub

Don't forget to always use the Set keyword when assigning an object reference, e.g.:-

Public Property Get Foo() As Dictionary
    Set Foo = myDictionary
End Sub