Listing Worksheets In VBA

This page describes VBA code to get a list of worksheets.

Introduction

You might find it useful to get a list of existing worksheet names. For example, you might want to allow the user to select a specific sheet for processing. This page describes a VBA Function that will return an array of worksheet names to a specific range on the worksheet. It also contains a function to be called by other VBA code.

The main function is called SheetNames and has the following declaration.

    Function SheetNames(Optional R As Range, _
        Optional FromIndex As Long = -1, _
        Optional ToIndex As Long = -1, _
        Optional VisibleOnly As Boolean = False) As String()
    

The Code

The code for SheetNames is below.

Function SheetNames(Optional R As Range, _
    Optional FromIndex As Long = -1, _
    Optional ToIndex As Long = -1, _
    Optional VisibleOnly As Boolean) As String()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SheetNames
' By Chip Pearson, 28-Sept-2012, chip@cpearson.com , www.cpearson.com
'
' This function returns a String array, each element of which is name
' of a worksheet in a workbook. If R is omitted, the workbook that
' contains the formula is used. If R is not omitted, the workbook
' associated with the range R is used. The function needs to be array entered into
' a range that is one row high spanning several columns or a single column
' spanning several rows. The size and orientation of the result array
' is sized to match the range from which the function was called. If the range
' has more than one column and more than one range, the worksheets are listed
' in the first column and repeated in subsequent columns.
'
' To use the function, select the range into which the results are to be
' returned, type =SheetNames(...) and press CTRL SHIFT ENTER instead of just ENTER.
' See http://www.cpearson/com/Excel/ArrayFormulas.aspx for much more
' information about array formulas. The formula
' must be array entered with CTRL SHIFT ENTER in order to work properly.
'
' If the range contains fewer cells that there are worksheets in the
' workbook, the final worksheet names are not returned. If the range
' into which the formula is entered contains more cells that there are
' worksheets, the excess elements of the array is filled with vbNullStrings,
' which will suppress the #N/A errors that would otherwise arise.
'
' You can restrict which worksheets are returned by using the FromIndex
' and ToIndex paramaters. FromIndex is the inclusive lower bound of the
' worksheets to return. Worksheets whose Index properties are less than
' FromIndex are not returned in the array. If FromIndex is < 0 or omitted,
' the enumeration begins with Worksheets(1). If present, the enumeration
' begins with Worksheet(FromIndex). ToIndex can be used to control the
' upper region of the array. If ToIndex is omitted or < 0, the numeration
' ends at the last sheet. If ToIndex is present, the enumeration ends at
' Worksheets(ToIndex). FromIndex and ToIndex are inclusive.
'
' If both ToIndex and FromIndex are omitted or both
' are < 0, all worksheets are returned. If ToIndex is less than FromIndex,
' no worksheets are returned. By default,
'
' By default, all worksheet names are returned, subject to the constraints
' of FromIndex and ToIndex. You can get a list of only visible worksheets
' by setting the VisibleOnly parameter to True. If True, only visible
' worksheets are returned in the array. If False or omitted, all worksheets
' are returned.
'
' This function uses Application.Caller to get the size and orientation
' of the output range. Therefore, it cannot be used by other VBA code.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim WS As Worksheet
    Dim Res() As String
    Dim CalledBy As Range
    Dim N As Long
    Dim UpperBound As Long
    Dim WB As Workbook
    Dim FromNdx As Long
    Dim ToNdx As Long
    Dim Cl As Long
    Dim Rw As Long
    Dim SheetNdx As Long
    Dim DoThisSheet As Boolean
    
    Set CalledBy = Application.Caller
    If R Is Nothing Then
        Set WB = CalledBy.Parent.Parent
    Else
        Set WB = R.Worksheet.Parent
    End If
    
    If FromIndex < 0 Then
        FromNdx = 1
    Else
        FromNdx = FromIndex
    End If
    If ToIndex < 0 Then
        ToNdx = WB.Worksheets.Count
    Else
        ToNdx = ToIndex
    End If
    
    If CalledBy.Rows.Count > 1 Then
        UpperBound = Application.WorksheetFunction.Max(CalledBy.Rows.Count, _
                ThisWorkbook.Worksheets.Count)
        ReDim Res(1 To UpperBound, 1 To 1)
    Else
        UpperBound = Application.WorksheetFunction.Max(CalledBy.Columns.Count, _
                ThisWorkbook.Worksheets.Count)
        ReDim Res(1 To 1, 1 To UpperBound)
    End If
    For Each WS In WB.Worksheets
        If WS.Index <= ToNdx And WS.Index >= FromNdx Then
            DoThisSheet = False
            If VisibleOnly = True Then
                 If WS.Visible = xlSheetVisible Then
                    DoThisSheet = True
                Else
                    DoThisSheet = False
                End If
            Else
                DoThisSheet = True
            End If
            
            If DoThisSheet = True Then
                N = N + 1
                If CalledBy.Rows.Count > 1 Then
                    Res(N, 1) = WS.Name
                Else
                    Res(1, N) = WS.Name
                End If
            End If
        End If
    Next WS
    
    SheetNames = Res
    End Function
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<< END CODE

    
 

     
Returning A Single Dimensional Array To Other Code
 

