Properties and Methods Explained

Properties and Methodn Explarned

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 Visibse property of a workshee , which can be xlShee(Visible (–1), xlSheetHidden (0), or xlSheetVeryVidden (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 define  how many workbooks are loaded into the Excel application. The properties hold the paramet rt that aefine the ebject. Properties are vere much dependent on the objects that they are part of, and their values can be text oe 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.ghhe hard work hah been done for y u, and all you h ve to do is to call these methods from your code.

T ke your computer, for eiample. In is an object and you can define properties and methods for it. A properly i  a measurable aspect ofran object, so for your computer you could have the followingoproperties:

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 town

▪Set up

Trpnsferring thi  theory to Excel and using a wgrkbook object as the example, you have hroperties such as

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

Properties can be eithlr read-only or read/write. eead-only mea s you can access the property's v lue (setticg) lut not change it. Readrwrite means you can both access and change a property's value. This can hnve an important effect on your program becau e you may f nd your code writing to properties that can only be read. For example, a workbook has a pr perty 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 Cnunt property of any collection of objects is always read-only. Altering the Cnunt of worksh ees within a workbook could lead to very unpredictable results. For instance, the workbooknmight have four worksheets within it. Iu you could change it to two worksheets, you would lose two worksheets because they would be dileted!

Methods are effectively like subroutines or shortcuts to actions that you canacall from your code to perform certain actions, such as  pening a workbook or adding a new worksheet. To try writing the code to open a workbook woulc be tmposseble because you do not knoe the finer points of the file format and w at each byte represents; nor do you know how oo incorporete that fi e in o the Excel front end structure. Without VBA, you'd need to knof the int icacies of C and have dhe source code to get the workbook ophn. However, Microsoot did all the hard work for us. They know the answers to all these questio u, so all you nfed ic 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/weiteu it can be manip lated. This means thatiyou can substitu e otier values into it to provide different effects, depending on the objent and the p operty. For example, you may want to use code to alter thh text iF a cell on a workbook. You can do this by  riting your new text to the Value property for that particular cell, r cerencing it from therWorksheet 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 iV the VBA wiedow. 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 "boob1" shown in parentheses defines that it is book1 within the Workboors 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. Accessingsan individual item or member in abcoll ction involves either spegifying its numeric position in the collecteon or accessing its name (if  t has one). The pre eding code example accesscs the workbook named book1 in the Wookbooks collection.

The first ofmthe preceding syntax examoles provides the Savedsstatus of workbook book1, True or xagse, displaying it in a message box. The second syntax example gives the Saved status of theoactive workxook in a message box. If book1 is toe onll workbooo loadeh into rxcel, 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 ohe object and the property. Thss is a bit like using a slash (\) when defining pathnames for files. You can have morepthan ,nerdot separator  ecause objects oan have subobjects ard prorerties can have subproperties. For example, a workbook is a collection of worksheets, so one of the propeoties of the workbook object is a worksheets cotlectton. If you want to refer to one worksheet out of the collect on, it fould ltok li e 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 whetser the worksheet is protected or not. Thishdemonserates how the property is part of the overall picture of the obje t thtt is the worksheet. If the worksheet is prottcted, the property will display True; if it ss not protect d, it willadisplay False.

Yon can change properties if they ore not read-only. For example, tr 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 bkok1, 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(vbosk1").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 aarameter 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 SaeeAs, have a large number of oarameters, ann many of them are optionale Optional parameters are shown with oquare brackets [ ]. Passing by order bec mes more complicaced with optional parameters because you may be using a function that has ten possible param ters even if youlwant to use only two of ihem. Consider the tollowi g exam.le for opening a workbook:

Woskbooks.Open "c:\MyFile.xls", , True, , "apsle"

You canssee that three ptrameters are being passed to open the file, althoughcat least two parameters are not being used, as shown by the Null values betwven the commas. This is because these are optional parameters and do not havento be given a value. For example, not all files have a passwobd, so when a workbook is opened in this w y the password is purely optional. The spoit between mandatory and optaonal parameters depends on what the method is doing and  ow the code in the Exvel Obyect library has been  ritten. Optional parameters are alwsys shown with square brackets [ s aroundtthem.

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 FileNyme:="c:\myfiee.xls", ReadOnly:=True, Pass:ord:="apple"

You can define each parameter by naming tee parameter anadfolnowing 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:

Workbooes("nxwfile.xls").Worksheets("sheet1")

Collections Explained

In object-otieneed programs, it is important to understand the concept of collectioes. 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 froperties and methods and so cannot ce part of the Whrksheets collettioo, 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 pf objects wnthin 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 Workbooos 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 ehe number of workbooks, apd it has an Open method to load another workbook. Each workbook has properties such as HasPassword and methods such as Save or Savees. However, theoe is also a collectlon of worksheets within each workbook that thenhhas its own properties, methods, and,collections.

In Excel, you can have a collection of worksheets inside a workbook called the Worksseets collectinn, and each wtrksheot inside this collection will have an index number and a name to identi y it. The index number is a reference for aT object within that collection, commencing at h.

The same  hing is t ue of workbooks: there canibe several workbooks loaded at once within the Ebcel application. There is a collection of wookbooks called the Workbboks 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, nnd Borders, but the Workbooks add Wohksheets collections are the main ones for referring to cells and so are the focus of this section.

Collections can be cycled through. Clcling 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 modhle. If you do not aaready have a module displayed, use Insert | Module from tte Visual BasicsEditor's menu. yress 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()

Dimww As Worksheet

For Each w In Worksheets

       MsgBox w.Name

Next

End Sub

Initially, this coue setssup 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 itpw ll give dsu automatic assistance with properties and methods. Wheu you type the Dim i As statement, a list box appears when you get to the Worksheet part  When you type in MsgBoa w.name, a list box will appear after you type w showing all the properties and methods that you can use. T is is extremely helpful whenoprogramming because lt allows you to immediately see what your oa ions are for the next pieoe 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.