Excel 2010 VBA - When to use sheet1.something versus worksheets(1).something

brothertruffle880
brothertruffle880 used Ask the Experts™
on
What is the difference between using sheet1 and using worksheets(1).
They both refer to individual sheets in a workbook.  
1. What are the features of each of these, and
2.  why use them?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Using the code name of a worksheet is handy because you can rename and reorder the worksheets at will and it will always refer to the correct sheet.

So this...

   Sheet1.Name

... will always return the name of that sheet regardless of it's position and displayed name, where as this...

  Worksheets(1).Name

...will return the name of the first worksheet in the workbook.
Likewise, this...

    Worksheets("Sheet1").Name will always return "Sheet1", except where the worksheet's name has been changed, in which case it will throw an error.
Ryan ChongSoftware Team Lead
Commented:
>>What are the features of each of these
basically, sheet1 and worksheets(1) refer to same object, hence all features should be the same.

>>why use them?
user preferences... other than that, we referring to a worksheet using collection (like: worksheets( i ) ) in case:

1. you're doing some looping logic to accomplish a task
2. you're not sure about the object name, but certainly we know its sequence in the collection.
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015
Commented:
A Worksheet is one type of Sheet.  Sheets can also be chart sheets and other types.

I personally use Sheets since that is all the sheets, not just the sheets that are worksheets.

To see what I mean:
1. open a workbook that has other types of sheets.
2. Press Alt-F11 to go to the VB Editor
3. press Ctrl-G to Go to the Immediate window
4. type:
?activeworkbook.workSheets.Count
and press ENTER
5. type:
?activeworkbook.Sheets.Count
and press ENTER
sheet1. brings up the intellisense whereas worksheets(1). does not
Group Finance Manager
Commented:
This article might help

Sheets in VBA

Author

Commented:
Great insight.  Thanks to all!
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
you're welcome ~ happy to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial