Category: Excel

Did you know that you can quickly navigate multi-sheet workbooks by right-clicking on the arrow buttons at the bottom left-hand side of the screen? Doing so causes a menu with all sheet names to appear. Left click on the sheet of your choice, and then that sheet will be immediately activated.

Learn about over 270 Excel shortcut keys on the Accessibility section Microsoft web site at http://www.microsoft.com/enable/products/keyboard/keyboardsearch.asp

Be sure to enter formulas in Excel in all lowercase letters. When you press enter, Excel converts all valid portions of the formula to UPPER CASE. For instance, in the case of =sum(jj1:j10), pressing enter causes Excel to return the #NAME error, with =SUM(jj1:J10). Notice that the incorrect JJ1 is still in lower case, while the rest of the formula was capitalized.

Tommy Flynn's VBA site offers many VBA routines that automate various tasks in Excel.

Be careful when working with hidden sheets within a workbook via VBA. Excel will allow you to copy a hidden sheet out of a workbook, into a new file. You can then, via VBA, save that workbook with only a hidden worksheet. Of course, this is a corrupt workbook that you then can't open.

From time to time, the properties window of the Visual Basic Editor (VBE) will fail to dock properly. When this happens, users who are comfortable with– and choose to back up–the Windows registry can rename the following key from
HKEY_USERSDefaultSoftwareMicrosoftVBAOfficeDock
to
HKEY_USERSDefaultSoftwareMicrosoftVBAOffice;Dock

This will solve any errant docking behavior within the VBE. If you're using Windows 2000 and Office XP, the registry key is
HKEY_CURRENT_USERSoftwareMicrosoftVBA6.0CommonDock

Editing the registry is a dangerous operation that should only be performed by an experienced user at your own risk.

Here's a code snippet that shows how:

strDate = DateValue(“01/01/2000”)
strMonth = Month(strDate)
stryear = Year(strDate)
MsgBox DateSerial(stryear, strMonth + 1, 0)

Using a zero for the day argument of the DateSerial function causes VBA to return the last day of the month. Note that you have to add 1 to the month argument to get the end of the current month, otherwise DateSerial would return the last day of the previous month.

Doug Jacobson has a created a great color chart that has the decimal values that you can use with the RGB function for setting colors of objects with VBA. Alternatively, a hardcopy version is available from Amazon.

The new Outlook security measures implemented in Outlook 2000 SR-2 and Outlook 2002 make it an exercise in tedium to send e-mails from Excel or Access through Outlook. Fortunately, there are solutions:

• Outlook Redemption is a programming-based solution.
• Express Click Yes is a free download that automatically clicks through the security prompts for you.
• Additional guidance is offered at www.slipstick.com , the preeminent site for Outlook/Exchange.

Although Word allows you to search and replace non-printable characters, such as line feeds and carriage returns, it's not as easy in Excel. This line of code will does the trick, though. In this case, it replaces carriage returns with the HTML line break tag: Selection.Replace What:=Chr(10), Replacement:=”<BR>” If you simply need to eliminate non-printing characters, use Excel's Clean function.

In the Page Setup dialog box, enter “Page &[Page] of &[Pages]” in the Header/Footer sections to create a header or footer that reads “Page n of nn”.

If you're using Excel to process data to be imported into a contact manager, such as Act!, save the data in dBase IV format, rather than a .CSV file. Act! often chokes when importing .CSV files, but easily accepts .DBF files.

The next time that you need to copy data from one column or row to another, try dragging while holding down the right mouse button. When you drop the data, Excel presents a special menu with some time saving options.

From time to time, Excel's name space can become corrupt. In such cases, Excel will immediately crash whenever you reference certain named ranges from VBA. For instance, a perfectly functioning VBA routine will suddecausing a General Protection Fault for no apparent reason. If this happens to you, try creating a new workbook, and copy your VBA code and data into the new workbook.