1) Excel Macros Overview If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. When you record a macro, Excel stores information about each step you take as you perform a series of commands. You then run the macro to repeat, or “play back,” the commands. You can run a macro by choosing it from a list in the Macro dialog box. To make a macro run you click a particular button or press a particular key combination. After you record a macro, you can view the macro code with the Visual Basic Editor. Computer viruses are often transmitted by means of Macros. Excel provides safeguards that help protect against viruses. Enabling Macros and Saving a Workbook as a Macro-Enabled Workbook Open Excel. Is there a tab called Developer on the right side of the tabs? If yes, click the Developer tab. If no, go to File | Options | Customize Ribbon. Look to the right of the screen. Under Customize the Ribbon | Main Tabs select the Developer box and then hit the OK button. Click the Macro Security button on the Ribbon to display the Trust Center dialog box. When the Trust Center dialog box appears, click the Enable all macros option button to select it. Click the OK button to close the Macro Security dialog box. Click the File Tab and then click Save As to display the Save As dialog box. When the Save As dialog box appears choose Macro as the File Name. Then click the Save as type box arrow and then click Excel Macro-Enabled Workbook (*.xlsm) in the Save as type list. Click the Save button in the Save As dialog box to save the workbook as an Excel Macro-Enabled Workbook file type. Recording a Macro to insert the text Delgado Community College If necessary, click the Developer tab on the Ribbon Click the Record Macro button on the Ribbon to display the Record Macro dialog box When the Record Macro dialog box appears, type DCC in the Macro name text box Type d in the Shortcut key text box to set the shortcut key for the macro to CTRL+d Make sure the Store macro in box displays This Workbook and then type Macro inserts the text Delgado Community College in the Description text box. Click the OK button to begin recording the macro and cause the Record Macro button to become the Stop Recording button Type the text Delgado Community College in any cell and then hit ENTER. (Note: this is the step that students have the most problems with: do not type anything except what is specified in this step no mouse clicks; no escape keys) Click the Developer tab on the Ribbon and then click the Stop Recording button to stop recording the worksheet activities Setting the Macro Security Level to Medium Excel provides four levels of protection from macro viruses: 1.) Disable all macros without notification, 2.) Disable all macros with notification, 3.) Disable all macros except digitally signed macros, and 4.) Enable all macros. The following steps set the level of protection to disable all macros with notification. Click the Developer tab on the Ribbon Click the Macro Security button on the Ribbon to display the Trust Center dialog box Click the Disable all macros with notification option button Click the OK button in the Trust Center dialog box to close it Save the workbook. Close Excel. Opening a Workbook with a Macro and Executing the Macro Open Excel then open the Macro workbook. Click the Options button in the Security Warning box to display the Microsoft Office Security Options dialog box. Click the Enable content option button to allow the macro to run. When the Macro workbook opens, press CTRL+d in a few random cells to run the macro and insert the text Delgado Community College. Viewing a Macros VBA Code Click the Developer tab on the Ribbon. Click the Macros button on the Ribbon to display the Macro dialog box. If necessary, when the Macro dialog box is displayed, click DCC in the Macro name list. Click the Edit button to start the Microsoft Visual Basic Editor. Click the Microsoft Visual Basic Editor Close button on the right side of the title bar.Show more
