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

Examining recorded macro code: Using the VB Editor and Help

Let’s take a look at the code you recorded in Chapter 1 to see whether it makes more sense now that you know about objects, properties, and methods. You can also see whether it’s possible to correct the errors created by the macro recorder.

Figure 2-2 shows the first code that Excel recorded in the example from Chapter 1.

FIGURE 2.2 Here is the recorded code from the example in Chapter 1.

Now that you understand the concept of Noun.Verb or Object.Method, consider the first line of code that reads Workbooks.OpenText. In this case, Workbooks is a collection object, and OpenText is a method. Click the word OpenText and press F1 for an explanation of the OpenText method (see Figure 2-3).

FIGURE 2.3 This shows part of the Help topic for the OpenText method.

The Help file confirms that OpenText is a method, or an action word. The default order for all the arguments that can be used with OpenText appears in a Parameters table. Notice that only one argument is required: Filename. All the other arguments are listed as optional.

Optional parameters

The Help file can tell you what happens if you skip an optional parameter. For StartRow, the Help file indicates that the default value is 1. If you leave out the StartRow parameter, Excel starts importing at row 1. This is fairly safe.

Now look at the Help file note about Origin. If this argument is omitted, you inherit whatever value was used for Origin the last time someone used this feature in Excel on this computer. That is a recipe for disaster. For example, your code might work 98 percent of the time. However, immediately after someone imports an Arabic file, Excel remembers the setting for Arabic and thereafter assumes that this is what your macro wants if you don’t explicitly code this parameter.

Defined constants

Look at the Help file entry for DataType in Figure 2-3, which says it can be one of these constants: xlDelimited or xlFixedWidth. The Help file says these are the valid xlTextParsingType constants that are predefined in Excel VBA. In the VB Editor, press Ctrl+G to bring up the Immediate window. In the Immediate window, type this line and press Enter:

Print xlFixedWidth

The answer appears in the Immediate window. xlFixedWidth is the equivalent of saying 2 (see Figure 2-4). In the Immediate window, type Print xlDelimited, which is really the same as typing 1. Microsoft correctly assumes that it is easier for someone to read code that uses the somewhat English-like xlDelimited term than to read 1.

FIGURE 2.4 In the Immediate window of the VB Editor, you can query to see the true value of constants such as xlFixedWidth.

If you were an evil programmer, you could certainly memorize all these constants and write code using the numeric equivalents of the constants. However, the programming gods (and the next person who has to look at your code) will curse you for doing so.

In most cases, the Help file either specifically calls out the valid values of the constants or offers a hyperlink that opens the Help topic, showing the complete enumeration and the valid values for the constants (see Figure 2-5).

FIGURE 2.5 Click the hyperlink to see all the possible constant values. Here, the 10 possible xlColumnDataType constants are revealed in a new Help topic.

If you read the Help topic on OpenText, you can surmise that it is basically the equivalent of opening a file using the Text Import Wizard. In step 1 of the wizard, you normally choose either Delimited or Fixed Width. You also specify the file origin and at which row to start. This first step of the wizard is handled by these parameters of the OpenText method:

Origin:=437
StartRow:=1
DataType:=xlDelimited

Step 2 of the Text Import Wizard enables you to specify that your fields be delimited by commas. Because you do not want to treat two commas as a single comma, the Treat Consecutive Delimiters As One checkbox should not be selected. Sometimes, a field may contain a comma, such as “XYZ, Inc.” In this case, the field should have quotes around the value, as specified in the Text Qualifier box. This second step of the wizard is handled by the following parameters of the OpenText method:

TextQualifier:=xlDoubleQuote
ConsecutiveDelimiter:=False
Tab:=False
Semicolon:=False
Comma:=True
Space:=False
Other:=False

Step 3 of the wizard is where you actually identify the field types. In this case, you leave all fields as General except for the first field, which is marked as a date in MDY (Month, Day, Year) format. This is represented in code by the FieldInfo parameter.

The third step of the Text Import Wizard is fairly complex. The entire FieldInfo parameter of the OpenText method duplicates the choices made in this step of the wizard. If you happen to click the Advanced button on the third step of the wizard, you have an opportunity to specify something other than the default decimal and thousands separators, as well as the setting Trailing Minus For Negative Numbers.

Remember that every action you perform in Excel while recording a macro gets translated to VBA code. In the case of many dialogs, the settings you do not change are often recorded along with the items you do change. When you click OK to close the dialog, the macro recorder often records all the current settings from the dialog in the macro.

Here is another example. The next line of code in the macro is this:

Selection.End(xlDown).Select

You can click to get help for three topics in this line of code: Selection, End, and Select. Assuming that Selection and Select are somewhat self-explanatory, click in the word End and press F1 for Help.

This Help topic says that End is a property. It returns a Range object that is equivalent to pressing End+up arrow or End+down arrow in the Excel interface (see Figure 2-6). If you click the blue hyperlink for xlDirection, you see the valid parameters that can be passed to the End function.

FIGURE 2.6 The correct Help topic for the End property.

Properties can return objects

In VBA, some properties give you simple values—like a number, a string, or True/False. But other properties give you something more powerful—another object. This is called returning an object. For example, the End(xlDown) property doesn’t just give you a value—it gives you a Range object that represents a specific cell. Once you have that object, you can do things with it, like select it, read its contents, or format it.

Consider the line of code currently under examination:

Selection.End(xlDown).Select

The End keyword is a property, but from the Help file, you see that it returns a Range object. You then call Select on that returned object. In the end, you’re calling a method (Select) on the result of a property (End). You’ll do this often in VBA when a property gives you an object you can immediately act on.

Selection can return different types of objects. It returns the object of whatever is selected, such as cells, charts, etc.