asked on

How do we get worksheet index based on Worksheet name

I am getting trouble to read sheet name in the workbook.

Atleast if i get the index, then and there i could able to read the sheet based on worksheet name.

Let us say Example...

I am having multiple sheet like (A,B,C,D,E,F,G,H,etc,).

Suppose if i give "D", then it should return "3" (4th index) (0 to 3)..

how to get this index in c#.net or linq C#

Saqib Husain

You can loop through all sheets and compare the names with the selected.

Daniel Pineault

How about

Sheets("YourSheetName").Index

Open in new window

Gani tpt

ASKER

Thanks!....

i tried below code...bit not working...
sheets("YourSheetName").Index ==> not working.

Roy Cox

Have you actually used the sheet name that you want?

e.g. the below code works perfectly for a sheet named Staff

Option Explicit

Sub GetIndex()
MsgBox Sheets("Staff").Index
End Sub

aikimark

bit not working...

1. How are you using the value returned by the .Index method?
2. The Excel objects are 1 origin, not 0 origin

Daniel Pineault

Sheets("YourSheetName").Index

Open in new window

Is the proper code to use, then again, you should simply be able to use

Sheets("YourSheetName")

Open in new window

to work with a sheet.

I think you're going to have to show us some of your code and tells us exactly what error message(s) you receive for us to be better able to guide you.

Martin Liss

Perhaps this.

Function Index(strName As String) As Integer
Dim varSheetNames As Variant
Dim lngIndex As Long

varSheetNames = Array("A", "B", "C", "D", "E", "F", "G", "H")

Index = -1

For lngIndex = 0 To UBound(varSheetNames)
    If varSheetNames(lngIndex) = strName Then
        Index = lngIndex
        Exit Function
    End If
Next
End Function

Open in new window

Usage: If not found it will return -1

Sub test()
    MsgBox Index("B")
End Sub

Open in new window

aikimark

I'd advise against using a method/property name as the name of your routine.

Daniel Pineault

aikimark has a very valid point there. Index is a Reserved word and shouldn't be used to name procedures, variables, ... to avoid potential issues with Excel. Best rename the procedure to something else say, MyIndex or GetIndex, ...

Martin Liss

You guys are absolutely right and I should have known better.

Function SheetIndex(strName As String) As Integer
Dim varSheetNames As Variant
Dim lngIndex As Long

varSheetNames = Array("A", "B", "C", "D", "E", "F", "G", "H")

SheetIndex = -1

For lngIndex = 0 To UBound(varSheetNames)
    If varSheetNames(lngIndex) = strName Then
        SheetIndex = lngIndex
        Exit Function
    End If
Next
End Function

Open in new window

Usage: If not found it will return -1

Sub test()
    MsgBox SheetIndex("B")
End Sub

Open in new window

Gustav Brock

To lookup the index of a worksheet with an exact name, use:

Index = ThisWorkbook.Worksheets("Exact Name").Index

Open in new window

Of course, if more workbooks are open, use a WorkBook object.

Norie

Gani tpt

If you are having trouble referring to sheets by name are you sure using their index will help?

Also, if you are comparing sheets between 2 workbooks it's not guaranteed that the index of a sheet in one workbook will be the same for the corresponding worksheet in the other workbook.

Daniel Pineault

Gustav,

Gani tpt stated that such code isn't working. I've requested to see the procedure in question and share with us what happens, any error code/messages.

Gustav Brock

The error is probably, that the workbook object isn't referred.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.