The Cheater’s Code, Pt. II: “Sorry I Asked” Edition

Introduction

I’ll warn you now that this is a long one. I’m trying to stick to my guns on making as few assumptions as possible about what you already know. I’m explaining things in a way that I wish someone had explained them to me when I was learning, and in a way that I hope can get me back up to speed quickly if I ever get code amnesia. So settle in. This is the “Sorry I Asked” Edition, and you’re the “I” in there.

This post picks up where the last one left off. If you didn’t do Part I, you may want to go back so you’ll have the proper context for what I’m talking about here. But the short version is that we recorded a macro, which automatically generated some code for us, and here I explain exactly what each line of that code means. I introduceseveral terms, including subroutine, function, and property. I also make a pretty good joke about the Great Illustrated Classics book series and make a compelling argument that Inspector Gadget is an excellent model for understanding some code structures. You don’t want to miss that.

Scope of Application

The screenshots and code for this post came from Microsoft Word 2013 running on a Windows 8.1 machine. But what I’ll cover applies at least as far back as Word 2007 and Windows XP and the equivalent versions of Word for the Mac. If you don’t have Word, you can get a trial copy from Microsoft’s website.

If I Put “The Secret of Macros” in the Header, it Will Seem More Mysterious

Last time, we recorded the following macro:

Sub singleDouble()
'
' singleDouble Macro
'
'
    ShowVisualBasicEditor = True
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = ". "
        .Replacement.Text = ".  "
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
End Sub

I promised to go over it in excruciating detail, and when it doesn’t require much of me, I keep my promises.

First, I’ll point out that I’ve gained some experience since the first time I saw this. I’m going to do my best to remember what it was like to look at it with fresh, ignorant eyes. But feel free to leave me a question in the comments if I fail to cover something you’re interested in. I’ll also mention that even though I’d written some programs by the time I recorded my first macro, I still didn’t really get it. When I originally looked at this code, I literally googled it line by line–even the stuff that seemed self-explanatory. It’s a fragmented way to go about learning a section of code, but it teaches you how to dig for things. It will also introduce you to new code as you go.

So let’s assume that despite all the wonderful things I had to say about HWPs in my last post, you didn’t run out and find one to practice on. You decided to wait patiently for me to come back and show you what’s what in this macro. You agree with everything I’ve said about Oreos so far, and you’ve decided to trust me on programming, too. (In other words, you have questionable judgment.) Well, good. Because I’m going to treat this macro like it makes absolutely no sense to you at all.

The Language

If no one has told you yet, there are more ways than one to skin a lobster. (I’m pretty sure that’s how that saying goes. Or is it skim a lobster bisque?) There are also more ways than one to program a computer. There are dozens, if not hundreds, of computer languages. The one we’re looking at here is called Visual Basic for Applications, or VBA, and it was developed by Microsoft. It’s one of a set of languages with Visual Basic in the title. This can be confusing. When you get to googling, you’ll find posts about Visual Basic.NET or Visual Basic Script and try to apply what you learn in your VBA macro only to find it doesn’t work. And for a while you’ll blame yourself, being the noob that you are. I’ll go ahead and tell you now that sometimes it’s not you. It’s the language. So as you tinker with macros and try to educate yourself on the topic, make sure you’re looking at the right information.

You might be wondering why there are so many computer languages, and it’s a worthwhile question. The short answer comes down to two things: money and preferences. We’ll save the full discussion for another time. For now, just know that some of the abstract principles we cover will apply practically everywhere, but the diction and syntax you see here may not mesh well with another language you might be interested in learning.

I’ve chosen to start with VBA because it’s where I started, and because it’s a pretty friendly entry-level language. The big downside is that you have to have a copy of a Microsoft application to really put it to any practical use. So it’s not a totally free way to get familiar with coding, and there are free options. We’ll get to a couple of them later. For now, if you’ve come this far, I’ll assume you’re willing to indulge me recording my own coding history while trying to pass on a few pointers.

The Top and the Bottom

We’re going to start at the top, but since programs are like sandwiches, we’re also going to start at the bottom.

Sub singleDouble()

End Sub

