|
|
|
|
|
|
Excel
|
Get Sheet Name from Sheet Index
|
|
Ease of Use
|
Easy
|
Version tested with
|
2000, 2002, 2003
|
Submitted by:
|
Zack Barresse
|
Description:
|
When using a formula and you need to refer to a sheet position, this can be rather difficult without using VBA. This function will allow you to use a sheet index position to return the string name of it.
|
Discussion:
|
This function coupled with the INDIRECT function can be used for looking on such sheets that only the index position is known. This is done in VBA with ease, but can be rather difficult with standard worksheet functions. If you will always be referencing the same sheet in order (e.g. always reference the second sheet in a workbook) this may help.
|
Code:
|
instructions for use
|
Option Explicit
Public Function Sheet(wsIndex As Long) As String
Application.Volatile = True
Sheet = Worksheets(wsIndex).Name
End Function
|
How to use:
|
- Copy above code.
- In Excel press Alt + F11 to enter the VBE.
- Press Ctrl + R to show the Project Explorer.
- Right-click desired file on left (in bold).
- Choose Insert -> Module.
- Paste code into the right pane.
- Press Alt + Q to close the VBE.
- Save workbook before any other changes.
|
Test the code:
|
- Install the code.
- Save workbook.
- In a blank cell, enter "=Sheet(1)"
- Replace the number 1 with the desired sheet index number.
|
Sample File:
|
SheetsEx.zip 6.96KB
|
Approved by mdmackillop
|
This entry has been viewed 391 times.
|
|