Beginning VBA: Navigating the Object Model

Excel has an object model. The object model is a big hierarchy of all the objects that you can use in VBA. At the top of this hierarchy is the Application object, and all the other objects are below it.

The dot operator (.) is used to navigate through the hierarchy. You connect objects with a dot to get to lesser objects. If you want to change the font in a cell, you need to get to a Font object. Let’s see how to get there

Start with the Application object, then navigate to the workbook, worksheet, range and font:

Application.ActiveWorkbook.ActiveSheet.Range("A1").Font

That’s a long way to go to get to a Font object. There are some shortcuts that I discuss in a moment, but whatever syntax you use, this is what’s happening under the hood.

Default Objects

Whenever you’re dealing with Excel’s object model, there are always default objects. For instance, the Application object is always assumed, regardless of where your code is. The path to the Font object could be written

ActiveWorkbook.ActiveSheet.Range("A1").Font

When you omit the Application object, Excel assumes that it’s there.

Standard Modules

When you’re in a standard module (like Module1), there are some more default objects at your disposal. When you omit the workbook reference, Excel assumes you want the ActiveWorkbook. This code is the same as the previous two snippets

ActiveSheet.Range("A1").Font

How much would you pay for these default objects. Wait, don’t answer yet, there’s more. You can omit the worksheet reference and the ActiveSheet will be assumed. Yet another equivalent code snippet

Range("A1").Font

That sure is shorter than the line we started with. Don’t get too excited, there’s a downside.

Class Modules

Class modules include ThisWorkbook, Sheet1 (and other sheet modules), Userforms, and class modules that you create. ThisWorkbook and the sheet modules are located in the Microsoft Excel Objects folder of the VBE’s Project Explorer. These modules are used to program event procedures (which we’ll talk about another day). What you need to know is that the default objects rules that apply to standard modules don’t apply in these modules.

When you omit the workbook reference in the ThisWorkbook module, the workbook that contains the code is assumed, not the ActiveWorkbook. Similarly, omitted sheet references in a sheet module are assumed to be the sheet whose module the code is in, not the ActiveSheet.

The Danger of Default Object

You can pretty much rely on default objects as I’ve described. That doesn’t mean that you should. I don’t, with a few exceptions. The main exception is the Application object. I almost never use it. When you use references that navigate down the hierarchy, they’re called fully qualified references.

There are two problems with references that aren’t fully qualified. The first is programmer error. Although you can rely on the ActiveWorkbook being the default workbook reference in a standard module, you can’t rely that you know which workbook is active. Even if you get it right, when you change your code later, you can screw it up royally. The second problem is execution speed. For any program that works with more than one sheet, you would have to activate different sheets in order for them to be the default sheet reference. If you read yesterday’s rant on Selecting and Activating, you already know how I feel about that issue.

Shortcuts to Fully Qualified References

Excel provides all these default object references for your convenience and I’m telling you not to use them – what a crock. Now your code is going to be ten times longer. Don’t fret, there are a few shortcuts you can use to keep your code managable.

The With keyword (commonly called a With Block) is used to access properties and methods of an object without having to type the object over and over. Take this snippet

ThisWorkbook.Worksheets(1).Range("A1").Value = 10
ThisWorkbook.Worksheets(1).Range("A1:B1").Font.Bold = True
ThisWorkbook.Worksheets(1).Range("A1").Interior.ColorIndex = 3

That’s a lot of typing. You can shorten it up and make it more readable with a With Block.

With ThisWorkbook.Worksheets(1)
.Range("A1").Value = 10
.Range("A1:B1").Font.Bold = True
.Range("A1").Interior.ColorIndex = 3
End With

You can also nest With Blocks

With ThisWorkbook.Worksheets(1)
With .Range("A1")
.Value = 10
.Interior.ColorIndex = 3
End With
.Range("A1:B1").Font.Bold = True
End With

Finally, you can get creative inside a with block

With ThisWorkbook.Worksheets(1).Range("A1")
.Value = 10
.Interior.ColorIndex = 3
.Resize(1,2).Font.Bold = True
End With

The Resize method is used to get to a different range based on A1.

Another way to have fully qualified references without all the mess is object variables. You use the Set keyword to assign an object to a variable and use that variable like you would a long object reference.

Dim rMyRange As Range

Set rMyRange = ThisWorkbook.Worksheets(1).Range("A1")

rMyRange.Value = 10
rMyRange.Interior.ColorIndex = 3
rMyRange.Resize(1,2).Font.Bold = True

Tomorrow: Review via Examples