Sub is short for subroutine. In the context of the Visual Basic family of languages, a subroutine is just a set of instructions that doesn’t return a value. I’m going to call a quick sidebar here and explain what that means.

Sidebar

I want you to think about three things you might find in a mall: an electronic door that you open by pushing a button, a parking meter, and one of those machines that smooshes a penny. The door doesn’t need anything from you except to push the button. It already knows everything it’s going to do. You just waltz up to it (unless you gallop, saunter, or hobble), push the button, and it does its thing. You see results and get something out of it, but you don’t provide the door with any special information about how fast you’re walking, how tall you are, or whether your expecting it to swing in or out. And the door wouldn’t care if you did. The door also doesn’t give you anything back. It does what it’s going to do regardless of whether you walk through it or not. This is just like one kind of subroutine in VBA. It’s just like our subroutine, actually. We push the button, and it runs. Yeah, when we recorded it, we had to tell it what the Find and Replace values were, but that was just because the code didn’t exist yet. Now it doesn’t need anything from us. It just needs us to call it by pushing the button. The parking meter takes some input: a certain number of coins. Once it has its input, it just does its thing and counts down. You don’t really get anything out of it. You get to not get a ticket, but it’s not like you own the parking space now or you get a special prize if you happen to put in a secret combination of coins. You just determined how long the clock will run by the input you gave it. In VBA, this would also be a subroutine, but one that takes arguments. The machine that smooshes the penny needs some input, usually at least a quarter and a penny, and it gives you something back. The smooshed penny is the return value. In VBA, a program that has a return value is a function. (Ok, you caught me. Technically, the parking meter has a return value: the time display. Shut up. It’s really hard to think of things at the mall that take your money, perform a function, and give nothing back…)

Nope. I said it had to perform a function.

Dummary: No return value, it’s a subroutine; yes return value, it’s a function. Subroutines and functions can both take input as arguments. Don’t get too bogged down in the terminology. I’d been programming on the job for a year before I took my first tech screen, mostly VBA code, and I couldn’t tell the screener what the difference was between a subroutine and a function. If you get it now, you’ll be a step ahead. If you want to stick with “program,” “code,” or “macro,” I won’t scoff at you. Only snotty nerds will, but there aren’t as many of them as you’d think. I’m going to use the official terminology, but replace “subroutine” with “macro” if it’s easier for you. The difference won’t matter today.

If you’re not at the sidebar, are you at the frontbar, the topbar, or the middlebar? Maybe the wunderbar! Anyway, sidebar is over, so we’re at the other bar.

So now we know we’re looking at a subroutine, and that’s why it has the Sub label.

Sub singleDouble()

End Sub

That makes it easy to guess what End Sub means, I hope. That’s where our subroutine ends. We need to point out where it ends for a couple of reasons. First, the computer isn’t as smart as you are. If you get a letter and someone forgets to sign it, you know that a half page of white space means there’s no more letter left. The computer just doesn’t get white space. It’s like an awkward silence that the computer has to get past as quickly as possible. If you don’t tell it where to end the subroutine, it’ll keep looking until it gets to a boundary like the end of the file. In other words, when the computer gets a letter from you that’s not signed, it just keeps reading. Trust me. It’s going to cause a breakdown. Make sure to remember to tell the computer where the code ends.

The other piece is the name of the subroutine, which we gave it when we turned on the recorder: singleDouble. But why are those parentheses there? I’m glad you asked. That’s the coin slot!

Think about the parking meter and the penny-smoosher in the mall. There’s a designated place where you can put the money. You can’t just lay the penny on top or hold it up where the machine can see it…the machine can’t see. And if you try to put a Canadian quarter in there, it won’t let you get away with that. The machine only accepts certain, pre-designated forms of currency. Programs that take input work the same way.

Our subroutine will eventually evolve to use the coin slot, but for now it’s going to stay empty. The important thing to know is that all of your subroutines and functions, whether they take input or not, have to have a coin slot. So as you start to look at different programming languages, you’ll get used to seeing parentheses all over the place. They serve an additional function, as well, but I’m going to back-pocket that for the moment.

(If you find yourself thinking, “I know I’ve heard the term coin slot somewhere else before…” this SNL video featuring Lindsay Lohan and Kristin Wiig might jog your memory.)

