Properties and Methods Explained

All objects in the Excel object model have properties or methods or both. Some have very few; others have many, depending on the complexity of the object. A property is a scalar attribute that defines various parameters of an object. An example is the Visible property of a worksheet, which can be xlSheetVisible ( ‚ 1) , xlSheetHidden (0) , or xlSheetVeryHidden (2) and dictates whether the worksheet is visible to the user or is hidden. This is done here using built-in constants (numeric values are in parentheses). The Workbooks object has a Count property that defines how many workbooks are loaded into the Excel application. The properties hold the parameters that define the object. Properties are very much dependent on the objects that they are part of, and their values can be text or numeric.

Methods are ways of executing actions based on a particular object. They provide a shortcut to doing something on a particular object. For example, you may want to delete a worksheet from a workbook. To do this, use the Delete method on the worksheets collection, specifying the worksheet you wish to delete. Similarly, the Open method opens an existing workbook. The hard work has been done for you, and all you have to do is to call these methods from your code.

Take your computer, for example. It is an object and you can define properties and methods for it. A property is a measurable aspect of an object, so for your computer you could have the following properties:

Property	Value
Make	Compaq
Year	2002
RAM	128MB
Hard Disk	20GB
Processor	Intel

Note that these are not all numeric. It is easy to get the idea that a property is always something directly measurable, such as the height of an object, and is therefore always numeric. However, properties may also be text, Boolean (True or False), graphic (in the case of pictures or icons), or enumerated from a specific list relating to that property. They can be any data type.

Methods are words that represent actions that can be performed by the object. For your computer these could be

Cold boot
Warm boot
Shut down
Set up

Transferring this theory to Excel and using a workbook object as the example, you have properties such as

Property	Description
Author	The person who built the workbook
FullName	The full name of the workbook
HasPassword	True or False, depending on whether the workbook has a password
Path	The path the workbook was loaded from

Of course, there are many other properties for a workbook, but this gives you an idea of what some properties look like. Examples of Workbook object methods are

Method	Description
Activate	Makes this workbook the current workbook
Close	Closes the workbook
NewWindow	Creates a new window for the active workbook
PrintOut	Prints the workbook

Properties can be either read-only or read/write. Read-only means you can access the property's value (setting) but not change it. Read/write means you can both access and change a property's value. This can have an important effect on your program because you may find your code writing to properties that can only be read. For example, a workbook has a property called HasPassword that shows True or False according to whether the workbook is protected by a password. You might think if you change this to False you will remove password protection from the workbook without knowing the password. However, Microsoft thought of this and made the property read-only to prevent this. To preserve the integrity of the certain other property's model, it is important to keep them read-only. For example, the Count property of any collection of objects is always read-only. Altering the Count of worksheets within a workbook could lead to very unpredictable results. For instance, the workbook might have four worksheets within it. If you could change it to two worksheets, you would lose two worksheets because they would be deleted!

Methods are effectively like subroutines or shortcuts to actions that you can call from your code to perform certain actions, such as opening a workbook or adding a new worksheet. To try writing the code to open a workbook would be impossible because you do not know the finer points of the file format and what each byte represents; nor do you know how to incorporate that file into the Excel front end structure. Without VBA, you'd need to know the intricacies of C and have the source code to get the workbook open. However, Microsoft did all the hard work for us. They know the answers to all these questions, so all you need is one line of code calling the Open method. With methods, you usually pass parameters as well; for example, the PrintOut method can be given parameters for From , To , Preview , Printer , and so on.

Manipulating Properties

If a property is read/write, it can be manipulated. This means that you can substitute other values into it to provide different effects, depending on the object and the property. For example, you may want to use code to alter the text in a cell on a workbook. You can do this by writing your new text to the Value property for that particular cell, referencing it from the Worksheet object.

Properties are generally manipulated by using code at runtime, when your program is executing. However, some properties are available at design time and can be changed using the properties window within VBE. Design time is when you are viewing the code window and designing and making changes to your code. To see an example of this, click the object This Workbook in the VBA window. Click the Saved property, and it will allow you to change it to True or False. However, you normally change these properties using code, in response to user actions.

Two examples of the syntax for reading properties are shown here:

 MsgBox Workbooks("book1").Saved 
 MsgBox ActiveWorkbook.Saved

All collections have indexes that define individual objects within the collection. The title "book1" shown in parentheses defines that it is book1 within the Workbooks collection that the code is referring to. There could be several workbooks loaded at once, and this is how VBA distinguishes between them.

Some objects are grouped together into other objects, or collections. For example, Excel can have many workbooks open. Each individual workbook is an object. All currently open workbooks in the Excel application are grouped together into the Workbooks object or collection. Accessing an individual item or member in a collection involves either specifying its numeric position in the collection or accessing its name (if it has one). The preceding code example accesses the workbook named book1 in the Workbooks collection.

