CP212 Lab 4 : Loops, Logic and Collections

If...Then...ElseIf...Else...End If

The complete structure for VBA decision statements is:

If condition1 Then
   ' something if true
ElseIf condition2 Then
   ' something else
Else
   ' something else
End If

To determine which button a user clicked on a message box, you can use a variable of type vbMsgBoxResult:

Dim result As vbMsgBoxResult

result = MsgBox("Are you sure?", vbQuestion, "Verify")

If result = vbOK Then
   msg = "You clicked OK."
Else
   msg = "You clicked something other than OK."
End If

MsgBox msg

Possible values of the vbMsgBoxResult enumeration:

Constant
vbAbort
vbCancel
vbIgnore
vbNo
vbOK
vbRetry
vbYes

For ... Next

Complete structure:

For i = start To end [Step stepSize]
   ' do something a few times, Step statement is optional
Next i

Example: What is the output?

Dim i As Byte ' Byte cannot be larger than 256, good for small loops

For i = 2 To 10 Step 2
   msg = msg & i
Next i
MsgBox msg

For Each ... Next

The For Each structure is very helpful when iterating through a collection, like a Range of cells, or Workbooks, or ChartObjects.

Dim item As Object

For Each item In Collection
   ' do something
Next

Example:

Sub forEachExample()
   Dim cell As Range
   Dim counter As Integer
   counter = 0

   For Each cell In Range("Data")
      If cell.HasFormula Then
          counter = counter + 1
      End If
   Next

   MsgBox "There are " & counter & " cells in the Data range that contain formulas."
End Sub

Do Loops: Do Until ... Loop, Do While... Loop, Do...Loop Until, Do...Loop While

Explained in detail in text. Basic usage:

Do Until condition
   ' do something
Loop

Example:

Dim isValid As Boolean
Dim password As String

isValid = False
Do Until isValid
   password = InputBox("Enter a valid password:")
   ' put  some code here so there won't be an infinite loop
Loop

---

Practice

You can practice some of the techniques in this lab by completing the samples below if you wish.

1. Write a subroutine that asks for two values of the Integer datatype and and determines which number is bigger. The output should be "The larger number is " followed by the larger number. Use an If statement in VBA, not any Excel worksheet functions. Use a third variable to store the largest value.

2. In the same file as Task 1, insert a new Module and call it PasswordTest. Use the code for the Do Until ... Loop shown above to write a basic password testing sub. The password should be "wordpass". When the user enters the correct password, display a message box shown here (with the question mark):

   

   

   If they click Yes, display a message about letting them continue. If they click No or Cancel, display a message saying they've decided to stop.

   This message should have an Exclamation point icon such as:

   

   

   

   Mac msgboxes don't show message box styling.

---

Instructions

All tasks are to be completed on your own.

• Save the spreadsheet file as a Macro Enabled Workbook (.xlsm) file before you write more code. Name your spreadsheet with your network login followed by _l04.xlsm. For example, if your network login was barn4520, then your lab file should be named barn4520_l03.xlsm. Save often.

• Show the Developer Tab in Excel. Click File / Options Customize Ribbon and check the option for Developer Tab.

• Put all code into a Module. Use a different Module for each task and name them appropriately as Task1 or Task2 etc.

• Copy and paste this header into your code modules and fill it out:

  Option Explicit
  
  ' ==== CP212 Windows Application Programming ===============+
  ' Name: Your Name
  ' Student ID:
  ' Date:
  ' Program title:
  ' Description:
  '===========================================================+
  

• Make sure you test your tasks before submitting the lab to be marked.

• When finished upload your file to the Lab 4 Task Dropbox in MyLearingSpace.

Tasks

Download the file monthly_sales.xlsx and complete the following task. Open the file and save it as a Macro Enabled Workbook (file extension .xlsm). Name this spreadsheet with your network login followed by _l04.xlsm. For example, if your network login was barn4520, then the file should be named barn4520_l04.xlsm. Remember to save your work frequently in case there are problems.

1. In the given file, write a subroutine that adds a worksheet named RF1 after the original worksheets, and then goes through Quarter 1 of the original worksheet hunting for cells with formulas. Each time it finds a formula in this sheet, a new row is added in the new RF1 worksheet which records the detected formula information.

2. In RF1 worksheet, formula records arranged as: the worksheet's name in column A, the formula as a string in column B, and the formula's value in column C. Cell A1 contains label worksheet and bold it, Cell B1 contains Formula label and bold it, and Cell C1 contains Value Label and bold it. Adjust each cell size in RF1 worksheet to fit the data it contains. Use a command button to run the required task.

3. Write a new subroutine to delete the new sheets using loop commands of VBA. Use a command button to run this subroutine.

   Hints:

   • To check whether a cell contains a formula, use the range property HasFormula.
   • To check all the cells that are being used, use of the worksheet property UsedRange.
   • To write a formula into a cell as a string, put an apostrophe (') in front of the string when you write it to the cell as a value. For example, to make the formula appear in a cell instead of Excel trying to execute the formula, I could type ‘=sum(A3:A6) into the cell.
   • To add a worksheet to the end of the list of worksheets you could use the following statement:

     	    Set wsNew = Worksheets.Add(after:=Worksheets(Worksheets.count))
     	    

   • To automatically make columns fit the widest text they contain, use the Autofit property.

     	    wsNew.Range("A:C").Columns.AutoFit
     	    

   • To delete a worksheet, use the wsNew.Delete and disabiling the alerts

     	    Application.DisplayAlerts = False
                 wsNew.Delete
     	    

Notes

Marking Scheme for Lab 4

You have to use Button(s) on worksheet(s) to run the task(s) in this lab and all next labs. Marks will be deducted if you do not use buttons.