VBA Programming Examples & Tech

VBA Programming Examples and Techniques
Working with Ranges
The examples in this section demonstrate how to manipulate worksheet ranges
with VBA.
The examples in this section are available on the companion CD-ROM.
Copying a range
Excel’s macro recorder is useful not so much for generating usable code as for
discovering the names of relevant objects, methods, and properties. The code
that’s generated by the macro recorder isn’t always the most efficient, but it can
usually provide you lots of useful insights.
For example, recording a simple copy-and-paste operation generates five lines of
VBA code:
Sub Macro1()
Range(“A1”).Select
Selection.Copy
Range(“B1”).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Notice that the generated code selects the cells. But in VBA, it’s not necessary to
select an object to work with it. You would never learn this important point by
mimicking the preceding recorded macro code, where two lines incorporate the
Select method. This procedure can be replaced with the following much simpler
routine, which takes advantage of the fact that the Copy method can use an argument
that represents the destination for the copied range:
Sub CopyRange()
Range(“A1”).Copy Range(“B1”)
End Sub
On the
CD-ROM
Both of these macros assume that a worksheet is active and that the operation
takes place on the active worksheet. To copy a range to a different worksheet or
workbook, simply qualify the range reference for the destination. The following
example copies a range from Sheet1 in File1.xls to Sheet2 in File2.xls. Because the
references are fully qualified, this example works regardless of which workbook
is active.
Sub CopyRange2()
Workbooks(“File1.xls”).Sheets(“Sheet1”).Range(“A1”).Copy _
Workbooks(“File2.xls”).Sheets(“Sheet2”).Range(“A1”)
End Sub
Another way to approach this task is to use object variables to represent the
ranges, as the following example demonstrates:
Sub CopyRange3()
Set Rng1 = Workbooks(“File1.xls”). _
Sheets(“Sheet1”).Range(“A1”)
Set Rng2 = Workbooks(“File2.xls”). _
Sheets(“Sheet2”).Range(“A1”)
Rng1.Copy Rng2
End Sub
As you might expect, copying is not limited to one single cell at a time. The following
procedure, for example, copies a large range. Notice that the destination consists of
only a single cell (which represents the upper left cell for the destination).
Sub CopyRange4()
Range(“A1:C800”).Copy Range(“D1”)
End Sub
Moving a range
The VBA instructions for moving a range are very similar to those for copying a
range, as the following example demonstrates. The difference is that you use the
Cut method instead of the Copy method. Note that you need to specify only the
upper-left cell for the destination range.
The following example moves 18 cells (in A1:C6) to a new location, beginning at
cell H1:
Sub MoveRange1()
Range(“A1:C6”).Cut Range(“H1”)
End Sub
Copying a variably sized range
In many cases, you need to copy a range of cells, but you don’t know the exact row
and column dimensions of the range. For example, you might have a workbook that
tracks weekly sales. The number of rows changes weekly as you add new data.
Figure 11-1 shows a very common type of worksheet. This range consists of several
rows, and the number of rows changes each week. Because you don’t know the
exact range address at any given time, writing a macro to copy the range requires
some additional coding.
Figure 11-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, A1).
Sub CopyCurrentRegion2()
Range(“A1”).CurrentRegion.Copy _
Sheets(“Sheet2”).Range(“A1”)
End Sub
Using the CurrentRegion property is equivalent to choosing the Edit➪Go To
command, clicking the Special button, and selecting the Current Region option. To
see how this works, record your actions while you issue that command. Generally,
the CurrentRegion property setting consists of a rectangular block of cells
surrounded by one or more blank rows or columns.
Selecting or otherwise identifying various
types of ranges
Much of the work you will do in VBA will involve working with ranges—either
selecting a range or identifying a range so you can do something with the cells.
In previous versions of Excel, recording a macro that selects cells (such as
Ctrl+Shift+→) was a hit or miss proposition. The macro recorder in Excel 2002
seems to handle these types of selections much better than in previous versions.
However, it’s always a good idea to check your recorded code very carefully to
make sure that the selection code works as you intended.
In addition to the CurrentRegion property (discussed above), you should also be
aware of the End method of the Range object. The End method takes one argument,
Feature
Note
which determines the direction in which the selection is extended. The following
statement selects a range from the active cell to the last nonempty cell:
Range(ActiveCell, ActiveCell.End(xlDown)).Select
As you may expect, three other constants simulate key combinations in the other
directions: xlUp, xlToLeft, and xlToRight.
Be careful when using the End method. If the active cell is at the perimeter of a
range, or the range contains one or more empty cells, the End method may not
produce the desired results.
The companion CD-ROM includes a workbook that demonstrates several common
types of range selections. When you open this workbook, you’ll see a new menu
command, Selection Demo. This menu contains commands that enable the user to
make various types of selections, as shown in Figure 11-2.
Figure 11-2: This workbook demonstrates how to select variably sized ranges
using VBA.
The following macro is in the example workbook. The SelectCurrentRegion
macro simulates pressing Ctrl+Shift+*.
Sub SelectCurrentRegion()
ActiveCell.CurrentRegion.Select
End Sub
Caution
Often, you won’t want to actually select the cells. Rather, you’ll want to work with
them in some way (for example, format them). The cell-selecting procedures can
easily be adapted. The following procedure was adapted from SelectCurrentRegion.
This procedure doesn’t select cells; it creates a Range object and then applies
formatting to the range. The other procedures in the example workbook can also
be adapted in this manner.
Sub FormatCurrentRegion()
Set WorkRange = ActiveCell.CurrentRegion
WorkRange.Font.Bold = True
End Sub
Prompting for a cell value
The following procedure demonstrates how to ask the user for a value and then
insert it into cell A1 of the active worksheet:
Sub GetValue1()
Range(“A1”).Value = InputBox(“Enter the value”)
End Sub
Figure 11-3 shows how the input box looks.
Figure 11-3: The InputBox function gets a
value from the user to be inserted into a cell.
This procedure has a problem, however. If the user clicks the Cancel button in the
input box, the procedure deletes any data already in the cell. The following modification
checks for the Cancel button clicks and takes no action:
Sub GetValue2()
UserEntry = InputBox(“Enter the value”)
If UserEntry <> “” Then Range(“A1”).Value = UserEntry
End Sub
In many cases, you’ll need to validate the user’s entry in the input box. For example,
you may require a number between 1 and 12. The following example demonstrates
one way to validate the user’s entry. In this example, an invalid entry is ignored and
the input box is displayed again. This cycle keeps repeating until the user enters a
valid number or clicks Cancel.
Sub GetValue3()
Dim MinVal As Integer, MaxVal As Integer
Dim UserEntry As String
Dim Msg As String
Dim IntEntry As Integer
MinVal = 1
MaxVal = 12
Msg = “Enter a value between “ & MinVal & “ and “ & MaxVal
Do
UserEntry = InputBox(Msg)
If UserEntry = “” Then Exit Sub
If IsNumeric(UserEntry) Then
IntEntry = CInt(UserEntry)
If IntEntry >= MinVal And IntEntry <= MaxVal Then
Exit Do
End If
End If
Msg = “Your previous entry was INVALID.”
Msg = Msg & vbNewLine
Msg = Msg & “Enter a value between “ & _
MinVal & “ and “ & MaxVal
Loop
ActiveSheet.Range(“A1”).Value = UserEntry
End Sub
Notice that the loop continues indefinitely. I use Exit Sub statements to get out of
the loop when the user clicks Cancel.
To keep things simple, this procedure doesn’t perform any validation.
Notice the statement that determines the value of the NextRow variable. If you don’t
understand how this works, try the manual equivalent: Activate cell A65536 (the
last cell in column A). Then press End, followed by Up Arrow. At this point, the last
nonblank cell in column A will be selected. The Row property returns this row number,
and it is incremented by 1 in order to get the row of the cell below it (the next
empty row).
Note that this technique of selecting the next empty cell has a slight glitch. If the
column is completely empty, it will calculate Row 2 as the next empty row.
Pausing a macro to get a user-selected range
You may create a macro that needs to pause while the user specifies a range of
cells. The procedure in this section describes how to do this using Excel’s
InputBox function.
Do not confuse Excel’s InputBox function with VBA’s InputBox function.
Although these two functions have the same name, they are not the same.
The Sub procedure that follows demonstrates how to pause a macro and let the
user select a cell:
Sub GetUserRange()
Dim UserRange As Range
Output = 565
Prompt = “Select a cell for the output.”
Title = “Select a cell”
‘ Display the Input Box
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) ‘Range selection
On Error GoTo 0
Note
Note
If UserRange Is Nothing Then
MsgBox “Canceled.”
Else
UserRange.Range(“A1”) = Output
End If
End Sub
The input box is shown in Figure 11-6.
Figure 11-6: Using an input box
to pause a macro
Specifying a Type argument of 8 is the key to this procedure. Also, note the use of
On Error Resume Next. This statement ignores the error that occurs if the user
clicks the Cancel button. If so, the UserRange object variable is not defined. This
example displays a message box with the text Canceled. If the user clicks OK, the
macro continues. Using On Error GoTo 0 resumes normal error handling.
By the way, it’s not necessary to check for a valid range selection. Excel takes care
of this for you.
Make sure ScreenUpdating is turned on. Otherwise, you won’t be able to select a
cell.
Counting selected cells
You may create a macro that works with the selected range of cells. You can 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 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