9. Object State When Recording/Running Macros

9.1 Recording Object State

As explained earlier, every object has a set of properties that are characteristics or "settings" of that object. At any point, each property of an object has a certain value. For example, the various settings for page setup are actually properties of the PageSetup object. The values of all the properties of an object at any particular time reflect that object's "state" at that time. When you record a macro, with some objects the entire state of the object is recorded, even if you only change one property. PageSetup is an example where this occurs.

9.2 Undesirable Recording of Macro State

Sometimes this recording of the entire object's state is not what you want to wind up with in the macro. If you recorded the TUT5 macro earlier, look at the code that was generated. You will see something something like this:
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Note: This is the macro code in Excel 2003. If you are using Excel 2007/2010 you will see additional lines reflecting first-page and odd/even headers and footers.

I added the emphasis on the part of the macro that sets the page orientation to landscape. You might have expected something like that line to be the only code in the macro. Instead, you see assignment of values to all of the properties of the PageSetup object.

Most of the properties assigned in the macro should be recognizable as settings available on the various tabs of the Page Setup dialog. When you open the Page Setup dialog, all of the fields are populated with the values currently in effect. (We tend to ignore those we are not changing, but they are there.) When you click OK, all the properties (fields) shown on the dialog are assigned; we normally don't think about this because it has no effect on properties we are not changing. Thus the code in the recorded macro reflects this assignment of all the properties of PageSetup.

The result is that every time you run this macro, it will set each property of the PageSetup object to the value as assigned in the macro. This is a problem if all you expect the macro to do is set the page orientation to landscape. Let us say you have changed the top margin on a worksheet to 1.5 inches at some point after you have recorded the macro. Later, you run this macro to change the page orientation to landscape. The macro does that, but it also changes the top margin to 1 inch (the top margin setting when you recorded the macro). This is probably not what you expect or want.

To avoid this, you need to remove all the commands from the macro, leaving only the one(s) that perform the action(s) that you want the macro to perform. To change the macro above so that it only changes the page orientation to landscape, the macro code should be this:

With ActiveSheet.PageSetup
.Orientation = xlLandscape
End With

Since we have only one property of the object to change, we can simplify the macro code by eliminating the With...End With construction (explained earlier in the tutorial). We can use one line of code: ActiveSheet.PageSetup.Orientation = xlLandscape. You should recognize the value xlLandscape as an example of an enumeration, explained earlier in the tutorial.

This issue can arise anywhere the entire state of an object is recorded. You are likely to encounter it when you change cell formats or page setup. If you record a macro including any of those actions, you will want to inspect the macro code and edit it as needed.

9.3 Desirable Recording of Settings

In an Excel macro, the functionality of Find is contained in the Find method of the Range object. Calling Range.Find is equivalent to clicking on Edit/Find while working a spreadsheet. When you record a macro including Find, the Find method is passed arguments that represent the various options on the Find dialog.

The settings in the Find dialog box are "sticky," in the sense that when you perform the next Find, the settings that appear in the dialog box are as they were when the previous Find was performed. If they are not changed, the Find will be performed with the same settings as the previous Find. Similar to the situation with the PageSetup object discussed above, recording a macro that performs a Find records all the settings in the Find/Replace dialog box as they are at that time. The following (or similar) is the macro code when you look at macro TUT4 that you recorded earlier:

Cells.Find(What:="hello", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
     :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
     False, SearchFormat:=False).Activate
Once again, the arguments should be familiar since they represent settings and values that can be entered on the Find dialog box.

If you had checked "Match case" while recording the macro, that setting would be saved in the macro, and each time it is run it would be run with MatchCase:= True. Also, it would result in the "Match case" checkbox being checked the next time you did Edit/Find manually, since the entries and settings on the Find dialog are sticky. This is true whether they are set manually or by a macro.

After the discussion about the PageSetup example above, you might think that since all you are doing is finding the next instance of "hello", you only need the following code in your macro: Cells.Find (What:="hello").Activate

That code will in fact find the next instance of "hello." But it will execute the Find with all the current arguments (i.e., dialog-box settings), whatever those happen to be. If the last Find that was peformed was with Match Case checked, the macro will execute the find with Matchcase:=True. This may or may not be what you want. Remember that when using Find in a macro, you should always specify all of the arguments (i.e., all of the settings on the Find dialog box), to make sure they are in fact the ones you want. In other words, all the arguments shown when you record Edit/Find should be preserved, edited as needed.

Some additional explanation on the Find macro code above:

The underscore character at the end of the line is the "continuation" character; this character allows you to split code that the compiler expects to see on one line, onto multiple lines.
Find is a method of the Range object. As we discussed earlier, the expression Cells returns a Range object consisting of all the cells of a worksheet.
The Find method returns a Range object representing the first cell where the value searched for is found. Since the expression returns a range, we can access any of the members of the Range object. Using Find manually finds the first matching cell and activates it; the macro does that by calling the Activate method on the range returned by Find.

9.4 Using Find to Move to a Particular Cell

Since we just covered performing a Find in a macro, this is a good point at which to discuss using Find to position the cursor.

When you are working manually in Excel you often need to move to a cell containing certain data. Because you can see where you are on the worksheet, you can easily do that by using one of the arrow keys. Having a macro do the same thing is not so simple. Arrow-key presses in Excel are not recorded in a macro; only the ultimate destination cell is recorded as being selected, by the cell address.

You can mimic the result of arrow-key presses by using the Offset property of the Range object. Offset has two parameters, rowoffset and columnoffset, which are the number of rows and columns "away" from the original range. The expression ActiveCell.Offset(3, 2) returns a range whose address is 3 rows down and 2 columns to the right. To move to that cell, you would use the Activate method. So, if the active cell is B1, ActiveCell.Offset(5, 4).Activate would make cell F6 the active cell.

Using Offset often will not work, however, because the specific arrow-key presses that work in one situation may very well not work to get to the correct cell in another situation. The macro has no way of knowing exactly where it is on the worksheet; in effect, the macro is working blind. Since rows and columns can be added, deleted or moved, relying on the macro equivalent of arrow-key presses to get to a particular cell is unreliable and should be avoided, except for situations where you are absolutely certain of the relative position of the cell to be moved to. The preferable way to move to a cell containing specific data is to use Find. By doing so, you can be sure that no matter which cell contains that data, the macro will find that cell and activate it.

If the data searched for is not found, the expression returns Nothing. The keyword Nothing is used when there is no object; here it means there is no Range object. Since there is no object, you cannot access any object members, and if you try to do so you will get an error. You can test whether a Find returns Nothing by using an If statement as described later in this tutorial.