1 / 14

Macros

CS1100 Computer Science and its Applications. Macros. What is an Excel Macro?. A macro is a set of instructions that tells Excel to perform one or more actions An Excel macro is like a computer program but it runs completely within Excel. Used to automate tedious or frequently repeated tasks

haig
Download Presentation

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. CS1100 Computer Science and its Applications Macros

  2. What is an Excel Macro? • A macro is a set of instructions that tells Excel to perform one or more actions • An Excel macro is like a computer program but it runs completely within Excel. • Used to automate tedious or frequently repeated tasks • Macro can carry out sequence of actions much more quickly

  3. Macro Advantages • Save time – even if you are an expert Excel user, the computer can beat you every time • Reduce errors – if the instructions are correct, programs do not make mistakes • Enforce standards – for example, each sales rep may be required to submit a weekly summary spreadsheet that follows a specific structure and format

  4. Macros • Two ways to create a macro: • Record it using the Macro Recorder • Build by typing instructions in a VBA (Visual Basic for Applications) module (beyond the scope of this course) • These two approaches can be combined

  5. Macro Security • Macro code can be used for evil too • VBA macros included in Microsoft Office documents (usually attached in emails) have contained viruses in the past • macro programs embedded in documents can be run automatically when the document is opened, a mechanism which can spread viruses. • To configure security settings: • File  Options  Excel Options  Trust Center Trust Center Settings

  6. Macro Viruses • One famous macro virus called Melissa, released in March, 1999: • The virus sent a file called “List.doc” which it claimed had passwords to 80 adult websites. • Anyone who opened the document would get a free copy of the Melissa macro virus. • Melissa would then gather the first fifty entries in the address book, and email itself to all of them. • Melissa infected so many systems that by March 26th, it was shutting down mail servers with all of the infected emails traveling across the net.

  7. Adding the Developer Tab • Click on the File tab of the ribbon to open the File menu • Click on Options to open the Excel Options dialog box. • Click on the Customize Ribbon option in the left hand window • Under the Main Tabs section of the options window, check off the Developer option. • Click OK the Developer tab should now be visible

  8. Workbook Formats • The Excel default workbook format (.xlsx) does not support macros • To save a workbook containing macros, you need to use one of the following formats: • .xlsm • .xlsb • .xls

  9. The Macro Recorder • The recorder allows you to create macros without knowing VBA • Best to plan your macro before starting the recorder • If you’re curious about VBA and want to learn to do more with macros, the recorder can be a great learning tool • You can examine the code that the recorder generates and edit it.

  10. The Macro Recorder dialog box • 4 options to complete in this dialog box: • Macro name - give your macro a descriptive name. The name must begin with a letter and have no spaces. Only letters, numbers and underscore permitted. • Shortcut key - (optional) fill in a letter, number, or other character in the available space. This will allow you to run the macro by holding down the CTRL key and pressing the chosen letter on the keyboard. 
 • Store macro in: • This workbook: the macro is available only in this file. • New workbook: this option opens a new Excel file and the macro is only available in the new file. • Personal macro workbook: this creates a hidden file Personal.xls which stores your macros and makes them available in all Excel files • Description: (optional) enter a description of the macro

  11. Macro Example • Create a macro that automates the formatting for a heading for the Atlantic Music Company:

  12. Editing/Step Into a Macro • An Excel macro is written in the Visual Basic for Applications (VBA) programming language. • Clicking on either the Edit or Step Into buttons in the Macro dialog box starts the VBA editor • Using the VBA editor and covering the VBA programming language is beyond the scope of this class.

  13. Recording with Relative References • By default a macro is recorded with absolute references. • If you are in Cell A1 when you record a macro, the action will play back in A1 • To record with relative references, click Use Relative References • Now the macro plays back in the selected cell.

  14. Macro Example • Let’s create a macro that automates the format for a monthly sales summary:

More Related