2

Is there a place where I can simply find the Object Model hierarchy and kind of API for VBA for Excel 2013?

I am looking at MSDN URL http://msdn.microsoft.com/en-us/library/office/ff841127(v=office.15).aspx

But that seems confusing.

Example:

If I am writing

Selection.Interior.ColorIndex = -4142

I would like to have an API (online or offline) to understand the Object Models starting with Selection and work (and explore) my way to ColorIndex. By reading the associated documentation as well.

The only way I think that's possible is by having a structured document like Java Docs.

Any references/tips will help.

System Info:
MS Excel 2013
VBA
Windows 8, 64 bit

UPDATE: While searching for answers, I got the below image from http://msdn.microsoft.com/en-us/library/aa141044.aspx

Object Model Hierarchy

Update: Based on entry by user Oliver below: My office help doesn't seem to be working. CLView.EXE issue

Zoe
  • 27,060
  • 21
  • 118
  • 148
Ayusman
  • 8,509
  • 21
  • 79
  • 132
  • There is a [Concepts](http://msdn.microsoft.com/en-us/library/jj733879(v=office.15).aspx) section, with some nice overviews about using objects such as Range and Worksheet. – Zev Spitz Aug 27 '14 at 12:38

4 Answers4

3

Generally, if a variable or other identifier can be resolved to a specific type, then Intellisense will show you the members of that type. For example:

Application.

will bring up a list of members.

Selection is a special case because although the currently selected item is often a range of cells, it could be something else as well - part of a chart sheet for example.

You can discover the underlying type of the object which Selection refers to, by adding a watch (Debug -> Add Watch...). Type Selection in the Expression box, and set the context to (All Procedures) and (All Modules) if it's not set that way already.

In the Watches window, you will see the actual type of the object referred to by Selection, and you can expand the + to see its properties. If the type says Object/Range, (meaning the type of the expression is Object and the type of the object referred to by the expression is Range), one of the properties will be Interior. If you look at the type column for Interior, you'll see Interior/Interior, because the type of the Interior property is indeed the Interior type.

If you type the following in code:

ActiveCell.

Intellisense will show you a list of members, including the Interior property, because the type of the ActiveCell property is the Range type.

Some other powerful tools for investigating the object model:

  • The Object Browser (View -> Object Browser) shows you a list of types available to your project, based on the libraries which the project references (can be viewed/changed at Tools -> References...). When a type is selected, you will see a list of members for that type. You can filter the types by library and/or by name. Members in the <globals> type can be used without any object references -- Interior needs some Range object to be used, but Selection can be used by itself.
  • The Immediate window (View -> Immediate window) lets you evaluate expressions (preface with a ? -- e.g. ?ActiveWorkbook.Sheets.Count) and run code in place (such as ActiveWorkbook.Save)
Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
1

The Excel Developer reference is most probably right on your computer, it's just difficult to find:

Manual way:

  1. Start Excel
  2. Hit Alt-F11 to enter VBA Editor
  3. On the Menu Bar, choose "?"->Microsoft Visual Basic Help

Now you are looking at the Excel-VBA Help and you can even search it in the top left box for "Selection".

Direct Link:

"C:\Program Files (x86)\Microsoft Office\Office15\CLVIEW.EXE" "EXCEL.DEV" "Microsoft Office Excel"

(At least this worked for Office 2007, i assume it does for 2013 as well)

Oliver
  • 3,225
  • 1
  • 18
  • 12
  • here is what my issue with CLVIEW.EXE looks like. Please take a look at the original post, I have added the error pop-up. – Ayusman Aug 28 '14 at 04:22
0

Do you mean, while coding you would like to see function/sub explanations like JavaDoc? Unfortunately this is not standard in VBA.

However you might want to take a look into this Is there a good VB6 documentation system similar to Javadoc?

Community
  • 1
  • 1
Krish
  • 5,917
  • 2
  • 14
  • 35
0

Another way to "see" the underlying Object Model called by Excel is to use the macro recorder.

  1. Open the excel sheet and the VBA environment (ALT+F11) side by side.
  2. Goto Developer tab and click "Record Macro".
  3. From the VBE open the module1 from project explorer (this is where exlce writes macro by default).
  4. Now make changes in your worksheet and see what code Excel is writing.
tawfiq
  • 61
  • 1
  • 3