How to Use a Macro to Automate Repetitive Tasks in Excel

If you’re someone who works with numbers, you probably spend a good bit of your day working in Excel. In that case, you’ll know that there are certain tasks you have to do over and over again – like entering the same data into different cells or copying and pasting rows and columns of information. 

Now, wouldn’t it be great if there were a way to automate those tasks so that you could spend less time on them?

Enter: macros, the revolutionary tool for Excellers. With macros, you can record a sequence of keystrokes and mouse clicks that will automatically repeat whenever you run the macro. That means that you can spend less time doing monotonous tasks and more time getting things done. 

What Are Macros?

Macros are a type of automation tool that allows you to record a sequence of keystrokes and mouse clicks. When you run the macro, the recorded sequence will replay automatically, making it easier for you to complete repetitive tasks.

Macros are extremely powerful and can accomplish almost anything you can imagine. As an example of what you can do with a macro, here’s a (very) brief list of things you can do with it:

• Make use of style and formatting
• Manipulate words and data
• Create a connection with a data source (database, text files, etc.)
• Make a completely new file
• Any of the aforementioned in any combination, in any sequence

How Do Macros Work?

When you create a macro, Excel records everything that you do while the macro is recording. That includes the keystrokes that you type, the mouse clicks that you make, and even the time that it takes for you to complete each step. When you run the macro, Excel will play back the recorded sequence exactly as it happened when you created the macro.

As businesses have shifted to remote work options, many of the logistical challenges that were once thought to be a barrier to a remote workforce have been overcome. Because of the necessity to work from home, businesses created innovative ways to create a safe environment during a time of uncertainty.  When restrictions started to lift and it was deemed safe to be in larger groups, the workforce now could choose between working in-person or remotely instead of being forced into a decision. The same SHRM article stated, “Looking ahead, 83 percent of respondents said that even after the health crisis has passed, they plan to put more flexible work policies in place, such as allowing more people to work from home or letting them adjust their schedules.”   

If working remotely proved anything, it’s that there is no longer a stigma or assumption that employees cannot be productive, contributing members of an organization when away from the office. This mindset shift merges with the next trend:  the rise of the contractor workforce. When businesses realize that substantial work can be accomplished outside the office, hiring from outside the organizational boundaries becomes a common theme, too.

Trend 2: The rise of the contractor workforce 

Organizations are shifting to a flexible structure that enables a swift response to change and incorporates freelance workers and remote employees as part of the organizational structure. 

As mentioned before, Excel macros playback from where your mouse pointer is positioned when you hit Run – so if you’re not careful, you could inadvertently run your macro on the wrong data set. To avoid this issue, always make sure that your mouse pointer is in the cell you want your macro to start on before clicking Run.

Add a Button to Run Your Macro

You may utilize a variety of Excel objects as macro buttons. In fact, you can add a macro button to any object on an Excel worksheet.

1. To create a macro button, you first need to insert a shape into your worksheet. Then, go to the Developer tab and click on the Control Button icon.
2. This will open up a palette of different shapes that you can choose from. Select the shape you want to use and then draw it onto your worksheet.
3. After you’ve inserted the shape, right-click on it and select “Assign Macro.”
4. This will open the Assign Macro dialog box. Select the macro you want to assign to the button and then click on OK.
5. The macro will now be assigned to your button, and you can press it to run the macro.

How To Customize Your Macro Buttons

• You can also change the button’s appearance by clicking the Format button. This will open the Format Control dialog box, where you can select a different shape, color, and other formatting options for your button.
• You can also add text to your button by clicking on the Text Box icon on the Control Button palette. This will insert a text box into your button. Type in the text you want to appear on your button, and then format it as you would any other text box.
• If you want to change the position of your button, you can do so by clicking on the Arrange button. This will open the Arrange Buttons dialog box, where you can select from various alignment and spacing options.
• You can also use the Size and Properties buttons to change the size and other properties of your button.

How To Change Macro Security Settings

