5. Run a macro automatically

Now we’re going to take automation one step further:

Now we’re going to look at three ways of automating the whole process and getting our macro to run much more quickly.

A: Assign a keyboard shortcut to a macro

Run a macro automatically: assign a keyboard shortcut

On the Excel developer tab, go “Code”, “Macros” and find the macro you created. Click on “options”. There you’ve got the ability to assign a shortcut key so that you can get your macro running really quickly. We’d recommend you use a key that’s meaningful for you but doesn’t interfere with one of your regular Excel Excel shortcuts (like “Ctrl” with “C” for copy, “V” for paste, “X” for cut”, “Z” for undo, “Y” for re-do, “F” for find, “I” for italics, “B” for bold” etc – don’t use those ones).

B: Link a macro to a button

Assign a macro to a button

If you haven’t seen this before you might be amazed at how easy it is. On the Excel developer tab, under “Controls” pick “Insert”. Under “Form Controls” click on the first one in (it’s a button). Move your cursor down into your spreadsheet and some light cross-hairs should appear. Click once and drag a short distance – you should have just found yourself drawing a button in your spreadsheet. Straight away Excel gives you the chance to link your macro to the button. Easy!

If you right mouse click on the button you can rename it to something more meaningful.

You can copy and paste these buttons across different tabs in your spreadsheet (cool).

Often you see people using buttons like this e.g. for creating a macro to automate printing or help with spreadsheet navigation (although you don’t really need macros to help navigate your spreadsheets – Excel gives you other ways to do that without going to the trouble of coding a macro – see here for more information about naming cells or using hyperlinks to help navigate between or within Excel spreadsheets).

By the way the “Controls” we’ve just played with can be really handy for automating parts of your spreadsheets or making them a bit more user friendly. Here we have some notes for you where we show you how to automate financial modelling scenarios with combo/ drop-down boxes and demonstrate how to use scrollbars to control key model inputs.

C: Link a macro to a menu item

Assign a macro to a menu item

Again, not hard if you know how:

  • Find your Excel options. In Excel 2007 you need to find the round button at the top left hand side. In later versions of Excel (2010 and beyond) find the “File” tab.
  • If you have Excel 2010 (the version with the “File” tab), in your Excel Options, choose the “Quick Access Toolbar”.
  • If you have Excel 2007 (the version with the round button at the top left hand side, from your Excel options choose “Customize”. From here on the instructions for both version of Excel are the same.
  • At the top of the first list select “Macros”. You should see your new macro on the list. Select your macro.
  • Click on “Add” to move it from the left hand list to the right hand list.

You’re setting your macro up so that you can run it really quickly from the quick access toolbar. You’re kind of done with the important stuff but you can assign an icon to the macro so that it stands out a bit more:

  • After adding your macro to the right hand list, click “Modify”
  • A list of icons pops up – choose one that you think is suitable
  • Click on “OK”
  • Click on “OK” again.
  • You should now have a new icon on your quick access toolbar which will run your macro. Click on it and check that it’s working. If you decide you want to remove the icon, just right click on it and select “Remove from Quick Access Toolbar”.

Macros: what next?

We’ve got lots more macro fun to have yet! Next we’re going to look at how to:

  • Make our shiny new macro available to all worksheets in Excel, not just the one we created it in
  • Simplify our macro code – the code we’ve been working with so far (an output from the recorder) does the job but it’s pretty big and ugly – we’re going to simplify and neaten it
  • Loop a macro so that, for example, we can get a macro working through a list of data down to the bottom.

About this online financial modelling course material

You’re looking at a free online financial modelling course extract covering macros.

7 comments so far:

...join the conversation...

Your email address will not be published. Required fields are marked *

  1. Superb

  2. Typical of Excel: we’ve been shown four ways to run a macro (including manually), and obviously there are more (such as calling it from another macro). And for each there will be circumstances when it isthe best.

  3. thank you! this is wonderful!

  4. Great!

  5. Great! Thanks.

  6. This is fun and I’m very impressed and excited about this subject. Thanks

  7. Merci c’est plus que parfait