Functions For Worksheets

This page describes VBA functions to provide information about worksheets.

Introduction

Excel doesn't provide any built-in functions for getting information about the worksheets in a workbook or workbooks open in the application. The CELL function can provide a bit of information, but this function returns a text string that is difficult to parse with formula text functions. Excel doesn't provide any method for relative sheet references, that is, referring to a sheet before or after the sheet into which a formula is entered. The VBA functions on this page attempt to remedy these deficiencies. The functions provided here and in the downloadable module file are listed below.

• SheetCount returns the count of worksheets in a workbook.
• SheetExists tests whether a worksheet exists.
• SheetIndex returns the Index number (position) of a worksheet.
• SheetName returns the name of a worksheet.
• SheetNames returns a list of all worksheets in a workbook.
• SheetNameOffset returns the name of a worksheet that is some number of sheets before or after another worksheet.
• WorkbookCount returns the count of open workbooks.
• WorkbookName returns the Name or FullName of a workbook.
• WorkbookNames returns a list of workbook names.
• WorkbookPath returns the folder path of a workbook.

Many of the functions take an optional parameter R as a Range that is used to identify a worksheet or workbook. This parameter is required if you are not calling the function directly from a worksheet cell. The parameter is optional if the function is called from a worksheet cell. If omitted, Application.Caller.Workseet.Parent is used to get a reference to the workbook.

These functions are described and the code listed below.

You can download the file with all the example code on this page.

SheetCount

The SheetCount function returns the number of worksheets in a workbook.

    Public Function SheetCount(Optional R As Range, Optional VisibleOnly As Boolean) As Long
    

If R is present, the sheet count of the workbook containing R is returned. If R is omitted, the sheet count of the workbook from which the function was called is returend. If VisibleOnly is True, only visible worksheets are counted. If VisibleOnly if False or omitted, all sheets are counted.

    Public Function SheetCount(Optional R As Range, Optional VisibleOnly As Boolean) As Long
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' SheetCount
    ' Returns the count of worksheets. If R is present, the count
    ' of worksheets in the workbook containing R is returned. If R
    ' is omitted, the count of worksheets in the workbook calling
    ' the function is returned. If VisibleOnly is True, only visible
    ' worksheets are counted. If VisibleOnly is False, the count
    ' of all worksheets is returned.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim WB As Workbook
        Dim WS As Worksheet
        Dim N As Long
        If R Is Nothing Then
            Set WB = Application.Caller.Worksheet.Parent
        Else
            Set WB = R.Worksheet.Parent
        End If
        For Each WS In WB.Worksheets
            If VisibleOnly = False Or _
                (VisibleOnly = True And WS.Visible = xlSheetVisible) Then
               N = N + 1
            End If
        Next WS
        SheetCount = N
    End Function
    

SheetExists

The SheetExists function returns True or False indicating whether a worksheet exists in a workbook.

    Public Function SheetExists(SheetName As String, Optional R As Range) As Boolean
    

SheetName is the worksheet name to test. If R is present, the function looks in the workbook containing R to find the worksheet. If R is omitted, the code returns looks in the workbook containing the cell from which the function was called to find the worksheet.

    Public Function SheetExists(SheetName As String, Optional R As Range) As Boolean
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' SheetExists
    ' This tests whether SheetName exists in a workbook. If R is
    ' present, the workbook containing R is used. If R is omitted,
    ' Application.Caller.Worksheet.Parent is used.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim WS As Worksheet
        Dim WB As Workbook
        If R Is Nothing Then
            Set WB = Application.Caller.Worksheet.Parent
        Else
            Set WB = R.Worksheet.Parent
        End If
        On Error Resume Next
        Err.Clear
        Set WS = WB.Worksheets(SheetName)
        
        If Err.Number = 0 Then
            SheetExists = True
        Else
            SheetExists = False
        End If
    End Function

SheetIndex

The SheetIndex function returns the Index (position) of a worksheet in a workbook.

    Public Function SheetIndex(Optional R As Range) As Long
    

