Tips for Taking on VBA & Excel Macros

For the advanced Excel users who are not programmers and new to VBA

It’s inevitable. If you are an Excel power user, you will reach a point where formulas and the built-in Macro Recorder just aren’t enough. When that happens, VBA (Visual Basic for Applications) macros may be just the thing you need.

Asking you to do programming – even basic programming – can feel a bit like being thrown into the deep end of the Excel pool! With that analogy in mind, here are a couple of “life preservers” in the form of Excel VBA macro tips to keep you afloat.

 Start with Excel’s Macro Recorder

Many times, the built-in recorder gets you 99% of the way to your solution. Begin by making small changes in the VBA code generated by recording rather than starting from scratch! Start with short recordings (only a few tasks) and small changes.

1. 1. Make a simple recording (this example turns the active cell background color to yellow).
   2. Right-click on the tab for the current sheet (bottom left) and select View Code. This will open the Visual Basic Editor.
   3. Double-click Module 1 (in the Project Explorer) under the Modules folder. This opens the recorded macro in the Code Editor frame.
   4. Experiment by making small changes to understand what each line of the macro affect. (i.e. change Color=35535 to 192 and see what happens) Move to more complex recordings to experiment with more complex, but still pre-coded, macros.

 Plan Your Macro

This may seem like an odd tip. You know what you want your Macro to do, right? But when coding, you need to think like a computer, and that means thinking step by step and making no assumptions. Have you ever played the “make a peanut butter sandwich” game? The game goes: Write down the steps to make a peanut butter sandwich as a computer would read it. Invariably, the novice coder will forget steps like “open the lid on the jar” or “dip the knife in the peanut butter” before telling the computer to spread the peanut butter on the bread.

By writing down what steps you need a Macro to perform – in detail – before you begin typing code VBA, you are less likely to miss important steps Excel needs to complete your task successfully. Flowcharts or decision trees can help you visualize the code you need to implement.

Next week – look for more tips on Macros!