You might encounter an error when running a macro, telling you that the macro has been disabled. This is because Excel’s Macro security settings have been changed to prevent potentially harmful macros from running.

The steps for changing macro security settings depending on which version of Excel you’re using:

• Excel 2010 and up: Click on the “File” tab and select “Options.” Then click on “Trust Center.”
• Excel 2007: Click on the “Office” button and select “Excel Options.” Then click on the “Trust Center” tab.
• Excel 2003: Click on the “Tools” menu and select “Macro Security.”

From here, you can change the security level for macros. The options are:

• Disable all macros without notification: This is the most secure option, but it also means that you will not be able to run any macros.
• Enable all macros with notification: This is less secure than the previous option, but it will allow you to run macros that you trust.
• Enable all macros (not recommended): This is the least secure option, and we do not recommend using it.

Editing Your Macros

You don’t have to rerecord a macro after you’ve generated it if you want to modify the way it acts. In many circumstances, changing its functionality by merely modifying its contents in Visual Basic Editor (VBA) may be more efficient.

You must first reveal this workbook by clicking on Window>Unhide in the dialog box and choosing PERSONAL.XLS before you can use VBA Editor to alter a macro stored in your Personal Macro Workbook.

Follow these procedures to open a macro in Visual Basic Editor for editing:

1. First, open the workbook that contains the macro you want to edit.
2. Then, go to the Developer tab and click on Visual Basic Editor.
3. This will open up a new window with two panes. The pane on the left is for browsing through the objects in your workbook, while the pane on the right is for viewing the code associated with the object selected in the left pane.
4. To select a macro for editing, click on it in either of these panes.
5. If you’re not sure which macro you want to edit, then you can search for it by typing part of its name in the Search text box at the top of each pane.

Editing Macros in VBA

Now that you’ve selected your macro for editing, the next step is to actually edit it. This is where you can change how the macro works by adding or changing code. For example, you could change the way a macro prints data to the screen by adding a statement that clears the screen before printing. You can also use VBA to create entirely new macros.

The possibilities are endless, so be creative and see what you can come up with.

“Basic” Visual Basic Editor (VBA) Codes

If you’re feeling adventurous, you may also want to try creating your own macros with Visual Basic for Applications (VBA), a basic programming language included in Microsoft Office. This can be a great way to automate complex or repetitive tasks in Excel.

To get started, open up the VBA Editor by pressing Alt+F11. This will open up a new window with a lot of code in it. Don’t worry if you don’t understand any of this code—you don’t need to in order to create macros.

All you need to do is type the following statement in the top-left corner of the window:

1. Sub MyMacro()

This tells Excel that you’re going to create a new macro called “MyMacro.” Anything that you want your macro to do should go between the parentheses after this statement.

For example, you could add the following line of code to print “Hello, world!” on the screen:

1. MsgBox “Hello, world!”

This would cause the text “Hello, world!” to pop up on-screen when the macro is running.

You can also use VBA to interact with other objects in your workbook, such as cells and ranges. For example, the following code would print the value of cell A1 to the screen:

1. MsgBox Range(“A1”).Value

This would cause the text “The value of A1 is: _____” to pop up on-screen when the macro is running.

You can find more information about VBA (including a tutorial) by going to the Microsoft Office website or here at ProsperSpark, we offer 1-on-1 hourly support aimed at answering your questions or providing training.

Conclusion

Macros can be a great way to automate complex or repetitive tasks in Excel. In this article, we’ve shown you how to create and edit macros. We’ve also provided some basic information about Visual Basic for Applications (VBA), a programming language that you can use to create your own macros. Be creative and see what you can come up with!

At ProsperSpark, we eat, sleep, and breathe macros and VBA.  If you’re just exploring how macros function, or if you’re genuinely interested in building your skill set, we are here to help!  Our wide range of services include the 1-on-1 hour session mentioned above to robust and dynamic project creation.  If you’re ready to take your macro game to the next level, we’d love to chat!