If R is present, the Index of the worksheet containing R is returned. If R is omitted, the Index of the worksheet from which the function was called is returned.

    Public Function SheetIndex(Optional R As Range) As Long
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' SheetIndex
    ' This returns the Index of the worksheet containing R. If R
    ' is omitted, the Index of the worksheet from which the function
    ' is called is returned.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If R Is Nothing Then
        SheetIndex = Application.Caller.Worksheet.Index
    Else
        SheetIndex = R.Worksheet.Index
    End If
    End Function
    

SheetName

The SheetName function returns the name of a worksheet in a workbook.

    Public Function SheetName(Optional R As Range) As String
    

If R is present, the name of the worksheet containing R is returned. If R is omitted, the name of the worksheet from which the function was called is returned.

    Public Function SheetName(Optional R As Range) As String
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' SheetName
    ' This returns a worksheet name. If R is present, it returns
    ' the name of the worksheet containing R. If R is omitted,
    ' it returns Application.Caller.Worksheet.Name.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If R Is Nothing Then
        SheetName = Application.Caller.Worksheet.Name
    Else
        SheetName = R.Worksheet.Name
    End If
    End Function
    

SheetNames

The SheetNames function returns an array containing the names of the worksheets in a workbook.

    Public Function SheetNames(Optional R As Range, _
        Optional VisibleOnly As Boolean = False) As String()
    

If R is present, the names of the worksheets in the workbook that contains R are returned. If R is omitted, the workbook from which the function was called is used. If VisibleOnly is True, only the names of visible sheets are returned. If VisibleOnly is False, all sheet names are returned. This function returns an array. If you want the results listed in a single row spanning several columns, select those cells, type the formula call, and press CTRL SHIFT ENTER. If you want the names in one column spanning several rows, select the cells, use the formula =TRANSPOSE(SheetNames()) and press CTRL SHIFT ENTER rather than just ENTER. See the array formulas page for more information about array formulas. If you enter the formula in a range with more than one row and more than one column, the result is #REF!.

    Public Function SheetNames(Optional R As Range, _
            Optional VisibleOnly As Boolean = False) As String()
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' SheetNames
    ' This returns an array of sheet names in the workbook containing
    ' the range reference R. If R is omitted, the calling workbook
    ' is used. If VisibleOnly is True, only names of visible worksheets
    ' are returned. If VisibleOnly is False, all worksheet names are returned.
    ' The result is an array of sheet names. The size of the array
    ' is the greater of Application.Caller.Cells.Count and
    ' Worksheets.Count.
    ' To display the list in one row spanning several columns, array enter
    ' =SheetNames() into the range of cells. To display the list in
    ' one column spanning several rows, array enter =TRANSPOSE(SheetNames())
    ' into the cells.
    ' If the range into which the formula is entered contains more than
    ' one row AND more than one column, #REF is returned.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim SS() As String
        Dim WB As Workbook
        Dim WS As Worksheet
        Dim N As Long
   
        If Application.Caller.Rows.Count > 1 And _
            Application.Caller.Columns.Count > 1 Then
                SheetNames = CVErr(xlErrRef)
                Exit Function
        End If
        If R Is Nothing Then
            Set WB = Application.Caller.Worksheet.Parent
        Else
            Set WB = R.Worksheet.Parent
        End If
        N = Application.WorksheetFunction.Max( _
            WB.Worksheets.Count, Application.Caller.Cells.Count)
        ReDim SS(1 To N)
        N = 0
        For Each WS In WB.Worksheets
            If VisibleOnly = False Or _
                (VisibleOnly = True And WS.Visible = xlSheetVisible) Then
                N = N + 1
                SS(N) = WS.Name
            End If
        Next WS
        SheetNames = SS
    End Function    
    

SheetNameOffset

The SheetNameOffset function the name of a workbook that is some number of sheets before or after a specific sheet.

    Public Function SheetNameOffset(N As Long, Optional R As Range, _
        Optional Wrap As Boolean) As Variant
    

