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

Seven tips for cleaning up recorded code

Chapter 1 gave you four tips for recording code. So far, this chapter has covered how to understand the recorded code, how to access VBA help for any word, and how to use the excellent VBA debugging tools to step through your code. The remainder of this chapter presents seven tips to use when cleaning up recorded code.

Tip 1: Don’t select anything

Nothing screams “recorded code” more than having code that selects things before acting on them. This makes sense in a way: In the Excel interface, you have to select row 1 before you can make it bold.

However, this is rarely done in VBA. There are a couple of exceptions to this rule. For example, you need to select a point on a chart before you can change its properties.

To streamline the code the macro recorder gives you, in many cases, you can remove the part of the code that performs the selection. The following two lines are macro recorder code before it has been streamlined:

Cells.Select
Selection.Columns.AutoFit

You can streamline the recorded code so it looks like this:

Cells.Columns.AutoFit

There are a couple of advantages to doing this streamlining. First, there will be half as many lines of code in your program. Second, the program will run faster because Excel does not have to redraw the screen after the lines that perform the selection.

After recording code, you can do this streamlining by highlighting the code from before the word Select at the end of one line all the way to the dot after the word Selection on the next line. Then, press Delete (see Figures 2-17 and 2-18).

FIGURE 2.17 Select the part of the code highlighted here…

FIGURE 2.18 …and press the Delete key. This is Cleaning Up Recorded Macros 101.

Tip 2: Use Cells(2,5) because it’s more convenient than Range("E2")

The macro recorder uses the Range() property frequently. If you follow the macro recorder’s example, you will find yourself building a lot of complicated code. For example, if you have the row number for the total row stored in a variable TotalRow, you might try to build this code:

Range("E" & TotalRow).Formula = "=SUM(E2:E" & TotalRow-1 & ")"

In this code, you are using concatenation to join the letter E with the current value of the TotalRow variable. This works, but eventually, you have to refer to a range where the column is stored in a variable. Say that FinalCol is 10, which indicates column J. The column in the Range property must always be a letter, so you have to do something like this:

FinalColLetter = MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",FinalCol,1)
Range(FinalColLetter & "2").Select

Alternatively, perhaps you could do something like this:

FinalColLetter = CHR(64 + FinalCol)
Range(FinalColLetter & "2").Select

These approaches work for the first 26 columns but fail for the remaining 99.85 percent of the columns.

You could start to write 10-line functions to calculate that the column letter for column 15896 is WMJ, but it is not necessary. Instead of using Range("WMJ17"), you can use the Cells(Row,Column) syntax.

Chapter 3, “Referring to ranges, names, and tables,” covers this topic in complete detail. However, for now, you need to understand that Range("E10") and Cells(10, 5) both point to the cell at the intersection of the fifth column and the tenth row. Chapter 3 also shows you how to use .Resize to point to a rectangular range. Cells(11, 5).Resize(1, 3) is E11:G11.

Tip 3: Use more reliable ways to find the last row

It is difficult to trust data from just anywhere. If you are analyzing data in Excel, remember that the data can come from who-knows-what system written who-knows-how-long-ago. The universal truth is that eventually, some clerk will find a way to break the source system and enter a record without an invoice number. Maybe it will take a power failure to do it, but invariably, you cannot count on having every cell filled in.

This is a problem when you’re using the End+down arrow shortcut. This key combination does not take you to the last row with data in the worksheet. It takes you to the last row with data in the current range. In Figure 2-19, pressing End+down arrow would move the cursor to cell A7 rather than the true last row with data.

One better solution is to start at the bottom of the worksheet and look for the first non-blank cell by using this:

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

FIGURE 2.19 End+down arrow fails in the user interface if a record is missing a value. Similarly, End(xlDown) fails in Excel VBA.

This method could fail if the very last record happens to contain the blank row. If the data is dense enough that there will always be a diagonal path of non-blank cells to the last row, you could use this:

FinalRow = Cells(1,1).CurrentRegion.Rows.Count

If you are sure that there are not any notes or stray activated cells below the data set, you might try this:

