Programming Dots

Talking about my post a couple of minutes ago.

I worked out the problem with the code. And also reminded myself why I hate most programming languages.

The problem with the code was one silly little ..

The thing with VBA is that it uses an interesting syntax. The average bit of code in VBA looks like this:

Sheets("Welcome").Select

The most important thing to note is the little .. In VBA, it represents something like a derivative of the selector before it — a more fine definition, or a property etc. While this is fine with a simple line like this, VBA also allows a very useful way of repeatedly do something to one specific object, using the WITH function:

WITH Sheets("Welcome")
.Cells(3,3).Copy Destination:= .Cells(5,2)
.Select
END WITH

This is great, except for one small catch. You can’t place the “.” at the end of the WITH function — it must come before any property within the WITH function that needs it in front. This is to prevent things like flawed programming — without this rule, VBA won’t know when the range is referring to the WITH or another property elsewhere.

These are the two bits of code, before and after.

BeforeAfter

My two bits of code. Try searching for the difference.

If you look really closely, you’ll see that the Range("C3:C25") has changed into a .Range("C3:C25"). While this is the smallest of changes I’ve ever made to code, it’s the entire reason why the programming wasn’t working the way it should. You’d think that VBA was smart enough to be able to realise I was always referring to that page, but instead it thinks I’m referring to the current sheet, ignoring the WITH command completely, and copied that data.

Simply because I missed the . when I was programming.