Excel-vba: Create a class with a dictionary as a member variable

Question

I want to have a Scripting.Dictionary as a member variable of a class.

I tried the following as suggested by Putting dictionaries into classes

But I get the impression that Mathieu's answer doesn't work for excel vba.

I put the following in a class module.

Option Explicit
Private dict As Scripting.Dictionary

Private Sub Class_Initialize()
    Set dict = New Scripting.Dictionary
End Sub

I get the error "User defined type not defined" for dict.

What went wrong?

Thanks.

Answer 1

You need to add a reference to Microsoft Scripting Runtime, from the VBE, Tools | References, then scroll down and check the option for Microsoft Scripting Runtime:

And then you could even reduce your code as:

Option Explicit
Private dict As New Scripting.Dictionary

Private Sub Class_Initialize()

End Sub

Otherwise you must use late-binding.

Option Explicit
Private dict As Object ' Scripting.Dictionary

Private Sub Class_Initialize()
    Set dict = CreateObject("Scripting.Dictionary") 'New Scripting.Dictionary
End Sub

Note that neither of these options will work on Mac OS.