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.
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.
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.
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!
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.
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.
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.
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 are a very powerful feature of Excel, but probably the most poorly documented by Microsoft.
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
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.