Chapter 14
VBA Programming Examples
In This Chapter
Exploring VBA examples
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 a refresher course 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 instance, 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 most 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 marching ants display 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-sized 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 on a worksheet. This range consists of several rows, and the number of rows will change 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 Down Arrow.
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-sized (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 might 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 each cell (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 of processing 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 of 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 a 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 into 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 about 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 2013, 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 was recorded:
Sub Macro1()
' Recorded by Excel 2013
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
I was actually kind of surprised to see this code, because I’d never heard of the AddChart2 method. As it turns out, AddChart2 is new to 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 will not work in Excel 2010. But the macro recorded in Excel 2010 will work in Excel 2013. In other words, the Excel 2010 macro exhibits forward compatibility. The Excel 2013 macro is not backward compatible.
A typical Excel 2013 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 will all versions of Excel that will be running the macros.
AddChart versus AddChart2
Here’s the official syntax for the AddChart method (which is compatible with Excel 2007 and later):
.AddChart(Type, Left, Top, Width, Height)
Here’s the syntax for the AddChart2 method (which is compatible only with Excel 2013):
.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 hand-crafted 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 on 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 different 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 on 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 scratched 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 on-screen action that occurs in the macro. Although doing this can be instructive, after getting the macro working properly, it’s often annoying and can slow down the course 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 into the cells. Now insert the following statement at the beginning of the procedure and execute it again:
Application.ScreenUpdating = False
The range is filled up much faster, and you don’t see the end result until the macro is finished running and screen updating is (automatically) set to True.
When debugging code, sometimes program execution ends somewhere in the middle, without 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 up 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, remember that 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 avoid these alert messages, insert the following VBA statement in 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 and 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 also speeds up your macros considerably. After creating object variables, I’ve seen some macros execute twice as fast as before.
Declaring variable types
You don’t usually 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.
But 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 presented other examples earlier in this chapter.