Objects, Properties, Methods and Events

In many ways, the logic used in the Excel/VBA environment is similar to that of natural language:

• Objects are analogous to nouns. They are essentially the parts of Excel that can be seen, including cells, ranges, rows, columns, workbooks, worksheets, charts, PivotTables, text boxes, cell comment boxes, shapes, and so on.
• Properties are analogous to adjectives, describing some aspect of an object, of which there are generally several. For example, properties of a book include its author, title, number of pages, weight, and so on, whereas those of an Excel cell may include its value, or its type of formatting.
• Methods are analogous to verbs (e.g. a book could be opened, closed, read, put on the bookshelf, bought or sold).
• Events are like methods, but where the action is triggered by the system (such as when a workbook is opened, the value in a worksheet is changed, and so on).

The objects, properties, methods and events are of course specific to those that are relevant to the Excel/VBA environment (and do not have the full richness and complexity of natural language). As a set of instructions to be carried out by a computer, the syntax of VBA is more concise and precise than that of natural languages: for example, rather than “Would you mind washing the dishes, please?”, the code would be analogous to “dishes.wash”. In addition, whilst in natural language it is generally clear from the context which dishes are being referred to (our dirty ones, rather than the neighbours' clean ones), such contextual issues are not necessarily uniquely defined when considered from a computer's perspective. To write robust code, it would generally necessary (or better) to specify such issues very precisely and unambiguously.

As in natural language, the properties of an object may result in another object: for example, the “screen property” of a computer is also an object (i.e. the screen). Similarly, in VBA, Range("A2:B5").Rows refers to the rows of that range (i.e. Rows 2, 3, 4 and 5), and Range("A2:B5").Rows.Count would use the Count method to count the number of rows (i.e. four). Other methods associated with range objects include Activate, ClearContents, ClearFormats, Copy, PasteSpecial and Select.

Note that one specific object is Application, which represents the entire application (i.e. Excel in this case).

Object Hierarchies and Collections

Within Excel/VBA, objects can exist in Collections as well as in Hierarchies.

Examples of Collections include:

• Workbooks is the set of all open workbooks.
• Worksheets is the set of worksheets in the specified or active workbook (the Sheets object includes both worksheets and chart sheets).
• ChartObjects is the set of charts in the specified or active worksheet. Charts is the set of chart sheets in the specified or active workbook, not the set of charts.
• Names is the set of named ranges in the specified or active workbook.
• PivotTables is the set of all PivotTables in the specified or active worksheet.
• WorksheetFunction is a top-level object, whose properties are the individual Excel functions.

This gives rise to hierarchies of objects. Thus, using the fact that Worksheets is a member of the Workbook object:

• Workbooks("Model.xlsm").Worksheets("Data1") refers to the indicated worksheet (i.e. Data1) in the Model workbook.
• Workbooks("Model.xlsm").Worksheets.Add is part of the syntax required to add a new worksheet to the Model workbook.

Note that when working in VBA, typing “.” after an object collection invokes VBA to provide a drop-down list of methods and properties that can be applied at that point.

The use of collections can allow certain operations to be conducted easily that might otherwise appear to be complex or time-consuming (see later examples).

Using Set…=… .

The use of the Set statement is of fundamental importance when working with Excel objects. For example, in the last chapter, we used basic code lines from such as:

i = Range("C2")
j = Range("C3").Value

On reflection, it becomes clear that the first line of code is unclear or ambiguous: the role of the variable i in the code is to take a value, whereas C2 is a cell (not just a value) that has several properties; its value, font size, colour, other formatting aspects, and so on. Therefore, the first line is trying to assign a multi-faceted object to a single number. This is inherently inconsistent, and indeed one may wonder as to why the code can execute at all (the reason being that the default property of a range object is its value). The second line of this code is clearer, because it explicitly works with the Value property of the range and assigns only this to the variable j.

The Set statement can be used not only to overcome this ambiguity, and is in fact generally necessary, especially when the left-hand side of an “=” statement is an object. For example:

• Set DataRange=Range("A1:A100")
• Set Rng = ActiveSheet.UsedRange

The failure to include Set is a common error, resulting in an error message, such as Object variable not set (Error 91) when one runs the code.

Using the With…End With Construct

The With…End With construct allows one to specify an object and to conduct several operations with that object using syntax that is more concise. Thus, one could write:

With Range("A1").Font
  .Name = "Calibri"
  .Size = 10
  .Color = -16776961
End With

The construct can be embedded within others, such as:

With Application.ThisWorkbook
 With Range("A1")
  With .Font
    .Name = "Calibri"
    .Size = 10
    .Color = -16776961
  End With
 End With
End With

The main uses in practice are:

• To increase speed of writing code that refers to the same object multiple times.
• To facilitate the use of full referencing, by making it less cumbersome to implement.
• To make the code more transparent by emphasising which object is being worked with.

Finding Alternatives to the Selection or Activation of Ranges and Objects

Typically, the code that results from recording processes contains steps in which cells, ranges or objects are selected (or activated). On the other hand, often the ultimate operation needed does not require such selection or activation processes. It is almost always much more computationally efficient and much quicker to run code that is written to eliminate the explicit selection or activation of objects (which is often, although not always, possible). In some cases, such as when working with charts or PivotTables, it may be necessary to activate objects before some operations can be performed on them, but it is generally worth trying to test if such activation is truly necessary.

For example, the recording of code to clear the range A1:E8 would give:

Range("A1:E8").Select
    Selection.ClearContents

whereas the direct equivalent would be

Range("A1:E8").ClearContents

(The word Selection is worksheet-specific and is a word reserved by VBA to refer to the last range that was last selected on that worksheet.)

Similarly, the use (but not the selection) of syntaxes such as xlCellTypeLastCell, CurrentRegion or UsedRange (discussed later in this chapter) are typically more efficient than the results of recording Excel operations such as Ctrl+Shift+RightArrow, which might give code such as:

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Basic Syntax Possibilities and Using Named Ranges

There are many ways to refer to Excel range objects:

• Range("B2") refers to a single cell.
• Range("B2:C10") refers to the range consisting of cells B2 to C10.
• Range("B2", "C10") also refers to the range consisting of cells B2 to C10.

Named Ranges and Named Variables

As mentioned earlier, within VBA code, it is almost always preferable to use named ranges instead of direct cell references. As well as being more robust if the worksheet structure were to change (new rows or columns added), their use generally creates more transparency.

There are several situations that arise, which affect the appropriate point at which a name should be assigned to a range:

• Where the size and location of the ranges are fixed, one can define the names in the Excel workbook (using Excel's Formulas/Name Manager menu) as the model is being built (or as the VBA code is being written). A range defined in this way would be referred to in the code using inverted commas (e.g. Range("PriceInput") or Range("NumberofTimestoRunLoop"), and so on). When auditing such code, to find out which range is being referred to by the name, one could use either the Excel Name Manager, or VBA's Immediate window (see later).
• Where the size or location of the range is determined only when the code is run, the name would be defined within the VBA code, such as:

Set dRange = ……

The right-hand side of the Set statement would refer to the (variable-sized) range that is determined at run time from the data set (perhaps using some of the techniques below, such as CurrentRegion, UsedRange and so on). The left-hand side creates an object variable that corresponds to that range. When referring to such a range within the code, one can then directly use the variable name (i.e. not the inverted comma format), such as:

NRows=dRange.Rows.Count

• One could also use the Add method within VBA to create an Excel name as the code is run (for both fixed- and variable-sized ranges). For example, to create a new name that refers to cells A1:B100 on the worksheet Sheet1, one could use:

Names.Add Name:="DataRange", RefersTo:="=Sheet1!$a$1:$B$100"

When doing so, one would need to ensure that the name does not already exist (for example, by attaching an indexation number to it that is tracked and incremented each time a name is added). Often, this approach is therefore not as useful or as convenient as the other approaches.

The CurrentRegion Property

The CurrentRegion property of a cell or range is the largest two-dimensional range that results from including within it all cells which are contiguous with some other cell of the region. That is, it is the range (surrounded by empty cells or the physical limits of Excel) that would be found if one started at any cell within the range, and included every non-empty cell which is contiguous to it, and then all non-empty cells contiguous to these, and so on, until there are no further new cells to include.

If one were not aware of the syntax, one could establish it by recording a macro, using either:

• The F5 (GoTo)/Special/Current region menu sequence.
• The short-cut Ctrl+* (Ctrl+Shift+8).

Note that the recording process will generally result in the range being selected, creating code such as:

Selection.CurrentRegion.Select

whereas it will generally be more efficient not to select objects (but to use only the CurrentRegion part of the syntax in the later code).

The xlCellTypeLastCell Property

One can locate the last cell that contains data or formatting by using either:

• The F5 (GoTo)/Special/Last cell menu sequence.
• The short-cut Ctrl+End.

Once again, the macro that results from the recording process will generally select the last cell, such as:

Sub Macro4()
    Range("A1").Select
    Selection.SpecialCells(xlCellTypeLastCell).Select
End Sub

However, for practical purposes, one would need to extract the relevant code elements without explicitly selecting any cells:

With Range("A1")
  Set lcell = .SpecialCells(xlCellTypeLastCell)
End With

after which further operations may be conducted, such as:

NRows = lcell.CurrentRegion.Rows.Count

Thus, in principle, if it were known (due to structural reasons) that all the data on a worksheet were in a single contiguous range, then a code line such as:

With Range("A1")
Set dRange=.SpecialCells(xlCellTypeLastCell).CurrentRegion
End With

would create an object variable that represents the full data set (including any headers).

Note that the last cell may be blank if the formatting or contents of that cell have been altered during a working session (e.g. content inserted and then deleted). Thus, when using this to identify a data range, one may have to take care as to whether such operations are conducted within the code, and adapt the code accordingly.

Worksheet Names and Code Names

In general, one may need to state which worksheet (and perhaps which workbook) is desired to be referred to during the run of the code. For example, a statement such as Range("A1") does not make clear on which worksheet the range is. Worksheets can be referred to either:

• Using their names as defined by the model builder when working in Excel (or using the Excel defaults, such as Sheet1 etc). The VBA code would be, for example:

With Worksheets("Sheet1")
…
End With

• Using the VBA code name for the sheet. Within the VBE window a worksheet can be given a name by first selecting it within the Project window, and changing its associated name within the Properties window. This code name can be different to the Excel name, so that the use of the code name will mean that the code will still work even if the worksheet name in Excel is changed:

With DataSheet
…
End With

The UsedRange Property

The UsedRange is a property of a worksheet that defines its used range (this may include blank rows and columns where these have been used in some form, including the addition and subsequent deletion of content).

The syntax requires a worksheet to have been specified, such as:

When using Excel worksheet names:

With Worksheets("Sheet1")
Set dRange = .UsedRange
End With

or when using code sheet names:

With DataSheet
Set dRange = .UsedRange
End With

One can easily see the UsedRange by running code such as:

With DataSheet
.UsedRange.Select
End With

This can be used in an analogous way to the xlCellTypeLastCell property, with the advantage this it unifies multiple non-contiguous ranges.

Note also that some (perhaps expected) range definitions do not exist in VBA (e.g. CurrentRange, ActiveRegion or ActiveRange do not exist).

The Cells Property

The Cells property creates a range defined relative to the starting point of another range. It can be used in many contexts:

To operate on all the cells in a worksheet. For example:

With Worksheets("Sheet1").Cells
.ClearContents
End With

or

With Worksheets("Sheet1").Cells
 With.Font
  .Name = "CourierNew"
  .Size = 10
 End With
End With

• To operate on cells defined relative to the starting point of a worksheet. For example, to place the value 400 in Cell C5, i.e. the 5th row and 3rd column of the worksheet

With Worksheets("Sheet1").Cells(5, 3)
.Value=400
End With

• To operate on cells defined relative to another range. For example, to place the value 500 into the cell that is 5 rows and 3 columns removed from Cell B2 (i.e. into Cell D6), one could write:

With Worksheets("Sheet1").Range("B2").Cells(5, 3)
.Value=500
End With

Note that the following statement refers to Cell C7, being the cell that is the 5th row and 3rd column of the range that starts at Cell A3:

Worksheets("Sheet1").Range("A3:B10").Cells(5, 3)

• To specify a range of cells on a worksheet, such as the range A1:C5:

With Worksheets("Sheet1")
Range(.Cells(1, 1), .Cells(5, 3)).Value=800
End With

• To find the first cell in the UsedRange of a worksheet:

With Worksheets("Sheet1").UsedRange
Set dstart = .Cells(1, 1)
End With

Cells can also be used in the context of the cell number in the worksheet or relative to a starting cell (from left to right and then top to bottom), such as Cells(1), Cells(2). In particular, this can be used to find the starting point of a range (i.e. Cells(1)). However, there is a risk of lack of compatibility and errors when using this property more widely, since code may have been originally written in Excel 2003 (which had 256 columns): Cells(257) would correspond to Cell A2 in Excel 2003 but to cell IW1 in Excel 2007.

The Offset Property

The Offset property of a range is conceptually similar to Excel's OFFSET function. However, there are a number of differences:

• It is a property of a range, not a function.
• Its reference range is external to it (not an integral part or parameter of the function).
• There are no optional height and width arguments.

Examples of its use include:

• Range("A1").Offset(2, 0) refers to Cell A3, being offset by two rows and no columns from Cell A1.
• Range("A1:B5").Offset(1,2) is the range C2:D6, i.e. that which is offset by one row and two columns from the reference range.

To a large extent, Offset is an alternative to Cells, so that they are interchangeable. In many cases, Offset may be more intuitive than Cells (just as, with Excel functions, OFFSET is often more intuitive than INDEX). In Excel, the OFFSET function is Volatile, and so is to be avoided unless no real alternative is available (see Chapter 5 and Chapter 26). However, no such restriction exists in VBA, so that the use of Offset or Cells is often just a case of personal preference (the author generally has the habit of using Offset).

The Union Method

The Union method unites several ranges. Thus, Union(Range("B2"), Range("C10")) refers only to the cells B2 and C10, whereas Range("B2", "C10") would refer to the full range B2:C10.

The following sets the values and the font formats of non-contiguous cells:

Set uRange = Union(Range("A1"), Range("B7"), Range("C5"))
With uRange
.Value = 500
 With .Font
  .Name = "Arial"
  .Size = 18
 End With
End With

InputBox and MsgBox

The InputBox and MsgBox can be used to communicate basic information to and from a user, including to take simple numerical information that could be used within the code (such as which scenario number is to be run, or the number of times to run a recalculation loop in a simulation model). In the code below, the first input box code line will provide a message but the user input is not stored, whereas the second input box code line will store the user's input in the variable x, and the message box will display this stored value:

Sub MRTakeInput1()
InputBox "Type a Number"
x = InputBox("Sorry, retype it")
MsgBox x
End Sub

Application.InputBox

In many applications, it can be important for the user to input a cell reference (not simply a number). For example, one may wish to point to the first cell of a data set, so that the code can then detect the full set of data that is contiguous with the cell (and which may form the definition of the data that is to be manipulated). In this case, the input cell reference would be an object variable, so that the Set statement is required, with the Type argument used to indicate that the input provided is a cell reference (by use of the value 8):

Set dInputCell = Application.InputBox("Select Any Cell within Data Range", Type:=8)

One can then define the data range as being (for example) the current region associated with that input cell:

Set dFullRange = dInputCell.CurrentRegion

and one can find the first cell of that range using:

Set dStartCell=dFullRange.Cells(1,1)

Defining Multi-cell Ranges

Where possible, it is usually more efficient (i.e. the code will run more quickly) to work with multi-cell ranges, rather than the individual cells within the range. For example, to place the same value in all cells of the range A1:C5, code such as the following could be used:

With Worksheets("Sheet1")
Set dRange1=Range(.Cells(1, 1), .Cells(5, 3))
dRange1.value=100
End With

Similarly, to place the same value in the range A12:C16, the following could be used:

With Worksheets("Sheet1").Range("A12")
Set DRange1 = Range(.Offset(0, 0), .Offset(4, 2))
dRange1.Value = 200
End With

Using Target to React to Worksheet Events

The word Target is a reserved word in VBA that is used to refer to a range that has been just changed by the user (not by an Excel recalculation). It can be used to react to events that occur within a worksheet (so that a macro is run as such events occur). The code containing this word must be placed in the code module of the worksheet in which one wishes to detect such changes (not in a general code module), and also use the Worksheet_Change subroutine (which is reserved syntax for code that is to run when such worksheet events occur).

As an example, the following code will ensure that any changes made to the sheet (such as the entry of data, formulae or text) will be displayed in green and with a font size of 14.

Sub Worksheet_Change(ByVal Target As Range)
 With Target.Font
  .ColorIndex = 10
  .Size = 14
  End With
End Sub

Similarly, the following code would ensure that the entire row and entire column of a cell is highlighted when one double-clicks on it (see Figure 29.1):

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 Set myWorkingRangeRow = Target.EntireRow
 Set myWorkingRangeCol = Target.EntireColumn
 Set myRangetoShow = Union(myWorkingRangeRow, myWorkingRangeCol)
myRangetoShow.Select
End Sub

Using Target to React to Workbook Events

In order to detect and react to changes anywhere in the workbook, one can use the Workbook_SheetChange subroutine, which must be placed in the ThisWorkbook module, for example:

Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 str = "You just changed cell " & Target.Address & " of " & Sh.Name & " to " & Target.Value
 MsgBox str
End Sub