CS105 Discussion 10 VBA

1
CS105 Discussion 10VBA

• MP4 is posted and is due on Saturday, October
  28th at 1130am.
• Midterm 2 will take place on October 30th. If you
  have a conflict with the exam time, sign up for
  the conflict exam as soon as possible.
• The review session will be held in 141 Wohlers on
  Sunday, October 29th at 3pm. The review will
  last until your questions run out, or for a
  maximum of two hours.

2
Overview

• Variables
• Error checking
• IF Statements
• Active Cell properties

3
Variables

• Variables are temporary containers that hold
  data. Variables have Type. Types define exactly
  what kind of data a variable can hold
• Variables are declared using the Dim statement in
  VBA.
• In CS105, we follow a naming convention for
  variable names. See the course guide for details.

4
Variable Declaration and Assignment

• Recall a variable is a temporary storage place
  for data. Variable DECLARATION is how such
  storage is created. Below is an example
• Dim intMyVariable As Integer
• A variable must also have data placed into it.
  Variable ASSIGNMENT is how this storage takes
  place. Below is an example
• intMyVariable 5

5
Option Explicit

• Well begin with the Start button. Go into
  design mode and double-click on the Start button.
  This will take you to the VBA project window.
• Before you start coding, write the words Option
  Explicit at the very top of your code. You only
  need to do this once in your code.

6
Option Explicit (cont'd)

• Option Explicit forces you to EXPLICITLY declare
  your variables. If you try to use a variable
  that has not been declared, you get an error.
• This is useful because it catches misspelled
  variables.
• Option Explicit
• Private Sub cmdDate_Click()
• Dim intDate As Integer
• intDaate InputBox("Date", "?")
• End Sub

Can you spot the error?
7
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

8
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.

9
Error checking Flowchart
Error?
True
Display error message
False
Exit
Continue on to next statement
10
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

11
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

12
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.

13
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")

14
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

15
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.

16
These are the discounts we want to give
17
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

18
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"

19
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

20
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