Why reference object properties and methods

There are many reasons why you would need to access object properties and methods within a VBA macro. They are:

First, it may be necessary to examine the current condition or status of an object by retrieving the value stored in a particular property. For example, you might want to examine the contents of a cell to decide on some action. In both of the following examples, if the outcome of some condition is true then a message will be displayed.

If ActiveCell.Value < 0 Then MsgBox "The active cell contains a negative value"

If Workbooks ("my_work").open =false then MsgBox "File named my_work does not exist"

Second, you might want to change the status of an object by setting the value stored in a particular property. For example:

Activecell.Value = 4 'Set the value of the ActiveCell to 4

Range ("mortgage_no").Formula = "=$A$1+$A$2"

'Set a formula in the range called mortgage number to A1 + A2

ActiveSheet.Name = "WeekSales" 'Set the ActiveSheet name to WeekSales

ActiveWorkbook.WorkSheets ("WeekSales").Range ("month_no").value =1 'Set the value of the month number range of the WeekSales worksheet ' in the active workbook to 1

Charts ("Chart1").Name = myChart

'Set the chart called chart1 to the name myChart.

Range ("d1").Formula= "=2+a2" 'Set a formula in range d1 to 2+a2

Third, you might want to use a methods to cause the object to carry out one of its built-in tasks. For example, you might want to use the Protect method to protect a worksheet from being altered. Examples using an object method to carry out a built-in task would include things like:

Worksheet ("Sheet1").Select 'Selecting the worksheet called sheet1

Worksheet ("Sheet1").Protect 'Protecting the worksheet called sheet1

'Copying the cell range $B$2:$B$6 by Absolute reference Range ("$A$2:$A$6").Paste

'Paste the cell range $A$2:$A$6 by Absolute reference

MsgBox ActiveCell.Address

'Display the cell address of the ActiveCell in a MsgBox

Set newSheet = ActiveWorkbook.Worksheets.Add

'Adds a new worksheet to the worksheets collection called newSheet

Some methods require arguments to clarify any possible ambiguity of intentions. For example, the UpdateSales macro in Chapter 2 included a statement in the macro which read:

Range ("sales_to_date"). PasteSpecial xlValues

This is an example of an object reference which includes the argument xlValues. Other possible arguments here could be xlFormats and xlFormulas. The purpose of using an argument is to enable Excel to know how to paste the range; whether it be pasting the values or the cell format, or the formulas. The notation used for referencing arguments is as follows:

Object.Method Argumentl, Argument2,

Examples

Range ("D1:D4").PasteSpecial xlFormats 'Pastes the cell formats only into the range D1 to D4.

ActiveCell.BorderAround Weight: =xlMedium 'Sets a medium weight border around the ActiveCell

Look at each of the statements in the example below. We can see that each line contains object references. We will identify the objects being used with their properties and methods. This program - and all future programs - has been stripped of comments for convenience. Nevertheless, you should make sure that all macros you write include plenty of comments.

Listing 4.1

1 Sub UpdateSales ()

2 ActiveSheet.Unprotect

3 Range ("End_month_sales").Copy

4 Range ("sales_to_date").PasteSpecial xlValues

5 Range ("Week_sales").ClearContents

6 Range ("month_no") = Range ("month_no") + 1

7 ActiveSheet.Protect

8 End Sub

You first saw this as Listing 3.4.

Lines 1 and 8 are the procedure begin and end statements and so no object references are made in these statements.

Line 2 is a reference to the Unprotect method of the ActiveSheet object.

Line 3 references the Copy method of the Range object called

End_month_sales.

Line 4 references the Paste method to paste the values of the Range object called sales_to_date by values as given in the xlValues argument.

Line 5 references the ClearContents method of the Week_sales object.

Line 6 increments the month_no range by 1 (Notice that the value property is implicit in this reference).

Finally, line 7 references the active worksheet method Protect.

0 0

Post a comment