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 confusing 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.