If R is present, the base worksheet is the worksheet containing R. If R is omitted, the base worksheet is the worksheet from which the function was called. N indicates the relative position of the worksheet, relative to the base worksheet, whose name is to be returned. This parameter is 0-based, so N = 0 returns the name of the base worksheet. If N is negative, the target sheet is to the left of the base worksheet. E.g, N = -1 is the sheet before the base worksheet. If N is positive, the target worksheet is to the right of the base worksheet. E.g., N = 1 is the worksheet after the base worksheet. If Wrap is False and N would reference a worksheet past the first or last sheet, the function returns #REF!. If Wrap is True and the first or last worksheet is encountered, the count wrap around to the last or first worksheet.

    Public Function SheetNameOffset(N As Long, Optional R As Range, _
    Optional Wrap As Boolean) As Variant
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' SheetNameOffset
    ' This returns the sheet name that is N sheets before or after
    ' a specified worksheet WS. If R is present, WS is the worksheet
    ' containing R. If R is omitted, WS is Application.Caller.Worksheet.
    ' The offset N is 0 based. SheetNameOffset(0) is WS itself. If
    ' N < 0, the returned sheet name is Abs(N) worksheets before WS. If
    ' N > 0, the returned sheet name is Abs(N) worksheets after WS. In
    ' either case, if N is greater than the offset to the first or
    ' last sheet, the function returns #REF. If Wrap is True and the
    ' first sheet is greater than N, the search wraps to the last sheet.
    ' If Wrap is True and the last sheet is less than N, the search
    ' wraps to the first sheet.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim M As Long
    Dim WS As Worksheet
    Dim WB As Workbook
    
    If R Is Nothing Then
        Set WS = Application.Caller.Worksheet
    Else
        Set WS = R.Worksheet
    End If
    Set WB = WS.Parent
    If N = 0 Then
        SheetNameOffset = WS.Name
        Exit Function
    End If
    On Error Resume Next
    If N < 0 Then
        For M = -1 To N Step -1
            Set WS = WS.Previous
            If WS Is Nothing Then
                If Wrap = True Then
                    With WB.Worksheets
                        Set WS = .Item(.Count)
                    End With
                Else
                    SheetNameOffset = CVErr(xlErrRef)
                    Exit Function
                End If
            End If
        Next M
    Else
        For M = 1 To N
            Set WS = WS.Next
            If WS Is Nothing Then
                If Wrap = True Then
                    With WB.Worksheets
                        Set WS = .Item(1)
                    End With
                Else
                    SheetNameOffset = CVErr(xlErrRef)
                    Exit Function
                End If
            End If
        Next M
    End If
    SheetNameOffset = WS.Name
    End Function
    

WorkbookCount

The WorkbookCount function returns the number of open workbooks.

    Public Function WorkbookCount(Optional VisibleOnly As Boolean) As Long
    

If VisibleOnly, only visible workbooks are counted. If VisibleOnly is False or omitted, all workbooks are counted.

    Public Function WorkbookCount(Optional VisibleOnly As Boolean) As Long
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' WorkbookCount
    ' This returns the count of all open workbooks. If VisibleOnly
    ' is True, only visible workbooks are counted. If VisibleOnly is
    ' False, all workbooks are counted.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim WB As Workbook
    Dim N As Long
    For Each WB In Application.Workbooks
        If VisibleOnly = False Or (VisibleOnly = True And WB.Windows(1).Visible = True) Then
            N = N + 1
        End If
    Next WB
    WorkbookCount = N
    End Function
    

WorkbookName

The WorkbookName function returns the name of a workbook.

    Public Function WorkbookName(Optional R As Range, Optional FullName As Boolean) As String
    

