News & tips
Creating a simple macro in Excel is not a daunting task. Now it may seem contradictory, but you do not need to learn Visual Basic for Applications (VBA) to record simple macros in Excel.
VBA does however, come in handy if you would like to write complex macros or edit existing macros.
We cover creating macros in great detail in our advanced Excel training course.
Macros are blocks of code, which assist with automating tasks. The word macro is short for macroinstruction.
You can create a simple macro by using the Macro recorder, in which case Excel generates the VBA code for you.
A simple macro, created with the Macro recorder should contain a series of steps that tells Excel how to accomplish a certain task.
Let’s say every day, a student makes his favourite omelette for breakfast. He first beats three eggs in a bowl. He then adds some butter to a frying pan and waits until the butter is melted.
He pours the eggs into the frying pan. He lets the eggs cook for a little bit. He then adds spinach, garlic and five sundried tomatoes to the eggs.
He lets this mixture cook for a little while and then slides the omelette onto a plate when ready.
Now let’s say this student doesn’t want to make breakfast for himself every morning. He wants someone else to make it for him, so that he can save time in the morning when he gets up, since his favourite omelette is ready.
Now in this context, you can think of a macro like a recipe in a way. Excel is the chef (seasoned professional) following the recipe, in order to create the omelette that this student would usually have to make for himself.
Let’s review some advantages and disadvantages of using macros in your workbooks.
Due to the potential security risk, by default macros are not enabled.
When opening a workbook containing macros, you will see a Security Warning.
Click on the Enable Content Button in order to enable macros in your workbook.
In our example, every week an office worker receives an Excel workbook. The workbook contains four worksheets with the weekly sales data of interest, for each salesperson.
There are a series of things she has to do formatting wise and it is the same for each sheet:
Since all the worksheets have the same structure and the steps are simple, she can record a macro to do this for her instead of doing it manually every time for each sheet.
To do this, go to:
1) So on the sheet called JaneSmith select cell H10. Go to the Developer Tab on the Ribbon (Step 1 in the image). In the Code group, click on Record Macro (Step 2 in the image).
2) The Record Macro Dialog Box will appear.
Enter a name for your macro, which in this case is StandardFormattingRequired.
Assign a shortcut key, use Ctrl-Shift-B in this case.
Choose where you would like to store your macro.
We will choose This Workbook.
Enter a description for your macro. This is optional but helpful if you are sharing your workbook with other colleagues. In this case, type “This macro applies the standard formatting needed.”
The result is shown below.
4) Once you’ve completed the formatting go to the Developer Tab on the Ribbon (Step 1 in the image), in the Code group click on Stop Recording (Step 2 in the image).
5) Now save the file by going to File and clicking on Save As. Once you have selected a location. Give your workbook a name.
Instead of saving the workbook as a standard Excel workbook, choose Excel Macro-enabled Workbook in the Save as type: section and click Save
For example, let’s say you changed the font colour to orange first and then blue. Excel would record this and while it won’t affect the functionality of your macro, there will be unnecessary lines of code generated.
We want to see if the macro we just created works.
1) So go to the sheet called JohnAbrams.
2) On the Developer Tab (Step 1 in the image), in the Code group click Macros (Step 2 in the image).
You should see the Macro Dialog Box.
The name of the macro we just created should be there.
With this macro selected. Click Run.
Now this sheet should be formatted in exactly the same way that the previous sheet was.
3) Now go to the sheet called AlisonWillis and this time press the shortcut key we assigned to the macro which was Ctrl-Shift-B.
The result is shown below.
Let’s see how this works.
1) Go to the sheet called IanScott.
2) On the Developer Tab on the Ribbon (Step 1 in the image), click on Insert (Step 2 in the image) in the Controls group.
3) Under Form Controls, select Button.
4) Click on any place on the worksheet.
5) The Assign Macro Dialog Box should appear.
6) Select the name of the macro which you’d like to assign to the button, which in this case is StandardFormattingRequired. Click Ok.
7) Ensure Design Mode is on.
You can resize the button by dragging the edges. Type the text “Apply Standard Formatting” on the button.
Now make sure Design mode is not selected.
8) Click on the button we just created and the macro will run.
1) To do this, go to File and then choose Options. Choose Quick Access Toolbar.
2) Scroll down to View Macros.
3) Click Add.
4) Click Ok.
5) Go to the button on the Quick Access Toolbar and click to View Macros and then run the macro from there.
This means that the keystrokes are recorded based on a cell’s starting position. The macro will not use exact locations, which is useful if anything changes in the structure.
Read more about absolute, relative and mixed references here.
Let’s review the skills you have learned:
1) You know what a macro is.
2) You have learned to think about the logical steps involved in creating a simple macro.
3) You have learned how to record your own simple macro.
4) You have learned how to run a macro.
5) You have learned how to assign a macro to a button.
6) You have learned how to assign a macro to the Quick Access Toolbar.
7) You know how to save Excel workbooks as macro-enabled workbooks.
Macros are extremely useful for speeding up your work flow in Excel and increasing productivity.
If you would like to continue your VBA journey and learn about how to create more complex macros, then please see our Microsoft Excel VBA course which will get you up to speed with VBA.