The first of the preceding syntax examples provides the Saved status of workbook book1 , True or False, displaying it in a message box. The second syntax example gives the Saved status of the active workbook in a message box. If book1 is the only workbook loaded into Excel, these examples will represent the same information because the active workbook has to be book1 . If there is more than one workbook loaded, the second example will give the Saved status on the workbook that the user is currently working. The workbook (as defined) is the object, and Saved is the property. The message box will display True if the workbook has been saved and has no new changes. The message box will return False if the workbook has been changed and has not yet been saved.

Note that a dot is used as a separator between the object and the property. This is a bit like using a slash (\) when defining pathnames for files. You can have more than one dot separator because objects can have subobjects and properties can have subproperties . For example, a workbook is a collection of worksheets, so one of the properties of the workbook object is a worksheets collection. If you want to refer to one worksheet out of the collection, it would look like this:

 MsgBox Workbooks("book1").Worksheets("sheet1").ProtectContents

This will display True or False depending on whether sheet1 in book1 has its contents protected. The ProtectContents property holds a True or False value according to whether the worksheet is protected or not. This demonstrates how the property is part of the overall picture of the object that is the worksheet. If the worksheet is protected, the property will display True; if it is not protected, it will display False.

You can change properties if they are not read-only. For example, to set the workbook property Saved to True, regardless of whether it has been saved or not, you could use the following code:

 Workbooks("book1").Saved = True

The result is that if you close the workbook, you will not get prompted for saving. This is an example of needing to know what you are doing when writing to properties so you can preserve the integrity of your application. For example, if you place this code in a workbook's SheetChange event, every time the user makes changes to the data in the worksheet, the Saved property will be set back to True. When the user finally closes down the workbook, there will not be any prompt to save because the program will check the Saved property, find it set to True, and assume that it is already saved. The spreadsheet will not be saved, which could easily lead to the loss of work, with the user none the wiser on why it happened . This is certainly not the way to write professional VBA code.

Calling Methods

As explained earlier, methods effectively are subroutines based on objects that take certain actions, sometimes dependent on parameters passed to them. The method is effectively a shortcut to an action, but you may need to specify parameters to define to VBA exactly what it is that you want to do.

An example is opening a workbook from a file. You use the Open method on the Workbooks collection to do this, but you also have to pass parameters, such as the filename and pathname, so that VBA knows what it is required to open. For example, in the following code,

 Workbooks.Open ("c:\MyFile.xls")

C:\MyFile.xls defines the location of the file to be opened; this is a mandatory parameter for this method. There are other optional parameters that can be passed, such as a password if required, and read-only. Optional parameters are shown in the tip text for the method with square brackets around them, such as [Title]. The tip text appears as you type in the VBA statement. You will see a tip text box with a yellow background appear, which shows all available parameters.

Sometimes it is unnecessary for a method to have arguments such as when you save the workbook to its original location with the Save method:

 Workbooks("book1").Save

This will perform a save on book1 , assuming that it has already been saved previously, similar to the File Save command on the menu. However, if the file has not been already saved, you will not be prompted for a filename. Instead, it will be saved as book1.xls and placed in the default path set by Excel or the last folder chosen from the last Open or Save operation.

You can also use the SaveAs method, which does have a parameter for saving a workbook with a different filename and/or to a different location:

 Workbooks("book1").SaveAs ("newfile.xls",,"apple")

This passes the parameter "newfile.xls" as the filename. This is called passing by order because the parameters are being passed in the order in which they are defined in the function, separated by commas. In this case, you are passing a filename called newfile.xls and ignoring the file format parameter (which is optional) and providing a password parameter of "apple" .

When you enter the opening bracket , you will see a list of parameters appear, highlighted in bold as you enter each one. You have to stick to the order shown. If this is a function that assigns the result to a variable, you do not need to include the parentheses. You will get an error in some cases if you include them.

Some methods, such as SaveAs , have a large number of parameters, and many of them are optional. Optional parameters are shown with square brackets [ ]. Passing by order becomes more complicated with optional parameters because you may be using a function that has ten possible parameters even if you want to use only two of them. Consider the following example for opening a workbook:

 Workbooks.Open "c:\MyFile.xls", , True, , "apple"

You can see that three parameters are being passed to open the file, although at least two parameters are not being used, as shown by the Null values between the commas. This is because these are optional parameters and do not have to be given a value. For example, not all files have a password, so when a workbook is opened in this way the password is purely optional. The split between mandatory and optional parameters depends on what the method is doing and how the code in the Excel Object Library has been written. Optional parameters are always shown with square brackets [ ‚ ] around them.

