Excel Macros - A Power Tool for Advanced Excel Users
What is a Macro?
MS Excel has a whole bunch of built-in functions but also lets you write your own to perform various tasks, usually repetitive. Macros are powerful pieces of code recorded and stored as a series of commands, and can do pretty much anything with your data, for instance:
- Perform repetitive or routine tasks like printing reports,
- Manipulate data and text in an excel sheet,
- Facilitating creation of tables and graphs
- Apply style and formatting, and even
- Communicate with other data sources like database, text files, or within spreadsheets.
Two primary components of writing macros are the:
The Macro Recorder: This is a tool that lets you record steps or user actions, and then replay them at a later time. The macro recorder is incredibly useful to record complex actions and replay them with other set of data in a matter of seconds. This is obviously a great time saving tool and is also the first tool to familiarize yourself with before writing macros.
The Visual basic Editor (VBE): The VBE allows users to write code in a language that is familiar to excel, much like sending people messages in a language they understand. The VBE assists with writing code, flagging errors, and by providing debugging tools that help you to track and detect errors in code.
Since writing macros would require you to use the VBE often, here are the basic visual components to get familiarized with the editor:
- Standard Toolbar: Contains the basic buttons which perform functions like saving, editing, debugging, switching to Excel and hide/show other windows.
- Object Box: Displays the name of the selected object chosen from the drop down box.
- Procedure Box - This displays the name of the procedure or event of the object.
- Code Window: This is where you maintain the VBA code.
- Project Explorer: All the code associated with a workbook is stored in the ‘Project’ window. This is automatically saved with the Workbook.
- Properties Window: Properties are characteristics of the selected object. You can change these characteristics to a worksheet, workbook and user-form.
Writing a macro.
1. Enable macros from the Trust Centre
Before you start writing a macro, there is a security setting in Excel you must change. To protect your machine against virus, Excel has built-in security, to block running of unauthorized macros and functions. To do so navigate to excel options, and then to Trust Centre Settings, select one of the relevant macro settings and click OK.
For a list of excel macro security settings please visit this page or take the Advanced MS Excel course on the Manipal ProLearn website.
Next, for easy access place a command button on your worksheet by clicking on the Developer tab, click Insert, and in the ActiveX Controls group, click Command Button.
Begin by opening an excel sheet with some data organized in it already.
2. Record your macro.
A huge advantage about using Excel macros are that we can build functions without knowing how to code in VBA.
Now, to record a macro,
- go to View > Macros > Record Macro.
- Assign a macro name (without spaces)
- Perform a few actions on the data:
If you’re working with numbers, create a few additional columns and perform a few operations like SUM, AVERAGE, MEDIAN.
Format cells in your sheet - if you’re working exclusively with numbers, then you could visually format cells by adding a header row or summary/total row (bold, color italics, centered etc.).
- Once complete, stop recording the macro and save it.
Congratulation, you have now created your first excel macro. Excel will record every cell change, scroll action, window resize and everything other action you have performed.
3. Edit your macro or look under the hood
Like we read earlier a macro is a piece of code that is written and recorded for excel to interpret. This means the marco we just recorded is saved as a set of VBA commands and can easily be viewed or edited.
All the steps that were recorded by the Macro Recorder will be stored between the Sub MACRO_NAME() and End Sub. You can edit the macro if you’re comfortable programming in VBA or save it for use.
4. Replay your macro.
To test your macro, open the original excel sheet you had created or an excel sheet with similar data.
- Go to the macros menu (under the View tab) and select View Macros.
- You will see the macro name you saved earlier, select this and say Run.
- You will see the cursor jump around a little on the screen, replicating every action you recorded earlier.
A few guidelines to writing or recording macros in Excel:
- Keep macro names short and descriptive: If you record a lot of macros, this will let you uniquely identify macros. Excel also provides a description field that can be used to distinguish what the macro does.
- Use relative cell addresses: Absolute cells are the exact cell locations are recorded into the macro such as B13 or D1. This may limits the macro’s ability to function Relative means the macro’s recorded keystrokes are relative to the starting cell’s position.
- Keep macros small: The bigger the macro, the slower it runs. Debugging a large macro is harder as compared to a set of smaller macros that run sequentially.
Having understood these basics go ahead and write your first macro and breeze through repetitive excel tasks. To create powerful dashboards and learn to write macros take the Advanced MS Excel course on the Manipal ProLearn website.