CODEWITHSUNDEEP
excelvba
jwil408
jwil408

Reputation: 23

Dynamic Sheet Index Number

I am trying to write a function that returns the index number of the sheet that the function is in.

I have this:

Function Sheet()    
Sheet = ThisWorkbook.ActiveSheet.Index    
End Function

I have multiple sheets in the workbook which all use the function but all need to refer to their own specific sheets. Using "activesheet" as soon as I update one sheet, all of the other sheets change their results to that new sheet instead of keeping to their own worksheet.

Is there a way to limit the function to the only the worksheet that it is on?

Upvotes: 2

Views: 901

Answers (3)

brettdj
brettdj

Reputation: 55672

You can also use range names (range formula) that utilise Excel's XLM langauage

  1. Define a range name, ShtNames, as =GET.WORKBOOK(1+0*RAND())
  2. Define a second range name, ShtName as =GET.CELL(32+0*NOW(),INDIRECT(SUBSTITUTE(REFTEXT(!$A$1),1,""),FALSE))

Then in the sheet where you want the Index,
=MATCH(ShtName,ShtNAmes,0)

Updates for Fadi's question

Rather than add the second name, you could use a formula as below (from any sheet) to return the sheet name to look up against AllSheets So say in A1 of sheet 1

=MATCH(RIGHT(CELL("filename",Sheet2!A1),LEN(CELL("filename",Sheet2!A1))-FIND("[",CELL("filename",Sheet2!A1))+1),AllSheets,0)

would return the sheet position of Sheet2

Upvotes: 1

Fadi
Fadi

Reputation: 3322

In Excel 2016, Excel 2013, Excel Online, Excel 2016 for Mac you can use Built-In Excel Function:

=SHEET()

Otherwise, use chris neilsen method or similar way from: Get sheet index number from cell reference (Function)

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

Suggesting: change the name of your UDF from Sheet to anything like MySheet.

if you want a formula to get the name of the worksheet (not the index) you can use this formula:

=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")

Upvotes: 2

chris neilsen
chris neilsen

Reputation: 53136

You can use Application.Caller to get a reference to the cell the UDF is in. From there is easy to get the index

Function Sheet()
    Dim rng as Range
    Set rng = Application.Caller
    Sheet = rng.Worksheet.Index
End Function

Please refer to the Help for full details of Caller and add appropriate error handling

Upvotes: 1

Related Questions