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

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?
brothertruffle880Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
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.
Wayne Taylor (webtubbs)Commented:
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 LeadCommented:
>>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.
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
Saqib Husain, SyedEngineerCommented:
sheet1. brings up the intellisense whereas worksheets(1). does not
Roy CoxGroup Finance ManagerCommented:
This article might help

Sheets in VBA

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brothertruffle880Author Commented:
Great insight.  Thanks to all!
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.