程序代写代做代考 Excel Topic 9 | Working With Objects

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.