Topic 9 | Working With Objects
In this section we explore VBA programming in more depth and formalise our
understanding of working with objects in an object-oriented programming
language. By the end of this lesson you should be able to write VBA code and so
include code that cannot be created through recording.
Introduction | Learning Outcomes | Study Time: Week 7
VBA is an example of Object Oriented Programming or OOP. This means that VBA code consists of
Objects together with the Methods that can be used with each type of object.
These objects are not the kind of objects you might see around your living room – a television, an
empty wine glass, dust particles … the objects are not material but digital or virtual! Each object type
has a specific set of methods associated with it.
In this section we will learn about:
Hierarchy of objects
Syntax
The range object
Working with ranges
Objects and collections
Working with cells, sheets and ranges
Starting Out With Objects | Hierarchy of Objects
In Excel there are many different kinds of objects, each kind with its own set of characteristics or
properties and its own set of methods that can be applied to the object.
Examples of objects within Excel are Worksheet, Range, Name, Window and Chart. The objects are
hierarchical with the object at the top of the hierarchy being the “Application” – Excel itself. Other
examples of Application Objects are Word and Outlook.
The adjacent diagram shows
examples of Objects at different
levels.
So for example a Range
Object is found within a
Worksheet Object which is
found within a Workbook
Object which is found within an
Application Object.
This is a bit abstract so for now
it’s sufficient to bear in mind
that this is the reason for the
sometimes lengthy lines of code
you see giving exact reference to
a particular object, e.g.
Workbooks(“Task17.xlsx”).Worksheets(“Sheet2”).Range(“A1:C5”)
At other times the code
Range(“A1:C5”)
might have sufficed if the context had already been specified.
Syntax
When writing code in a programming language great care must be taken to use the language
correctly.
The code must follow the specific rules of the programming language, i.e. the syntax of the code must
be correct.
Incorrect code will result in syntax or compile errors!
Range
A Range can refer to an individual cell or to a collection of cells, e.g.
Range(“B1”) refers to cell B1
Range(“B1:D4”) refers to all the cells in the rectangle defined by B1 and D4
Range(“B1:C5,E2:F6,G1”) refers to three separate groups of cells, which are all treated as one
range here
You can name ranges for easier use and manipulation in code.
A range is defined using the Define Name option on the Formulas tab. For example: if the name
MyCells has been defined as the cells B1:B5, then the cells can be referred to in the code as
Range(“MyCells”).
Note the syntax: Object(“Reference”) – the quotation marks and brackets are necessary.
Activity 9.1 | Working With Ranges
1. Define a range of multiple areas of cells in a worksheet.
2. Create a macro that puts the date and time into the each of the cells
formatted in red, italics and bold.
3. Create a button on your worksheet that runs the macro.
4. Modify your macro or include additional macros that format in different ways.
5. Save your workbook as a macro-enabled workbook
Tips
1. Define the Range by using the Formulas tab and Define Name or select the cells, right click on the
selection and select Define Name. To select multiple areas of cells use the control key when
moving between the areas.
2. You can’t record this as a macro so you’ll have to write the code (or modify code you already
have). To create a new macro click on Macro in the Developer tab, type in a name for your new
macro and click on Create. This will take you into the Visual Basic Editor into a prepared Sub
space.
You can use the code below, making sure you match the name in the macro with the named range
you created, or you can copy and edit the code in your macro from Task 1 in the first VBA lesson
xlBlueText.xlsm.
Sub macroFillWithDate()
Range(“rgDemoCells”).Value = Now()
With Range(“rgDemoCells”).Font
.Color = vbRed
.Bold = True
.Italic = True
End With
End Sub
Note that in this example the formatting is applied to a range and not a selection.
3. Create a button using Insert on the Developer tab. Hover over the icons to identify and then select
Button (Form Control) and then drag an area for your button. A dialogue box then appears for you
to assign a macro to the button.
See if you can label your button with more appropriate text.
Here’s what it should all look like ( it doesn’t matter where your multiple areas are):
4. Experiment with different formatting in this or additional macros. You can record macros to
investigate appropriate code, but note that recorded macros often include unnecessary code. Try to
delete everything that is not necessary.
You could add additional buttons for different formatting.
Information | Some Notes About Code
(1) Sub macroFillWithDate()
(2) Range(“rgDemoCells”).Value = Now()
(3) With Range(“rgDemoCells”).Font
(4) .Color = vbRed(5) .Bold = True
(6) .Italic = True
(7) End With
(8) End Sub
Note that I have now numbered the lines of code: this is to make this explanation easier to follow. If
you keep the line numbers in your code, it won’t run.
Lines 1 & 8
Some lines come in pairs. For example, lines 1 and 8. Line 1 says when the macro (in this case a
subroutine) starts. Line 8 says when it finishes. This marks out the beginning and end of the
section of code for this macro. The () at the end of the sub name might look unnecessary, but it
needs to be there. It’s used for passing data to other macros or parts of the macro.
Line 2
Line 2 addresses the Range that has already manually been defined and given the name
rgDemoCells. We then consider the value(s) in the cells of this range. Value is a property of the
Range Object.
Now() is a built in function of VBA that retrieves the current date and time. So, in total, line 2
writes the current date and time into each cell in the range.
Lines 3 & 7
Lines 3 and 7 go together as a pair and contain further lines related to these lines of code. We want
to do three things to the font in our defined range: make it red, make it bold and make it italicised.
We could do this one line at a time like this:
Range(“rgDemoCells”).Font.Color = vbRed
Range(“rgDemoCells”).Font.Bold = True
Range(“rgDemoCells”).Font.Italic = True
Using the statements With and End With groups together statements of the same type (i.e. about
the font in this case) and gives us a shorthand way to do it. In this case it is not a big saving as we
only want to change three aspects, so the total amount of code is similar. However, this could have
been 100 changes. Any repetition of code (you can sometimes spot it visually) should be
investigated to see whether there’s a more efficient way of doing what you need to do.
Note how code between the paired lines is indented to add clarity.
Taking Objects Further | Objects and Collections
As we have seen, the key parts of Excel which we handle in our code – such as cell ranges,
worksheets, charts – are called objects. These objects are arranged in a hierarchy.
In addition to the individual objects, VBA also allows us to handle collections.
A collection is a group of objects of the same type A
collection is also an object
Some examples are shown in the following table:
A collection of all Chart objects (Chart
Charts sheets) in a Workbook.
A collection of all Worksheets in a
Worksheets
Workbook.
A collection of all sheets, both
Sheets Worksheets and Chart sheets in a
Workbook.
Collections also have properties and methods, e.g. Sheets.Count – the count property tells us how
many sheets there are in the collection.
Working With Cells
Although an Excel worksheet is made up of cells, a cell is not in fact an Object in VBA. We have
already met the Range object: this contains cells so that if we wish to select a particular cell, D2 say,
as an object we would use the following code.
Range(“D2”).Select
However, referring to cells directly is also possible and this is done using the Cells property with two
arguments, the row and the column. So the following code could be used to select the cell D2.
Worksheets(Sheet1).Cells(2,4).Select
In practice there is very little difference.
Another example of how the Cells property might be used is:
Range(Cells(1,2), Cells(4,5)).Value =101
which would fill cells B1 to E4 with the value 101.
Cell References
You will have noticed that in this formulation cells are referenced in a different way and order from
how you would normally reference a cell in Excel.
When we use the reference C4 we are referring to the column first and the row second.
When we use the cell property, cell C4 is referred to as Cells(4,3) referencing the row first and the
column second.
For example, the code
Worksheets(“Sheet1”).Cells(3,8)
refers to the cell H3 and not C8.
This will probably be very confusing but needs to be remembered!
If you have worked with entries in a matrix you will recognise this referencing order:
[a1,1 a1,2 …. a2,1 a2,2 … a3,1 a3,2….]
Cells Property | To Use Or Not To Use
You might think that you can avoid this confusion by sticking to the Range object and ignoring the
Cells property. However, there is also a very useful Range property which uses this aspect of cell
referencing. We will look at this on the next page.
Also, it can be very useful to use the Cells property if your references are not fixed and you need to
use variables for the cell row and/or column.
Referencing & Arguments | Cells Property vs Range Object
With the Cells property you reference the row and column as separate arguments.
With the Range object the cell reference is one argument.
Offset Property
The Offset property is used with the Range object. It enables you to move the focus of attention
relative to the specific cell(s) in the range. For example:
Range(“E4”).Offset(2,3)
Offset (2,3) refers to the cell 2 rows down and 3 columns across from the cell E4, i.e. cell H6. An
alternative example:
Range(“E4”).Offset(-2,-3)
Offset (-2,-3) refers to the cell 2 rows up and 3 columns to the left from the cell E4, i.e. cell B2.
Some care needs to be taken with using Offset to ensure that you are referring to a range on the sheet
and haven’t gone ‘over the edge’. Use of the following code will be rewarded with an error message!
Range(“E4”).Offset(-5,-5)
The ActiveCell Object
A very useful object to use in VBA is the ActiveCell object. This is the cell that is currently selected
on the worksheet. For example
ActiveCell.Copy
will copy the contents of the selected cell,
ActiveCell.Offset(1,-3).Select
will select the cell that is 1 row down and 3 columns to the left from the currently selected cell and
ActiveCell.Offset(-1,2).Value = 43
will enter 43 into the cell that is 1 row above and 2 rows to the right of the currently selected cell.
NB:. In this last example we have not selected a different cell, so the active (i.e. selected) cell
will remain the same.
The following code shows another way to carry out actions in a range offset from the ActiveCell. In
this case Range itself is used, but as a relative reference:
ActiveCell.Range(“A2:D3”).Value=43
Whatever the actual reference of the currently active cell, it will be treated as if it were A1. So this
Range identifies the cells from 1 below the active cell (A2) to 2 below and 3 to the right (D3). Using
this code, all these cells will be assigned the value 43.
This takes some thinking about! However, it has the advantage that you can select, fill, etc a range of
cells rather than a single cell. Note the built-in drawback of this system: cells above or to the left
cannot be referred to in this way.
Activity 9.2 | Using ActiveCell and Range Objects
1. Open a new workbook and create a macro from scratch called
PrettyCells or something similar.
To create a new macro click on the Macro icon on the Developer tab,
type in a name for the new macro (e.g. PrettyCells) and click Create.
The VBE will open with the Macro set up for you to type in.
2. Enter the code below:
Sub PrettyCells()
Range(“E6”).Select
ActiveCell.Value = “O”
ActiveCell.Offset(1, 1).Value = “X”
ActiveCell.Offset(2, 2).Value = “X”
ActiveCell.Offset(-1, 1).Value = “X”
ActiveCell.Offset(-2, 2).Value = “X”
Columns(“A:Z”).Select
Selection.ColumnWidth = 2
Range(“A1”).Select
End Sub
3. Return to the Excel workbook (Alt+F11) and run the macro
Click on the Macro icon on the Developer tab, select the macro and click on Run
4. Return to the VBE (Alt+F11) and edit the macro so that the pattern below is completed. You
should be able to do a lot of copy and pasting to save time.
Run your macro.
You can design your own pattern if you want!
5. Edit the macro again so that the user selects the active cell at the centre of the pattern.
Run your macro and draw patterns all over the worksheet!
The Sheets Collection
Working in Excel you have the option to use a number of separate worksheets in order to organise
your workbook.
You will have seen the tabs at the bottom of the Excel page for the worksheets that are available in
your workbook and you may have worked with sheets in a workbook, copying, moving, renaming,
deleting etc.
Right clicking on a worksheet tab (e.g. Sheet1) displays the following options:
You can record code in a macro to edit sheets. However, code is more efficiently written without
recording.
For example:
Sheets(“Sheet1”).Select
Sheets(“Sheet2”).Delete
Sheets(“Sheet3″).Name=”MyName”
Activity 9.3 | Working With Sheets & Ranges
1. Open a new workbook in Excel
2. Create a new macro from scratch to delete Sheet1 and Sheet2 and
rename Sheet3 to MyName.
You can use the code below:
Sub TidySheets()
Sheets(“Sheet1”).Delete
Sheets(“Sheet2”).Delete
Sheets(“Sheet3”).Name = “MyName”
End Sub
3. Return to the Excel workbook (Alt+F11) and run the macro.
NB: Excel will give you a warning before you delete a worksheet unless you use a VBA statement
to suppress it. Can you find out what the necessary code is?
4. Enter some data on your MyName sheet in cells C1 to D5.
5. Write a new macro that will copy the data from C1:D5 to a new worksheet that you create after the
worksheet MyName. You may need to record a macro to discover how the code needs to be
written, but make sure that you delete all unnecessary code from your macro.
Summary
In this section we looked at VBA in a little more depth in order to formalise our understanding of
VBA coding and working with objects.
In this section we learned about:
Hierarchy of objects
Syntax
Ranges
Working with ranges
Objects and collections
Working with cells, sheets and ranges
In the next section we will learn about Variables & Functions.