How to Enable Macros in Excel
Microsoft Excel

HOW TO ENABLE MACROS IN EXCEL

What Are Macros?

This is 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 a Macro 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

Developer Tab

The Macros function is located in the Developers tab. Microsoft keep this tab hidden, so you can access it via these steps:

  1. File → Options → Customize Ribbon.
  2. Under ‘Main Tab’ tick the box marked ‘Developer.’

How to access developer tab

Developer tab

The tab should now remain every time you open Word.

Search Tool

You can access the Macros pop-box by typing in ‘Macros’ in the search field.

View Tab

You can also access a Macros button in the View tab. This has the option to view macro and start/stop recording Macro.

View tab

How to Record a Macro

To start recording Macros:

1. Go to Developer tab (or view tab) → Record Macro.

How to record macro

2. In the ‘Record Macro’ pop-up box, enter a name, shortcut key and description → Ok.

Record a Macro

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 several ways:

Macros Button

1. Go to Developer tab → Macros (OR view tab → View Macros).

Run Macro

2. A ‘Macro’ pop-up box will appear which lists the Macros you create.

3. Highlight your Macro (e.g. ‘Profit’) → Run.

How to run a Macro

4. All the commands of the Macro will be applied to your spreadsheet.

Keyboard Shortcut

Use the keyboard shortcut you created in the Macro. In this example, press ctrl+q.

Visual Basic

VBA stands for ‘Visual Basic for Applications.’ Basically, it’s Microsoft’s programming language.

1. Go to Developer tab → Visual Basic.

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

Run VBA

You can find more options here, such as adding a button to the Quick Access Toolbar.

How to Edit a Macro

1. Go to Developer tab → Macros (or view tab → View Macros).

2. Select your Macro and click the ‘Edit’ button.

How to edit a Macro

3. This brings up the Visual Basic box with the Macro coding.

4. Edit as needed (e.g. make Bold font Italic).

Edit Macro code in VBA

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.

Macro-enabled workbook message

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.

How to save as micro-enabled workbook

*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.

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:

Macro Settings

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.

please like, share, comment, subscribe

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s