Sheet#. vs Sheets(" vs WorkSheets("

[SensualCarrots]

I've been searching the net, but am having a hard time finding an answer. I'd like to understand this before continuing my work.

I read that the difference between Sheets(" and WorkSheets(" is that in older versions of excel, there are different types of sheets, and that for today's standards, we should use WorkSheets("

However, I'm not clear when defining a variable, which of the following I should use when using a standard module to reference a range or object on a sheet.

Dim ws as Sheet1
Dim ws as Sheets
Dim ws as Worksheet
Dim ws as Worksheets


Set ws = Sheets("Sheet1") 'This is the one I read not to use
Set ws = Worksheet("Sheet1")
Set ws = Worksheets("Sheet1")

Which of these is the best to use, and how do they correlate with eacth other? What's the difference between the singular and plural version of Worksheet?

Also, I was trying to call the value of a spin button on a worksheet. It was on Sheet3, labeled "Price Builder". When I use

Dim ws as Sheet3
ws.SpinButton1.Value

or

Sheet3.Spinbutton1.Value

it works perfectly. However, if I try to use any of the combinations of:

Sheets("Price Builder").SpinButton1.Value
Worksheets("Price Builder").SpinButton1.Value
Worksheet("Price Builder").SpinButton1.Value

...then I get an error message.

Is there an easy explanation for all of this? Or is it more complicated than that?

 

[glfiedler]

Sheets object is a collection of all the Chart and Worksheets in the Workbook.
Worksheets object is a collection of all the Worksheets in the Workbook.
Worksheet is a specific sheet in the Worksheet collection.
Each of these objects have specific properties and methods.

 

[glfiedler]

Go to this link
https://docs.microsoft.com/en-us/office/vba/api/excel.Sheets
Once there you can scroll down the left sidebar to Worksheets etc.

 

[SensualCarrots]

Thanks, but I really couldn't find what I was looking for. For example, it says...

Use Sheets (index), where index is the sheet name or index number, to return a single Chart or Worksheet object. The following example activates the sheet named Sheet1

Sheets"Sheet1".Activate

This works no problem for activate, range, cells, etc. However in my workbook, i tried to use a macro in a public module to change the value of a spinbutton, which we'll call SpinButton1. The name of the Sheet is Sheet3 with a description of Price Builder. So when I write...

    If Sheets("Price Builder").Range("D39") = True Then Sheets("Price Builder").SpinButton1.Value = 0

then I get an error, but if I write...

    If Sheets("Price Builder").Range("D39") = True Then Sheet3.SpinButton1.Value = 0

then it works fine. There is obviously a difference in how you reference an object vs a range, and that's what I'm looking for more information on. In my workbook, I resorted to simply using Sheet3. vs Sheet("Price Builder") in all alreas. The only problem is that I can dim Sheet("Price Builder") and get it to work, but couldn't find a way to dim Sheet3. I tried every combination I could think of (dim ws as sheet3, dim ws as range, dim ws as worksheet & set ws = sheet3, ws = sheet3, etc.) and could not get it to work. Any ideas?

 

[mikerickson]

You might like this
https://www.mrexcel.com/forum/excel...ksheets-info-reference-loop-add-etcetera.html

 

[Norie]

You don't really need to declare Sheet3, that's already a reference to a worksheet object.

If you did want to use a variable to refer to Sheet3 you would declare it as Sheet or Worksheet.

As for referring to controls on worksheets, it really depends on which type of control, Forms or ActiveX.

 

[RoryA]

What was the actual error message?