6StringJazzer's Excel Tips

Jeff is a moderator on the Excel Forum as 6StringJazzer.

This is not a reference or a tutorial on VBA, but just a few tips that I find that I explain time and time again.

How to Install VBA Code

Have some VBA code and no idea how to use it? These instructions show how to take existing VBA code and install it in your workbook.

Types of Excel VBA Code Modules

There are several code modules available in Excel VBA. These are not generally understood by the more casual user who just records a quick macro. Understand what these are and how to use them to be a VBA programmer.

Declare Your Variables!

It's not just a good idea, it can help you find errors before you even start debugging. VBA will nag you if you just ask it to!

Conditional Formatting (From 2007 Forward)

In Excel 2007, conditional formatting got a facelift. It now has much more powerful features, but is also harder to learn. This set of tips helps the newbie over the most confusing aspects of writing conditional formatting rules with custom formulas.

How To Clean Up Code from the Macro Recorder

The macro recorder is a great way to kick-start your coding but it leaves behind a lot of junk code that good programmers should clean up.

Qualify Your References

References to objects in VBA have defaults if you don't explicitly qualify them. I generally advise making these explicit, which reduces errors due to confusion about the defaults.

VBA References

Seamlessly integrate multiple Office applications with VBA by referencing libraries of other applications. You can create Word documents using Excel data, use Excel to analyze Microsoft Project data, but first you have to set up references.

Array Formulas

Array formulas are a very powerful feature of Excel, but probably the most poorly documented by Microsoft.

Tools and Utilities

VBA Code Analyzer

This tool reviews the VBA code in an Excel macro-enabled file and generates two reports:


A hierarchical inventory of:

*Lines of code is physical, nonblank, non-comment lines of code.

If multiple physical lines are split using a continuation character (_) they are counted as one line

If a single physical line has multiple statements by using a split character (:) they are counted as multiple lines

Call Map

A catalog of all Subs/Functions/Class Properties and what other Subs or Functions they call, and are called by.

No capabilty exists at this time to determine the source of calls to Class Properties so "Called By" for Class Properties is not listed.