Make Your Spreadsheets Faster Using Microsoft Excel Macros

Do you use Excel frequently and find that you often have to repeat the same tasks over and over? If so, you can use Microsoft Excel macros to automate those tasks and cut down on how much time it takes to complete them!

You can create a macro by recording your keystrokes and mouse clicks, but you can also create code directly into the VBA Editor. Let’s see how to do it both ways!

How to Record a Microsoft Excel Macro

Macros are pieces of code that automate tasks in software. They can be even more powerful than advanced Excel functions.

With the click of a button, you can add your own small (but effective) features and additions to help you swiftly complete whatever task is at hand. 

An easy way to get started is to use the Macro Recorder. Think of it like this: Excel will watch and record your mouse clicks and key strokes. Then, you can ask Excel to reply the recording in the future to repeat those same series of steps.

When you are ready to record your macro:

• Navigate to the View tab and click Macros > Record Macro.
• In the resulting Record Macro dialog, you can give the macro a descriptive name, provide a shortcut key, or write a description. You can also decide which workbook should store the macro. Macros are saved in a specific workbook. To run it in the future, that workbook needs to be open. When you are ready, click OK to begin recording.
• Do the task you want to automate, like filling in generic data, entering text, adjusting rows, applying formatting, etc. Most everything you do is being recorded and translated into VBA code.
• After you’ve finished, head back to the View tab and select Macros > Stop Recording.

The macro is then recorded for you to run in the future whenever you need it. When you want to run the macro, click the View tab, and then Macros > View Macros. In the resulting Macro dialog, select the desired macro and click Run. Excel will immediately perform the sequence of recorded steps. Nice!

Using VBA to Create Macros

Recording Microsoft Excel macros can be an excellent introduction to Visual Basic for Applications (VBA), Microsoft’s programming language. By inspecting the resulting code, you can begin to learn and understand VBA.

While using the macro recording feature of Excel works well in many scenarios, the code produced frequently contains unnecessary additions. The macro recorder has some restrictions as well. 

For those reasons, you may want to write VBA code by hand to create more meaningful and powerful macros.

It may take a little time to write a VBA macro code at first, but once you’ve finished, you can keep it for reference and use it whenever you need it again!

But, before you get started, you’ll probably want to turn on the Developer tab because it contains many tools that help with VBA. To display the Developer tab, open the Excel Options dialog, Customize Ribbon, and check the Developer box in the Main Tabs group.

Microsoft Excel Macro in VBA Example

There are many commands on the Developer tab, and you’ll want to explore them. But the first one called Visual Basic opens the Visual Basic Editor. Think of the Editor as a specialized word processor that allows you to write and edit VBA. Since it knows the VBA language, it helps guide you along the way. You can also open the Editor with Alt + F11 (or fn + Option + F11 on Mac).

Below is a simple macro that closes all workbooks when used:

Sub CloseAllWorkbooks()
    Dim wb As Workbook
    For Each wb In Workbooks
        wb.Close SaveChanges:=True
    Next wb
End Sub

Note that this Microsoft Excel macro code will examine each workbook individually before closing. A message will appear if they haven’t been saved, asking you if you want to do so.

You can actually take this VBA code (or VBA code from other sites) and paste it into your Editor and run it. Note: if you take code from the web, be sure you understand what the code does before running it. VBA is powerful and you don’t want to risk messing up your files or system.

To copy code and paste it into your workbook:

• Open a workbook.
• Open up the VB Editor.
• Right-click on an item in the Project section.
• Click Insert and then Module.
• Copy your code and paste it in the code window.

Once the code has been added to a workbook, you can save it with the .XLSM or .XLS extension.

To run the code in your worksheet, head back to the Developer item and click Macros. A window will pop up with your list of saved macros – just select Run to execute it!

Check Out Some Other Microsoft Excel Macro Examples

In the past, we’ve shared a few Microsoft Excel macros that help you perform all sorts of tasks. Use them for inspiration when creating your own!

SUBTOTAL Macro 

We explain how to create a shortcut button that inserts the SUBTOTAL function, which is a great replacement for the SUM function. 

SUBTOTAL Macro v2

This post builds on the first SUBTOTAL Macro post by adding the ability to select the sum range manually.

Number Format Macro

Excel users tend to spend a lot of time formatting numbers, and manually applying all that formatting takes a lot of time and gets pretty repetitive. That’s the perfect example of when you can use a macro to speed things up!

This post will demonstrate how to set up a macro to format every number in a spreadsheet.

Macros can be very simple or more complicated, depending on your needs – but they’re a great way to maximize your time and learn a little bit about VBA.

Do you have any favorite macros that you use all the time? Let us know in the comments!