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

Clayman wrote:
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...

Worksheet is a single worksheet-object.
Worksheets is a collection of Worksheet-objects.

HTH, CoRrRan

x=ActiveWorkbook.Worksheets("Sheet1").Range("A1")
msgbox x
assumes you have a sheet named sheet1
to get the 1st one try
x=ActiveWorkbook.Sheet1.Range("A1")

--
Don Guillett
SalesAid Software

"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

Worksheets is a collection. Worksheet is a type of object

to refer to the first sheet in the worksheets collection

ActiveWorkbook.Worksheets(1).Range("A1").value

Then you don't have to know the name.

--
Regards,
Tom Ogilvy



"Clayman" wrote:

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

Cool. Worksheets(1) fixed my problem.

I need to buy some sorta VBA for Dummies. I've never done visual programming
before this job. It's a far cry from the old stuff I'm used to.

Thanks, Mr. Ogilvy, for all the help you've provided so far on this project
(and on the upcoming one, as well!)
--
Adios,
Clay Harryman


"Tom Ogilvy" wrote:

Worksheets is a collection. Worksheet is a type of object

to refer to the first sheet in the worksheets collection

ActiveWorkbook.Worksheets(1).Range("A1").value

Then you don't have to know the name.

--
Regards,
Tom Ogilvy



"Clayman" wrote:

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

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

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

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

OK - that makes a lot of sense. Thank you so much for your time and effort on
this subject.

There's quite a bit to absorb, but it looks as if you've answered my
questions. Learning all this object-oriented stuff is not easy for an old
linear-programmer.
--
Adios,
Clay Harryman


"Chip Pearson" wrote:

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