sheets vs. sheet

[bill g]

In Excel2000, how come when you code something like this:

For i = 2 To Sheets.Count
If Sheets(i).Visible = True Then

If Sheets(i).Range("z79") < -1000 And Sheets(i).Range("d83") = "no"

you use Sheets(i) and not Sheet(i). You would think you are going through
each sheet not sheets. Just wondering why this is?

Thanks

[John Wilson]

Bill,

It's just an Excel naming convention.
"Sheet" are "Sheets"...."Sheets" is not "Sheet"

Some of them can be ambiguous...
Like "Workbooks.Open
Well.... I can only open 1 workbook at a time, so why the "s"???
There are plenty of other examples.

Anyway, for compatibility purposes, you should be using
"Worksheets" (with the "s") as opposed to "Sheets"

I've learned (the hard way) not to question why Excel wants things
formatted (syntax'd) in a specific way, but just to conform (as Excel
is very unforgiving if you leave out an "s" somewhere).

John

[Orlando Magalhães Filho]

Hi,

In VBA Sheets is a collection and Sheet is a property. From a collection you
can extract items from it, in Sheets you can extract Sheet with item
property.
The Item property returns a single object from a collection. The following
example sets the first sheet variable to a Sheet object that represents
sheet one.
Set firstSheet = Sheets.Item(1)
The Item property is the default property for most collections, so you can
write the same statement more concisely by omitting the Item keyword.
Set firstSheet = Sheets(1)

--
HTH

---
Orlando Magalhães Filho

(So that you get best and rapid solution and all may benefit from the
discussion, please reply within the newsgroup, not in email)

"bill g" <su...@worldnet.att.net> escreveu na mensagem
news:wxDC9.15469$vM1.1...@bgtnsc04-news.ops.worldnet.att.net...

[Chip Pearson]

> Some of them can be ambiguous...
> Like "Workbooks.Open
> Well.... I can only open 1 workbook at a time, so why the "s"???

The reason is that Open is a method of the Workbooks collection. There
is a class named Workbook, that represents a single workbook, but there
is no pre-defined (instantiated) object called Workbook. A Workbook
(singular) object refers to an existing, open workbook. The idea of
Workbook.Open makes no sense, but Workbook refers to a specific
workbook.

> I've learned (the hard way) not to question why Excel wants things
> formatted (syntax'd) in a specific way, but just to conform (as Excel
> is very unforgiving if you leave out an "s" somewhere).

Actually, the naming of Excel's objects is quite good, and has very
little ambiguity. The plural forms always refer to collections, whose
members are always instances of classes with the singular name. E.g.,
members of WorkbookS are are always Workbook objects.

You have to remember that the Excel object model provides hundreds of
classes, some of which are automatically created (instantiated) by Excel
itself. Workbooks is an example of this. You can declare a variable as
type Workbooks, but Excel already creates a variable named Workbooks
based on the class Workbooks. But code like

Dim WBS As Workbooks
Set WBS = Application.Workbooks

still works. Excel did the equivalent of the following for you:

Dim Workbooks As Workbooks
Set Workbooks = Application.Workbooks

When you use "Workbooks" in your code, you are using the variable
Workbooks (on the left side of the "As") not the class Workbooks (on the
right side of the "As").

> (as Excel
> is very unforgiving if you leave out an "s" somewhere).

As it should be. The plurals are objects based on a Collection object.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"John Wilson" <jwi...@optonline.net> wrote in message
news:3DDB08D1...@optonline.net...

[John Wilson]

Chip,

Thanks for the explanation (and mini course) on collections and
objects. Reminds me that I still have a lot to learn.

Re: the errant "s"'s and Excel being so unforgiving was more in
the way of venting my frustration with not knowing many of the
why's and wherefor's of the underlying engine behind Excel.
I'm better with the "s"'s but some of those triple quotes, commas
and apostrophies still drive me crazy. Again attributable to my
lack of experience.

Ambiguous was probably the wrong word to use. I do understand
that there's a method to the madness and a rhyme and reason
to it all (even those damned apostrophies). Sometimes human nature
has a tendency to take a great product (which I think Excel is) and
upon finding a flaw (or what is percieved to be flaw) knock the
whole thing in general. My apologies for being human in this instance.

I truly appreciate the criticism and the style in which you wrote it.
Someone in .functions could learn a lot from your style.

John

[Chip Pearson]

John,

I'm glad to help. I think sometimes there is a sense of presumption,
even arrogance, among developers like me. We live and breathe this
stuff, we wallow in it 8 hours a day, and we mistakenly assume that it
is intelligible and logical to those who have better things to do. Like
real work.

I try, when I can, to post "why" answers as well as "how" answers.
Programming is cryptic enough (and VB is perhaps the least cryptic of
all languages, save COBOL) but the subtleties of "object models" can be
down right mysterious.

When you start writing projects in which you have create you own object
models, you learn how they work, why they work, and how to (and how not
to) design them. Despite the tomes that have been written on the topic,
it remains as much an art as science. The differences between a "bad"
and "good" and "excellent" object model are as subtle and subjective as
what makes a "good" painting better than a "bad" painting. There are
rules to follow, but it is still art, not science. MS does a pretty good
job with their object models.

> I truly appreciate the criticism and the style in which you wrote it.
> Someone in .functions could learn a lot from your style.

My pleasure.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"John Wilson" <jwi...@optonline.net> wrote in message

news:3DDEC0DC...@optonline.net...

[Sean Bartleet]

Hi,

Here here, The why answers are invaluable to beginners like me.

Thank you for all the time you donate to people like us.

Regards

Sean Bartleet