Part VI: Automating Excel
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 identi-
fies the type of object selected:
Sub SelectionType()
MsgBox TypeName(Selection)
End Sub
A workbook that contains this macro is available on this book’s website at http://www.wiley.com/go/
excel2019bible. The file is named selection type.xlsm. For objects that let you enter text,
the macro won’t work in Edit mode. If you click the button and nothing happens, press the Esc key to
exit Edit mode.
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 method, which returns a collection of all Range objects in
the selection. The Count property returns the number of objects that are in the collection.
