VBA Excel Snippets

Is the selection a range?

Often, you want to know if an Excel object (usually the selection) is of the Range type. There are many ways to check this; here’s one that also works even if the Excel type library isn’t available.

Public Function IsObjectOfTypeRange(obj As Object) As Boolean
    On Error GoTo ExitError
        IsObjectOfTypeRange = (StrComp(TypeName(obj), "Range", vbTextCompare) = 0)
    Exit Function
ExitError:
End Function

Is the selection a single cell?

There are many occasions when you want to know if the selection is a single cell. Often, even a merged area—where multiple cells are combined—could count as a single cell for your purposes. The following code handles both cases.
(Make sure to check beforehand that the selection is a range.)

Public Function IsRangeSingleCell(TheRange As Range, AllMergedHandleAsSingleCell As Boolean) As Boolean
    On Error GoTo ExitError
    If IsNull(TheRange.MergeCells) Then
        Exit Function
    Else
        If Not TheRange.MergeCells Then If TheRange.Cells.Count > 1 Then Exit Function
    End If
    If TheRange.Cells.Count > 1 And Not AllMergedHandleAsSingleCell Then Exit Function
    IsRangeSingleCell = True
    Exit Function
ExitError:
End Function

Cell Count of a Range

When getting the cell count of a Range, there are two pitfalls. First, if the count exceeds the maximum value of a Long, use the CountLarge property. However, only 64‑bit Excel supports the LongLong type, so we use Variant to ensure compatibility with 32‑bit Excel.

Second, a Range can consist of multiple areas, so we must account for that. The following function handles both scenarios.

Public Function GetCellCountOfRange(TheRange As Range) As Variant
    Dim areaCounter As Long
    On Error GoTo ExitError
    With TheRange
        For areaCounter = 1 To .Areas.Count
            GetCellCountOfRange = GetCellCountOfRange + TheRange.Areas(areaCounter).Cells.CountLarge
        Next areaCounter
    End With
    Exit Function
ExitError:
End Function