How To Fix “Invalid Property Assignment (Error 450)”

If you are testing the return value of a function in the immediate window in VBA and get the following error:

Wrong number of arguments or invalid property assignment (Error 450)

What you are doing is something like this:

Function GetCollection() As Collection
    Dim coll As New Collection
    coll.Add "Hello"
    coll.Add "World"
    Set GetCollection = coll
End Function

Then in the immediate window typing:

? GetCollection()

The code appears to work fine, but the error is a mystery. The reason for the error is that the immediate window call expects a collection, but isn’t receiving one.

Therefore, to properly test your functions returning a collection data type create a test subroutine that calls the function and receives the collection returned. Such as:

Sub Test()
    Dim a As Collection
    Set a = GetCollection()
    Debug.Print a.Item(1)
End Sub

Then in the immediate window enter the name of the subroutine just created:

Test

You should then see the following output:

Test
Hello

Conclusion

When testing functions that return a Collection data type instead of using the immediate window defer instead to calling them from a test subroutine. Then output the desired response to visually check your code works, or instead of printing to the immediate window, use Debug.Assert like this:

' Debug.Print a.Item(1)
Debug.Assert a.Item(1) = "Hello"

If there’s a problem with your function when the subroutine is ran in the immediate window it will break at the failed assertion test.

Either way, learning this has helped brush up my rusty Excel VBA skills as I jump back into Excel VBA 2016.