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. The CELL function can provided a bit of information,
but this function returns a text string that is difficult to parse. Excel doesn't provide
any method for relative sheet references, that is, refering 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 name exists.
- SheetIndex returns the Index number (position) of a worksheet.
- SheetName returns the name of the current worksheet or another worksheet referenced by a range parameter.
- 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.
These functions are described and the code listed below.
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
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
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 workbook containing R is used to find SheetName.
If R is omitted, the workbook from which the function was called is used to find
SheetName.
Public Function SheetExists(SheetName As String, Optional R As Range) As Boolean
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
Set WS = WB.Worksheets(SheetName)
Err.Clear
If Err.Number = 0 Then
SheetExists = True
Else
SheetExists = False
End If
End Function
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
If R Is Nothing Then
SheetIndex = Application.Caller.Worksheet.Index
Else
SheetIndex = R.Worksheet.Index
End If
End Function
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
If R Is Nothing Then
SheetName = Application.Caller.Worksheet.Name
Else
SheetName = R.Worksheet.Name
End If
End Function
The SheetNames function returns a list of the worksheet 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
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
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
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
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
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()
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
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
|
This page last updated: 12-February-2011. |