Objects
• Visual Basic is an (OO) object-oriented
language.
• Performing a task in Visual Basic (VB) or
Visual Basic for Applications (VBA) involves
manipulating various types of objects, each of
which may have several different properties
and methods.
• To perform a task using VBA you return an
object that represents the appropriate Excel
element and then manipulate it using the
objects’ methods and properties.
1B40: Data analysis 1
http://www.hep.ucl.ac.uk/~za/
Objects
A simple statement
Range(“A1”).Select
illustrates an important characteristic of VB.
The syntax of many statements first specify an object,
Range(“A1”), and an action upon it, Select.
An object is a special type of variable that contains both
data and code and represents an element of Excel.
Objects exist only in the computer’s memory; they
don’t appear in your code.
1B40: Data analysis 2
http://www.hep.ucl.ac.uk/~za/
Objects
• One important difference between an object variable
and any other type of variable is that an object
variable holds only a reference to a specific object,
rather than the object itself.
• Consider the example
Dim numA As Integer, numB As Integer
numA = 1
numB = numA
numB = 2
MsgBox ("A=" & numA & ", B=" & numB)
numB is a copy of numA, so setting numB
to have the value 2 has no effect on
numA, which still has the value 1.
1B40: Data analysis 3
http://www.hep.ucl.ac.uk/~za/
Objects
• The situation is different for object variables:
Dim fontA As Font, fontB As Font
Set fontA =
ActiveSheet.Range("A1").Font
fontA.Bold = False
Set fontB = fontA ’Note: fontB and
fontA refer to same object
fontB.Bold = True ’so changing object
fontB changes object fontA
1B40: Data analysis 4
http://www.hep.ucl.ac.uk/~za/
Methods
• In VB an action that an object can perform is referred to as a
method.
• Consider the object Dog. To cause it to bark we could write
Dog.Bark
• However a Dog is capable of more than barking, for example we
could have
Dog.Sit, Dog.Fetch.
• In Excel, for example, the statement
ActiveCell.Delete
• calls the Delete method of the ActiveCell object, which
deletes the contents of the cell.
• The list of methods that an object can perform depends on the
object. The Range object supports about 80 different methods.
1B40: Data analysis 5
http://www.hep.ucl.ac.uk/~za/
Properties
• An object can have properties.
• A property is a quality or characteristic of the object, e.g the
length of the dog’s tail, the loudness of its bark.
• If you think of objects as the nouns of VB, then properties are its
adjectives and methods are its verbs.
• In Excel the properties may themselves be either primitive data
types such as numbers, strings or Boolean values, or may
themselves be objects of some kind.
• ActiveCell is of the object type called Range, and one of its
properties is called Value and represents the value (number,
string or formula) held by the cell.
• The statement
Application.ActiveCell.Value = “Hello”
will place the string “Hello” in the active cell.
1B40: Data analysis 6
http://www.hep.ucl.ac.uk/~za/
Collections
• A collection is an object that contains a group of
related objects. Each object within the collection is
called an element of the collection. Collections are
objects so have associated methods and properties.
• An example is the Sheets collection, which
represents the worksheets in the active workbook.
This behaves a bit like an array, in that a specific
worksheet in the collection can be referenced using a
numeric index: Sheets(2).Activate
• This makes the second worksheet active.
• Unlike a normal array, the index in a collection object
can be a name instead of a number:
Sheets(“Chart1”).Activate
1B40: Data analysis 7
http://www.hep.ucl.ac.uk/~za/
Looping collections
• To loop over all the members of a particular collection, one can
use the For Each syntax:
Dim rangeX As Range, cellY As Range
Dim i As Integer
Set rangeX = ActiveSheet.Range("A1:C3")
i = 1
For Each cellY In rangeX.Cells
cellY.Value = i
i = i + 1
Next
• The above piece of code uses a loop, in which the object
variable cellY refers to each cell member of the collection
rangeX.Cells in turn. The code assigns the value 1 to A1, 2
to A2, 3 to A3, 4 to B1 etc and 9 to C3.
1B40: Data analysis 8
http://www.hep.ucl.ac.uk/~za/
With statement
• The With statement provides a way to carry out several
operations on the same object with less typing, and often leads
to code that is easier to read and understand.
• For example,
Selection.Font.Name = "Times New Roman"
Selection.Font.FontStyle = "Bold"
Selection.Font.Size = 12
Selection.Font.ColorIndex = 3
could be rewritten as
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 12
.ColorIndex = 3
End With
1B40: Data analysis 9
http://www.hep.ucl.ac.uk/~za/
Macro recorder
• The macro recorder is a convenient way to build expressions that return objects
as it knows the object model of the application and the methods and properties
of the objects.
• However it can produce very verbose code. Consider the following example to
change the size and font in a chart’s title:
Sub Macro1()
ActivateChart.ChartTitle.Select
With Selection.Font
.Name = “Times New Roman”
.FontStyle = “Bold”
.Size = 24
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = False
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
End Sub 10
1B40: Data analysis
http://www.hep.ucl.ac.uk/~za/
Macro recorder
• This code contains many redundant lines.
• Only the size and fontstyle were changed from the
default values. This code after recording should be
changed to
Sub FormatChartTitle()
With Charts(“Chart1”).ChartTitle.Font
.FontStyle = “Bold”
.Size = 24
End With
End Sub
1B40: Data analysis 11
http://www.hep.ucl.ac.uk/~za/
Getting, setting properties
• With some properties you can set and return their
values – these are called read-write properties.
• With others you can only return their values – these
are read-only properties.
• We have already met examples of setting a property,
Worksheets(“Sheet1”).Range(“A1”).Value = 42
Worksheets(“Sheet1”).Range(“A1”).Value = _
Worksheets(“sheeet2”).Range(“B2”).Value
• To get the value property of cell A1 in sheet1 we
would use
myValue = Worksheets(“sheet1”).Range(“A1”).Value
1B40: Data analysis 12
http://www.hep.ucl.ac.uk/~za/
Count property
• There are some properties and methods that are unique to
collections.
• The Count property is one.
• It returns the number of elements in a collection.
• It is useful if you want to loop over the elements of the collection
The following example uses the Count property to loop over the
worksheet in the active workbook, hiding alternate ones:
Sub HideEveryOtherSheet()
For i = 1 To Worksheets.Count
If i Mod 2 = 0 Then
Worksheets(i).Visible = False
End If
Next i
End Sub
1B40: Data analysis 13
http://www.hep.ucl.ac.uk/~za/
Looping collections
• The recommended way to loop over collections is to use the
For Each … Next loop. In this structure VB automatically sets
an object variable to return each object in the collection in turn.
The following code loops over all workbooks open in Excel and
closes all except the one containing the procedure:
Sub CloseWorkbooks()
Dim wb As Workbook
For Each wb In Application.Workbooks
If wb.Name <> ThisWorkbook.Name Then
wb.Close
End If
Next wb
End Sub
1B40: Data analysis 14
http://www.hep.ucl.ac.uk/~za/
Range – object and method
• The range object can represent a single cell, a range
of cells, an entire row or column even a 3-D range.
The range object is unusual in representing both a
single cell and multiple cells.
• One of the most common ways to return a range
object is to use the range method. The argument to
the range method is a string, e.g. A1 or a name
“myRange”.
• Examples are:
• Range(“B1”).Formula = “=10*RAND()”
• Range(“C1:E3”).Value = 6
• Range(“A1”, “E3”).ClearContents
• Range(“myRange”).Font.Bold = True
• Set newRange = Range(“myRange”)
1B40: Data analysis 15
http://www.hep.ucl.ac.uk/~za/
Cells method
• The cells method takes numeric arguments instead of string
arguments.
• When used to return a single cell the first argument is the row,
the second is the column.
Cells(1,2).Formula = “=10*RAND()”
• The cells method is useful if you want to refer to cells in a loop
using counters. The example loops through the cells A1:D10
and replaces any whose value is less than 0.01 by zero.
Sub SetToZero()
For colIndex = 1 To 4
For rowIndex = 1 To 10
If Worksheets(“Sheet1”).Cells(rowIndex,ColIndex)< _
0.01 Then
Worksheets(“Sheet1”).Cells(rowIndex, _
colIndex).Value = 0
End If
Next rowIndex
Next colIndex 1B40: Data analysis 16
End Sub http://www.hep.ucl.ac.uk/~za/
Combined Range and Cells
• You can combine the range and cell methods.
Suppose you want to create a range object defined
by a top row, a bottom row and left and right columns.
• The code below returns a range object that refers to
cells A1:D10.
• The cells method defines cells A1 and D10 and the
range method then returns the object defined by
these cells:
Set areaObject = _
Worksheets(“Sheet1”).Range(Cells(1,1), Cells(10,4))
1B40: Data analysis 17
http://www.hep.ucl.ac.uk/~za/
Offset method
• Sometimes you want to return a range that is a certain number
of rows and columns from another cell.
• The Offset method takes an input range object, and rowoffset
and columnoffset arguments to return a range.
• Suppose the data in a column of cells is either a number or text.
The following code writes “Text” or “Number” in the adjacent
column.
For Each c in _
Worksheets(“sheet1”).Range(“A1:A10”).Cells
If Application.IsText(c.Value) Then
c.Offset(0, 1).Formula = “Text”
ElseIf Application.IsNumber(c.Value) Then
c.Offset(0, 1).Formula = “Number”
End If
Next c
1B40: Data analysis 18
http://www.hep.ucl.ac.uk/~za/
