CS105 Discussion 10 VBA

1
CS105 Discussion 10VBA

• MP5 and MP6 due dates have been changed (pushed
  back).
• Midterm 2 will take place on Tuesday, April 4th
  at 700pm.
• If you have a conflict with the exam time, sign
  up for the conflict exam as soon as possible.
• Review sessions will be held in 141 Wohlers on
  Sunday, April 2. One review starts at 3 p.m. and
  one at 7 p.m. Reviews last until your questions
  run out, or for a maximum of two hours.

2
Overview

• Error checking
• Nested IF-Statement
• Active Cell properties

3
Active Cell Exercise

• Go to the Cell Info worksheet
• There is a 3x3 Board on the Excel Worksheet. You
  are to select a cell in the worksheet, and get
  information about it by clicking on the "Get Cell
  Info" button.
• This button determines if the Active Cell is on
  the board. If it is, it will display the value in
  that cell. Otherwise, it will display an error
  message.
• To figure out if the Active Cell is on the board,
  we need to use the Row and Column properties.

4
Each active cell has a row and column property

• When you click on a cell, the computer
  automatically records the row and column of the
  Active Cell.
• ActiveCell.Row is 3
• ActiveCell.Column is 2

5
cmdGetInfo

• Let's first get the row and column number of the
  Active Cell
• Declare two Integer variables and set them to the
  row and column numbers of the Active Cell
• intRow ActiveCell.Row
• intCol ActiveCell.Column

6
cmdGetInfo (cont'd)

• Now we need to determine if the Active Cell is
  off the board.
• The board is in C4E6. What must be the row
  number of the Active Cell in order for it to be
  off the board?
• What must the column number be?

7
cmdGetInfo code using error-checking

• If intRow lt 4 Or intRow gt 6 Or _ intCol lt 3 Or
  intCol gt 5 Then
• MsgBox "Cell is out of bounds!"
• Exit Sub
• End If
• MsgBox "Value is " ActiveCell.Value

8
Alternative Approach

• Alternatively we can write this using
  error-checking.
• Instead of checking if the cell is OUTSIDE the
  board, we need to check if the cell is INSIDE of
  the board.
• How will the condition change?

9
cmdGetInfo code

• If intRow gt 4 And intRow lt 6 And _ intCol gt
  3 And intCol lt 5 Then
• MsgBox "Value is " ActiveCell.Value
• Else
• MsgBox "Cell is out of bounds!"
• End If

10
Catering Service

• In this lab, well implement a small user
  interface for entering data for a catering
  service.
• Three buttons have been provided
• Name used to enter a clients name.
• Guests used to enter number of guests at the
  event
• Event type used to enter the type of event.
• Clear clears the worksheet

11
Name

• Currently the Name button just gets the
  clients name.
• We want to add error-checking to this. We want to
  make sure that the client enters a valid name.
  If not, well exit the subroutine using Exit Sub.

12
Error checking Flowchart
Error?
True
Display error message
False
Exit
Continue on to next statement
13
Error in the Name?

• We want to ensure that the name is valid. Well
  check that the user did not enter an empty name.
  To check for an empty name, we use the following
  IF statement
• If strName "" Then
• End If

14
What to Do in Case of Error?

• If the user didnt enter an appropriate name, we
  want to display a message box and exit the
  subroutine, like so
• If strName "" Then
• MsgBox "No Name Entered!", vbOKOnly,"Error"
• Exit Sub
• End If

15
Entering the Guest Size

• The Guests button has been minimally implemented.
  But you get an error if you type twenty
  instead of 20. We need to fix this.

16
Error Checking on Guest Size

• You get an error because of the type.
• Well assign the InputBox() function used to get
  the age to a VARIANT type variable.
• Dim vntSize As Variant
• vntSize InputBox("Number of Guests?", _ "Guest
  Size")

17
Error Checking on Guest Size

• Now well check to make sure the VARIANT variable
  is a number using the IsNumeric() function. If
  not, then well exit using Exit Sub.
• If Not IsNumeric(vntSize) Then
• MsgBox "Not a number!", vbOKOnly, "Error"
• Exit Sub
• End If
• intSize vntSize
• Cells(7, 4).Value intSize

18
Event type and discount

• Take a look at the code for the cmdType button.
  You can see that it asks the user for the event
  type, and then checks that the type is valid (an
  event must be one of Birthday, Reunion, or
  Other).
• Now we want to calculate the discount based on
  the guest size and the type of event. Any event
  that has 25 guests or more receives a discount.
  How much of a discount depends on the event.

19
These are the discounts we want to give
Size gt 25 Size lt 25
Birthday 50 0
Reunion 20 0
Other 10 0
20
Nested If

• To check more than one condition in VBA, one can
  use a Nested IF statement, which is placing an If
  statement inside of another If statement. The
  syntax is
• If ltcondition 1gt Then
• If ltcondition 2gt Then
• ltwhat to do if 1 and 2 are truegt
• End If
• End If

21
ElseIf

• Another way to check multiple conditions is the
  ElseIf statement. The syntax is
• If ltcondition 1gt Then
• ltwhat to do if 1 is truegt
• ElseIf ltcondition 2gt Then
• ltwhat to do if 1 is false and 2 is truegt
• End If
• Note that unlike the Nested-If statement, this
  uses only one If statement, and so needs only one
  "End If"

22
Check for Discount conditions

• If the guest size is 25 or more and the event
  type is Birthday, then the client gets a 50
  discount .
• If intSize gt 25 Then
• If strType "Birthday" Then
• Cells(9, 4).Value 50
• End If
• Else
• Cells(9, 4).Value 0
• End If

23
Check for Discount conditions (continued)

• How about the other discounts?
• If intSize gt 25 Then
• If strType "Birthday" Then
• Cells(9, 4).Value 50
• ElseIf strType "Reunion" Then
• Cells(9, 4).Value 20
• Else
• Cells(9, 4).Value 10
• End If
• Else
• Cells(9, 4).Value 0
• End If