17 thoughts on “Beginning VBA: Navigating the Object Model

  1. Hello
    Do you know of a site that provides the Excel Object Model, so that I can “cut & paste” the Complete Tree (objects,methods,properties,events) it into a word document and study it in peace?.
    Thanks

  2. Hi,
    My original question was:
    Do you know of a site that provides the Excel Object Model, so that I can “cut & paste” the Complete Tree (objects,methods,properties,events) it into a word document and study it in peace?.

    You’ve replied:
    Try going into VBA help (Index tab) and type Microsoft_Excel objects.

    Now…that’s NOT fair!
    Don’t you think I’ve passed elementary solutions?
    what I want is a place from where I can “cut & paste” the complete stuff, not just SEEing it and not being able to “touch it”!.

    I mean the COMPLETE stuff in “One Go”, NOT a page by page, 100 years of hard labour.

    Please try again…
    Thanks
    Martin

  3. “Now…that’s NOT fair!”

    All’s fair in love and Excel. That’s the best I’ve got. I know I’ve seen a complete object model before, but it’s been a while. I seem to remember that it was in the appendix of a book, but it’s not in any of the books currently on my book shelf.

    If you do find one, let me know, please.

  4. Want to clarify SET keyword. Does it have any other purpose other than stated above? What happens if you assign object w/o SET?

    Thks

  5. Tom: SET is used to assign an object to a variable, that’s the only use that I know. If you try to assign an object to an object variable without using Set, you’ll get error 91, Object or With not Set. If you don’t declare your variable as an object variable, however, you won’t get an error, just bad results. For instance

    Dim Rng1 as Variant
    Dim Rng2 as Range

    Rng1 = Range(“A1?)
    Rng2 = Range(“A1?)

    Rng1 will work because it will take the default property (the Value property) of Range(“A1?) instead of the object Range(“A1?). Rng2 will give the error because it is explicitly data typed as an object so it expects an object and not a Value property.

  6. The Excel object model is in the Excel help file. Look for the topic ‘Microsoft Excel Objects’. In version 2000 I’m running at work, it’s under Microsoft Excel Visual Basic Reference.

  7. Microsoft Excel Object Model is where I have found a diagram of the all the different Excel VBA objects. It’s in the Help under Microsoft Excel Visual Basic Reference at the top and immediately above “What’s New”. I’m using Excel 2003.

    I do have a question though. Why is it the Worksheets object/collection isn’t showing on this diagram?

    Is there a website on the web that diagrams the Excel Object Model better?

    Regards,

  8. I’ve seen people make simple web browsers in Visual Basic. They generally use the Navigate method(?) to go to a page. This method is nonexistant in VBA. Is there a way to make a web browser in VBA? Thank you in advance for any input!

    Pat

  9. In your VB project, set a reference to Microsoft Internet Controls (c:windowssystem32shdocvw.dll). Declare and create a browser object:

    Dim oBrowser As SHDocVw.InternetExplorer
    Set oBrowser = New SHDocVw.InternetExplorer

    Make it visible and browse to a URL:

    oBrowser.Visible = True
    oBrowser.Navigate “www.peltiertech.com”

  10. Public RunWhen As Double

    Sub StartBlink()
    With Worksheets(“Sheet1?).Range(“A1?).Font
    If .ColorIndex = 3 Then ‘ Red Text
    .ColorIndex = 2 ‘ White Text
    Else
    .ColorIndex = 3 ‘ Red Text
    End If
    End With
    RunWhen = Now + TimeSerial(0, 0, 1)
    Application.OnTime RunWhen, “‘” & ThisWorkbook.Name & “‘!StartBlink”, , True
    End Sub

    Sub StopBlink()
    ThisWorkbook.Worksheets(“Sheet1?).Range(“A1?).Font.ColorIndex = _
    xlColorIndexAutomatic
    Application.OnTime RunWhen, “‘” & ThisWorkbook.Name & “‘!StartBlink”, , False
    End Sub

    ————————————————––
    This is the code that i am trying to run from excel.However when i run it i cant copy and paste if i have another excel file open and it is running this script.I need to have this running when the file is opened and it should not affect other excel files if they are opened.

    Can someone help me out on this one?Thankyou.

  11. I would suggest taking out the blinking formatting. That would drive me absolutely nuts as a user, and if it is continually running, it will interfere with lots of useful code. Just use regular CF and trust the users to understand what it means.

  12. I’ve been doing a lot of reading on Accessible content for people with disabilities. Blinking can cause seizures.
    Excel can be hard enough without it causing a physical response.

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax