Excel 2010 Bible

Part VI: Programming Excel with VBA

894

On the CD
A workbook that contains this macro is available on the companion CD-ROM. The file is named
selection type.xlsm.

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

Another way to approach this task is to define a custom function that returns TRUE if the selection

(represented by the sel argument) is a Range object, and FALSE otherwise. The following func-

tion does just that:

Function IsRange(sel) As Boolean

IsRange = False

If TypeName(sel) = “Range” Then IsRange = True

End Function

Here’s a more compact version of the IsRange function:

Function IsRange(sel) As Boolean

IsRange = (TypeName(sel) = “Range”)

End Function

If you enter the IsRange function in your module, you can rewrite the CheckSelection pro-

cedure as follows:

Sub CheckSelection()

If IsRange(Selection) Then

‘ ... [Other statements go here]

Else

MsgBox “Select a range.”

Exit Sub

End If

End Sub