View Single Post
  #4 (permalink)  
Old September 20th, 2006, 04:24 PM
woodyz woodyz is offline
Friend of Wrox
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts

Quote: if Add is a method then how can we use the .(DOT) operator after the function name and one thing more that there is no parenthesis here too.
This is becuase the Add method has a return type of Workbook, which is an object with methods and properties that are accessible using the dot notation.

Book.Add.Worksheets - Is an object refering to the Worksheets collection object of the Workbook just added to the Workbooks

As I am getting in this line you want to say that worksheets is the class..
So if it is class then how a class can pass the parameter..
as it is passing like worksheets(1)
Okay - in that line of code the Worksheets is a collection class that has a default method of "Item".
This is another way you could write the same line:

Set Wsheet = Book.Add.Worksheets.Item(1)

This should be a little clearer - the parenthesis are actually for passing the index parameter to the Item method. You can also pass in a Key, rather than the index most of the time with collections in Visual Basic. I assume it is the same here with Excel.

Restated: Worksheets is a class that is a collection of Worksheet objects. "Item" is a method of the Worksheets class that allows you to access the Worksheet items in the collection. A class can have a method marked as the Default method which is the method that will be used on that class when the programmer does not use a ".Method" notation. The purpose of this is to make it a little more natural to use some classes - in this case, it is a collection class, and perhaps it feels a little more natural to access its items with "Worksheets(1)" than with "Worksheets.Item(1)". It is up to the developer using the object to decide what they like best.

Make better sense now?

I suggest that if you are working with VB6, that you write some simple classes of your own to start testing these ideas. If you have a class with a method that returns an object of another class you will start to see how you can chain together method calls using the dot notation. Learning to write classes will speed up your understanding of how to use classes. When properly done, writing classes in your programs makes things a lot easier - it is just that at first it seems more complicated and difficult... but once you get a feel for what it is all about, you might find it simplifies the programs you write.

Further, learning to write a collection class is very useful. This is a bit more complicated, but there is lots of info on the internet on how to do this, and most of the books with this info in it are now extremely cheap to buy on or other internet sites.

I hope this helps.

Woody Z