Part VI: Programming Excel with VBA

If the active sheet contains a range named data, the following statement assigns the number of cells in the data range to a variable named CellCount:

CellCount = Range(“data”).Count

You can also determine how many rows or columns are contained in a range. The following expression calculates the number of columns in the currently selected range:

Selection.Columns.Count

And, of course, you can also use the Rows property to determine the number of rows in a range. The following statement counts the number of rows in a range named data and assigns the number to a variable named RowCount:

RowCount = Range(“data”).Rows.Count

Working with Workbooks

The examples in this section demonstrate various ways to use VBA to work with workbooks.

Saving all workbooks

The following procedure loops through all workbooks in the Workbooks collection and saves each file that has been saved previously:

Public Sub SaveAllWorkbooks()

Dim Book As Workbook

For Each Book In Workbooks

If Book.Path <> “” Then Book.Save

Next Book

End Sub

Notice the use of the Path property. If a workbook’s Path property is empty, the file has never been saved (it’s a new workbook). This procedure ignores such workbooks and saves only the workbooks that have a nonempty Path property.

Saving and closing all workbooks

The following procedure loops through the Workbooks collection. The code saves and closes all workbooks.

896

Chapter 44: VBA Examples

Sub CloseAllWorkbooks()

Dim Book As Workbook

For Each Book In Workbooks

If Book.Name <> ThisWorkbook.Name Then

Book.Close savechanges:=True

End If

Next Book

ThisWorkbook.Close savechanges:=True

End Sub

The procedure uses an If statement within the For-Next loop to determine whether the workbook is the workbook that contains the code. This is necessary because closing the workbook that contains the procedure would end the code, and subsequent workbooks would not be affected.

Working with Charts

Manipulating charts with VBA can be confusing, mainly because of the large number of objects involved. To get a feel for working with charts, turn on the macro recorder, create a chart, and perform some routine chart editing. You may be surprised by the amount of code that’s generated.

When you understand how objects function within in a chart, however, you can create some useful macros. This section presents a few macros that deal with charts. When you write macros that manipulate charts, you need to understand some terminology. An embedded chart on a worksheet is a ChartObject object, and the ChartObject contains the actual Chart object. A chart on a chart sheet, on the other hand, does not have a ChartObject container.

It’s often useful to create an object reference to a chart (see “Simplifying object references,” later in this chapter). For example, the following statement creates an object variable (MyChart) for the embedded chart named Chart 1 on the active sheet.

Dim MyChart As Chart

Set MyChart = ActiveSheet.ChartObjects(“Chart 1”)

The following sections contain examples of macros that work with charts.

On the CD

These macros are available on the companion CD-ROM. The file is named chart macros.xlsm.

897

Part VI: Programming Excel with VBA

Modifying the chart type

The following example changes the chart type of every embedded chart on the active sheet. It makes each chart an area chart by adjusting the ChartType property of the Chart object. A built-in constant, xlColumnClustered, represents a standard column chart.

Sub ChartType()

Dim ChtObj As ChartObject

For Each ChtObj In ActiveSheet.ChartObjects

ChtObj.Chart.ChartType = xlColumnClustered

Next ChtObj

End Sub

The preceding example uses a For-Next loop to cycle through all the ChartObject objects on the active sheet. Within the loop, the chart type is assigned a new value, making it an area chart.

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

Sub ChartType2()

Dim Cht As Chart

For Each Cht In ActiveWorkbook.Charts

Cht.ChartType = xlColumnClustered

Next Cht

End Sub

Modifying chart properties

The following example changes the legend font for all charts that are on the active sheet. It uses a For-Next loop to process all ChartObject objects and uses the HasLegend property to ensure that the chart has a legend. The code then adjusts the properties of the Font object contained in the Legend object:

Sub LegendMod()

Dim ChtObj As ChartObject

For Each ChtObj In ActiveSheet.ChartObjects

ChtObj.Chart.HasLegend = True

With ChtObj.Chart.Legend.Font

.Name = “Arial”

.FontStyle = “Bold”

.Size = 8

End With

Next ChtObj

End Sub

898

Chapter 44: VBA Examples

Applying chart formatting

This example applies several different formatting types to the specified chart (in this case, Chart 1 on the active sheet):

Sub ChartMods()

With ActiveSheet.ChartObjects(“Chart 1”).Chart

.ChartType = xlArea

