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
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
Martin
Try going into VBA help (Index tab) and type Microsoft_Excel objects.
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
“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.
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
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.
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.
“You release me baby!”
Awesome hard house tune…
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,
here is the Excel 2003 VBA object model online and interactive…
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl11/html/xltocOMMap_HV01049651.asp
Nick
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
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”
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.
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.
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.