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.

Common Mistakes With Jekyll Front Matter

As I don’t blog very often whenever I create a new blog post in GitHub Pages (using Jekyll’s template framework) there a couple of times I forget the basics and spend a few minutes trying to figure out why the page didn’t load properly.

So this post today is more of a note to self.

Firstly, for blog posts name the file according to the following convention:

YYYY-MM-DD-BlahBlah.md

Secondly, in the front matter area don’t use another colon in the value of the variable. For example don’t do this:

title: Errors: Why do they happen?

Just use another type of punctuation, like:

title: Errors - Why do they happen?

Lastly, every variable in the front matter must contain a name followed by the colon. You cannot create a one-liner variable like this:

redirect_from "yoo/hoo/im-here"

Must be in this type of format in the case of redirect_from:

redirect_from: - yoo/hoo/im-here

Anyway, these are the pesky little bugs that trip me up all the time and tend to cost me a few minutes to an hour of scratching my head trying to figure out what on earth is happening. Hopefully by documenting them I can squash them quicker!