Exercises

Explain briefly the distinction between syntax, compilation and logical errors.

Explain briefly why testing is necessary.

What debugging features ofVBA w ould y ou use to check the v alue that a cell takes in a range, to see that each value is correct. Explain how you would do this?

Open a new workbook, call it ERROR.XLS and name two worksheets mySheetl and mySheet2. Create the following VBA macro, and run it.

Sub errors()

Application.DisplayAlerts = True Worksheets("mySheet2").Delete End Sub

Explain what has happened. Now change the line: Application.DisplayAlerts = True so that it becomes

Application.DisplayAlerts = False Worksheets("mySheet1").Delete

Now run the program again and explain what has changed.

Give reasons for using the Immediate window when using VBA?

Open the VBA Help system and read the sections on debugging.

Open the macro getValidRepName as shown below and run it to find a particular RepName. Test it by repeatedly typing in wrong RepNames before typing a correct one. Now create a range of test data and to ensure the program is working properly.

Sub getValidRepName()

Dim thisRepName As Variant Dim myCell As Object Dim isFound As Boolean Worksheets("Weeklysales").Select isFound = False 'assume RepName repName is not found yet Do Until isFound thisRepName = InputBox(prompt:="enter a rep name") 'for loop tries to find this representative name in list For Each myCell In Range("Rep_name")

If myCell = thisRepName Then 'good - it has been found myCell.Interior.ColorIndex = 4 isFound = True

MsgBox "found at " & myCell.Address End If Next Loop End Sub

Open the getValidRepName macro from the previous exercise. Click the cursor on the line isFound = True. Click on the breakpoint button. Now run the macro. The macro should stop (break) at line 7 and show you the debugger window. Make sure the Watch pane is visible. Select the variable isFound and click on the Watch button, then click Add to add it to the Watch pane. Inspect the value of IsFound. Also, in the same way, add watches to the variables, myCell and thisRepName and to the expression myCell = thisRepName. Look at their values in the Watch pane. Now start stepping through the macro - and keep an eye on the values in the Watch pane. This should help you get familiar with the debugger, and with the macro code. Use the following macro to try out testing and debugging.

Sub setHighSales() Dim myCell As Object Worksheets("Weeklysales").Select ActiveSheet.Unprotect For Each myCell In Range("Week_sales") If myCell < 20 Then myCell.Interior.ColorIndex = 7 ElseIf myCell > 20 And myCell < 40 Then myCell.Interior.ColorIndex = 8 ElseIf myCell > 40 Then myCell.Interior.ColorIndex = 9 End If Next

ActiveSheet.Protect End Sub

9 Subs and functions

Subroutines 170

Functions 172

Creating functions 174

Passing parameters 182

Exercises 186

0 0

Post a comment