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
Set GetCollection = coll
Then in the immediate window typing:
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:
Dim a As Collection
Set a = GetCollection()
Then in the immediate window enter the name of the subroutine just created:
You should then see the following output:
When testing functions that return a
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.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.