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:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
 

Test the code:

  1. Install the code.
  2. Save workbook.
  3. In a blank cell, enter "=Sheet(1)"
  4. 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.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express