Excel VBA Programming For Dummies. Dick Kusleika

Скачать книгу

designed it. But you won’t be surprised to learn that it mostly mirrors what you see in the user interface. Worksheet objects are children of a Workbook object just like how workbook hold worksheets in Excel. A Worksheet object is the parent of a PivotTable object because PivotTables exist on sheets in Excel.

      Understanding the object hierarchy

      The Application object is at the very top of the hierarchy in the object model. It represents Excel itself and every other object is a child or grandchild of the Application object. The following are some of the more useful objects contained just below Application in the hierarchy:

       Addin

       Window

       Workbook

       WorksheetFunction

      Each object contained in the Application object can contain other objects. For example, the following are some objects that can be contained in a Workbook object:

       Chart (which is a chart sheet)

       Name

       VBProject

       Window

       Worksheet

In turn, each of these objects can contain still other objects. Consider a Worksheet object, which is contained in a Workbook object, that is contained in the Application object. Some of the objects that can be contained in a Worksheet object are

       Comment

       Hyperlink

       Name

       PageSetup

       PivotTable

       Range

      

If you want to do something with a range on a particular worksheet, you might find it helpful to visualize that range in the following manner:

      Range ⇒ contained in Worksheet ⇒ contained in Workbook ⇒ contained in Excel

      

Excel has more objects than you can shake a stick at. Even power users would get overwhelmed if they tried to learn every object in the object model. The good news is that you don’t have to do anything with most of these objects. You could go your whole VBA programming life and never need to use the Excel4MacroSheet object. You only need to know the objects for the problem your working on and most of the time the macro recorder tells you everything you need.

      Referring to objects

      Referring to an object is important because you must identify the object with which you want to work. After all, VBA can’t read your mind — yet. The Application object, being the top dog, is easy. Just type Application and a period (called a dot operator) to get to its properties and methods. For example, if you wanted to change Excel’s behavior so that the cursor doesn’t move when you press Enter, you could write the following code:

       Application.MoveAfterReturn = False

Some of the Application object’s properties return other objects. Just like the MoveAfterReturn property return a value (either True or False), a property can also return an object. AutoCorrect is an object in the object model that’s just below the Application object. The way you refer to the AutoCorrect object is by using the AutoCorrect property. Yes, the property and the object it returns have the same name. If you want to tell Excel to fix your typing when you leave Caps Lock on, you would do so with the following code:

       Application.AutoCorrect.CorrectCapsLock = True

      In this case, CorrectCapsLock is a property of the AutoCorrect object and you are able to address that object via the AutoCorrect property of the Application object.

      In the previous section, I wrote that cars are similar to Excel objects because they have properties like color and number of pistons. Cars also have steering wheels and steering wheels are objects, too. They’re just one rung down the car object hierarchy. If a car were a computer program, it would have a property called SteeringWheel that you would use to access the SteeringWheel object.

      

Workbook names also have a dot to separate the filename from the extension (for example, Book1.xlsx). That’s just a coincidence. The dot in a filename has nothing at all to do with the dot operator referred to a few paragraphs ago.

      Using Collection objects

      Now you can work with the Application object and any object that’s just below it via one of its properties. To get another level below that you need collection objects.

      Collection objects are objects that give you access to single objects contained inside them. In many cases, the collection object’s name is the plural of the single object’s name. For example, if you want to work with a specific Workbook object, you need to go through the Workbooks collection object to get there.

      Here are a few examples of commonly used collections:

       Workbooks: A collection of all currently open Workbook objects

       Worksheets: A collection of all Worksheet objects contained in a particular Workbook object

       Charts: A collection of all Chart objects (chart sheets) contained in a particular Workbook object

       Sheets: A collection of all sheets (regardless of their type) contained in a particular Workbook object. To get to a Workbook object, you can start with the Application object like in the following code: Application.Workbooks.Item("Book1").Path

Just like Application had an AutoCorrect property to get to the AutoCorrect object, it has a Workbooks property to get to the Workbooks collection object. All collection objects have an Item property to get to a singular instance of an object they collect. In this example, the Item property returns a Workbook object because that’s the type of object in the Workbook collection.

      A collection’s Item property takes one argument. That argument can be a string (enclosed in double quotes) or an integer. If you provide a string argument, Item returns an object whose name matches the string. In the example above, Book1 is the name of the workbook returned. If you provide an integer argument, Item returns the object whose position in the collection matches that number. The following code returns the first worksheet of the second workbook:

       Application.Workbooks.Item(2).Worksheets.Item(1)

      You might not always know how the objects are ordered in the collection. With worksheets, they are ordered the same way as in Excel. The left-most worksheet is the first worksheet in the collection. But workbooks aren’t so straightforward. There are hidden workbooks like Add-Ins and the Personal Macro Workbook that are in the collection

Скачать книгу