Excel VBA Practice 2 - VBA For Excel

  1. Explain briefly the distinction between syntax,compilation and logical errors.
  2. Explain briefly why testing is necessary.
  3. What debugging features of VBA 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?
  4. Open a new workbook, call it ERROR.XLS and name two worksheets mySheet1 and mySheet2. Create the following VBA macro, and run it.
  5. 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.

  6. Give reasons for using the Immediate window when using VBA?
  7. Open the VBA Help system and read the sections on debugging.
  8. Open the macro get Valid RepName 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.
  9. 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
  10. 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.
  11. Use the following macro to try out testing and debugging.
  12. 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

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

VBA For Excel Topics