How to handle them both?

[prabha_friend]

I have sheetnames stored in a text field.

sample data: (assume the field name as SheetName)
1 (considered as an index)
MySheet (considered as a Name)

How to handle them both in a single statement:
vartype(SampleData!SheetName) returns 'Text' as Datatype for all values.

How to consider the numbers as indexes?

 

[llkhoutx]

Assume the index is 1, or append it to the return value.

 

[The_Doc_Man]

Typically, collection syntax allows you to specify things two ways:

Worksheets - where n is an integer - is a reference to sheet n - but NOT necessarily to SHEETn (see discussion below).

Worksheets("name") - where "name" is a legit spreadsheet name - is a reference to the sheet named "name"

You can also enumerate the sheets on a loop from 0 to Worksheets.Count -1, or something like that, then do Worksheet(number).Name to get the name of the worksheet. The only "gotcha" is that when using collection syntax, that is zero-based, from 0 to count-1. So if you have 10 sheets in the workbook, they are indexes 0-9 even though named SHEET1 through SHEET10.

The problem with the syntax you used is that you must remember: Excel, being the WONDERFUL product that it is (pardon me while I wipe off some of the excess irony), always names its sheets SHEETn as a text string ending with a number, unless you happen to rename the sheet to something else. So a default spreadsheet doesn't have a numbered sheet. They are ALL named as a mixed alphanumeric item.

 

[vbaInet]

As a side note, following The_Doc_Man's advice, remember to be explicit with your referencing, i.e. TheWorkbook.Worksheets("name") or TheWorkbook.Worksheets