Copying a range

Copying a range is a frequent activity in macros. When you turn on the macro recorder (using absolute recording mode) and copy a range from A1:A5 to B1:B5, you get a VBA macro like this:

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

This macro works, but it's not the most efficient way to copy a range. You can accomplish the same result with the following one-line macro:

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

This code takes advantage of the fact that the Copy method can use an argument that specifies the destination. Useful information about properties and methods is available in the Help system.

The example demonstrates that the macro recorder doesn't always generate the most efficient code. As you see, you don't have to select an object to work with it. Note that CopyRange2 doesn't select a range; therefore, the active cell doesn't change when this macro is executed.

Copying a variable-size range

Often, you want to copy a range of cells in which the exact row and column dimensions are unknown.

Figure 44.1 shows a range on a worksheet. This range contains data that is updated weekly. Therefore, the number of rows changes. Because the exact range address is unknown at any given time, you can't hard-code the address in your VBA code.

The macro that follows demonstrates how to copy this range to Sheet2 (beginning at cell A1). It uses the CurrentRegion property, which returns a Range object that corresponds to the block of used cells surrounding a particular cell. This is equivalent to choosing Home ➪ Editing ➪ Find & Select ➪ Go To, clicking the Special button, and then selecting the Current Region option:

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

Another approach is to use a table to store the data. When you add new rows to a table, the table's range address adjusts automatically, so you can use a procedure like this:

Sub CopyTable()
  Range("Table1[#All]").Copy Sheets("Sheet2").Range("A1")
End Sub

Selecting to the end of a row or column

You're probably in the habit of using key combinations, such as pressing Ctrl+Shift+→ and Ctrl+Shift+↓, to select from the active cell to the end of a row or column. When you record these actions in Excel (using relative recording mode), you'll find that the resulting code works as you would expect.

The following VBA procedure selects the range that begins at the active cell and extends down to the last cell in the column (or to the cell above the first empty cell, whichever comes first). When the range is selected, 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

Notice that the Range property has two arguments. These arguments represent the upper-left and lower-right cells in a range.

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

Selecting a row or column

The macro that follows demonstrates how to select the column of the active cell. It uses the EntireColumn property, which returns a range that consists of a column:

Sub SelectColumn()
  ActiveCell.EntireColumn.Select
End Sub

As you may suspect, an EntireRow property also is available, which returns a range that consists of a row.

If you want to perform an operation on all cells in the selected row or column, you don't need to select the row or column. For example, when the following procedure is executed, all cells in the row that contains the active cell are made bold:

Sub MakeRowBold()
  ActiveCell.EntireRow.Font.Bold = True
End Sub

Moving a range

Moving a range consists of cutting it to the Clipboard and then pasting it to another area. If you record your actions while performing a move operation, the macro recorder generates code as follows:

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

As demonstrated with copying earlier in this chapter (see “Copying a range”), this method is not the most efficient way to move a range of cells. In fact, you can do it with a single VBA statement as follows:

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

This statement takes advantage of the fact that the Cut method can use an argument that specifies the destination.

Looping through a range efficiently

Many macros perform an operation on each cell in a range, or they may perform selective actions based on the content of each cell. These operations usually involve a For-Next loop that processes each cell in the range.

The following example demonstrates how to loop through all the cells in a range. In this case, the range is the current selection. In this example, Cell is a variable name that refers to the cell being processed. (Notice that this variable is declared as a Range object.) Within the For-Next loop, the single statement evaluates the cell. If the cell is negative, it's converted to a positive value:

Sub ProcessCells()
  Dim Cell As Range
  For Each Cell In Selection.Cells
    If Cell.Value < 0 Then Cell.Value = Cell.Value * -1
  Next Cell
End Sub

The preceding example works, but what if the selection consists of an entire column or an entire range? This is not uncommon because Excel lets you perform operations on entire columns or rows. In this case, though, the macro seems to take forever because it loops through each cell—even those that are blank. What's needed is a way to process only the nonblank cells.

You can accomplish this task by using the SpecialCells method. In the following example, the SpecialCells method is used to create a new object: the subset of the selection that consists of cells with constants (as opposed to formulas). This subset is processed, with the net effect of skipping all blank cells and all formula cells:

Sub ProcessCells2()
  Dim ConstantCells As Range
  Dim Cell As Range
'  Ignore errors
  On Error Resume Next
'  Process the constants
  Set ConstantCells = _
    Selection.SpecialCells(xlConstants, xlNumbers)
  For Each Cell In ConstantCells
    If Cell.Value < 0 Then Cell.Value = Cell.Value * -1
  Next Cell
End Sub

The ProcessCells2 procedure works fast, regardless of what is selected. For example, you can select the range, select all columns in the range, select all rows in the range, or even select the entire worksheet. In all of these cases, only the cells that contain constants are processed inside the loop. This procedure is a vast improvement over the ProcessCells procedure presented earlier in this section.

Notice that the following statement is used in the procedure:

On Error Resume Next

This statement causes Excel to ignore any errors that occur and simply process the next statement. This statement is necessary because the SpecialCells method produces an error if no cells qualify and because the numerical comparison will fail if a cell contains an error value. Normal error checking is resumed when the procedure ends. To return to normal error-checking mode inside a procedure, use the following statement:

On Error GoTo 0

Prompting for a cell value

