How to Create Macros in Excel 2016

Edited 2 weeks ago by ExtremeHow Editorial Team

Microsoft OfficeExcelMacrosAutomationSpreadsheetProductivityCodingDataWindowsMacProgrammingEfficiency

Excel is a powerful tool used for various calculations, data analysis, and visualization tasks. One of its less obvious, but incredibly powerful features is the ability to automate repetitive tasks using macros. Macros in Excel allow you to record the actions you perform and then play them back, saving you time and effort, increasing productivity, especially when dealing with repetitive tasks. In Excel 2016, creating macros is a straightforward process, and this guide will explain every step in detail, making it easy for beginners to get started.

Before we begin, it's useful to understand what macros are. A macro in Excel is a sequence of instructions that automates tasks such as formatting a spreadsheet or applying a formula. They're written in a programming language called VBA (Visual Basic for Applications). You don't need to be a programmer to create basic macros because Excel can record your actions, but learning some basic VBA can be helpful for more complex tasks.

Enabling the developer tab

To create macros, the first step is to enable the Developer tab in Excel 2016. The Developer tab contains all the options you need to control macros. By default, this tab is not visible in Excel. To enable it:

1. Open Excel 2016.
2. Click the File tab in the upper-left corner.
3. Select options from the menu.
4. In the Excel Options dialog box, click Customize Ribbon from the left sidebar.
5. In the right pane, you will see a list of available tabs. Check the box next to Developer.
6. Click OK to apply these settings. The Developer tab should now appear on the ribbon.

Macro recording

Recording a macro is the simplest way to create a macro in Excel 2016. This method allows Excel to automatically generate VBA code based on your actions. Here's how you can record a macro:

1. Click the Developer tab on the ribbon.
2. In the Code group, click Record Macro. A Record Macro dialog box will appear.
3. In the Macro name box, type a name for your macro. The name must begin with a letter and must not contain spaces.
4. Alternatively, you can enter a shortcut key to quickly run the macro. For example, press Ctrl+a letter (e.g., Ctrl+M).
5. Use the Store macro in list to specify where to store the macro. You can choose This workbook to make it available only in the current workbook, a new workbook, or a personal macro workbook for global access.
6. If you want, include a brief description of your macro's function in the Description box.
7. Click OK to start recording.

Perform the actions you want to automate. Excel will record every action you take with your mouse and keyboard. Once you've completed the actions, stop the recording by going back to the Developer tab and clicking Stop Recording.

Running a macro

Once you record a macro, you can run it anytime to perform the actions you recorded. Here's how to run a macro in Excel 2016:

1. Click the Developer tab on the ribbon.
2. In the Code group, click Macros. This opens the Macro dialog box.
3. Select the macro you want to run from the list. Excel lists all the macros available in the current workbook.
4. Click Run to execute the macro.

If you assigned a shortcut key to the macro, you can also run it by pressing that key combination. For example, if you set Ctrl+M as the shortcut key, press Ctrl+Shift+M to execute the macro.

Editing a macro

Sometimes you may need to edit a macro to adjust the actions it performs. You can edit a macro in the Visual Basic for Applications (VBA) editor. Here's how you can do it:

1. Click the Developer tab on the ribbon.
2. In the Code group, click Macros.
3. Select the macro you want to edit from the list.
4. Click on Edit. This will open the VBA editor.

You can now edit the macro's code in the VBA editor. If you are not familiar with VBA, it is advisable to proceed with caution and save a backup copy of your workbook before making changes.

Deleting a macro

If you no longer need a macro, you can delete it using these steps:

1. Go to the Developer tab on the ribbon.
2. In the Code group, click Macros.
3. Select the macro you want to delete from the list.
4. Click Delete, and then confirm your choice when prompted.

Once deleted, the macro will no longer be available, so make sure you really want to delete it, or have a backup of it.

Saving workbooks with macros

Excel workbooks that contain macros require special attention when saving them, because not all Excel file formats support macros. Here's how to save a workbook that contains macros:

1. Click on File Tab
2. Select Save As.
3. Choose where you want to save the file.
4. In the Save as type list, choose Excel Macro-Enabled Workbook (*.xlsm). This format supports macros.
5. Enter a name for your workbook and click Save.

Macro security

Macros can contain harmful code; therefore, Excel has security features to protect you. By default, Excel is set to disable macros with a notification that prompts you to manually enable them if necessary. To adjust macro security settings:

1. Click the File tab, and then click Options.
2. In the Excel Options dialog, select Trust Center from the left panel, then click Trust Center Settings.
3. Click on Macro Settings.
4. Choose the security level you want. It's usually recommended to use the default settings for everyday use.
5. Click OK to apply these settings.

Using VBA to create and edit macros

Recording macros is great for simple tasks, but you may want to perform more complex operations that aren't possible through recording alone. This is where learning some basic VBA comes in handy. The VBA editor gives you a complete environment for editing and creating new macros.

Basic example: Let's create a simple VBA macro that displays a message box when run:

sub showMessage()
    MsgBox "Hello, welcome to your first VBA macro!"
end Sub

In this example, Sub begins the macro definition, and End Sub ends it. MsgBox function displays a message box with the text specified in quotes.

To create this macro:

1. Open the VBA editor by pressing Alt + F11.
2. In the editor, to add a new module, first click Insert, then click Module.
3. Type the above code in the code window.
4. Press F5 to run the macro and see the message box.

Best practices for using macros

Macros are incredibly useful, but to use them effectively and safely, follow these best practices:

• Back up your workbooks: Always back up files before running or editing macros.
• Test macros in a safe environment: Run the macros in a copy of your workbook to make sure everything works as expected.
• Document your macros: Comments in your macros make it easier for you or others to understand what they do when they return to them later.
• Keep macros as simple as possible: Aim for simplicity to ensure that your macros are easy to maintain and debug.
• Be cautious with macros from unknown sources: Enable macros only from trusted sources to avoid malicious code.

Understanding and harnessing the power of macros in Excel 2016 can open up a world of possibilities for automating repetitive tasks and saving time. With knowledge of both the recording process and basic VBA programming, you are well equipped to take full advantage of these capabilities. Always remember to keep security considerations in mind and back up your work regularly.

  Share This Article

If you find anything wrong with the article content, you can request an update