CS 1100 Computer Science and its Applications MACROS

CS 1100 Computer Science and its Applications MACROS CS 1100 1

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

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

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

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 Settings

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 26 th, it was shutting down mail servers with all of the infected emails traveling across the net.

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

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

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.

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

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

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.

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

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