Did You Have a Comment?

Most programming langagues–all the ones I have any familiarity with–provide a way to make comments in the code. These are lines that you tell the computer to ignore because they’re for human readers. In VBA, you start a comment with an apostrophe ( ‘ ) <– Hey! That kind of looks like a coin slot!

‘singleDouble Macro

The macro recorder automatically creates some comment lines at the beginning of each macro and fills in the macro name. Many programmers use comments to summarize what a piece of code does. For example, we might describe our subroutine as follows:

‘singleDouble Macro

‘Finds a period followed by a space and replaces it with a period followed by two spaces

The computer will see those apostrophes and just keep moving until it gets to a line that doesn’t start with an apostrophe–you know, like you always skipped every other page of those Great Illustrated Classics books. (That’s right. We know.)

Finally, Let’s Do Something

Now we get to the first line of the code that we can see makes something happen on the screen.

ShowVisualBasicEditor = True

Remember how we got that line in there? While we had the macro recorder running, we hit Alt-F11 to open the code editor.

code-editor

This line is formally called an assignment expression. The first part, ShowVisualBasicEditor, is the name of a property. The name exists within VBA. We didn’t make it up, and Word didn’t make it up. It’s part of the VBA language. Luckily, it’s a pretty easy-to-understand name.

The equals sign doesn’t technically mean “equals” here. It’s an assignment operator. And… veer off!

Some Terms

  • operation — I know you’re thinking of that poor guy with all the exposed bones and organs, but computers were built to do math, and a lot of their jargon comes out of the math world. A mathematical operation is what the rest of us generally refer to as “doing math.” 2 + 2 is an example of a mathematical operation of addition.
  • operator — Remember in elementary school when they taught you about the plus sign and the minus sign? Well, more official terms for those are the addition operator and the subtraction operator.
  • operand — The numbers in 2 + 2 are the operands, or the things that the operator acts on.

In the elementary-school-math context, the equals sign isn’t an operator. It’s just notational, not functional. In other words, the plus sign in “2 + 2 = 4” is actually making something happen. It’s joining two things. But the equals sign isn’t doing anything. It just shows up to tell us that the things on either side of it may look different but are the same.

In the computer programming context, the equals sign almost always is an operator.

To really get what’s happening here, you need to understand that ShowVisualBasicEditor is more like a variable than a number. Consider the following equations:

2 + 2 = 4

A + B = 4

In the first equation, everything is straightforward: we’ve got a couple of twos, and we’re joining them together. In the second equation, things are a little more abstract. We can set A to have any value 0, 1, 2, 3, or 4. We can then solve for B, or vice versa. In a very general way, that’s how ShowVisualBasicEditor is behaving here. There’s no reason you should know this without reading the documentation, but ShowVisualBasicEditor can either be true or false. That’s known as a boolean value. So the difference between “2 + 2 = 4” and “ShowVisualBasicEditor = False” is as follows:

  • When we write “2 + 2 = 4,” we’re not making a decision. It’s not as though sometimes 2 + 2 = 4 and sometimes 2 + 2 = 99. We’re not communicating that “Until I say otherwise, 2 + 2 = 4.” We’re saying, “There’s two ways to look at the number 4.”
  • When we write “ShowVisualBasicEditor = False,” we are making a decision. We’re saying, “ShowVisualBasicEditor can have one of two values: True or False. I don’t care what it was before. I’m assigning it the value of False now.”

That probably overexplains things sufficiently. It should be pretty obvious that if you set ShowVisualBasicEditor to False, the code editor closes. You can try it if you want. Copy that line of code and put it at the bottom of the macro, before End Sub. Change the True to False, save it (Ctrl + S), and then go back and click the button for your macro. The window opens up ever so briefly and then closes again. Blink and you might miss it. Dummary: Equals doesn’t always mean equals. In programming, most of the time something like “NameOfThing = ValueIWant” means “Find the thing this name refers to and assign it the value I want.”

Halfway There!

We’ve only examined one line very closely. Even if that didn’t all sink in yet, you get that we’re telling Word to either show or hide the code editor window by setting the property that shows the editor to either true or false. Well, take another look at our subroutine:

