Create dictionary in function and use it in a sub

user_18949787 0
Viewed 44

I am trying to create a dictionary in a function that is used in a sub, but I get the error

"User defined type not defined".

I don't know if it's very obvious but I can't see what is the problem.

I've simplified the code to make a simple code:

Public Function create_dic(ByVal var As String) As Dictionary

   my_dic.CompareMode = vbTextCompare

   If var = "test" Then
       my_dic.Add Key:="var 1", Item:=1
   Else
       my_dic.Add Key:="var 2", Item:=2
   End If

   Set create_dic = my_dic
            
End Function
    

Sub prueba()
    Set respuesta = create_dic("test")
End Sub
vba function dictionary
1 Answers

Have you set a reference to the "Microsoft Scripting Runtime"? Dictionary is not part of standard VBA and needs the library. See VBA Dictionary References.

Also when you use my_dic.CompareMode it does not exist yet. Make sure you use Option Explicit and declare all your variables properly. Also you need to create a new dictionary object first before you can use it.

Option Explicit

Public Function create_dic(ByVal var As String) As Dictionary
   Dim my_dic As Dictionary  ' declare variable type
   Set my_dic = New Dictionary  ' create a new dictionary object

   my_dic.CompareMode = vbTextCompare

   If var = "test" Then
       my_dic.Add Key:="var 1", Item:=1
   Else
       my_dic.Add Key:="var 2", Item:=2
   End If

   Set create_dic = my_dic
            
End Function
    

Public Sub prueba()
    Dim respuesta As Dictionary  ' declare variable type
    Set respuesta = create_dic("test")
End Sub

For more about dictionaries this is a good reference: Excel VBA Dictionary – A Complete Guide.

user_32196130
Related