I'm not sure how you got to be so advanced without being familiar with OO concepts in general, and with the VBA editor's Watch window in particular. But then, I'm sort of self-taught (in the OO world anyway) and it's left some curious holes in my education; I still have a lot to figure out about recordsets, for example. So I guess I shouldn't be surprised if you're in the same boat about object-watching.
Let's look at a simpler object, one that has only 20 properties. I just created a scratch program:
Code:
Sub Main()
Set so = ThisWorkbook.Worksheets(1)
Set co = so.Range("A1")
Set fo = co.Font
Stop
End Sub
You can create that program in any workbook, even an empty one; it doesn't depend on any actual content. If you'll run it until it stops, and then set a watch on the object I named "fo" (for "font object"), you'll see fo show up in the Watch window with a boxed "+" next to it.
In my Watch window I see four columns:
Expression: In this case this is just the name of the object. But you can "watch" something more complicated, such as an arithmetic expression or a complex object with properties, maybe even methods (I've never tried that).
Value: No value is listed for fo; fo is an object and has no value of its own. Or you can say that fo has many different values, if you care to look at it another way: That is, it has lots of properties and most of them have values. But none of them are listed here.
Some objects have a "default" property, and the Watch window would display its value here. A cell object, for example, has many properties (Parent, Borders, Font, Column and Row etc); but its default property is Value, so Watch would display that here. But apparently fo has no default property.
Type: This tells you what kind of variable it is. I'm looking at a number of items in my Watch window just now, and they display things like this:
Long: A variable of the Long data type
Variant/Boolean: This is a variable that can be any data type (that's the "Variant" part); just now it's Boolean.
Range: I guess you know what a Range is.
For fo it says "Variant/Object/Font", meaning fo is a variable that could have other types of data but just now it's an object pointer, and the object it points to is of type Font.
Context: This tells me where the variable resides and has its meaning. In this simple program, all the items in my Watch window are labeled "Scratch.Main" ("Scratch" is the name of the module put this program in). If I were watching some variables in my main program, and in a form, and in a subroutine somewhere, the Context would be different for each of them.
Now let's look at fo's properties. Click on the '+' sign and it'll expand to show you each of them. Most of them are scalar variables (ie having just one value apiece), but two of them are further objects. "Application" is a pointer back up to the main Excel app itself, with all its properties; if you were to drill down there you could find the Workbooks object, and then the workbook this code is running in, and then the worksheet, and then cell A1 of that worksheet, and then that cell's font, and you'd have come full circle and found yourself where you started (only several layers deeper). And Parent is a pointer to the parent of fo, which should be the cell object, co....yep, in the Type column for Parent it says "Range", so it's talking about the cell this font object applies to.
The rest of the values have to do with the definition of the font itself: typeface, size, bold, strikethrough, italic and so on.
Let's look at a more complex object; set up a watch for co, the cell object that is fo's parent. Here you'll see lots and lots of properties—I think I count 85—many of which mean little or nothing to me. Oh, I recognize some: Parent, for example, points to the parent worksheet. Worksheet points to the very same thing. IndentLevel has to do with the cell's alignment. ColumnWidth is obvious, so is Value, so will others be to you too. But what in the world is Precedents? Or PrefixCharacter? Or CountLarge? If I ever need to know, no doubt I'll find out eventually. Oh, and notice Font; that's the property you already looked at in fo, and if you expand Font you'll see it's exactly the same. It has to be the same; it's the same object, just pointed two in two different ways.
---
Now, when I looked at your ListColumn thingy, I didn't know what I would find. But I could already tell (from the Type in the Watch window) that a ListColumn isn't a range; and yet it seemed from the syntax of the command, and from your expectations, and from the term "ListColumn" itself, that it's about a column in Excel. If so, I thought there might be a range
in it, as one of its properties. I didn't know what it might be named, or even how far down I might have to look in the object "tree"; I was prepared to give up after a short look and go back to the documentation. But it happened to be named simply "Range" and to be in the first level, so it was easy to spot. That's pretty much all I did: I started with a suspicion (that the error message meant that the ListColumn object is not a Range object) and stumbled across the answer more or less right away.
---
If you're used to doing this in Access, then I'm pretty sure that what leaves you gasping isn't the difference in VBA itself—they're the same language—but the differences between the Access and Excel objects. Since they're two different applications, I guess it makes sense that they should be different; Access doesn't think of terms of cells, columns and formulae, but of rows, SQL queries and recordsets. And wait 'til you first try automating something in Word; it's totally different. And Outlook is even worse; I
really don't get Outlook, though I've made a start. But take heart; VBA hasn't changed, it's just the objects with their properties and methods.
---
I kept hearing how OOP is different, and when I first started working on a macro in VBA (back just before Y2K, that was) I couldn't figure out what all the fuss was about. True, I had repeated visits from an error message "...does not support this property or method", and that one drove me crazy. It was only years later that I began to understand why a "method" is not a function, and why, therefore, I should not expect it to be generally available. If that's one of the things bothering you, I'd be happy to spare you the pain I went through, by explaining it better than it was explained to me. If I can, that is.