June 2016 – > script everything

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.