Sub singleDouble()
'
' singleDouble Macro
'
'
    ShowVisualBasicEditor = True
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = ". "
        .Replacement.Text = ".  "
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
End Sub

About half of the code looks almost exactly like the line we just analyzed. So now that you understand the structure of that line, you’ve got a good idea of what all we’re looking at here. Our macro is littered with assignment operations! Once we know what those different properties are, we’ll just be a couple lines from understanding the whole thing.

Before we get to those other properties, let’s make a quick visual connection. Go back to Word and click Ctrl + H to bring up the Find and Replace dialog. In the bottom left corner, there’s a button that says “More>>”.

moreButton

Click it to expand all the Find and Replace options.

f-n-r-all

Look at the options on the left. They’re worded a little differently except for “Match case” and “MatchCase,” but you’ll pick up on the correlation to some of our properties quickly. “Use wildcards” and “.MatchWildcards”, “Sounds like (English)” and “.MatchSoundsLike”, etc. If you don’t know what these properties mean, I’m leaving you in the hands of your favorite search engine. It’s clear that I love a good tangent, but we all have to draw an arbitrary line somewhere. I’m drawing mine here.

I am going to go a little deeper into wildcards in another post, but you’ll still want to do a little extra reading if you want to leverage them to their full extent.

What’s with all the periods?

So parentheses don’t mean the same thing in code as they do in regular writing. The equals sign doesn’t mean “equals.” Why would a period (or a dot) mean what it normally means?

In VBA, the period is a dot, and it’s an operator–it’s a dot operator. Think about our discussion of mathematical jargon earlier, and how that informs what you should expect out of an operator. First, an operator requires operands of some kind. These properties attached to the dots, then, must be the operands.

In some languages, the dot is called the access operator, which is a little more clear. The dots access things. Specifically, they access functions and properties. A visual might help here. Remember Inspector Gadget?

What am I thinking? Of course you do. If Inspector Gadget wanted to use his accessories, he usually uttered his famous catch phrase, “Go, go gadget…” and then the name of the thing. The dot operator is like the “Go, go gadget…” of programming. For example, the Inspector’s helicopter doesn’t exist by itself. It’s a part of the Inspector, as is his screwdriver. So if we wanted to rephrase Gadgetese into VBA, it might look something like this:

InspectorGadget.Copter

  You read the above phrase, “Inspector Gadget dot Copter.” Now look at the following line of code again:

Selection.Find.ClearFormatting

“Selection dot Find dot Clear Formatting.” So Selection has a gadget called Find, which has its own gadget called ClearFormatting.

To go back to some of the terminology we introduced earlier:

  • Find is a Property
  • ClearFormatting is a Subroutine

Selection is something new. It’s an Object. Objects in this context are easy to understand. In our example, Inspector Gadget is the object. His tools like the badge and flashlight are properties of Inspector Gadget. VBA translation:

InspectorGadget.Badge

InspectorGadget.Flashlight

 You should be starting to see the hierarchy. If the color of Gadget’s hair is a property of Gadget, then the color of his flashlight is a property of the flashlight. VBA translation:

InspectorGadget.Flashlight.Color

Both objects and properties can also have actions or work attached to them. For example, a flashlight shines when you push the button–but not until you push the button. In VBA, making the flashlight shine might look like this:

InspectorGadget.Flashlight.Shine

I’m sure you’ve cracked the code by now, but here’s one way to describe what’s going on in Selection.Find.ClearFormatting now that we have some jargon to talk about it:

  • Selection: An object that represents the text currently selected in your document. If you haven’t selected any text, it defaults to the entire document.
  • Find: A property of Selection that holds all the properties related to Word’s Find and Replace functionality.
  • ClearFormatting: A subroutine of Find. This particular sub tells Word to forget about looking at any formatting of the text and just concentrate on the characters themselves. (It’s possible, for instance, to search for the word “bold” only in bold format. The ClearFormatting command makes sure Word doesn’t do that. It sets all the formatting properties to False.)

The next line is also clear now, I hope.

    Selection.Find.Replacement.ClearFormatting

It makes sure we’re not using any formatting in our replacement term. When we replace the old period with a new one, the new one won’t be bold or italicized–though I challenge you to see the difference without some serious magnification.

