Excel VBA Programming For Dummies, 4th Edition (2015)

Part III. Programming Concepts

Chapter 14. VBA Programming Examples

In This Chapter

Working with ranges in your VBA code

Changing Boolean and non-Boolean settings

Manipulating charts with VBA

Making your VBA code run as fast as possible

My philosophy for figuring out how to write Excel macros places heavy emphasis on examples. I find that a good example often communicates a concept much better than a lengthy description of the underlying theory. Because you’re reading this book, you probably agree with me. This chapter presents several examples that demonstrate common VBA techniques.

I organize these examples into the following categories:

· Working with ranges

· Changing Excel settings

· Working with charts

· Speeding up your VBA code

Although you may be able to use some of the examples directly, in most cases you must adapt them to your own needs.

Working with Ranges

Most of your VBA programming probably involves worksheet ranges. (For some background on Range objects, refer to Chapter 8.) When you work with Range objects, keep the following points in mind:

· Your VBA doesn’t need to select a range to work with it.

· If your code does select a range, its worksheet must be active.

· The macro recorder doesn’t always generate the most efficient code. Often, you can create your macro by using the recorder and then edit the code to make it more efficient.

· It’s often a good idea to use named ranges in your VBA code. For example, using Range(“Total”) is better than using Range(“D45”). In the latter case, if you add a row above row 45, you need to modify the macro so that it uses the correct range address (D46). Note that you name a range of cells by choosing Formulas  ⇒  Defined Names  ⇒  Define Name.

· When running a macro that works on the current range selection, the user might select entire columns or rows. In most cases, you don’t want to loop through every cell in the selection; that could take a long time. Your macro should create a subset of the selection consisting of only the nonblank cells.

· Excel allows multiple selections. For example, you can select a range, press Ctrl, and select another range with your mouse. Your code can test for a multiple selection and take appropriate actions.

The examples in this section, which are available at this book’s website, demonstrate these points.

If you prefer to enter these examples yourself, press Alt+F11 to activate the VBE. Then insert a VBA module and type the code. Make sure that the workbook is set up properly. For example, if the example uses two sheets named Sheet1 and Sheet2, make sure that the workbook has sheets with those names.

Copying a range

Copying a range ranks right up there as one of the favorite Excel activities of all time. When you turn on the macro recorder and copy a range from A1:A5 to B1:B5, you get this VBA macro:

Sub CopyRange()
Range("A1:A5").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Notice the last statement. This statement was generated by pressing Esc, which cancels the dotted-line outline that appears in the worksheet when you copy a range.

