How to Create Macros in Excel 2021

Edited 23 hours ago by ExtremeHow Editorial Team

Microsoft OfficeExcelMacrosAutomationData EntryProgrammingWindowsMacAdvancedProductivity

Microsoft Excel is a powerful tool for data analysis, data management, and visualization. One of the features that makes Excel even more useful is its ability to automate repetitive tasks through the use of macros. A macro in Excel is essentially a set of instructions that can be triggered to complete a process that you might otherwise do manually.

Introduction to macros

Before we create a macro, let's understand what a macro is. In Excel terms, a macro is a sequence of commands and functions that are stored in a Visual Basic for Applications (VBA) module. Macros help you perform tasks that you do regularly, such as formatting cells, performing calculations, or manipulating data across multiple sheets.

Macros are created using VBA, which is a programming language that allows you to interact with Excel through code. While you can write VBA code directly to create a macro, Excel also provides a feature called the Macro Recorder, which allows you to record your actions in Excel and automatically generate the corresponding VBA code. This is especially useful for beginners who may not be familiar with programming.

Why use macros?

There are several reasons why you might want to use macros in Excel:

• Efficiency: If you regularly repeat the same series of steps, macros can significantly speed up the process.
• Accuracy: Macros can eliminate human errors that may occur when performing tasks manually.
• Convenience: Automating repetitive tasks allows you to focus on more complex analyses, making your work more productive and enjoyable.

Getting started with macros

To start creating macros in Excel 2021, there are a few pre-requisites you need to make sure you have set up:

Enable the Developer tab

Macros in Excel are accessed through the Developer tab. By default, the Developer tab is not enabled, so you'll need to activate it first.

1. Open Excel and go to the File tab.
2. Select Options from the bottom-left corner.
3. In the Excel Options dialog box, go to Customize Ribbon.
4. In the right pane, check the Developer box under Main Tabs.
5. Click OK to close the Excel Options dialog box.

Macro recording

Once the Developer tab is enabled, you can begin recording your first macro. Follow these steps to record a macro:

1. Click the Developer tab on the ribbon.
2. Click Record Macro in the Code group. A Record Macro dialog box will appear.
3. Enter a name for your macro in the Macro Name field. Excel macro names cannot contain spaces, so use underscores if necessary.
4. Optionally, you can assign a shortcut key to your macro, choose a location to store your macro, and provide a brief description of what the macro does.
5. Click OK to start recording. Excel is now recording your actions. Perform the sequence of actions in Excel that you want to automate.
6. After you finish your steps, go back to the Developer tab and click Stop Recording.

You have now recorded a macro! The recorded macro is stored in the Visual Basic for Applications Editor.

Writing macros using VBA

If you want more control and functionality, you may prefer to write VBA code directly. Follow these steps to write a basic macro using VBA:

1. Click the Developer tab, and then click Visual Basic to open the Visual Basic for Applications editor.
2. In the VBA editor, go to Insert and then click Module. This opens a blank module where you can write your VBA code.
3. Enter the VBA code for your macro. Here's an example of a simple macro that displays a message box:

Sub HelloWorld()
    MsgBox "Hello, world!"
End Sub

In this code, we have defined a new macro named HelloWorld, which contains the command to show a message box displaying "Hello, World!".

Running macros

You can run macros in several ways:

Using the Developer tab

1. Click the Developer tab on the ribbon.
2. Click Macros in the Code group. The Macro dialog box will appear with a list of available macros.
3. Select the macro you want to run from the list and click Run.

Using shortcut keys

If you specified a shortcut key while recording the macro, you can simply press the corresponding key combination to run the macro.

Assigning macros to a button

To make running macros even more accessible, you can assign them to a button in your worksheet:

1. Go to the Developer tab and click Insert in the Controls group.
2. In the Form Controls section, click the Button (Form Control) option.
3. Click anywhere on your worksheet where you want the button to appear. An Assign Macro dialog box will pop up.
4. Select the macro you want to assign to the button and click OK.
5. You can then reposition and resize the button as you like. Right-click the button and select Edit Text to change the button label.

Editing macros

To extend or modify the functionality of a recorded or written macro, you can edit the macro's VBA code:

1. Click the Developer tab, and then click Visual Basic to open the VBA editor.
2. In the Project Explorer window, find the module containing the macro you want to edit.
3. Double-click the module to view the VBA code.
4. Edit the code as needed. Be sure to follow correct VBA syntax and programming practices to ensure the macro works as expected.
5. Once you've made the changes, save your work by clicking Save from the File menu.

Saving workbooks with macros

When you save a workbook that contains macros, save it as an Excel macro-enabled workbook (.xlsm) to preserve the macros. If you save it as a standard Excel workbook (.xlsx), all macros will be lost.

1. Go to the File tab and click on Save As.
2. In the Save as type dropdown menu, select Excel Macro-Enabled Workbook (*.xlsm).
3. Select the desired location and click Save.

Security and macros

Since macros can execute code, they can be used to harm your computer if they come from an untrusted source. It's important to only enable macros from sources you trust:

1. Go to the File tab, choose Options, and then click Trust Center.
2. Click on Trust Center Settings and select Macro Settings.
3. Choose the appropriate option. For maximum security, select Disable all macros with notification.
4. Click OK to apply the settings.

Creating complex macros

Once you become comfortable with basic macros, you can begin writing more complex macros that may include conditional statements, loops, and interacting with various Excel objects. Here is a brief example of a macro with a loop:

Sub HighlightNegativeNumber()
    Dim Cell As Category
    For Each cell in ActiveSheet.UsedRange
        If IsNumeric(cell.Value) And cell.Value < 0 Then
            cell.Interior.Color = RGB(255, 0, 0) ' this will change the color of the cells to red
        End If
    Next Cell
End Sub

This macro goes through all the cells in the active sheet and highlights any cells in red that contain a negative number. This is achieved by using a loop to iterate over the range and applying a condition using an If statement.

Troubleshooting macros

Debugging and troubleshooting macros can be challenging, especially for beginners. Here are some tips:

• Use Debug.Print to output values and diagnose the state of your macro at various points.
• Set a breakpoint in VBA by clicking on the left margin next to the line number in the code editor.
• Use F8 to view the macro line by line and understand where errors are occurring.
• Look for specific VBA errors or problems online, as there are countless resources and forums dedicated to solving Excel VBA problems.

Conclusion

Macros can greatly increase your Excel productivity by automating repetitive tasks and eliminating human errors. As you gain experience, you will discover even more sophisticated ways to use macros to meet your data processing needs. Whether you are using the macro recorder for simple tasks or writing complex VBA scripts, Excel macros are a powerful tool in your arsenal.

Further education

Learning how to write Excel macros is just the beginning. Consider diving deeper into VBA programming to unlock even more useful functionality. There are many free and paid resources available online to help you master Excel macros and VBA scripting.

Always remember to save your work and maintain backups of your files, especially when working with automation scripts such as macros. By doing so, you ensure that you can recover your data in the unlikely event of an error or unforeseen problem.

Good luck in your exploration of Excel macros, and may your Excel worksheets increase in power and efficiency!

  Share This Article

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