What Are Macros?
Before you learn how to enable Macros in Excel, let’s summerise what they are.
Macros are a function to record your mouse clicks and keystrokes. It’s a Microsoft tool that can speed up your progress when working on repetitive tasks. Instead of manually repeating tasks (e.g. entering the same formulas), you can run Macros to do it automatically for you.
Learn how easy it is to enable, record, run, edit and delete Macros in Microsoft Excel. These steps can also be used in Word.
Video Breakdown: Developer Tab 0.26, Record 1.05, Run 1.53, Visual Basic 2.10, Edit & Save 2.28, Disable 3.20
How to Enable Macros in Excel
The Macros function is located in the Developers tab. This tab is usually hidden so you can access it via these steps:
- File → Options → Customize Ribbon.
- Under ‘Main Tab’ tick the box marked ‘Developer.’
The tab should now remain every time you open Word.
You can access the Macros pop-box by typing in ‘Macros’ in the search field.
You can also access a Macros button in the View tab. This has the option to view macro and start/stop recording Macro.
How to Record a Macro
To start recording Macros:
1. Go to Developer tab (or view tab) → Record Macro.
2. In the ‘Record Macro’ pop-up box, enter a name, shortcut key and description → Ok.
3. Perform your functions (as you have put in your Macro description).
4. Developer tab (or view tab) → Stop Recording.
Creating a Shortcut Key
When you create a shortcut key for Windows, the command will be ctrl+(lowercase letter) or ctrl+shift+(capital letter). In this example, we used ctrl+q.
See Your Macro in Action
To run the Macro you just recorded, you can do this in several ways:
1. Go to Developer tab → Macros (OR view tab → View Macros).
2. A ‘Macro’ pop-up box will appear which lists the Macros you create.
3. Highlight your Macro (e.g. ‘Profit’) → Run.
4. All the commands of the Macro will be applied to your spreadsheet.
Use the keyboard shortcut you created in the Macro. In this example, press ctrl+q.
VBA stands for ‘Visual Basic for Applications.’ Basically, it’s Microsoft’s programming language.
1. Go to Developer tab → Visual Basic.
2. A pop-up box will appear ‘Microsoft Visual Basic for Applications.’
3. You can view all your Macros in the example box.
4. Click your cursor inside your chosen Macro code.
5. In the top menu, press ‘Run’ or the green play button, or F5 → close box.
Keyboard Shortcut: alt+F11
You can find more options here, such as adding a button to the Quick Access Toolbar.
Enable Macros in Excel – How to Edit
1. Go to Developer tab → Macros (or view tab → View Macros).
2. Select your Macro and click the ‘Edit’ button.
3. This brings up the Visual Basic box with the Macro coding.
4. Edit as needed (e.g. make Bold font Italic).
5. In the top menu, press ‘Run’ or the green play button or F5.
6. Press save (either in Visual Basic or general screen). You will get a message that the features can’t be saved in a macro-free workbook.
7. Press ‘No’ and the save option appears → in the ‘Save As Type’ drop-down list, select ‘Excel Macro-Enabled Workbook (*.xlsm)’ → Save.
View video for a full demonstration.
*Note – if you want to change the Macro description, go to Macros → Options.
How to Disable Macros
As above, you need to be in an Excel Macro-enabled workbook. You can locate this file in the folder you had saved it to.
With Macro security settings, you can control which macros run and when, or disable macros. This is through the Trust Center.
1. Go to Developer tab → Macro Security.
2. Macro Settings → select your preferred option.
E.g. ‘Disable all macros with notification’ will show a message in the top bar when you open your spreadsheet:
As you can see, using Macros can be of great value. Taking a few minutes now to learn this function can save you hours of time in the future.