1 / 24

Lab 8: Macros

Lab 8: Macros. Announcements Online Quiz 4 is up and is due on Wednesday, March 29 th at 8 am. MP4 will released on Thursday, March 9 th . It is due on Friday, March 17 th at 8 pm. NO late submissions will be accepted for this MP. Objectives. Record a simple macro.

menefer
Download Presentation

Lab 8: Macros

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. Lab 8: Macros • Announcements • Online Quiz 4 is up and is due on Wednesday, March 29th at 8 am. • MP4 will released on Thursday, March 9th. It is due on Friday, March 17th at 8 pm. NO late submissions will be accepted for this MP. CS105 – Spring 2006

  2. Objectives • Record a simple macro. • Learn the differences between absolute and relative macros. • Use VBA button controls • Gain some understanding about VBA code. CS105 – Spring 2006

  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 > Macro > Security and select the Medium Security Level. Click "OK", and restart Excel. CS105 – Spring 2006

  4. Recording a Macro • Macros are a means of recording user actions. A macro enables you to perform multiple tasks at one go. • Let’s create a simple macro that will insert your name and address into cells B2:B3. CS105 – Spring 2006

  5. NameMacro • Click on the “Macros” worksheet. • Go to Tools, Macro, Record New Macro • In the Name box, type NameMacro (one word) • Use ctrl+n as the shortcut • Add your name and section in the description. • Click OK CS105 – Spring 2006

  6. Recording NameMacro • You have begun recording your macro. • Click on cell B2. • Type in your name. • Now click on cell B3 and type in your address. • Click on the stop recording button. CS105 – Spring 2006

  7. Running the Macro • Clear the Name worksheet. Select B2:B3 and hit Delete. • Now run the macro by hitting ctrl+n • What happens? CS105 – Spring 2006

  8. Find your macro code • To see what Excel recorded, go to Tools, Macro, Macros. • In the dialog box, choose NameMacro and click on Edit. • This will bring up the Project Explorer with the NameMacro in the VBA code window. • Notice the cell references: Range("B2").Select ActiveCell.FormulaR1C1 = "Kevin Johnn" Range("B3").Select ActiveCell.FormulaR1C1 = "1234 Main Street" • What two cells are always referenced? What type of Macro is this? CS105 – Spring 2006

  9. Relative Macros • Clear the Name worksheet. • Select cellB2. • Record a new Macro with the name NameRelative and shortcut key ctrl+r. • Click on the relative reference icon so that it is depressed. CS105 – Spring 2006

  10. Relative Macros (cont) • We are going to repeat the same steps we did when we inserted our name and address: • Type your name in B2 (it is already selected) • Now, select B3 and type your address • Now stop recording. CS105 – Spring 2006

  11. Running the Macro • Now, select any cell and hit ctrl+r • Does the macro do what you expected?How is it different from NameMacro? • Take a look at the code for the NameRelative macro: ActiveCell.FormulaR1C1 = "Kevin Johnn" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "1234 Main Street" • How doesOffset(1, 0)select a new cell? CS105 – Spring 2006

  12. Command Buttons • Instead of using the Ctrl-shortcut to call a macro, we can also create a button on the Excel worksheet which, when pressed, will invoke the macro. • To do this, we’ll start by creating the macro we want to call. CS105 – Spring 2006

  13. Top 10 Movies of the Week • We want to create a macro to format the Top 10 Movies table. First select the Movies worksheet. • Name your macro FormatMacro (This name is case sensitive!). Pick the letter “f” for your shortcut key. • This should be an absolute macro. Make sure the relative reference icon is not depressed. • Format the movie data to be visually pleasing. CS105 – Spring 2006

  14. Stop Recording • When the formatting is to your satisfaction, press the “Stop Recording” button on the “Stop Recording” toolbar. CS105 – Spring 2006

  15. Clear Formatting Button • Clear your formatting by clicking on the “Clear Formatting” button already on your worksheet. You may also use the Alt-c shortcut. CS105 – Spring 2006

  16. Adding a button • Now we’ll make a similar button to call your formatting macro. • First we must display the VBA toolbars • Go to View, Toolbars • Click on Visual Basic (if it does not have a check beside it) CS105 – Spring 2006

  17. Displaying the VBA Controls • Now click on the hammer/wrench icon on the VB toolbar • The VB controls toolbox should appear CS105 – Spring 2006

  18. 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. CS105 – Spring 2006

  19. 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 cmdFormat CS105 – Spring 2006

  20. More properties • Change the Caption property of the button to Format • Change the Accelerator property to m • Change the background color, the foreground color and the font. CS105 – Spring 2006

  21. So what is an Accelerator? • An accelerator is like the shortcut keys you used when recording your macros. Now to run your button code, you can either click on it or hit the keys: alt + <Accelerator Value>(alt+m in this case) • Macros used ctrl + <Shortcut key> CS105 – Spring 2006

  22. Writing Code • In design mode double-click on your button to bring up the code window. • Between Sub cmdFormat_Click() and End Sub, type the name of your macro: FormatMacro • Now when you click the Format button it will call (i.e. run) this macro CS105 – Spring 2006

  23. Running the Code • Go back to the Excel window. • Click on the design mode (triangle) button to get out of design mode • You are now in run mode: click on your button. Does it work? CS105 – Spring 2006

  24. Modifying the Macros • We want to change the code so that the user can only click on the Clear button after the Format button has been clicked. • In design mode, double-click on the Clear Formatting button and add the following line of code at the end: cmdClear.Enabled = False • Now, in the cmdFormat code add: cmdClear.Enabled = True • Try it out. CS105 – Spring 2006

More Related