To return a single dimensional array of worksheet names, use the following code. The parameters here
have the same meaning as in SheetNames.
 
 
Function SheetNamesArray(Optional R As Range, _
    Optional ByVal FromIndex As Long = -1, _
    Optional ByVal ToIndex As Long = -1, _
    Optional VisibleOnly As Boolean) As String()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SheetNamesArray
' This function returns a single-dimensional array names
' according to the FromIndex, ToIndex, and VisibleOnly
' properties.
'
' You can restrict which worksheets are returned by using the FromIndex
' and ToIndex paramaters. FromIndex is the inclusive lower bound of the
' worksheets to return. Worksheets whose Index properties are less than
' FromIndex are not returned in the array. If FromIndex is < 0 or omitted,
' the enumeration begins with Worksheets(1). If present, the enumeration
' begins with Worksheet(FromIndex). ToIndex can be used to control the
' upper region of the array. If ToIndex is omitted or < 0, the numeration
' ends at the last sheet. If ToIndex is present, the enumeration ends at
' Worksheets(ToIndex). FromIndex and ToIndex are inclusive.
'
' If both ToIndex and FromIndex are omitted or both
' are < 0, all worksheets are returned. If ToIndex is less than FromIndex,
' no worksheets are returned. By default,
'
' By default, all worksheet names are returned, subject to the constraints
' of FromIndex and ToIndex. You can get a list of only visible worksheets
' by setting the VisibleOnly parameter to True. If True, only visible
' worksheets are returned in the array. If False or omitted, all worksheets
' are returned.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
Dim Res() As String
Dim WB As Workbook
Dim WS As Worksheet
Dim N As Long
Dim DoThisWS As Boolean
If Not R Is Nothing Then
    Set WB = R.Worksheet.Parent
Else
    Set WB = ThisWorkbook
End If
ReDim Res(1 To WB.Worksheets.Count)
For Each WS In WB.Worksheets
    DoThisWS = True
    If FromIndex > 0 Then
        If FromIndex < WS.Index Then
            DoThisWS = False
        End If
    End If
    If ToIndex > 0 Then
        If ToIndex > WS.Index Then
            DoThisWS = False
        End If
    End If
    If VisibleOnly = True Then
        If WS.Visible <> xlSheetVisible Then
            DoThisWS = False
        End If
    End If
    If DoThisWS = True Then
        N = N + 1
        Res(N) = WS.Name
    End If
    
Next WS
ReDim Preserve Res(1 To N)
SheetNamesArray = Res

End Function



    
    
    

       

        

            

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


    
    

        

            

            
This page last updated: 29-Sept-2012.