.ChartArea.Font.Name = “Arial”

.ChartArea.Font.FontStyle = “Regular”

.ChartArea.Font.Size = 9

.PlotArea.Interior.ColorIndex = 6

.Axes(xlValue).TickLabels.Font.Bold = True

.Axes(xlCategory).TickLabels.Font.Bold = True

End With

End Sub

One way to learn about these properties is to record a macro while you apply various changes to a chart.

VBA Speed Tips

VBA is fast, but it’s often not fast enough. This section presents programming examples that you can use to help speed your macros.

Turning off screen updating

You’ve probably noticed that when you execute a macro, you can watch everything that occurs in the macro. Sometimes, this view is instructive; but, after you get the macro working properly, it can be annoying and slow things considerably.

Fortunately, you can disable the normal screen updating that occurs when you execute a macro. Insert the following statement to turn off screen updating:

Application.ScreenUpdating = False

If, at any point during the macro, you want the user to see the results of the macro, use the following statement to turn screen updating back on:

Application.ScreenUpdating = True

899

Part VI: Programming Excel with VBA

Preventing alert messages

One benefit of using a macro is that you can perform a series of actions automatically. You can start a macro and then get a cup of coffee while Excel does its thing. Some operations cause Excel to display messages that must be attended to, however. For example, if your macro deletes a sheet, you see the message that is shown in the dialog box in Figure 44.3. These types of messages mean that you can’t execute your macro unattended.

FIGURE 44.3

You can instruct Excel not to display these types of alerts while a macro is running.

To avoid these alert messages (and automatically choose the default response) , insert the following VBA statement:

Application.DisplayAlerts = False

To turn alerts back on, use this statement:

Application.DisplayAlerts = True

Simplifying object references

As you may have discovered, references to objects can get very lengthy — especially if your code refers to an object that’s not on the active sheet or in the active workbook. For example, a fully qualified reference to a Range object may look like this:

Workbooks(“MyBook.xlsx”).Worksheets(“Sheet1”).Range(“IntRate”)

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

Set Rate= Workbooks(“MyBook.xlsx”).Worksheets(“Sheet1”).Range(“IntRate”)

After this variable is defined, you can use the variable Rate instead of the lengthy reference. For example

Rate.Value = .0725

900

Chapter 44: VBA Examples

Besides simplifying your coding, using object variables also speeds your macros quite a bit. I’ve seen macros execute twice as fast after creating object variables.

Declaring variable types

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

If you want your procedures to execute as fast as possible, though, you should tell Excel in advance what type of data is going be assigned to each of your variables. Providing this information in your VBA procedure is known as declaring a variable’s type.

Table 44.1 lists all the data types that are supported by VBA. This table also lists the number of bytes that each type uses and the approximate range of possible values.

TABLE 44.1

901

Part VI: Programming Excel with VBA

If you don’t declare a variable, Excel uses the Variant data type. In general, the best technique is to use the data type that uses the smallest number of bytes yet can still handle all the data assigned to it. An exception is when you’re performing floating-point calculations. In such a case, it is always best to use the Double data type (rather than the Single data type) to maintain maximum precision. Another exception involves the Integer data type. Although the Long data type uses more bytes, it usually results in faster performance.

When VBA works with data, execution speed is a function of the number of bytes that VBA has at its disposal. In other words, the fewer bytes that are used by data, the faster VBA can access and manipulate the data.

To declare a variable, use the Dim statement before you use the variable for the first time. For example, to declare the variable Units as a Long data type, use the following statement:

Dim Units as Long

To declare the variable UserName as a string, use the following statement:

Dim UserName as String

If you declare a variable within a procedure, the declaration is valid only within that procedure. If you declare a variable outside of any procedures (but before the first procedure), the variable is valid in all procedures in the module.

If you use an object variable (as described in “Simplifying object references,” earlier in this chapter), you can declare the variable as the appropriate object data type. The following is an example:

Dim Rate as Range

Set Rate = Workbooks(“MyBook.xlsx”).Worksheets(“Sheet1”).Range(“IntRate”)

To force yourself to declare all the variables that you use, insert the following statement at the top of your module:

Option Explicit

If you use this statement, Excel displays an error message if it encounters a variable that hasn’t been declared. After you get into the habit of correctly declaring all your variables, you will find that it helps eliminate errors and makes spotting errors easier.

902