Macros from Beginners to Experts

One of the ways to truly maximize the power of the MS Office Suite is through macros.

A macro is basically a set of instructions to perform specific task. I set macros in Word, Powerpoint, but where it truly shines is in Excel. I saved hundred of hours of work just by automating a number of repeating task with macros.

Here’s how I would proceed with creating macros if I’m a

Beginner

The Record Macro button. It’s found from View > Macros > Record Macros.

Give the macro a name and OK to start. It’ll immediately record all the actions done until the Stop Recording square button is clicked at the bottom left to stop recording.

Attached the macro to a button by going to Insert > Shapes to put in a button. Right click the button and select Assign Macro to link the macro created to this button.

Now, each time the button is clicked, the macro is activated and the same steps will be repeated without any interventions.

The pro about this method is that it’s easy and simple. You just have to perform what you want to do, and the macro will repeat it each time.

The con on the other hand is that it is crude. I’m not kidding when I said that it will record everything action including superfluous ones like scrolling the screen etc. The code created are not the most efficient and sometimes, more complex sequences couldn’t be performed through this method.

Intermediate

This is the level is where most of my projects are at. It’s at a level where you are comfortable to look into the source code and modify it. There are tutorials for writing macros available in the internet and the goldmine of source codes.

To access the source code, right click on the tab and select View Code to enter the VBA sheet.

On the left hand side, search for the Modules or wherever you want the macro to be at and write the codes there.

At this level, there’s no need to recreate the wheel. A lot of suggestions and sample codes are available online. I usually start from there. Far easier to work from something existing than to restart from scratch. If I do need to start from nothing, I would actually use the Record Macro function in previous method to record the actions I wanted to do and continue modifying from there. It’s also here where you can realize why there’s a lot of redundant codes created if you used Record Macro function.

Don’t let the mind be limited by what a macro can do. Since I’m doing a finance focused job, I automated my data extraction, parameter refresh, chart refresh, and even copy paste the charts to ppt. Yup, preparing and transferring charts as pictures to specific ppt were all done through macros.

To get better at this stage, I put in the lessons I learned from computer programming in the past. I envisioned what I want the macro to achieve at the end. It might appear utterly complicated, but I break them down into the simplest part on what each function should achieve. At this point, it might be easier to create a flow chart of actions. You can see the relationship on how to create the macro and the flow of data.

I inserted many comments to provide clarify if I revisit the source code in the future to remind myself, or anyone else who inherited the file on what each function should do. In the VBA screen, creating Toggle Point between two line of codes allowed to see how the codes interacted and where did it break down. It’s often how I diagnosed the codes if they don’t work as needed. This is where breaking down the macro to the simplest form allow easy checking on each functions.

Expert

At this level, it involved more in depth knowledge of macro than I’m used to. I’m talking about changing the GUI and creating addins etc.

Something like above. Almost new environment created inside Excel itself. I don’t think most work with Excel will ever need to reach this level but it’s there. And that file above is something I used on a frequent basis.