Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA: How to use the Object Browser?

Tags:

excel

vba

I don't know if this is the right place for my question, but: how do I use the object and the browser? I feel so inept when it comes to helping myself. I know I can google stuff (and I do, a lot), but I want to be able to use all resources at hand.

Let's take the border of a chart. Google helped me out once again and told me I can change it like this:

Sheets("Sheet1").ChartObjects(1).Chart.ChartArea.Border.LineStyle = xlContinuous

But how do I figure this out with the object browser? Let's say I type in "chart". I get a long list and after going through this, it's probably not how I figure it out because I'd have to know that I'm looking for ChartArea. Still, one question:

  1. I found ChartArea for an entry where the Library is Excel, Class is Chart and Member is empty - does member being empty tell me something, is this something I should look for?

  2. Anyway, moving on, I type in "border". I would've expected to find something in "class" that has something to do with chart. But I don't. So how am I supposed to know how to find what I'm looking for?

  3. And while I'm at it, let me ask about the Microsoft help. I googled my way to the Border.LineStyle property. Obviously there's the code example that tells me about using it with Charts().ChartArea, but again, can I do this more methodically? On the left hand side, you can see LineStyle is a property of Border. From there, how do I move up one level? How do I see that Border is/can be a property of ChartArea?

like image 417
NoNameNo123 Avatar asked May 13 '19 19:05

NoNameNo123


1 Answers

TL;DR:

The ChartArea.Border property is hidden, you need to toggle "show hidden members" on in the Object Browser to see it.

Below is essentially everything there is to know about the Object Browser.


Library Filter & Search Box

The top part looks like this:

libraries dropdown, search box

That dropdown contains all referenced type libraries, identified by their programmatic name:

  • Excel: the Excel type library, defines things like Worksheet, Range, etc.
  • Office: a dependency of the Excel type library; defines things like CommandBars.
  • stdole: another dependency; defines lower-level things like StdFont and StdPicture.
  • VBA: the VBA standard library, defines things like MsgBox, Collection, etc.
  • VBAProject: the type library of your compiled VBA project.

You'll want to use that dropdown to limit what you're looking at to the specific type library you're exploring - for example, Excel. Under the dropdown, there's a search box you can use to search for strings that appear in part of an identifier and populate the "search results" view - but you know that already.

Show Hidden Members

Right-click an empty area in the toolbar; select "Show hidden members" - now the object browser and InteliSense will be listing members that are otherwise hidden, and you'll quickly discover that the Excel type library is much larger than it seems.

object browser context menu with "Show hidden members" toggled

Edit Module/Member Descriptions

Navigate to your VBAProject library, and find one of your modules in the left pane; the right pane will be listing the members of that module. Right-click either the module or one of its members, and select "Properties" - the "Member Options" dialog pops up and lets you enter a description:

Member Options dialog

Module and member descriptions show up in the bottom pane when they exist:

Module description displayed in the bottom pane

If you exported that module, you would see that it now has a hidden VB_Description attribute with a string value that matches what you entered for a description.

If you're using Rubberduck, you can use special comments ("annotations") to control these hidden attributes, too:

'@ModuleDescription("This module contains some boring recorded macros.")
Option Explicit

'@Description("Does something...")
Public Sub Macro1()
    '...
End Sub

Rubberduck annotations can also control/synchronize other hidden attributes that the Object Browser isn't exposing, but I digress.


The left pane ("classes") displays all the types in the selected library; the right pane lists the members of the selected type, grouped by kind (properties, methods, events, etc.); what you must keep in mind, is that no matter how many members are named the same as types/classes/interfaces, a member is a member, not a type.

Taking this expression:

Sheets("Sheet1").ChartObjects(1).Chart.ChartArea.Border.LineStyle = xlContinuous

We can start with finding what object type the Sheets member call belongs to. If we filter for the Excel library and search for Sheets, we find 2 interesting results where a member is named Sheets:

  • Application.Sheets
  • Workbook.Sheets

