CS105 Discussion 8 Intro to VBA

1
CS105 Discussion 8Intro to VBA

• Announcements
• Online Quiz 4 is up and will be due on October
  17th at 800pm.
• MP4 will be posted early next week.
• Quiz 3 grades have been posted.
• Midterm 2 is on October 30th at 700pm. If you
  have a conflict with the exam time, you must sign
  up for a conflict exam from the course website.

2
Overview

• Learn how to create Buttons
• Use InputBox function to enter data in a
  spreadsheet
• Reference cells using both Range and Cells
  notation
• Use the Message Box subprocedure

3
Downloading the file

• Go to the course website and download the Excel
  Worksheet for Lab 8.
• http//www.cs.uiuc.edu/class/cs105
• Click "Enable Macros" when the warning message
  appears.
• If you did not see a message, you need to change
  the security level in Excel. Go to Tools gt Macro
  gt Security and select the Medium Security Level.
  Click "OK", and restart Excel.

4
Ticket Admission

• In this lab, well implement a small user
  interface for entering data for ticket admission
  to a local water park.
• We need three buttons
• Name used to enter a customers name.
• Age used to enter a customers age.
• Clear used to clear entered data.
• The Clear button is provided but we must add the
  other two

5
Adding a button

• First, we are going to make the Name button
• We must display the VBA toolbars
• Go to View, Toolbars
• Click on Visual Basic (if it does not have a
  check beside it)

6
Displaying the VBA Controls

• Now click on the hammer/wrench icon on the VB
  toolbar
• The VB controls toolbox should appear

7
Adding a button

• We want to make a button.
• Click on the button icon from the VB controls
  toolbox.
• Click and drag on the spreadsheet to create a
  button.

8
Set the button properties

• Now that you have a button, we want to set the
  Properties for this button
• Right Click on the button and select properties.
  A box will appear with lots of properties that
  you can change
• Change the Name property of the button to cmdName

9
More properties

• Change the Caption property of the button to Name
• Change the Accelerator property to n
• Change the background color, the foreground color
  and the font.

10
So what is an Accelerator?

• An accelerator is a shortcut key that allows you
  to quickly press the button using your
  keyboard.
• To run your button code, you can either click on
  it or hit the keys alt ltAccelerator Valuegt
  (altn in this case)

11
Age Button

• Now repeat the process you used to create the Age
  button
• The name of the button should be cmdAge
• The caption should be Age
• The accelerator key will be a

12
Name Code

• Now we want to put in the code that will make the
  Name button work
• 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

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

14
A Function Returns a Value

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

15
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 location 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
16
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)

17
Cells() Notation (cont'd)

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

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

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

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

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

22
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