VBA Beginner - Confused, please help!

[SeanL]

I’m new to VBA and I’m having a few difficulties getting my head around some of the concepts (I have no other programming experience). I am using Excel 2002 Bible by Brian Underdahl and John Walkenbach as a reference. One example given in the book is as follows:

MsgBox ActiveSheet.Comments(1).Parent.Address

The explanation given is: Comments(1) returns the first Comment object in the Comments collection. The Parent property of the Comment object returns its container, which is a Range object. The message box displays the Address property of the Range.

It then goes on to say that the fact that a property can return an object is an important concept in VBA programming.

I’m having difficulty understanding this concept – can anybody explain it to me? Maybe the book I’m using is not the best available, especially for getting to grips with the basic fundamentals? Can anybody recommend a better book for beginners?

 

[amigos]

Lets try Irish way

Let say you go into Pub, than you see Pints (they are in Drawers), you pick up one of them (the first one you see) and you want to say where they are stored:

Pub.Pints(1).Parent.Address

"Pub" is main object - it stores everything - pints, tables, beer etc

"Pints" is collection of pints that are in Pub, but to let Excel know which Pint you want you have to use it's name or index number - that's why you say Pints(1) - it means first pint in collection of Pints

Than Pint have a Property called "Parent" - which is place where it belongs (or is stored, like comment is "stored" in Cell) - lets say "Drawer"

And than you ask for Address of that place (Drawer) - let's say it is "top Drawer"

So result of “Pub.Pints(1).Parent.Address” would be “Top drawer” and as you can see property (Address property of object Drawer ) gave you another object which is “Top Drawer”



I hope that makes any sens and helps you

 

[Andrew Poulsom]

In VBA objects are arranged in a hierarchy, and each object can act as a container for other objects. When you want to drill down through the hierarchy, you separate each object using the dot separator, eg:

Worksheets("Sheet1").Range("A1").Font.Size = 10

In that statement the first 3 items are objects and the last is a Property.

But the way VBA resolves the statement is to use the property of the object which returns an object with the same name. So in:

Worksheets("Sheet1")

VBA uses the Worksheets property of the Excel Application object to return the Worksheet object, and in:

Worksheets("Sheet1").Range("A1")

VBA uses the Range property of the Worksheet object to return the Range object, and in:

Range("A1").Font

VBA uses the Font property of the Range object to return the Font object.

In Object model speak Objects are Classes of the Excel Application. And the Members (Properties) of those Classes are used to reference a Class with the same name.

Of course all this happens behind the scenes so all you need to worry about is getting the hierarchical order right.

Keep at it with Walkenbach's book. I think it's well written and user friendly. Grasping the object model is probably the hardest part of learning VBA.

 

[SeanL]

Andrew / Amigos,

Thanks for the help - makes things much clearer. It was the part about a property returning an object that had confused me.

I find Walkenbach’s book very good in general, though the explanation of one or two concepts could be phrased slightly better.

Amigos: The pints analogy is a good one but one thing has me baffled: I’ve never been to a pub where pints are stored in drawers!!! Anyway, I get the p(o)int!! CHEERS!

 

[amigos]

They were drawers in washing machine

 

[SeanL]

...with full pints of Guinness??? - so that's where the frothy head comes from !!!!

Thanks again.