If R is present, the name of the workbook containing R is returned. If R is omitted, the name of the workbook from which the function was called is returned. If FullName is True, the workbook's fully qualified file name is returned. If FullName is omitted or False, only the file name, with no folder qualification, is returned.

    Public Function WorkbookName(Optional R As Range, Optional FullName As Boolean) As String
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' WorkbookName
    ' This returns the name or full name of a workbook. If R is
    ' present, it returns the name of the workbook containing R.
    ' If R is omitted, it returns the name of the workbook from
    ' which it was called. If FullName is omitted or False, the
    ' Name is returned. If FullName is True, the FullName is returned.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If R Is Nothing Then
        If FullName = False Then
            WorkbookName = Application.Caller.Worksheet.Parent.Name
        Else
            WorkbookName = Application.Caller.Worksheet.Parent.FullName
        End If
    Else
        If FullName = False Then
            WorkbookName = R.Worksheet.Parent.Name
        Else
            WorkbookName = R.Worksheet.Parent.FullName
        End If
    End If
    End Function
    

WorkbookNames

The WorkbookNames function returns a list of all open workbooks.

    Public Function WorkbookNames(Optional FullName As Boolean, _
        Optional SavedOnly As Boolean, _
        Optional VisibleOnly As Boolean) As String()
   

If FullName is True, the workbooks' FullNames are returned. If FullName is False or omitted, only the workbooks' names, with no folder qualification, are returned. If SavedOnly is True, only workbooks that have been saved to disc (i.e., a workbook whose Path property is not an empty string) are returned. If VisibleOnly is True, only the names of workbooks that are visible are returned. If VisibleOnly if False or omitted, all workbook names are returned. This is an array formula, so you must select the range for the results, type the formula, and press CTRL SHIFT ENTER. See the array formulas page for more information. If entering the formula in a range that is one column spanning several rows, use TRANSPOSE to transpose the results. E.g., =TRANSPOSE(WorkbookNames()).

    Public Function WorkbookNames(Optional FullName As Boolean, _
        Optional SavedOnly As Boolean, _
        Optional VisibleOnly As Boolean) As String()
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' WorkbookNames
    ' This returns an array of strings containing workbook names.
    ' If FullName is True, the workbook's FullName is returned. If
    ' FullName is False, the workbook's Name is returned. If SavedOnly
    ' is True, only workbooks that have been saved to disc are returned.
    ' If SavedOnly is False, all workbook names are returned. If
    ' VisibleOnly is True, only the names of visible workbooks are
    ' returned. If VisibleOnly is False, all workbook names are
    ' returned.
    ' To display the list in one row spanning several columns, array enter
    ' =WorkbookNames() into the range of cells. To display the list in
    ' one column spanning several rows, array enter =TRANSPOSE(WorkbookNames())
    ' into the cells.
    ' If the range into which the formula is entered contains more than
    ' one row AND more than one column, #REF is returned.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim Arr() As String
    Dim N As Long
    Dim WB As Workbook
    
    If Application.Caller.Rows.Count > 1 And _
            Application.Caller.Columns.Count > 1 Then
        WorkbookNames = CVErr(xlErrRef)
        Exit Function
    End If
    
    N = Application.WorksheetFunction.Max( _
        Application.Workbooks.Count, Application.Caller.Cells.Count)
    ReDim Arr(1 To N)
    N = 0
    For Each WB In Application.Workbooks
        If SavedOnly = False Or (SavedOnly = True And Len(WB.Path) > 0) Then
            If VisibleOnly = False Or _
                (VisibleOnly = True And WB.Windows(1).Visible = True) Then
                N = N + 1
                If FullName = True Then
                    Arr(N) = WB.FullName
                Else
                    Arr(N) = WB.Name
                End If
            End If
        End If
    Next WB
    WorkbookNames = Arr
    End Function
    

WorkbookPath

The WorkbookPath function returns the path of a specified workbook.

    Public Function WorkbookPath(Optional R As Range) As String
    

If R is present, the path of the workbook containing R is returned. If R is omitted, the path of the workbook from which the function was called is returned.

    Public Function WorkbookPath(Optional R As Range) As String
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' WorkbookPath
    ' This returns the path of the workbook containing range R.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If R Is Nothing Then
        WorkbookPath = Application.Caller.Worksheet.Parent.Path
    Else
        WorkbookPath = R.Worksheet.Parent.Path
    End If
    End Function
    

You can download the file with all the example code on this page.

This page last updated: 12-February-2011.