The filename is mandatory, the read-only parameter is set to True, and the password is ‚“apple. ‚½ It looks confusing, and anyone reading the code will not be able to immediately interpret what is going on and what the parameters mean. If you are looking at a VBA application that has been written by someone else, it may take more time to interpret what is going on than if the passing by name method was used. Code should always be easy for another person to understand in case they have to perform maintenance of it. If you have written a professional application for commercial use and you are suddenly unavailable to maintain it, someone else needs to be able to look at your work and quickly understand your code.

Passing by name is another way of passing parameters that makes it less confusing and shows the names of the parameters being passed. Passing by name enables you to selectively pass arguments without having to specify Null values for arguments you don't want to use. It also makes it easier to understand what is being passed to the method. If you pass by name, the preceding example can be rewritten as follows :

 Workbooks.Open FileName:="c:\myfile.xls", ReadOnly:=True, Password:="apple"

You can define each parameter by naming the parameter and following it with a colon and an equal sign ( := ). When passing by name, you can pass the parameters in any order, unlike passing by order, which strictly defines the order passed.

You can also save this file under another name, as follows:

 Workbooks("book1").SaveAs FileName:="newfile.xls"

If you run this code and save the file as newfile.xls, you must then refer to that workbook by its new name in subsequent code; otherwise , you will get the error ‚“Subscript out of range, ‚½ meaning the previous filename book1 can no longer be found. The following example correctly refers to the newly saved file:

 Workbooks("newfile.xls").Worksheets("sheet1")

Collections Explained

In object-oriented programs, it is important to understand the concept of collections. Collections are objects that contain a group of like objects. An example is the Worksheets collection, which contains all the worksheet objects for a given workbook. All the worksheets are like objects because they have the same properties and methods. An object such as a Chart has different properties and methods and so cannot be part of the Worksheets collection, but it would fit into the Charts collection.

In Excel, all objects are either singular objects referenced by name, or objects in a collection referenced by index or name. Collections also have their own properties and methods apart from the objects that they hold. For example, collections always hold a Count property that represents the number of objects within the collection, and they always have an Add method to add a new object into the collection.

Collections have their own properties and methods that are entirely separate from the objects that they contain. These objects also have their own properties and methods and can also contain further collections of objects. An example is the Workbooks collection, which contains a collection of Workbook objects, representing all workbooks currently loaded into Excel. As you have already learned, it has a Count property to index the number of workbooks, and it has an Open method to load another workbook. Each workbook has properties such as HasPassword and methods such as Save or SaveAs . However, there is also a collection of worksheets within each workbook that then has its own properties, methods, and collections.

In Excel, you can have a collection of worksheets inside a workbook called the Worksheets collection, and each worksheet inside this collection will have an index number and a name to identify it. The index number is a reference for an object within that collection, commencing at 1.

The same thing is true of workbooks: there can be several workbooks loaded at once within the Excel application. There is a collection of workbooks called the Workbooks collection, and each workbook inside will be enumerated with an index number and a name to identify it. There are other collection objects such as Windows , ChartObjects , and Borders , but the Workbooks and Worksheets collections are the main ones for referring to cells and so are the focus of this section.

Collections can be cycled through. Cycling is the best term to describe what happens in a For Each..Next loop. For Each..Next loops are covered in Chapter 4. You use the following syntax: For Each Object within Collection, Next . This cycles through each object within the collection, giving you the chance to examine or manipulate the properties of it or to use a method by calling it (for example, Workbooks("book1").Save ).

Try putting the code shown in the following listing in a VBA module. If you do not already have a module displayed, use Insert Module from the Visual Basic Editor's menu. Press F5 to run it, and you will see each sheet's name displayed, as shown in Figure 12-1.

Figure 12-1: Displaying names of all worksheets within the Worksheet collection

 Sub ShowName() 
 Dim w As Worksheet 
 For Each w In Worksheets 
 MsgBox w.Name 
 Next 
 End Sub

Initially, this code sets up a variable w to represent a worksheet object. This represents the current worksheet being cycled through. The code then cycles through the Worksheets collection using a For Each..Next loop. The code takes each worksheet in turn , gets the name property, and displays it in a message box onscreen, as shown in Figure 12-2. Your code sheet should look like Figure 12-1.

Figure 12-2: Message box displaying names of worksheets

The Dim statement is short for Dimension; it creates a space in memory for a variable. In this case, it creates space for a standard worksheet. You can run this routine without the Dim line, but it does have advantages because it will give you automatic assistance with properties and methods. When you type the Dim w As statement, a list box appears when you get to the Worksheet part. When you type in MsgBox w.name , a list box will appear after you type w showing all the properties and methods that you can use. This is extremely helpful when programming because it allows you to immediately see what your options are for the next piece of code.

The structure of the Excel object model is extremely complicated, and you should not expect to remember every object, collection, property, and method within it. If you do not use the automatic list boxes that appear as you type object code in, you will be constantly referring to the Object Browser for this information because it can be difficult to know what command to use next. It is also easy to make mistakes that cause your code to produce errors when you run it.