All properties of Application.ActiveSheet in Excel - Object Browser does not list the properties of Members

Question

Microsoft Docs do not list all the properties of Object Application.ActiveSheet: https://learn.microsoft.com/en-us/office/vba/api/excel.application.activesheet

It has only included some of the properties like: ActiveSheet.Name. But from VBA scripts I found on the internet, I know that there are more properties such as: ActiveSheet.Type

So I thought maybe I can list them all with this VBA code: from: VBA collection: list of keys

Sub AktivSheet()
Dim key As Variant
For Each key In Application.ActiveSheet
    Debug.Print "Key: " & key, "Value: " & Application.ActiveSheet.Item(key)
Next
End Sub

But it didn't work, and Microsoft Docs suggests to use Object Browser. But Object Browser does not list the properties for Application.ActiveSheet:

I'm out of ideas! I just want a list of all ActiveSheet properties. Because I simply need to know the Text Direction of the Active Sheet, as in if it's Left to Right OR Right to Left.

Answer 1

I just want a list of all ActiveSheet properties

You cannot easily get that programmatically with 100% VBA code, VBA has pretty much zero reflection capabilities.

Now, assuming we're not looking for a programmatic way to retrieve object properties, here's how to use the object browser to get what you're looking for.

First, right-click anywhere in the object browser and select the "Show hidden members" option to reveal the full breadth of the libraries you're looking at. This affects the names list dropdown when editing code: you'll be shown hidden members now.

One of the hidden modules in the Excel type library, is a module named Global, with a hidden _Global interface:

That hidden global module is how you can type MsgBox ActiveSheet.Name and it "just works" (assuming there is an ActiveSheet - it could always blow up with error 91 when there's no active workbook open in the Application instance you're working with) even though you haven't specified what Workbook you're working with: implicitly, ActiveSheet is just working off whatever the ActiveWorkbook is.

So ActiveSheet is a property, not an object. It's a property that returns an object, but its declared type is Object.

This means any member call you make directly against ActiveSheet, is implicitly late-bound: you can type MsgBox ActiveSheet.Naem and VBA will happily compile the typo (Option Explicit can't save you here), and only blow up at run-time with error 438 "I can't find that property!".

In order to know what properties the ActiveSheet has, we need to know what run-time type we're looking at. And since a sheet in a Workbook object can be a Worksheet, a Chart, or several other types of legacy "sheet" objects, there is indeed no member accessible at compile-time, because at compile-time the ActiveSheet is just a pointer to an Object, and what type of object that is will only be known at run-time.

So instead of coding against ActiveSheet, we code against Worksheet, because we know the particular sheet we're expecting to work with is a Worksheet object.

Dim Sheet As Worksheet
Set Sheet = ActiveSheet

Now when we type Sheet., we're early-bound (types involved are known and resolved at compile-time) and helpfully provided with a list of all available members:

Every time you access a member (function, property) that returns an Object or a Variant, any member call made against it will be late-bound.

Strive to stay in the early-bound realm: declare local variables as needed, such that the compiler gets to "see" and validate everything! Try typing the below code to feel the difference - whenever you type a . dot and nothing comes up, it's a sign the compiler is losing sight of what's going on and you're possibly moving compile-time errors to run-time:

MsgBox ActiveSheet.DisplayRightToLeft '<~ late bound

Dim Sheet As Worksheet
Set Sheet = ActiveSheet
MsgBox Sheet.DisplayRightToLeft '<~ early bound