This means unless that line of code is in the code-behind of ThisWorkbook, what we're calling can't be Workbook.Sheets - but we're not qualifying it with Application either! If we reveal hidden members, we discover a hidden Global class and a _Global interface that both expose a Sheets member! From there we can infer that the Excel object model is "redirecting" our unqualified Sheets call to the Application object, which looks like it's giving us the Sheets member of whatever the ActiveWorkbook is. In any case, the relationship between _Global and Global is confusing: we select _Global.Sheets and the bottom panel tells us we're looking at a member of Excel.Global:

_Global.Sheets

Notice the property doesn't have any parameters: it simply yields a reference to a Sheets object. So we look at the returned Sheets type - either by clicking the hyperlink in the bottom panel, or by browsing the left panel to find the Sheets collection type (which conveniently happens to have the same name as the Sheets property of the Global, Application, and Workbook classes).

Note that the Workbook.Worksheets property also yields a Sheets collection object.

Like all other collection classes, the Sheets class has a default member, and this one is hidden, it's named _Default, and it returns an Object. We can guess that the property is forwarding the call to the Item indexer, because collection classes normally expose a way to access their items by index or by name/key (by convention it's named Item but sometimes it's something else, like Recordset.Fields)... but now that property also returns an Object.

Default Members
Classes in VBA can have a member (only one!) that can be implicitly invoked when the object is coerced into a value. This member has a hidden VB_UserMemId attribute with a value of 0, and the object browser identifies it with a blue/cyan dot on the member's icon. That's how Application.Name gets output when you write Debug.Print Application; that's also how you get Range.Value when you Let-coerce a Range object into a Variant or any other non-object type without using the Set keyword... and it's why the Set keyword is required when assigning object references: without it the compiler wouldn't have a way to tell whether you mean to assign the object itself or its default member value... which can very well be another object reference.
In general, it's best to avoid implicit default member calls and be explicit, so that the code says what it does, and does what it says.

At this point we're stumped, just like the compiler is: every further member call on that line of code is only resolvable at run-time - it's all late-bound, and when you type it you get no IntelliSense, no autocompletion, no compile-time validation: even Option Explicit can't save you from a typo, and if you make one you'll know because VBA will throw error 438 "I can't find that member" at you.

Sheets._Default returns an Object: not all sheets are Worksheet objects - a Chart could be a sheet as well! That's why we usually prefer to use the Workbook.Worksheets property instead, so that we're certain to get a Worksheet object. Right? "Sheet1" is a Worksheet, we know as much!

We could restore early binding by declaring a Worksheet variable:

Dim sheet As Worksheet
Set sheet = ActiveWorkbook.Worksheets("Sheet1")
sheet.ChartObjects(1).Chart.ChartArea.Border.LineStyle = xlContinuous

So we browse to the Worksheet type in the left pane, find its ChartObjects method (it's a Function), which also returns an Object. Looks like naming properties after their type is a common convention in the Excel type library - there's a ChartObjects object collection in the left pane, and we can probably assume its items are ChartObject objects; so we find the ChartObject class again in the left pane, and see that it has a Chart property that returns a Chart object:

Excel.ChartObject class members

At this point we can restore early binding further down the chain of member calls, by extracting another variable:

Dim targetChartObj As ChartObject
Set targetChartObj = sheet.ChartObjects(1)
targetChartObj.Chart.ChartArea.Border.LineStyle = xlContinuous

The ChartArea property yields a ChartArea object, so we find the ChartArea class in the left panel, ...and see that it has a hidden Border property!

Excel.ChartArea.Border is hidden!

The ChartArea.Border property returns a Border object, which again we find in the left panel to see that it has a LineStyle property... of type Variant. How are we supposed to know xlContinuous is even a thing then? Is there no hope?

At this point we could google up Border.LineStyle and see if the docs give us a clue about the legal values, ...or we could try to search for LineStyle in the search box...

searching for LineStyle turns up the XlLineStyle enum

...and see that there's an XlLineStyle enum with a member named xlContinuous, alongside all other constants defined under that enum. A quick online search brings up the official docs and confirms that Border.LineStyle wants an XlLineStyle enum value!

Now, this was moving left-to-right. Going right-to-left, you can work your way up by leveraging the search box; searching for "Border" with hidden members visible, lists the ChartArea.Border member in the search results.

like image 56
Mathieu Guindon Avatar answered Oct 12 '22 22:10

Mathieu Guindon