As discussed in Chapter 41, “Creating UserForms,” you can take advantage of the VBA InputBox function to ask the user to enter a value. Figure 44.2 shows an example.

You can assign this value to a variable and use it in your procedure. Often, however, you want to place the value into a cell. The following procedure demonstrates how to ask the user for a value and place it into cell A1 of the active worksheet, using only one statement:

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

This procedure has a problem, however: if the user clicks Cancel, the contents of cell A1 are replaced with an empty string. Here's a modified version in which the InputBox entry is assigned to a variable named UserVal. The code checks this variable and takes action only if the variable is not empty:

Sub GetValue()
  UserVal = InputBox("Enter the value for cell A1")
  If UserVal <> "" Then Range("A1").Value = UserVal
End Sub

Here's a variation that accepts only a numeric value. If the user enters a non-number, the InputBox keeps appearing until a number is entered. Only when a number is entered does the code exit the Do Loop, and the value is entered into A1. Another line inside the loop allows the user to click Cancel and get out of the procedure:

Sub GetValue()
 
  Do
    UserVal = _
      InputBox("Enter a numeric value for cell A1")
    If UserVal = "" Then Exit Sub
  Loop Until IsNumeric(UserVal)
 
  Range("A1").Value = UserVal
 
End Sub

Determining the type of selection

If your macro is designed to work with a selected range, you need to determine that a range is actually selected. Otherwise, the macro most likely fails. The following procedure identifies the type of object selected:

Sub SelectionType()
  MsgBox TypeName(Selection)
End Sub

If a cell or a range is selected, the MsgBox displays Range. If your macro is designed to work only with ranges, you can use an If statement to ensure that a range is actually selected. The following is an example that displays a message if the current selection is not a Range object:

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

Identifying a multiple selection

Excel enables you to make a multiple selection by pressing Ctrl while you select objects or ranges. This method can cause problems with some macros; for example, you can't copy a multiple selection that consists of nonadjacent ranges. The following macro demonstrates how to determine whether the user has made a multiple selection:

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

This example uses the Areas property, which returns a collection of all areas in the selection. An area is a Range object consisting of contiguous cells. The Count property returns the number of objects that are in the collection.

You may want to work with multiple selections rather than just ignore them—for example, if you want to copy the selection. You can loop through the Areas collection of the Range object as this example shows:

Sub LoopAreas()
 
  Dim Area As Range
 
  For Each Area In Selection.Areas
    'Copy each selection 10 columns to the right
    Area.Copy Area.Offset(0, 10)
  Next Area
 
End Sub

Counting selected cells

You can create a macro that works with the selected range of cells. Use the Count property of the Range object to determine how many cells are contained in a range selection (or any range, for that matter). For example, the following statement displays a message box that contains the number of cells in the current selection:

MsgBox Selection.Count

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 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

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:

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 one 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.

Creating a workbook

Use the Add method of the Workbooks collection object to create a new workbook, as in the following example:

Workbooks.Add

Some methods, like the Add method, return a value. In this case, the returned value is the workbook that was created. You can store the returned value in a variable and use it later in your code. The following code declares a variable, creates a new workbook and stores it in the variable, and uses that variable to save the new workbook to disk:

Dim NewBook As Workbook
Set NewBook = Workbooks.Add
NewBook.SaveAs "test.xlsx"

Because the Add method returns a workbook, all the workbook's properties are available to you, such as the Sheets property. If you want to create a new workbook and interact with the first sheet in it, you can put the sheet in a variable rather than the workbook, as in the following example:

Dim NewSheet As Worksheet
Set NewSheet = Workbooks.Add.Sheets(1)
NewSheet.Range("A1").Value = Now

Modifying the chart type

The following example changes the chart type of every embedded chart on the active sheet. It makes each chart a clustered column 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 a clustered column 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 sets the HasLegend property to True. 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

Applying chart formatting

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

Sub ChartMods()
  With ActiveSheet.ChartObjects("Chart 1").Chart
    .ChartType = xlColumnClustered
    .ChartTitle.Text = "XYZ Corporation"
    .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

The best way to learn about the objects, properties, and methods that you need to code for charts is to record a macro while you create or apply various changes to a chart.

Turning off screen updating

You've probably noticed that when you execute a macro, you can watch everything that occurs in it. 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's execution you want the user to see the results of the macro, use the following statement to turn screen updating back on:

Application.ScreenUpdating = True

When the macro completes, Excel automatically turns screen updating back on.

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 addressed, 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.

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

As with screen updating, Excel turns alerts back on when the macro is done.

Simplifying object references

As you may have discovered, references to objects can get 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 object variable Rate instead of the lengthy reference. Here's an example:

Rate.Font.Bold = True
Rate.Value = .0725

Besides simplifying your coding, using object variables speeds your macros quite a bit. We've seen complex 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 most of the data types that VBA supports. This table also lists the number of bytes that each type uses and the approximate range of possible values.

If you don't declare a variable, Excel uses the Variant data type. In general, the best approach is to use the data type that uses the smallest number of bytes yet one that can still handle all the data assigned to it. An exception is when you're performing floating-point calculations. In such a case, it's 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 data uses, the faster that 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. Here 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 and Excel encounters a variable that hasn't been declared, Excel displays an error message. After you get into the habit of correctly declaring all your variables, you'll find that it not only can speed up code execution, but it helps eliminate errors and simplifies spotting errors.