Excel

Get sheet index number from cell reference (Function)

Ease of Use

Easy

Version tested with

2000, 2002, 2003 

Submitted by:

Zack Barresse

Description:

This function will return a sheet index number for a given reference. If no reference is given, the assumption is for the sheet the formula was entered on. 

Discussion:

The need for the sheets index number may arise, this is an easy way to tell. This would be helpful if you needed to always index the next sheet no matter what sheet it was. Then you could use, "=SheetNum()+1". This may be helpful for indexing chronological sheets, possibly monthly tabs. 

Code:

instructions for use

			

Option Explicit Function SheetNum(Optional celRef As Range) As Long With Application.Caller.Parent If celRef Is Nothing Then SheetNum = .Index Else SheetNum = celRef.Parent.Index End If End With 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 "=SheetNum()"
  4. Enter a reference inside the parentheses (ref. can be a cell on another sheet).
 

Sample File:

SheetNumEx.zip 6.75KB 

Approved by mdmackillop


This entry has been viewed 201 times.

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