But periods at the beginning of the line? Are they accessing a mysterious “Nothing” like the villain from The NeverEnding Story?

The next section has potential to be a little confusing, especially if you haven’t followed me thus far. If your head is already swimming, take a break before moving on. Maybe with some Oreos. Go ahead. I’ll wait…

Welcome back! Thanks for not sharing your Oreos, jerk.

At this point, you should understand that the dot or period in VBA is an accessor that calls gadgets (or, in more official programming terms, exposes properties). So what about these lines?

    With Selection.Find
        .Text = ". "
        .Replacement.Text = ".  "
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With

The dots are at the beginning of most of these lines. What are they accessing?

This is probably another spot where you’re already way ahead of me, because it’s in pretty plain English. Since I’m a dummy, I thought that the code was fancy and foreign by default. But we can apply the sandwich principle here again. Look at those first and last lines:

With Selection.Find

End With

Everything in between these two lines gets treated like it has Selection.Find at the start of it. It’s really just shorthand for what would otherwise have to look like this:

    Selection.Find.Text = ". "
    Selection.Find.Replacement.Text = ".  "
    Selection.Find.Forward = True
    Selection.Find.Wrap = wdFindContinue
    Selection.Find.Format = False
    Selection.Find.MatchCase = False
    Selection.Find.MatchWholeWord = False
    Selection.Find.MatchWildcards = False
    Selection.Find.MatchSoundsLike = False
    Selection.Find.MatchAllWordForms = False

You’ll find that programmers don’t like to repeat themselves. There are good reasons for this, and we’ll talk about them as we go. So even though we could just copy and paste Selection.Find for each line, the designer of the VBA language gave us a shortcut that saves us some grief in that respect. It also makes the code just a little more readable, which is also important.

So if you didn’t see it before, now it should be clear. We’re just setting 10 properties of Selection.Find, most importantly the text we want to find, and the text we want to put in place of what we find. As I said before, if you’re not sure what all of these properties are, you can google them. Or you can review Word’s help documentation on the Find and Replace function.

Hello, there, little macro. How do you do?

This leaves us with one more line:

Selection.Find.Execute Replace:=wdReplaceAll

Well, you know what the word execute means. So this is the line that actually sets action in motion. Up until this point, we’ve been telling Word what we want to look for and how we want to look for. Now we’re telling it to get started. Think of that moment in Snow White when the queen is giving orders to the huntsman. While she’s telling him to bring back Snow White’s lungs and liver (according to Grimm), nothing is happening yet.

Hunstman.CollectHerLiver = True

Huntsman.CollectHerLungs = True

At some point, the Queen has to tell the hunstman to go, otherwise he just sits there and listens to her talk.

Huntsman.GoGetHer

Our macro works the same way. It listens carefully as we explain what we want, taking notes.

Selection.Find.Text = “. “

Then when we give it the signal, it gets to work.

Selection.Find.Execute

And you probably already caught that the last piece there has the same effect as hitting the “Replace All” button on the Find and Replace dialog in Word.

Replace:=wdReplaceAll

The structure of this looks a little different from other things we’ve encountered. I’m going to explore that in more depth in another post–partly because this post is already plenty long, and partly because it’s a little out of scope.

A Dummy No Longer… about this, anyway

You know this macro like the back of your hand now, right?

So do you know why it’s not working the way we want? It’s doing exactly what we told it to–finding a period followed by a space, and then replacing it with a period followed by two spaces. But that’s the problem, isn’t it? Let’s look at it another way.

Find: abc

Replace: abcd

If we run that once on the text abc, we’ll get abcd. If we run it again, we’ll get abcdd. Again and we’ll get abcddd.

As soon as the computer finds abc, it recognizes a match and replaces the text. It doesn’t care that the next character is d and that it’s going to put two d‘s in a row, even if we don’t want it to. It’s completely obedient to the instructions we gave it. So in our use case with the period and spaces, we actually don’t want the computer to replace a period followed by one space with a period followed by two spaces. We want it to replace a period followed by a space and then any character that is not another space with a period followed by two spaces. If the period is already followed by two spaces, we want it to keep moving.

How do we do that? We’ll talk about that next!

Love and kisses,

Tyler

Advertisements