Clay,

"Worksheet" in and of itself doesn't refer to any particular existing
sheet -- until it is Set to an existing worksheet, it is Nothing, indicating
that it doesn't refer to anything at all. It is a object data type to which
you can assign an existing worksheet, or the result of the Add method of the
Worksheets object.

Dim WS As Worksheet
Set WS = ThisWorkbook.Worksheets("Sheet1")
' or perhaps
Set WS = ActiveSheet
' or
Set WS = Worksheets.Add()

Workbook.Worksheet.Range(),

Similarly, "Workbook" is just a data type that doesn't refer to any workbook
in particular. You can Set a variable declared As Workbook to a particular
workbook. E.g.,

Dim WB As Workbook
Set WB = Workbooks("Book1.xls")

Until you Set WB to an existing workbook, it is Nothing and cannot be used
for anything meaningful.

For both Worksheet and Workbook, you can use Active* to specify the
worksheet or workbook that is currently active in Excel. E.g.,

Dim WB As Workbook
Dim WS As Worksheet
Set WB = ActiveWorkbook
Set WS = WB.ActiveSheet

For Workbook objects, there is also the ThisWorkbook object which always
refers to the workbook containing that code, regardless of what workbook
happens to be active in Excel.,

Dim WB As Workbook
Set WB = ThisWorkbook

Thus, you can use
ActiveWorkbook.ActiveSheet.Range("A1").Value = 123

To read or write the cell that is active on the worksheet that is active in
the workbook that is active, you can use simply

Dim V As Variant
V = ActiveCell.Value
' or
ActiveCell.Value = 1234

If you are writing code that is in the ThisWorkbook module or one of the
Worksheet code modules, you can use the "Me" object that will always refer
to he object in which it occurs. For example,

' In ThisWorkbook code module
Private Sub Workbook_Open()
Debug.Print Me.Name
End Sub

' In a Sheet's code module:
Private Sub Worksheet_Activate()
Debug.Print Me.Name
End Sub

"Me" is a reserved word in VBA and always refers to its containing class
instance. This includes not just the ThisWorkbook module and the Sheet
modules, but also Class modules and UserForm code modules. You cannot user
"Me" is regular code modules.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"Clayman" wrote in message
...
So, is it possible to access a single worksheet via Worksheet? I've
resigned
myself to always using Worksheets() and defining the sheet I need in the
collection (I guess).

There really wouldn't be a need to address something like
Workbook.Worksheet.Range(), would there? Since the workbook and worksheet
need to be defined for this context, you would need to use Workbooks and
Worksheets, right?
--
Adios,
Clay Harryman


"Chip Pearson" wrote:

A Worksheet is a single sheet (tab). Worksheets is a collection of all
worksheet objects in a workbook. Generally speaking, if a object name is
plural (e.g., "Worksheets", "Workbooks", "Hyperlinks"), it is a
collection
of zero or more objects of the type having the singular form of the word
("Worksheet", "Workbook", "Hyperlink"). The major exception to this rule
is
that "Cells" is not composed of an object named "Cell".

Just as aside, there are two collections for accessing sheets in a
workbook.
"Sheets" contains all types of sheets: Worksheet, Chart, Module, etc).
"Worksheets" contains only worksheets, not charts or other types of
sheets.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"Clayman" wrote in message
...
What's the difference? When do I use Worksheet? When do I use
Worksheets?

The reason I'm asking is that I'm trying to find the value in cell A1
of
the
first sheet of a workbook. This sheet may appear by different names.
I've
successfully called this sheet before using
ActiveWorkbook.Worksheets("Sheet1").Range("A1").va lue. This time, I'm
getting
a "Subscript out of Range" error.

Yeah - I wanna be a Geico Caveman...
--
Adios,
Clay Harryman