Returning dictionary from function in vba. Error 450

user_7069089 0
Viewed 2047

I wrote a function which create a dictionary and have faced with strange behavior: I can retrieve value by key only inside function. When i try to do it with dictionary returned by function, I receive Run-time error '450' My code is:

Option Explicit
Function year_range_dict() As Object
    Dim d
    Set d = CreateObject("Scripting.Dictionary")
    d.Add "a", "aaa"
    d.Add "b", "bbb"
    d.Add "c", "ccc"
    If d.Exists("c") Then
        MsgBox d("c")
        End If
    Set year_range_dict = d
End Function
Sub DefaultRates()
    MsgBox year_range_dict()("a"), "outside of function"
End Sub

When this code is run, I receive message box with "CCC" and then error message

vba excel dictionary
2 Answers

I'm sorry for such idiotism: I forgot just one thing: Set. This is working code:

Option Explicit
Function year_range_dict() As Object
    Dim d
    Set d = CreateObject("Scripting.Dictionary")
    d.Add "a", "aaa"
    d.Add "b", "bbb"
    d.Add "c", "ccc"
    If d.Exists("c") Then
        MsgBox d("c")
        End If
    Set year_range_dict = d
End Function
Sub DefaultRates()
    Dim d
    Set d=year_range_dict()
    MsgBox d("a")
End Sub
user_70690890

Voila:

Function year_range_dict() As Object

    Dim d

    Set d = CreateObject("Scripting.Dictionary")
    d.Add "a", "aaa"
    d.Add "b", "bbb"
    d.Add "c", "ccc"
    If d.Exists("c") Then
        MsgBox d("c")
    End If
    Set year_range_dict = d

End Function

Sub DefaultRates()

    MsgBox year_range_dict()("a")

End Sub

The problem was that you were giving additional parameter "outside of function". No idea what you were trying to do with it, but year_range_dict()("a") returns the value of the dictionary for the key "a".

If the idea of "outside of function" was that it should be the title of the MsgBox, then it should be like this: MsgBox year_range_dict()("a"), Title:="outside of function".

Press Ctrl+I to see the parameters of the MsgBox:

enter image description here

user_54486260
Related