Project Scheduling in Windows

For you project scheduling needs, consider MS Project. They have come out with an awesome new 2010 version. The buzzword is "Prioritize, Align and Execute Effectively".

For those on a lower budget, perhaps a low budget Gannt chart maker could do nicely.

Mind the Brackets: Coding Formulas in Excel

When coding formulas in Excel (using VBA or an Excel worksheet function) make really really sure that brackets are in the right place. Wrongly grouping expressions leads to erroneous results!!! Know your formulas inside out. Practise the calculations thoroughly with a calculator first if possible. Cultivate the ability to spot bugs in formulas quickly!!!

Use of TABLE worksheet function to do What-if Analysis

Microsoft Excel has some tools for performing what-if analysis, most useful in financial planning. (Of course, since TABLE is a worksheet function, it can't be used as a UDF name).

Excel's "Advanced" Display Options (Worksheet Focus)

Perusing Excel's "Advanced" Display Options via the Microsoft Office button demonstrates three different categories of Display Options ("Display", "Display options for this work&book", and "Display options for this work&sheet"). Keep hitting "Alt-S" until you get to the worksheet display options. Here you can make page breaks disappear and gridlines disappear also. There is also an option to show zero in cells that have a zero value. There is also an explicit page break view which you can access from the (now-familiar) view portion of the chaotic Ribbon.

Statistics Action in Excel

Excel worksheet functions for the Normal Distribution

NORMDIST - return the CUMULATIVE normal distribution
NORMSDIST - return the STANDARD normal cumulative distribution (Z~N(0,1))

The Mysterious Type Declaration Characters of VBA

Suppose you are writing a calculation in VBA. You may notice a mysterious "type declaration character" appearing after the number you have typed. This could be one of several possibilities.

# Double
$ String
@ Currency

Debug.Print Demystified

You can insert Debug.Print statements anywhere in your VBA code. These will appear in the Immediate Window in the VBE (just strike Cntrl-G to display the results of these statements). From Excel 2000, you can also do Debug.Asset (condition) which will break the code if the assertion is violated.

Debugging unfamiliar VBA can be made easier by the use of Debug.Print.

For example, macros may hang and go into infinite loops. This can be due to lack of "Option Explicit". For example, suppose you loop through the numbers I from 1 to 30. You call a function that also uses the loop counter I, which causes I to get stuck at 15 in your main loop - infinite loop! Debug.Print can help you find these problems in loop counters quite easily.

Cntrl-G is your friend!

If you want to take VBA debugging to the next level, you can also use conditional compilation using the #const directive, together with #if and #end if macros. for example:

#CONST DEBUG_ = True
#If DEBUG_ Then
    Debug.Assert (X<109)
#End If

Excel 2007 Keyboard Shortcuts

Inserting a new row in two steps

Shift + Spacebar: Select the current row
Cntrl + Shift + Plus Sign: