This sounds like BASIC, so why doesn't it look familiar?

In this chapter, you will:

• Find out how VBA is different from BASIC

• Understand the parts of VBA “speech”

• Find out that learning VBA is not really hard

• Examine recorded macro code using the VB Editor and Help

• Use debugging tools to figure out recorded code

• Get to know the Object Browser

• Learn seven tips for cleaning up recorded code

As mentioned in Chapter 1, “Unleashing the power of Excel with VBA,” if you have taken a class in a procedural language such as BASIC or COBOL, you might be confused when you look at VBA code. Even though VBA stands for Visual Basic for Applications, it is an object-oriented version of BASIC. Here is a bit of recorded VBA code:

Selection.End(xlDown).Select
Range("A11").Select
ActiveCell.FormulaR1C1 = "Total"
Range("E11").Select
Selection.FormulaR1C1 = _
  "=SUM(R[-9]C:R[-1]C)"
Selection.AutoFill _
  Destination:=Range("E11:G11"), _
  Type:=xlFillDefault

This code likely makes no sense to anyone who knows only procedural languages. Unfortunately, your first introduction to programming in school (assuming that you are more than 40 years old) would have been a procedural language.

Here is a section of code written in the BASIC language:

For x = 0 to 9
  Print Rpt$(" ",x)
  Print "*"
Next x

If you run this code, you get a pyramid of asterisks on your screen:

*
 *
  *
   *
    *
     *
      *
       *
        *
         *

If you have ever been in a procedural programming class, you can probably look at the code and figure out what is going on because procedural languages are more English-like than object-oriented languages. The statement Print "Hello World" follows the verb–object format, which is how you would generally talk. Let’s step away from programming for a second and look at a concrete example.

Understanding the parts of VBA “speech”

If you were going to write code for instructions to play soccer using BASIC, the instruction to kick a ball would look something like this:

"Kick the Ball"

Hey, this is how you talk! It makes sense. You have a verb (kick) and then a noun (ball). The BASIC code in the preceding section has a verb (Print) and a noun (the asterisk, *). Life is good.

Here is the problem: VBA doesn’t work like this. In fact, no object-oriented language works like this. In an object-oriented language, the objects (nouns) are most important, hence the name, “object-oriented.” If you were going to write code for instructions to play soccer with VBA, the basic structure would be as follows:

Ball.Kick

You have a noun (Ball), which comes first. In VBA, this is an object. Then, you have the verb (Kick), which comes next. In VBA, this is a method.

The basic structure of VBA is a bunch of lines of code with this syntax:

Object.Method

Needless to say, this is not English. If you took a romance language in high school, you will remember that those languages use a “noun–adjective” construct. However, no one uses “noun–verb” to tell someone to do something:

Water.Drink
Food.Eat
Girl.Kiss

That is why VBA is confusing to someone who previously took a procedural programming class.

Let’s carry the analogy a bit further. Imagine that you walk onto a grassy field, and there are five balls in front of you: a soccer ball, basketball, baseball, bowling ball, and tennis ball. You want to instruct a kid on your soccer team to “kick the soccer ball.”

If you tell them to kick the ball (or Ball.Kick), you really aren’t sure which one of the five balls they will kick. Maybe they will kick the one closest to them, which could be a problem if they are standing in front of the bowling ball.

For almost any noun or object in VBA, there is a collection of that object. Think about Excel. If you can have one row, you can have a bunch of rows. If you can have one cell, you can have a bunch of cells. If you can have one worksheet, you can have a bunch of worksheets. The only difference between an object and a collection is that you add an s to the name of the object:

Row becomes Rows.

Cell becomes Cells.

Ball becomes Balls.

When you refer to something that is a collection, you have to tell the programming language to which item you are referring. There are a couple of ways to do this. You can refer to an item by using a number. For example, if the soccer ball is the second ball, you might say this:

Balls(2).Kick

This works fine, but it could be a dangerous way to program. For example, it might work on Tuesday. However, if you get to the field on Wednesday and someone has rearranged the balls, Balls(2).Kick might be a painful exercise.

A much safer way to go is to use a name for the object in a collection. You can say the following:

Balls("Soccer").Kick

With this method, you always know that it will be the soccer ball that is being kicked.

So far, so good. You know that a ball will be kicked, and you know that it will be a soccer ball. For most of the verbs or methods in Excel VBA, there are parameters that tell how to do the action. These parameters act as adverbs. You might want the soccer ball to be kicked to the left and with a hard force. In this case, the method would have a number of parameters that tell how the program should perform the method:

Balls("Soccer").Kick Direction:=Left, Force:=Hard

When you are looking at VBA code, the colon–equal sign combination (:=) indicates that you are looking at the parameters of how the verb should be performed.

Sometimes, a method will have a list of 10 parameters, some of which are optional. For example, if the Kick method has an Elevation parameter, you would have this line of code:

Balls("Soccer").Kick Direction:=Left, Force:=Hard, Elevation:=High

Here is the confusing part: Every method has a default order for its parameters. If you are not a conscientious programmer and happen to know the order of the parameters, you can leave off the parameter names. The following code is equivalent to the previous line of code:

Balls("Soccer").Kick Left, Hard, High

This throws a monkey wrench into our understanding. Without :=, it is not obvious that you have parameters. Unless you know the parameter order, you might not understand what is being said. It is pretty easy with Left, Hard, and High, but when you have parameters like the following:

ActiveSheet.Shapes.AddShape Type:=1, Left:=10, Top:=20, Width:=100, Height:=200

It gets confusing if you instead have this:

ActiveSheet.Shapes.AddShape 1, 10, 20, 100, 200

The preceding line is valid code. However, unless you know that the default order of the parameters for this Add method is Type, Left, Top, Width, Height, this code does not make sense. The default order for any particular method is the order of the parameters, as shown in the Help topic for that method.

To make life more confusing, you are allowed to start specifying parameters in their default order without naming them, and then you can switch to naming parameters when you hit one that does not match the default order. If you want to kick the ball to the left and high but do not care about the force (that is, you are willing to accept the default force), the following two statements are equivalent:

Balls("Soccer").Kick Direction:=Left, Elevation:=High
Balls("Soccer").Kick Left, Elevation:=High

However, keep in mind that as soon as you start naming parameters, they have to be named for the remainder of that line of code.

Some methods simply act on their own. To simulate pressing the F9 key, you use this code:

Application.Calculate

Other methods perform an action and create something. For example, you can add a worksheet by using the following:

Worksheets.Add Before:=Worksheets(1)

However, because Worksheets.Add creates a new object, you can assign the results of this method to a variable. In this case, you must surround the parameters with parentheses:

Set MyWorksheet = Worksheets.Add(Before:=Worksheets(1))

One final bit of grammar is necessary: adjectives. Just as adjectives describe a noun, properties describe an object. Because you are an Excel fan, let’s switch from the soccer analogy to an Excel analogy. There is an object to describe the active cell. Fortunately, it has a very intuitive name:

ActiveCell

Suppose you want to change the color of the active cell to red. There is a property called Interior.Color for a cell that uses a complex series of codes. However, you can turn a cell to red by using this code:

ActiveCell.Interior.Color = 255

You can see how this can be confusing. Again, there is the noun-dot-something construct, but this time, it is Object.Property rather than Object.Method. How you tell them apart is quite subtle: There is no colon before the equal sign. A property is almost always set equal to something, or perhaps the value of a property is assigned to something else.

To make this cell color the same as cell A1, you might say this:

ActiveCell.Interior.Color = Range("A1").Interior.Color

Interior.Color is a property. Actually, Interior is a property of the Range object, and Color is a property of the Interior property. By changing the value of a property, you can make things look different. It is kind of bizarre: Change an adjective, and you are actually doing something to the cell. Humans would say, “Color the cell red,” whereas VBA says this:

ActiveCell.Interior.Color = 255

Table 2-1 summarizes the VBA “parts of speech.”

TABLE 2-1 Parts of the VBA programming language

VBA Component	Analogous To	Notes
Object	Noun	Examples include cell or sheet.
Collection	Plural noun	Usually specifies which object: Worksheets(1).
Method	Verb	Appears as Object.Method.
Parameter	Adverb	Lists parameters after the method. Separate the parameter name from its value with :=.
Property	Adjective	You can set a property (for example, ActiveCell.Height=10) or store the value of a property (for example, x = ActiveCell.Height).