CS105 Discussion 9 InputBox and MsgBox

1
CS105 Discussion 9InputBox and MsgBox

• Announcements
• MP4 is due on Tuesday, October 25th at 8 pm.
• Answer Keys for Midterm 1 have been posted under
  the Answer Keys section.
• Midterm 2 will take place on Tuesday, November
  1st at 700pm. You must submit requests for
  conflicts by October 25th.

2
Overview

• Use InputBox function to enter data in a
  spreadsheet
• Reference cells using both Range and Cells
  notation
• Use the Message Box subprocedure
• Learn about the VBA debugger
• Download the Excel Worksheet for Lab 9
  http//www.cs.uiuc.edu/class/fa05/cs105

3
Ticket Admission

• In this lab, well implement a small user
  interface for entering data for ticket admission
  to a local water park.
• Three buttons have been provided
• Name used to enter a customers name.
• Age used to enter a customers age.
• Clear used to clear entered data.

4
Name

• Go into Design Mode and double-click on the Name
  button.
• We want to ask the customer for his/her name and
  store it on the worksheet
• To do this, we will use the function InputBox

5
InputBox

• The syntax for InputBox is
• InputBox(ltpromptgt, lttitlegt, ltdefaultgt)
• The InputBox function creates a dialog box that
  displays a message using ltpromptgt and has lttitlegt
  on its windows title bar. You may also provide a
  ltdefaultgt , but this is optional

• If the user were to click OK the InputBox
  function would return the string Tanya.

6
Functions

• Youve seen functions before. The SUMIF, COUNT,
  and MONTH functions in Excel are all examples.
  But when you are working with VBA code, you must
  remember
• A FUNCTION RETURNS A VALUE
• This means that whenever you use a function, you
  must use the value it returns or store it
  somewhere.
• For the Name button, we will store the value
  returned from InputBox in cell D11.

7
Entering the customer's name

• We can do this using RANGE notation
• Range("D11").Value InputBox("Enter name", _
  "Name", "John")
• Note that when a value is stored in a location,
  the value must always appear on the LEFT of the
  "" sign.
• Unlike in SQL, statements in VBA must be on one
  line only. To separate a statement into multiple
  lines, you must use the underscore ("_") to mark
  continuation

continuation
8
Cells() Notation

• A second way to do this is using CELLS notation.
• When using the Cells notation, you need to
  specify two things
• The row number
• The column number
• The format of the Cells( ) notation is as
  follows
• Cells(ltRowgt, ltColumngt)

9
Cells() Notation (cont'd)

• Rewriting cmdName_Click() using Cells notation
• Cells(11,4).Value InputBox("Enter Name", _
  "Name", "John")

10
Entering the customer's age

• Now we want to ask for the customer's age.
• As with the Name button, edit the code for the
  Age button so it uses the InputBox function to
  ask for the customer's age and display the age in
  D12. You dont need to include a default value.

11
Displaying discount

• The water park gives a "junior" discount to any
  customer 12 years old or under
• The formula in cell D13 calculates the customer's
  discount
• We want a message box to display the discount
• To do this we will use the Message Box
  subprocedure.

12
MsgBox subprocedure

• The syntax of a MsgBox is
• MsgBox ltPromptgt, ltButtonsgt, ltTitlegt
• The Prompt argument is the message that shows up
  on the message box.
• The Buttons argument determines what type of
  buttons will appear on the message box.
• The Title argument is shown on the blue bar at
  the top of the message box.

13
MsgBox subprocedure (cont'd)

• We want a Message Box to display the discount.
• To create the Message Box, type
• MsgBox "Discount " Range("D13").Value , _
  vbOKOnly, "Discount"
• If the value in D13 is 15, then prompt will be
  "Discount 15" and the title will be "Discount"
• Unlike a function, a subprocedure, such as the
  MsgBox subprocedure, does not return a value.

14
Computing ticket price

• Finally, we want to compute the ticket price.
• Subtract the initial ticket price in D7 with the
  calculated discount in D13 and store the result
  in D14

Range("D14").Value Range("D7").Value - _
Range("D13").Value
15
Finding a bug

• What happens if you make a mistake in your code?
• Let's say you had the following
• Private Sub cmdName_Click()
• Cells(11, 0).Value InputBox("Name", "?")
• End Sub
• This code won't work (why?)

16
Finding a bug (cont'd)

• If we tried to run this code, we get the
  following error message

17
Finding a bug (cont'd)

• If you already know what caused the error, then
  click on the "End" button to stop running the
  code.
• However in most cases, you don't know what caused
  the error, in which case click "Debug" to run the
  code in the VBA Debugger

18
VBA Debugger

• The VBA debugger highlights the line where the
  error occurred

19
Stopping the Debugger

• To stop the debugger, click on the Reset button