This Page: www.cpearson.com/Excel/SheetNameFunctions.aspx

Last Updated: 06-Nov-2013

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 29-Jul-2017

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Search The Site:

Functions For Worksheets

This page describes VBA functions to provide information about worksheets.

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