dictionarys and functions: User-defined type not defined

Question

I have typed the following code in Excel VBA: The function should create a dictionary acording to unique values in a certain column part.

Function CreateDictForFactors(xcord As Integer, ycord As Integer, length As Integer) As Dictionary
Dim Dict As Dictionary
Set Dict = New Dictionary
Dim i As Integer
For i = 0 To length - 1
If Not Dict.Exists(Cells(xcord + i, ycord)) Then
            Dict.Add Cells(xcord + i, ycord), 0
            End If
Next i
Set CreateDictForFactors = Dict
End Function

Sub test2()
Dim dict1 As Dictionary
Set dict1 = CreateDictForFactors(7, 6, 12)

End Sub

I found this code as an excample for dictionaries and functions:

Sub mySub()
    dim myDict as Dictionary
    set myDict = myFunc()
End Sub

Function myFunc() as Dictionary
    dim myDict2 as Dictionary
    set myDict2 = new Dictionary
            'some code that does things and adds to myDict2'
    set myFunc=myDict2
End Function

However when I try to run the makro test2 it gives the error message:

User-defined type not defined

Can anyone tell where I made a mistake? Thank you in advance

Answer 1

G'day,

Did you add "Microsoft Scripting Runtime" as a reference to your project?

Once you've done that, declare dict as a Scripting.Dictionary like this:

Dim dict As Scripting.Dictionary

You can create the object as follows:

Set dict = New Scripting.Dictionary

This is a good website describing the use of a dictionary:

https://excelmacromastery.com/vba-dictionary/

Hope this helps.

Answer 2

You can also late bind the code like this:

Function CreateDictForFactors(xcord As Integer, ycord As Integer, length As Integer) As Dictionary
Dim Dict As Object
Set Dict = CreateObject("Scripting.Dictionary")
Dim i As Integer
For i = 0 To length - 1
If Not Dict.Exists(Cells(xcord + i, ycord)) Then
            Dict.Add Cells(xcord + i, ycord), 0
            End If
Next i
Set CreateDictForFactors = Dict
End Function

Sub test2()
Dim dict1 As Object
Set dict1 = CreateDictForFactors(7, 6, 12)

End Sub

Note that neither method will work on a Mac.