Microsoft Office Tutorials and References
In Depth Information
The Object Model
Therefore, we can say that object-oriented programming, upon which VBA is based, is a style of
programming language that cares primarily about objects, and how those objects can be
manipulated based on their inherent qualities.
THE oBjEcT ModEl
The Excel object model is the heart and soul of how VBA is used in Excel. While VBA is the
programming language for Excel, it is also the programming language for Office applications in Word,
Access, PowerPoint, and Outlook. Even though all these applications are programmable with VBA,
they have their own programming needs because they are different software applications and hence
are designed to serve different functions. Excel does not receive e-mails as Outlook does, and Word
does not produce reports from its own database tables as Access does.
Every VBA action you take in your Excel workbook sends a command through the Excel object
model. The object model is a large list of objects that relate to Excel, such as worksheets, cells,
ranges, and charts. The VBA code in your macro that adds a worksheet to the workbook will make
sense to Excel, because it is communicating with the objects that are recognized to be present in the
Excel object model. For example, that same macro to add a worksheet would not work in Outlook.
The Outlook object model does not include worksheets because Outlook is an application that
maintains e-mails and appointment calendars, not worksheets.
The object model of any VBA application is hierarchical by design. In the Excel object model, the
Application object is at the top of the model because it is the entire Excel application. Under the
Application object is a whole host of other objects, one of them being the Workbook object. Under
Workbook is the Worksheet object, among many others, and under the Worksheet object are Range
and Cell objects, and so on.
The result of this hierarchy is what drives the proper syntax for your VBA macros. For example, if
you want to enter the word “Hello” in cell A1 of Sheet1 of the workbook you are currently working
in, the line of code to handle that could be:
Application.ActiveWorkbook.Worksheets(“Sheet1”).Range(“A1”).Value = “Hello”
VBA is a smart language. It knows you are working in Excel if you are specifying a Workbook
object. It also knows you are doing something in a workbook if you are specifying a Worksheet
object. Therefore, the preceding line of code can be shortened to:
Worksheets(“Sheet1”).Range(“A1”).Value = “Hello”
And that can be shortened further if you are working on Sheet1 when the code line is executed. If the
parent Worksheet object is not specified, VBA’s default assumption is that you want the active
worksheet to receive the word “Hello” in cell A1, and in that scenario the line of code would simply be:
Range(“A1”).Value = “Hello”
A bit of theory on the subject of objects. In an object-oriented programming environment, VBA
regards as an Excel object pretty much any element of the Excel application you can think of,
whether it is a button, or a row, or a window — even the Excel application itself.