Exercises

1 Open a new workbook. Using the default Sheetl, enter the values into the cell range A1 to A5 the numbers 3, 11, 9, 12, and 5 respectively. Now enter the following macro using the VBE:

Sub GreaterThanTen() Dim thisCell as Object For each cell in Range ("A1:A5") If thisCell >10 Then

MsgBox "Another One Found" thisCell.Font.Bold =True End If Next End Sub

Explain what you think this program may be doing. Run the macro and check, using the data given to see that it works properly.

2 Using the macro that you have written in Exercise 1, describe each line of code that contains an object reference. Identify properties and methods in the references.

3 Use the Object Browser to look up the Range object and find out about the property or method called Cells. State whether it is a property or a method.

4 Given the same worksheet data as in Exercise 1, state the following property value:

5 Explain what the following section of code is doing:

Charts (1).Activate With ActiveChart .Type = xlLine .HasTitle = True

.ChartTitle.Text = "February Week Sales" End With

6 Open the SALESMAN workbook and make the weeklysales sheet active. Now create and run a macro to reset the color of all the cells in the week_sales range to the colour light grey. (Hint: Use the With...End With construct that was used in Listing 4.2, and set the Colorlndex = 15). Check to see that it has run correctly.

Open the SALESMAN workbook and make the weeklysales sheet active. Now design pseudocode, and from it, create a new macro that will check each cell in the end_month_sales range and if their sales have exceeded 200, then set a border of medium thickness around the cell.

Create another macro using the SALESMAN workbook that will check the same as above, but this time instead of setting a medium thickness border around the end_month_sales cells that are greater than 200; it will instead set the border around the corresponding salesperson. (Hint: Use the same macro code as above, but this time use the Offset method to reference the corresponding salesperson position in the worksheet).

5 Variables

What are variables? 92

Variable declarations 96

Explicit and implicit declarations . . 97

Assigning values to variables 99

VBA program using variables . . . 101

Using constants in VBA 104

User defined data types 105

Using arrays in VBA 107

Exercises 111

0 0

Post a comment