Excel 2019 Bible

Chapter 42: Introducing Visual Basic for Applications

42

The Excel object model

VBA is a language designed to manipulate objects. Some objects are contained in the lan-
guage itself, but most of the objects that you will use when programming VBA for Excel
come from the Excel object model.

At the top of the object model is the Application object. This object represents Excel
itself, and all other objects are below it in the hierarchy. One way to think about writ-
ing code is to ask which object you’d like to change and which property or method con-
trols that aspect of the object. For example, if you want to force users to enter data
using the Formula bar as opposed to entering data directly in cells, you can change the
EditDirectlyInCell property of the Application object.

If you don’t know which object or property to change—and when you’re just starting out
you won’t—you can use the macro recorder. Start recording a macro, and make the changes.
Then see what the recorder came up with. If you record a macro and choose File ➪ Options
➪ Advanced and uncheck Allow editing directly in cells, you will see this recorded macro.

Sub Macro1()

'

' Macro1 Macro

'

'

Application.EditDirectlyInCell = False

End Sub

Now you know that the EditDirectlyInCell property of the Application object is
where that setting is stored, and you can use that in your own code.

Objects and collections

In addition to the Application object, there are hundreds of other objects available to
use in your code, such as Ranges, Charts, and Shapes. These objects are arranged in a
hierarchy with the Application object at the top.

Objects of the same type are contained in collections. (Collections are also an object.)
Collection objects are named using the plural form of the objects that they contain. Each
open workbook is a Workbook object, and all of the open workbooks are in the Workbooks
collection object. Similarly, the Shapes collection object contains all of the objects of type
Shape.

There are a few places where the plural collection naming convention breaks down. The
Range object is an important exception that we’ll discuss later in this chapter.

You reference a specific object by traversing the hierarchy. To reference cell A1, you might
use code such as the following:

Application.Workbooks.Item("MyBook.xlsx").Worksheets.Item(1).

Range("A1")