This macro works fine, but you can copy a range more efficiently than this. You can produce the same result with the following one-line macro, which doesn’t select any cells (and also doesn't require setting CutCopyMode to False):

Sub CopyRange2()
Range("A1:A5").Copy Range("B1")
End Sub

This procedure takes advantage of the fact that the Copy method can use an argument that specifies the destination. I found that by consulting the VBA Help system. This example also demonstrates that the macro recorder doesn’t always generate the most efficient code.

Copying a variable-size range

In many cases, you need to copy a range of cells but don’t know the exact row and column dimensions. For example, you might have a workbook that tracks weekly sales. The number of rows changes as you add new data.

Figure 14-1 shows a range in a worksheet. This range consists of several rows, and the number of rows changes from day to day. Because you don’t know the exact range address at any given time, you need a way to write code that doesn't use a range address.

Figure 14-1: This range can consist of any number of rows.

The following macro demonstrates how to copy this range from Sheet1 to Sheet2 (beginning at cell A1). It uses the CurrentRegion property, which returns a Range object that corresponds to the block of cells around a particular cell. In this case, that cell is A1.

Sub CopyCurrentRegion()
Range("A1").CurrentRegion.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.CutCopyMode = False
End Sub

Using the CurrentRegion property is equivalent to choosing Home  ⇒  Editing  ⇒  Find & Select  ⇒  GoTo Special (which displays the GoTo Special dialog box) and choosing the Current Region option. To see how this works, record your actions while issuing that command. Generally, the CurrentRegion consists of a rectangular block of cells surrounded by one or more blank rows or columns.

You can make this macro even more efficient by not selecting the destination. The following macro takes advantage of the fact that the Copy method can use an argument for the destination range:

Sub CopyCurrentRegion2()
Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1")
End Sub

It's even a bit easier if the data is in the form of a table (created in Excel using Insert  ⇒  Tables  ⇒  Table). The table has a name (such as Table1) and expands automatically when new data is added.

Sub CopyTable()
Range("Table1").Copy Sheets("Sheet2").Range("A1")
End Sub

If you try this, you'll find that the header row in the table is not copied because the name Table1 does not include that row. If you need to include the header row, change the table reference to

Range("Table1[#All]")

Selecting to the end of a row or column

You’re probably in the habit of using key combinations such as Ctrl+Shift+right arrow and Ctrl+Shift+down arrow to select a range that consists of everything from the active cell to the end of a row or a column. Not surprisingly, you can write macros that perform these types of selections.

You can use the CurrentRegion property to select an entire block of cells. But what if you want to select, say, one column from a block of cells? Fortunately, VBA can accommodate this type of action. The following VBA procedure selects the range beginning at the active cell and extending down to the cell just above the first blank cell in the column. After selecting the range, you can do whatever you want with it — copy it, move it, format it, and so on.

Sub SelectDown()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

You can make this type of selection manually: Select the first cell, hold down the Shift key, press End, and then press the down-arrow key.

This example uses the End method of the ActiveCell object, which returns a Range object. The End method takes one argument, which can be any of the following constants:

· xlUp

· xlDown

· xlToLeft

· xlToRight

Keep in mind that it’s not necessary to select a range before doing something with it. The following macro applies bold formatting to a variable-size (single-column) range without selecting the range:

Sub MakeBold()
Range(ActiveCell, ActiveCell.End(xlDown)).Font.Bold = True
End Sub

Selecting a row or column

The following procedure demonstrates how to select the column that contains the active cell. It uses the EntireColumn property, which returns a Range object that consists of a full column:

Sub SelectColumn()
ActiveCell.EntireColumn.Select
End Sub

As you may expect, VBA also offers an EntireRow property, which returns a Range object that consists of an entire row.

Moving a range

You move a range by cutting it to the Clipboard and then pasting it in another area. If you record your actions while performing a move operation, the macro recorder generates code like the following:

Sub MoveRange()
Range("A1:C6").Select
Selection.Cut
Range("A10").Select
ActiveSheet.Paste
End Sub

As with the copying example earlier in this chapter, this is not the most efficient way to move a range of cells. In fact, you can move a range with a single VBA statement, as follows:

Sub MoveRange2()
Range("A1:C6").Cut Range("A10")
End Sub

This macro takes advantage of the fact that the Cut method can use an argument that specifies the destination. Notice also that the range was not selected. The cell pointer remains in its original position.

Looping through a range efficiently

Many macros perform an operation on each cell in a range, or they perform selected actions based on each cell’s content. These macros usually include a For-Next loop that processes each cell in the range.

The following example demonstrates how to loop through a range of cells. In this case, the range is the current selection. An object variable named Cell refers to the cell being processed. Within the For Each-Next loop, the single statement evaluates the cell and applies bold formatting if the cell contains a positive value.

Sub ProcessCells()
Dim Cell As Range
For Each Cell In Selection
If Cell.Value > 0 Then Cell.Font.Bold = True
Next Cell
End Sub

This example works, but what if the user's selection consists of an entire column or row? This is not uncommon because Excel lets you perform operations on entire columns or rows. In such a case, the macro seems to take forever because it loops through all cells (all 1,048,576 of them) in the column — even the blank cells. To make the macro more efficient, you need a way to process only the nonblank cells.

The following routine does just that by using the SpecialCells method. (Refer to the VBA Help system for specific details about its arguments.) This routine uses the Set keyword to create two new Range objects: the selection’s subset that consists of cells with constants and the selection’s subset that consists of cells with formulas. The routine processes each of these subsets, with the net effect of skipping all blank cells. Pretty slick, eh?

Sub SkipBlanks()
Dim ConstantCells As Range
Dim FormulaCells As Range
Dim cell As Range
' Ignore errors
On Error Resume Next

' Process the constants
Set ConstantCells = Selection.SpecialCells(xlConstants)

For Each cell In ConstantCells
If cell.Value > 0 Then
cell.Font.Bold = True
End If
Next cell

' Process the formulas
Set FormulaCells = Selection.SpecialCells(xlFormulas)
For Each cell In FormulaCells
If cell.Value > 0 Then
cell.Font.Bold = True
End If
Next cell
End Sub

The SkipBlanks procedure works equally fast, regardless of what you select. For example, you can select the range, all columns in the range, all rows in the range, or even the entire worksheet. It’s a vast improvement over the ProcessCells procedure presented earlier in this section.

Notice that I use the following statement in this code:

On Error Resume Next

This statement tells Excel to ignore any errors that occur and simply process the next statement. (See Chapter 12 for a discussion of error handling.) This statement is necessary because the SpecialCells method produces an error if no cells qualify.

Using the SpecialCells method is equivalent to choosing the Home  ⇒  Editing  ⇒  Find & Select  ⇒  GoTo Special command and selecting the Constants option or the Formulas option. To get a feel for how this works, record your actions while you issue that command and select various options.

Looping through a range efficiently (Part II)

And now, the sequel. This section demonstrates another way to process cells in an efficient manner. This method takes advantage of the UsedRange property — which returns a Range object that consists only of the used area of the worksheet. It also uses the Intersect method, which returns a Range object that consists of cells that two ranges have in common.

Here's a variation of the SkipBlanks procedure from the previous section:

Sub SkipBlanks2()
Dim WorkRange As Range
Dim cell As Range
Set WorkRange = Intersect(Selection, ActiveSheet.UsedRange)
For Each cell In WorkRange
If cell.Value > 0 Then
cell.Font.Bold = True
End If
Next cell
End Sub

The WorkRange object variable consists of cells that are common to the user's selection and the worksheet's used range. Therefore, if an entire column is selected, WorkRange contains only the cells that are in that column and also within the used area of the worksheet. It’s fast and efficient, with no time wasted on processing cells that are outside the worksheet's used area.

Prompting for a cell value

As shown in Figure 14-2, you can use VBA’s InputBox function to get a value from the user. Then you can insert that value into a cell. The following procedure demonstrates how to ask the user for a value and place the value in cell A1 of the active worksheet, using only one statement:

Sub GetValue()
Range("A1").Value = InputBox( _
"Enter the value for cell A1")
End Sub

Figure 14-2: Use the VBA InputBox function to get a value from the user.

If you try this example, you'll find that clicking the Cancel button in the InputBox erases the current value in cell A1. Erasing the user's data isn't very good programming practice. Clicking Cancel should do nothing at all.

The following macro demonstrates a better approach: using a variable (x) to store the value entered by the user. If the value is not empty (that is, the user didn’t click Cancel), the value of x is placed in cell A1. Otherwise, nothing happens.

Sub GetValue2()
Dim x as Variant
x = InputBox("Enter the value for cell A1")
If x <> "" Then Range("A1").Value = x
End Sub

The variable x is defined as a Variant data type because it could be a number or an empty string (if the user clicks Cancel).

Determining the selection type

If you design your macro to work with a range selection, the macro must be able to determine whether a range is actually selected. If something other than a range is selected (such as a chart or a shape), the macro will probably bomb. The following statement uses the VBA TypeName function to display the type of object that is currently selected:

MsgBox TypeName(Selection)

If a Range object is selected, the MsgBox displays Range. If your macro works only with ranges, you can use an If statement to ensure that a range is selected. This example displays a message and exits the procedure if the current selection is not a Range object:

Sub CheckSelection()
If TypeName(Selection) <> "Range" Then
MsgBox "Select a range."
Exit Sub
End If
' … [Other statements go here]
End Sub

Identifying a multiple selection

As you know, Excel allows multiple selections by pressing Ctrl while choosing objects or ranges. This can cause problems with some macros. For example, you can’t copy a multiple selection that consists of nonadjacent cells. If you attempt to do so, Excel scolds you with the message shown in Figure 14-3.

Figure 14-3: Excel doesn't like it if you try to copy a multiple selection.

The following macro demonstrates how to determine whether the user made a multiple selection so your macro can take appropriate action:

Sub MultipleSelection()
If Selection.Areas.Count > 1 Then
MsgBox "Multiple selections not allowed."
Exit Sub
End If
' … [Other statements go here]
End Sub

This example uses the Areas method, which returns a collection of all ranges in the selection. The Count property returns the number of objects in the collection.

Changing Excel Settings

Some of the most useful macros are simple procedures that change one or more of Excel’s settings. For example, if you find yourself making frequent trips to the Excel Options dialog box to change a setting, that's a good candidate for a simple time-saving macro.

This section presents two examples that show you how to change settings in Excel. You can apply the general principles demonstrated by these examples to other operations that change settings.

Changing Boolean settings

Like a light switch, a Boolean setting is either on or off. For example, you might want to create a macro that turns the worksheet page break display on and off. After you print or preview a worksheet, Excel displays dashed lines to indicate the page breaks. Some people (author included) find these dashed lines very annoying. Unfortunately, the only way to get rid of the page break display is to open the Excel Options dialog box, click the Advanced tab, and scroll down until you find the Show Page Breaks check box. If you turn on the macro recorder when you change that option, Excel generates the following code:

ActiveSheet.DisplayPageBreaks = False

On the other hand, if page breaks are not visible when you record the macro, Excel generates the following code:

ActiveSheet.DisplayPageBreaks = True

This may lead you to conclude that you need two macros: one to turn on the page break display and one to turn it off. Not true. The following procedure uses the Not operator, which turns True to False and False to True. Executing the TogglePageBreaks procedure is a simple way to toggle the page break display from True to False and from False to True:

Sub TogglePageBreaks()
On Error Resume Next
ActiveSheet.DisplayPageBreaks = Not _
ActiveSheet.DisplayPageBreaks
End Sub

The first statement tells Excel to ignore any errors. For example, a chart sheet doesn't display page breaks, so if you execute the macro when a chart sheet is active, you won't see an error message.

You can use this technique to toggle any settings that have Boolean (True or False) values.

Changing non-Boolean settings

You can use a Select Case structure for non-Boolean settings. This example toggles the calculation mode between manual and automatic and displays a message indicating the current mode:

Sub ToggleCalcMode()
Select Case Application.Calculation
Case xlManual
Application.Calculation = xlCalculationAutomatic
MsgBox "Automatic Calculation Mode"
Case xlAutomatic
Application.Calculation = xlCalculationManual
MsgBox "Manual Calculation Mode"
End Select
End Sub

You can adapt this technique for changing other non-Boolean settings.

Working with Charts

Charts are jam-packed with different objects, so manipulating charts with VBA can be a bit of a challenge.

I fired up Excel 2016, entered some numbers in A1:A3, and selected that range. Then I turned on the macro recorder and created a basic column chart with three data points. I deleted the chart's gridlines and changed the chart's title. Here's the macro that I recorded:

Sub Macro1()
' Recorded by Excel 2016
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$A$3")
ActiveChart.SetElement (msoElementPrimaryValueGridLinesNone)
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "This is my chart"
End Sub

The AddChart2 was introduced in Excel 2013. If you record that same macro in Excel 2010, you get this code:

Sub Macro1()
Recorded by Excel 2010
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$A$3")
ActiveChart.Axes(xlValue).MajorGridlines.Select
Selection.Delete
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = "This is my chart"
End Sub

What does it all mean? It means that the macro recorded in Excel 2013 or Excel 2016 will not work in Excel 2010. But the macro recorded in Excel 2010 will work in Excel 2013 and later. In other words, the Excel 2010 macro exhibits forward compatibility. The Excel 2013 (and later) macro is not backward-compatible.

A typical Excel user would probably know nothing about macro compatibility as it relates to chart creation. But if you share your macro with someone who uses an earlier version, you'll find out about it quickly. Bottom line? If you rely on the macro recorder for chart-related macros, make sure that you test the macros with all versions of Excel that will be running the macros.

AddChart versus AddChart2

Here's the official syntax of the AddChart method (which is compatible with Excel 2007 and later):

.AddChart(Type, Left, Top, Width, Height)

Here's the syntax of the AddChart2 method (which is compatible only with Excel 2013 and Excel 2016):

.AddChart2 (Style, XlChartType, Left, Top, Width, Height, NewLayout)

As you can see, the AddChart2 method takes several additional arguments — arguments that specify the style, chart type, and layout. The AddChart method, on the other hand, simply creates an empty chart. The specifics for the chart must be provided in additional statements.

Examining the recorded code does reveal a few things that may be helpful in writing your own chart-related macros. If you're curious, here's a handcrafted version of that macro that creates a chart from the selected range:

Sub CreateAChart()
Dim ChartData As Range
Dim ChartShape As Shape
Dim NewChart As Chart

' Create object variables
Set ChartData = ActiveWindow.RangeSelection
Set ChartShape = ActiveSheet.Shapes.AddChart
Set NewChart = ChartShape.Chart

With NewChart
.ChartType = xlColumnClustered
.SetSourceData Source:=Range(ChartData.Address)
.SetElement (msoElementLegendRight)
.SetElement (msoElementChartTitleAboveChart)
.ChartTitle.Text = "This is my chart"
End With
End Sub

The macro is compatible with Excel 2007 and later. The chart that's created is a clustered column chart with a legend and a title. This basic chart-creating macro can be customized easily. One way to do it is to record your actions while you modify the chart and then use the recorded code to guide you.

By the way, I discuss the With End-With construct later in this chapter. It's a handy way to save some typing and make your code easier to read.

If you need to write VBA macros that manipulate charts, you must understand some terminology. An embedded chart on a worksheet is a ChartObject object. You can activate a ChartObject much like you activate a sheet. The following statement activates the ChartObject named Chart 1:

ActiveSheet.ChartObjects("Chart 1").Activate

After you activate the chart, you can refer to it in your VBA code as the ActiveChart. If the chart is on a separate chart sheet, it becomes the active chart as soon as you activate that chart sheet.

A ChartObject is also a Shape, which can be a bit confusing. In fact, when your VBA code creates a chart, it starts by adding a new Shape. You can also activate a chart by selecting the Shape object that holds the chart:

ActiveSheet.Shapes("Chart 1").Select

I prefer to use the ChartObject object in my code, just to make it perfectly clear that I'm working with a chart.

When you click an embedded chart, Excel actually selects an object inside the ChartObject object. You can select the ChartObject itself by pressing Ctrl while clicking the embedded chart.

Modifying the chart type

Here’s a confusing statement for you: A ChartObject object acts as a container for a Chart object. Read that a few times, and it might actually make sense.

To modify a chart with VBA, you don’t have to activate the chart. Rather, the Chart method can return the chart contained in the ChartObject. Are you thoroughly confused yet? The following two procedures have the same effect: They change the chart named Chart 1 to an area chart. The first procedure activates the chart first and then works with the active chart. The second procedure doesn't activate the chart. Rather, it uses the Chart property to return the Chart object contained in the ChartObject object.

Sub ModifyChart1()
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Type = xlArea
End Sub

Sub ModifyChart2()
ActiveSheet.ChartObjects("Chart 1").Chart.Type = xlArea
End Sub

Looping through the ChartObjects collection

This example changes the chart type of every embedded chart in the active sheet. The procedure uses a For Each-Next loop to cycle through each object in the ChartObjects collection, access the Chart object in each, and change its Type property.

Sub ChartType()
Dim cht As ChartObject
For Each cht In ActiveSheet.ChartObjects
cht.Chart.Type = xlArea
Next cht
End Sub

The following macro performs the same function but works on all the chart sheets in the active workbook:

Sub ChartType2()
Dim cht As Chart
For Each cht In ActiveWorkbook.Charts
cht.Type = xlArea
Next cht
End Sub

Modifying chart properties

The following example changes the Legend font for all charts on the active sheet. It uses a For-Next loop to process all ChartObject objects:

Sub LegendMod()
Dim chtObj As ChartObject
For Each chtObj In ActiveSheet.ChartObjects
With chtObj.Chart.Legend.Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 12
End With
Next cht
End Sub

Note that the Font object is contained in the Legend object, which is contained in the Chart object, which is contained in the ChartObjects collection. Now do you understand why it’s called an object hierarchy?

Applying chart formatting

This example applies several types of formatting to the active chart. I created this macro by recording my actions as I formatted a chart. Then I cleaned up the recorded code by removing irrelevant lines.

Sub ChartMods()
ActiveChart.Type = xlArea
ActiveChart.ChartArea.Font.Name = "Calibri"
ActiveChart.ChartArea.Font.FontStyle = "Regular"
ActiveChart.ChartArea.Font.Size = 9
ActiveChart.PlotArea.Interior.ColorIndex = xlNone
ActiveChart.Axes(xlValue).TickLabels.Font.Bold = True
ActiveChart.Axes(xlCategory).TickLabels.Font.Bold = True
ActiveChart.Legend.Position = xlBottom
End Sub

You must activate a chart before executing the ChartMods macro. Activate an embedded chart by clicking it. To activate a chart in a chart sheet, activate the chart sheet.

To ensure that a chart is selected, you can add a statement to determine if a chart is active. Here’s the modified macro, which displays a message (and ends) if a chart is not activated:

Sub ChartMods2()
If ActiveChart Is Nothing Then
MsgBox "Activate a chart."
Exit Sub
End If
ActiveChart.Type = xlArea
ActiveChart.ChartArea.Font.Name = "Calibri"
ActiveChart.ChartArea.Font.FontStyle = "Regular"
ActiveChart.ChartArea.Font.Size = 9
ActiveChart.PlotArea.Interior.ColorIndex = xlNone
ActiveChart.Axes(xlValue).TickLabels.Font.Bold = True
ActiveChart.Axes(xlCategory).TickLabels.Font.Bold = True
ActiveChart.Legend.Position = xlBottom
End Sub

Here's another version that uses the With-End With construct to save some typing and make the code a bit clearer. Once again, I'm getting ahead of myself. Flip ahead a few pages to read about the With End-With structure.

Sub ChartMods3()
If ActiveChart Is Nothing Then
MsgBox "Activate a chart."
Exit Sub
End If
With ActiveChart
.Type = xlArea
.ChartArea.Font.Name = "Calibri"
.ChartArea.Font.FontStyle = "Regular"
.ChartArea.Font.Size = 9
.PlotArea.Interior.ColorIndex = xlNone
.Axes(xlValue).TickLabels.Font.Bold = True
.Axes(xlCategory).TickLabels.Font.Bold = True
.Legend.Position = xlBottom
End With
End Sub

When it comes to using VBA to work with charts, this short section barely scratches the surface. There's a lot more to it, of course, but at least this basic introduction will get you headed in the right direction.

VBA Speed Tips

VBA is fast, but it’s not always fast enough. (Computer programs are never fast enough.) This section presents some programming examples you can use to speed up your macros.

Turning off screen updating

When executing a macro, you can sit back and watch all the onscreen action that occurs in the macro. Although doing this can be instructive, after you get the macro working properly, it’s often annoying and can slow the performance of your macro considerably. Fortunately, you can disable the screen updating that normally occurs when you execute a macro. To turn off screen updating, use the following statement:

Application.ScreenUpdating = False

If you want the user to see what’s happening at any point during the macro, use the following statement to turn screen updating back on:

Application.ScreenUpdating = True

To demonstrate the difference in speed, execute this simple macro, which fills a range with numbers:

Sub FillRange()
Dim r as Long, c As Long
Dim Number as Long
Number = 0
For r = 1 To 50
For c = 1 To 50
Number = Number + 1
Cells(r, c).Select
Cells(r, c).Value = Number
Next c
Next r
End Sub

You see each cell being selected and the value being entered in the cells. Now insert the following statement at the beginning of the procedure and execute it again:

Application.ScreenUpdating = False

The range is filled much faster, and you don’t see the result until the macro is finished running and screen updating is (automatically) set to True.

When you’re debugging code, program execution sometimes ends somewhere in the middle without your having turned Screen updating back on (and yes, this happens to me, too). This sometimes causes Excel’s application window to become totally unresponsive. The way out of this frozen state is simple: Go back to the VBE, and execute the following statement in the Immediate window:

Application.ScreenUpdating = True

Turning off automatic calculation

If you have a worksheet with many complex formulas, you may find that you can speed things considerably by setting the calculation mode to manual while your macro is executing. When the macro finishes, set the calculation mode back to automatic.

The following statement sets the Excel calculation mode to manual:

Application.Calculation = xlCalculationManual

Execute the next statement to set the calculation mode to automatic:

Application.Calculation = xlCalculationAutomatic

If your code uses cells with formula results, turning off calculation means that the cells will not be recalculated unless you explicitly tell Excel to do so!

Eliminating those pesky alert messages

As you know, a macro can automatically perform a series of actions. In many cases, you can start a macro and then go hang out in the break room while Excel does its thing. Some Excel operations, however, display messages that require a human response. For example, if your macro deletes a nonempty sheet, your code comes to a screeching halt while Excel waits for your response to the message shown in Figure 14-4. These types of messages mean that you can’t leave Excel unattended while it executes your macro — unless you know the secret trick.

Figure 14-4: You can instruct Excel to not display these types of alerts while running a macro.

The secret trick to avoiding these alert messages is inserting the following VBA statement into your macro:

Application.DisplayAlerts = False

Excel executes the default operation for these types of messages. In the case of deleting a sheet, the default operation is Delete (which is just what you want to happen). If you're not sure what the default operation is, perform a test to see what happens.

When the procedure ends, Excel automatically resets the DisplayAlerts property to True (its normal state). If you need to turn the alerts back on before the procedure ends, use this statement:

Application.DisplayAlerts = True

Simplifying object references

As you probably already know, references to objects can become very lengthy. For example, a fully qualified reference to a Range object may look like this:

Workbooks("MyBook.xlsx").Worksheets("Sheet1") _
.Range("InterestRate")

If your macro frequently uses this range, you may want to create an object variable by using the Set command. For example, the following statement assigns this Range object to an object variable named Rate:

Set Rate = Workbooks("MyBook.xlsx") _
.Worksheets("Sheet1").Range("InterestRate")

After defining this object variable, you can use the variable Rate rather than the lengthy reference. For example, you can change the value of the cell named InterestRate:

Rate.Value = .085

This is much easier to type (and understand) than the following statement:

Workbooks("MyBook.xlsx").Worksheets("Sheet1"). _
Range("InterestRate") = .085

In addition to simplifying your coding, using object variables speeds your macros considerably. After creating object variables, I’ve seen some macros execute twice as fast as before.

Declaring variable types

You usually don’t have to worry about the type of data you assign to a variable. Excel handles all the details for you behind the scenes. For example, if you have a variable named MyVar, you can assign a number of any type to that variable. You can even assign a text string to it later in the procedure.

If you want your procedures to execute as fast as possible (and avoid some potentially nasty problems), tell Excel what type of data will be assigned to each of your variables. This is known as declaring a variable’s type. (Refer to Chapter 7 for complete details.) Get into the habit of declaring all variables that you use.

In general, you should use the data type that requires the smallest number of bytes yet can still handle all the data assigned to it. When VBA works with data, execution speed depends on the number of bytes VBA has at its disposal. In other words, the fewer bytes data uses, the faster VBA can access and manipulate the data. An exception to this is the Integer data type. If speed is critical, use the Long data type instead.

If you use an object variable (as described in the preceding section), you can declare the variable as a particular object type. Here’s an example:

Dim Rate as Range
Set Rate = Workbooks("MyBook.xlsx") _
.Worksheets("Sheet1").Range("InterestRate")

Using the With-End With structure

Do you need to set a number of properties for an object? Your code runs faster if you use the With-End With structure. An additional benefit is that your code may be easier to read.

The following code does not use With-End With:

Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.WrapText = True
Selection.Orientation = 0
Selection.ShrinkToFit = False
Selection.MergeCells = False

Here’s the same code, rewritten to use With-End With:

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With

If this structure seems familiar to you, it’s probably because the macro recorder uses With-End With whenever it can. And I present other examples earlier in this chapter. When you use With-End With, make sure that each statement begins with a dot.