Macros Scare Me!!

May 17, 2019 | Excel Hints and Tips

Macros bring fear to many and there is very little need for this.  They are used to do repetitive tasks, can save huge amounts of time and are far more accurate than performing the same steps over and over again manually.  The worry often comes from the mention of the word programming!!  But programming does not necessarily need to be used.  It very much depends on the steps that need to be performed. Macros are available in most of the Microsoft products but are more often used in Excel. This article could be applied to any of the packages.

Recording a Macro

Image of Record Macro icon

This is used to record the steps that are performed on a regular basis.  This can be most things that you would normally do in Excel.

  • Start by recording the macro
  • Perform the steps you want to repeat
  • Stop recording the macro

This normally records exactly as you expect it to but not always!

Playing back the Macro

Image of the run macro icon

Once the macros has been recording it can be run again on a new set of data. There are several ways to run a macro.

  • From the macros icon on the view tab
  • By assigning a keyboard shortcut
  • By putting an icon on the quick access toolbar
  • Placing the macro behind a button directly on the sheet

Which method used will depend on what the macro does, how many macros you have and how much knowledge of macros the person using the macro has.

Saving a File Containing a Macro

Files containing macros cannot be saved as a normal xlsx file.  This file type does not support macros and the macros will be deleted.  The file must be saved as a macro enabled workbook.  Luckily if you forget this Excel will warn you before you loose what you have created.

Opening a File containing a Macro

When a file containing a macro is opened there is the choice to enable the macro.  This should only be done if the file is from a reliable source.  This is because the VBA (visual basic for applications) code that sits behind the macro could be potentially harmful.  VBA is the programming side that scares many!  However without the macro being enabled the macro cannot be used.

VBA Programming

Image of the Visual Basic icon

From the macros icon and view macros option there is a choice to edit that allows the code that has been recorded to be viewed and altered.  Some of the code is fairly easy to read but other parts are tricky.  Do not expect to become proficient in using this without a lot of blood, sweat and tears!!  However programming opens up the opportunity to make the macros more flexible than they can be by recording alone.

Want to learn more about any Microsoft products then email to discuss how I can help or have a look at the Courses I run.