FinalRow = Cells(1, 1).SpecialCells(xlLastCell).Row

The xlLastCell property is often wrong. Say that you have data in A1:F500. If you accidentally press Ctrl+down arrow from A500, you will arrive at A1048576. If you then apply Bold to the empty cell, it becomes activated. Or, if you type Total and then clear the cell, it becomes activated. At this point, xlLastCell will refer to F1048576.

Another method is to use the Find method:

FinalRow = Cells.Find("*", SearchOrder:=xlByRows, _
 SearchDirection:=xlPrevious).Row

You will have to choose from these various methods based on the nature of your data set. If you are not sure, you could loop through all the columns. If you are expecting seven columns of data, you could use this code:

FinalRow = 0
For i = 1 to 7
  ThisFinal = Cells(Rows.Count, i).End(xlUp).Row
  If ThisFinal > FinalRow then FinalRow = ThisFinal
Next i

Tip 4: Use variables to avoid hard-coding rows and formulas

The macro recorder never records a variable. Variables are easy to use, but just as in BASIC, a variable can remember a value. Variables are discussed in more detail in Chapter 4, “Laying the groundwork with variables and structures.”

It is recommended that you set the last row that contains data to a variable. Be sure to use meaningful variable names such as FinalRow:

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

When you know the row number of the last record, put the word Total in column A of the next row:

Cells(FinalRow + 1, 1).Value = "Total"

You can even use the variable when building this formula, which totals everything from E2 to the FinalRow of E:

Cells(FinalRow + 1, 5).Formula = "=SUM(E2:E" & FinalRow & ")"

Tip 5: Use R1C1 formulas that make your life easier

The macro recorder often writes formulas in an arcane R1C1 style. However, most people change the code back to use a regular A1-style formula. After reading Chapter 6, “R1C1 style formulas,” you’ll understand that there are times when you can build an R1C1 formula that is much simpler than the corresponding A1-style formula. By using an R1C1 formula, you can add totals to all three cells in the total row with the following:

Cells(FinalRow+1, 5).Resize(1, 3).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

Tip 6: Copy and paste in a single statement

Recorded code is notorious for copying a range, selecting another range, and then doing an ActiveSheet.Paste. The Copy method, as it applies to a range, is actually much more powerful. You can specify what to copy and also specify the destination in one statement.

Here’s the recorded code:

Range("E14").Select
Selection.Copy
Range("F14:G14").Select
ActiveSheet.Paste

Here’s better code:

Range("E14").Copy Destination:=Range("F14:G14")

Tip 7: Use With...End With to perform multiple actions

If you are making the total row bold with double underline, a larger font, and a special color, you might get recorded code like this:

Range("A14:G14").Select
Selection.Font.Bold = True
Selection.Font.Size = 12
Selection.Font.ColorIndex = 5
Selection.Font.Underline = xlUnderlineStyleDoubleAccounting

For four of these lines of code, VBA must resolve the expression Selection.Font. Because you have four lines that all refer to the same object, you can name the object once at the top of a With block. Inside the With...End With block, everything that starts with a period is assumed to refer to the With object:

With Range("A14:G14").Font
  .Bold = True
  .Size = 12
  .ColorIndex = 5
  .Underline = xlUnderlineStyleDoubleAccounting
End With

See Chapter 4 for more information on using With...End With blocks.

Sub FormatInvoice3()
'ImportInvoice Macro
Workbooks.OpenText Filename:="C:\Data\invoice.txt", Origin:=437, _
  StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
  ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
  Comma:=True, Space:=False, Other:=False, FieldInfo:=Array( _
  Array(1, 3), Array(2, 1), Array(3, 1), Array(4, 1), _
  Array(5, 1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(0, 4).Range("A1").Select
Selection.FormulaR1C1 = "=SUM(R2C:R[-1]C)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:C1"), Type:= _
  xlFillDefault
ActiveCell.Range("A1:C1").Select
ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Activate
Selection.Font.Bold = True
Application.Goto Reference:="R1C1:R1C7"
Selection.Font.Bold = True
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
End Sub