What’s in a colon?

A few times this week I’ve seen instances of people tearing their hair out over code not doing what was expected, due to one missing piece of punctuation: the humble colon.

In each case, the issue was that a named argument was being passed to a method – or at least, that was the intention, but the colon was missing. Consider this example:
Activesheet.Protect password = "foobar"
At first glance, if you’re in a rush or you wrote the code and therefore see what you think is there, this looks fine and you’d expect your sheet to be protected (which it is) with a password that’s “foobar” (which it is not).

The problem, in case you haven’t spotted it, is that the code should read:
Activesheet.Protect password:="foobar"
Note the colon before the = sign.
In the original version, what happens is that the variable ‘password’ is tested for equality with the value “foobar”, which will return either True or False. This returned value is then used as the value of the first parameter to the Protect method (which does happen to be the Password parameter) and the sheet is protected accordingly with either “True” or “False” as the password!

Of course, if you are a wise programmer and have Option Explicit set, this will probably never happen to you because you will get a run-time error telling you that the variable ‘password’ has not been declared (unless of course you happen to have already used that as a variable name…)

For the record, the other instance of this problem I saw was:
Activeworkbook.Close savechanges = False
I leave you to determine what actually happened. 😉


2 thoughts on “What’s in a colon?

  1. Pingback: Password issues on Workbook Open Event

  2. Pingback: VBA password protect, input box

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.