Macros

Macros

Recording Macros

It is not difficult to create a macro in Excel 2016, but first, you should make sure that the Developer tab is accessible. 

• To do this, click the Excel Options button on the File and then click the Customize Ribbon option on the left of the Excel options window. 
• Under the Top Options for working with Excel option, you will see a checkbox next to the Developer.

• If you check this box and click OK in the options window, the Developer tab will appear at the top of the Excel screen. If you click the Developer tab, you will see the Developer Ribbon.

• This Developer Ribbon is your gateway to creating and editing macros in Excel 2016. To record a macro, click the Record Macro button in the Code button group.
• This will display the Record Macro dialogue box.

• Here you can give your new macro a name (do not use spaces) and choose the location where it should be stored. Your options are to store it with this workbook, another workbook, or a special macro workbook.

This special macro workbook is called the personal macro workbook. A macro that is stored here can be run in an any Excel workbook on the same computer. If you are creating a macro that you think you would like to use in multiple workbooks you should consider saving it in the personal macro workbook.

• You can also enter a brief description of the macro you are about to create in the bottom text area (labeled Description). By default, the macro will be called Macro1 (if this is your first macro) and it will be saved in the current workbook.
• If you click OK, you will see a button appear in the lower left corner of the Excel screen in the status bar.
• The small square is the stop button, which will stop the macro from recording.
• When you see these buttons, every keystroke or Excel action that you take with the mouse will be translated into VBA (Visual Basic for Applications) code by Excel. This code is what makes up your macro.
• When you are finished performing the actions or keystrokes you wanted to record, you can stop recording the macro by clicking the small blue square in the status bar in the lower left.
• The sequence of Excel operations or procedures that you recorded will be replayed (meaning the actions will be performed) every time you run the macro.

If you record a macro and then try to save your workbook, you may see an alert like the following.

• If you click the Yes button, the macros created and stored in this workbook will not be saved. To keep these macros, you must specify a macro-enabled workbook in the Save As type field in the Save As dialogue box.
• If the macro is stored in the personal macro workbook, you can run the macro in any workbook you open (given the appropriate security settings), even if the open workbook was not saved as a macro-enabled workbook. 
• If you choose to store a macro in the personal macro workbook, you will get the following message when you close Excel.

• Clicking the Save button will commit the macro you created to the personal macro workbook. Macros in this personal macro workbook will be available for use when you open Excel.

Saving to Macros Enabled Workbook

When working with macros in Excel 2007 to 2016, you save and open workbooks in a new macro-enabled workbook format (.xlsm) that provides added security. When you create a macro, you must use a macro-enabled format to save your workbook or the macro won't be saved.

If you open a macro-enabled workbook, a Security Warning message states that the workbook contains macros. This protects you from possible harm – some macros might contain viruses or other hazards. You can choose to enable the content if the workbook is from a trusted source.

Saving a macro-enabled workbook:

1. Click the File tab and then choose Save As. 
2. Enter a name and select a location for your workbook. 
3. Click the Save as Type drop-down arrow. Select Excel Macro-Enabled Workbook (*.xlsm) extension to the filename then Save.

Note: If you create a macro in a workbook and neglect to save the workbook as a macro-enabled workbook, you see a warning message telling you that the macro will not be retained. 

Closing and Re-Opening a Macro Workbook

To close a macro workbook is the same method of closing a normal workbook. Just simply click on the close button at the top right corner of the Excel application.

Or the second method, click on the File tab go to Close command.

To re-open a macro workbook, will be the same as how you would open a normal Excel file. The only difference is you see a security warning message.

• Browse the file in windows explorer → double click on the file. 
• Or method 2 will be File tab → Open → browse for your file → Open.
• When the file opens, you will see the “SECURITY WARNING” message.

Security Warning Message

The next time you retrieve your file, you will see a “SECURITY WARNING” message band. This means you will have the option of choosing to allow or not allow the macro to run.

Notice how the security alert appears below the ribbon. If you click the Enable Content button. 

Once again, clicking the Enable Content button will allow you to run the macro.

Playing a Macro

When you play a macro, the actions or keystrokes you chose for your macro will be performed in the sequence in which they were recorded.

To play a macro, click the Macros button on the Developer Ribbon.

When you click this button you will see the Macro dialogue box.

Here you can see the macros available for use. Note that macros saved in the personal macro workbook will have Personal.XLSB! in front of their name (such as the macro selected in the list).

• To play the selected macro, click the Run button. 
• To view or edit the Visual Basic code for the selected macro, click the Edit button. 
• To delete the selected macro, click the Delete button.

When you click the Run button, the Macro dialogue box will disappear, and the sequence of instructions that was recorded in the macro will be performed on the current workbook.

Note: You can see the macros from this workbook and from the personal macro workbook because the “All open workbooks” option is selected in the bottom drop list of the macro dialogue box. If you select the “This workbook” option from the list or the “Personal.xlsb” option, only the macros corresponding to the specific option you select will be shown.

Assigning a Keystroke to a Macro

There are two ways to assign a shortcut key to a macro. You can assign a shortcut key when you are first recording your macro or you can assign a shortcut key to an existing macro.

• To assign a shortcut to a macro when you record it, click the Record Macro button on the Developer Ribbon. When you see the Record Macro box, just enter a letter in the Shortcut Key data field.
• Here you can see that the letter d has been assigned as a shortcut key for this macro. Once the macro has been recorded, pressing Ctrl + d will run it. This avoids the process of picking a macro from the Macro dialogue box every time you want to run it. If you press Shift + D when you enter the shortcut key, the shortcut will then become Ctrl + Shift + D for the macro.
  
• If you store the macro in the personal macro workbook, the shortcut will be carried with it wherever you use the macro.
• To assign a shortcut to an existing macro, click the Macro button to display the Macro dialogue box. In the Macro dialogue box, select the name of the macro you want to assign a shortcut key to and then click the Options button. 
  

• This will open the Macro Options dialogue box for the macro you selected.
  

There is a small data field that can be used to enter a shortcut key for this macro. You can enter a letter or use a Shift key/letter combination as before. If you click OK, the shortcut key will be assigned to the macro.