Excel Guides

Detecting Types of Sheets in VBA

There are many ways to detect the type of sheet in VBA. The most common way is to use the TypeName function. This function will return the name of the type of object passed to it. For example, if you pass it a worksheet object, it will return "Worksheet".

Another way to detect the type of sheet is to use the VBA Enum type XlSheetType. This enumeration has values for all of the different types of sheets in Excel. You can use this enumeration to compare against the Type property of a Worksheet object.

Here is some example code that shows how to use these methods:

Sub DetectSheetType()

    Dim ws As Worksheet

    'Loop through all sheets in the active workbook
    For Each ws In ActiveWorkbook.Sheets

        'Use the TypeName function
        Debug.Print TypeName(ws)

        'Use the VBA Enum type XlSheetType
        Select Case ws.Type

            Case xlChart: Debug.Print "Chart Sheet"
            Case xlDialogSheet: Debug.Print "Dialog Sheet"
            Case xlMacroSheet: Debug.Print "Macro Sheet"
            Case xlWorksheet: Debug.Print "Worksheet"

        End Select

    Next ws
    
End Sub

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.