1 / 24

CS105 Discussion 4 - Intro to VBA

Announcements MP1 is released, due on Monday, Sep 28 th at 9pm. MP1 is worth 110 “raw” points, scaled to 50 points You can grade your work as you go, as often as you like.

talisa
Télécharger la présentation

CS105 Discussion 4 - Intro to VBA

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Announcements MP1 is released, due on Monday, Sep 28th at 9pm. MP1 is worth 110 “raw” points, scaled to 50 points You can grade your work as you go, as often as you like. No matter what you score, you can resubmit MP1 for up to 55 more “raw” points until Oct 5th at 9pm. (Your final score cannot exceed 110 “raw” points). CS105 Discussion 4 - Intro to VBA CS105 Fall 2009 1

  2. Objectives • Get started with VBA • Creating Buttons • Reference cells using Range and Cells • Learn InputBox and MsgBox user interface • Understand assignment statements CS105 Fall 2009 2

  3. Setting up • Go to cs105 website “Lab Discussion Slides” • Right click the xls file, select “Save Link As” and save the file onto your Desktop • See next slides for • Enabling VBA code to run • Show Developer Tab CS105 Fall 2009

  4. Enable VBA code to run • Viruses are computer code • Unknown VBA Code can also be dangerous • That’s why Excel disables it by default • You must enable it for all the xls files downloaded from CS105 Lab Discussion Slides • Verify security by clicking on “Clear” button to clear content in Cell D11,D12 and D14 CS105 Fall 2009

  5. Displaying the VBA Controls • To enable Visual Basic tools click in order as shown CS105 Fall 2009 5

  6. Ticket Admission We will create an interface to generate admission tickets to a water park. It should ask user for input, and generate a ticket We need three buttons: Name: used to enter a customer’s name. Age: used to enter a customer’s age. Calculate: calculate ticket price and show to customer The Clear button (provided) used to clear entered data. CS105 Fall 2009 6

  7. Adding a Command Button • Click on Developer tab • Click on the Inserticon • Click and drag to create a button CS105 Fall 2009 7

  8. Design Mode • Go into Design Mode • In Design Mode, you can • Modify VBA Object properties • Write code for buttons • Etc • You can Exit Design Mode by clicking on it again ON OFF CS105 Fall 2009 8

  9. Button Properties • Set Button Properties • Right-click on the button and select Properties • Click on Properties in the Developer tab CS105 Fall 2009 9

  10. Setting button Properties • Set the Name property to cmdName • Set the Caption property to Please Enter Name • Set the Accelerator property to n • Set the Enabled property to True. • Can you change (Ask your TA if not): • the background color, • the foreground color and • the font? CS105 Fall 2009 10

  11. So What is an Accelerator? • An accelerator is a key that allows you to “press” a particular key using your keyboard • To run your button code, you can either click on it or hit the keys. • alt + <Accelerator Value> • Try clicking “Clear” button or Hit alt+c • NOTE: Design Mode has to be turned OFF! CS105 Fall 2009 11

  12. So What is a Name? • An object’s name is used to refer to an object in VBA code • CS105 has naming conventions for objects: https://agora.cs.uiuc.edu/display/cs105/Constant+and+Variable+Naming+Conventions (linked from “Lab Discussion Slides” page) • You must follow these conventions when naming objects in the MPs. • It is also printed in your Course Guide on pages 167-169. CS105 Fall 2009 12

  13. Other Command Buttons • Repeat the same process to create an Age button • The name of the button should be cmdAge • The caption should be Enter Age • The accelerator key will be a • Repeat for Calculate button • Button name : cmdCalculate • Button caption : Calculate CS105 Fall 2009 13

  14. Let’s write some VBA code • Design Mode ON • Double Click on cmdName • Write code here Range("D11").Value = "John" Cells(12, 4).Value = 18 • Go back to Excel Worksheet • Design Mode OFF • Click on cmdName, cmdAge and cmdClear button. What happens?

  15. What does the code do? Range("D11").Value = "John“ • D11 is a string. Specifies a cell address • Range("D11") is an object. Represents D11 cell • Value is an object’s property. Stores the cells content • =“John” is an assignment. It assigns the name “John” to what ever on the left side of “=” sign • What does the code do? CS105 Fall 2009

  16. Range and Cells Notations There are two ways to refer to cell D11 Range notation: Range(“D11”) Cells notation: Cells(11,4) 11 is row index 4 is column index column row row column CS105 Fall 2009 16

  17. What is a Function? • You’ve seen functions before. • They take argument(s) and return a value You must remember: A FUNCTION RETURNS A VALUE CS105 Fall 2009

  18. The InputBox Function • The syntax for InputBox is: [somewhere] = InputBox(<prompt>, <title>, <default>) What are these? • The InputBox function creates a dialog box that displays a message using <prompt>and has <title>on its window’s title bar. You may also provide a <default>, but this is optional. <title> If the user were to click “OK” the InputBox function would return the string “Tanya.” <prompt> CS105 Fall 2009 <default>

  19. Let’s write VBA code • Write code for cmdName • Ask user for name and put the name in cell D11 • Range("D11").Value = InputBox("Enter Name:", "Name", "John") Or • Cells(11,4).Value = InputBox("Enter Name:", "Name", "John") • Ask user for age and put the age in cell D12 • = InputBox("Enter Age:", "Age", "18") • Where do you put the above code? CS105 Fall 2009 19

  20. Computing the Ticket Price • The water park gives a "junior" discount to any customer that is 12 years old or under,the formula in D13 already calculates the customer's discount using Excel. • We want to compute the ticket price by VBA. • Subtract from the initial ticket price in D7 the calculated discount in D13 and store the result in D14: Range("D14").Value = Range("D7").Value -Range("D13").Value CS105 Fall 2009 20

  21. Displaying the Ticket Price • We want a message box displaying the ticket price. • To do this we will use the MsgBox sub procedure (explained in the next slide) • Display calculated price in cell D14 in a Message Box MsgBox "Total: $" & Range("D14").Value & " after $" & Range("D13").Value & " discount", vbOKOnly, "Ticket price“ NOTE: “&” character is used to concatenate values together as a string. CS105 Fall 2009 21

  22. The MsgBox Sub procedure MsgBox <prompt>, <buttons>, <title> • Creates a dialog box with • A message you defined in <prompt> • Must be a string • Buttons to display defined by <buttons> • Use “vbOKOnly” for now • A Title, defined in <title> • Must be a string • <buttons>, <title> are optional CS105 Fall 2009 22

  23. What you should know? • How to do basic work using Visual Basic in Excel 2007 (Developer Tab and settings) • How to create buttons and change their properties • How to use InputBox and MsgBox • What does A=B mean in VBA? (Does A get B’s value or does B get A’s value?) CS105 Fall 2009

  24. Exercises • What’s wrong with this • “John” = Range(“D11”).Content • Take a look at the code for cmdClear button. How many ways can you erase the content of a cell/range? • Take a look at the two buttons in “Scratch” worksheet and their code. How do you enable or disable a button? • The formula in D13 calculate the discount using Excel IF function. Can you do the same by VBA (Hints : use IF statement, see lecture notes or readings) CS105 Fall 2009

More Related