Understanding Objects Properties and Methods

Using Visual Basic for Applications, you can create procedures that control many features of Microsoft Excel. You can also control a large number of other applications. The power of Visual Basic comes from its ability to control and manage various objects. But what is an object?

An object is a thing you control with VBA. A workbook, a worksheet, a range in a worksheet, a chart, or a toolbar are just a few examples of things you may want to control while working in Excel. These things are objects. Excel contains over a hundred objects that you can manipulate in different ways. All Visual Basic objects are organized in a hierarchy. Some objects may contain other objects. For example, Microsoft Excel is an Application object. The Application object contains other objects, such as workbooks or command bars. The Workbook object may contain other objects, such as worksheets or charts. In this chapter, you will learn how to control the following Excel objects: Range, Window, Worksheet, Workbook, and Application. I have listed the Range object first for a very important reason: you can't do much work in spreadsheets unless you know how to manipulate ranges of cells.

Certain objects look alike. If you open a new workbook and examine its worksheets, you won't see any differences. A group of like objects is called a collection. For example, a collection of worksheets includes all worksheets in a particular Workbook, and the collection of CommandBars contains all the toolbars and menu bars. Collections are also objects. In Microsoft Excel, the most frequently used collections are the Sheets collection that represents all the worksheets and charts, the Workbook collection, the Worksheets collection, and the Windows collection. When you work with collections, you can perform the same action on all the objects in the collection.

Each object has some characteristics that allow you to describe the object. In Visual Basic, the object's characteristics are called properties. For example, a Workbook object has a Name property, and the Range object has such properties as Column, Font, Formula, Name, Row, Style, and Value. The object properties can be set. When you set an object's property, you control its looks or its position. Object properties can only take on one specific value at any one time. For example, the active Workbook can't be called two different names at the same time. The most difficult part of Visual Basic is understanding the fact that some properties can also be objects. Consider the Range object. You can change the looks of the selected range of cells by setting the Font property. But Font can have a different name (Times New Roman, Arial, ...), different size (10,12,14, ...), and different style (Bold, Italic, Underline, ...). These are Font properties. If the Font has properties, then the Font is also an object.

Properties are great. They let you change the look of the object, but how can you control the actions? Before you can make Excel carry out some tasks, you need to know another term. Objects have methods. Each action you want the object to perform is called a method. The most important Visual Basic method is the Add method. Using this method, you can add a new workbook or worksheet. Objects can use various methods. For example, the Range object has special methods that allow you to clear the cell contents (ClearContents method), formats (ClearFormats method), and both contents and formats (Clear method). Other methods allow the objects to be selected, copied, or moved.

Methods can have optional parameters that specify how the method is to be carried out. For example, the Workbook object has a method called Close. You can close any open workbook using this method. If there are changes to the workbook, Microsoft Excel displays a message asking whether you want to save the changes. You can use the Close method with the SaveChanges parameter set to False to close the workbook and discard any changes that have been made to it, as in the example below:

Workbooks("Chap01.XLS").Close SaveChanges:=False

0 0

Post a comment