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 …

Continue reading

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 …

Continue reading

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 …

Continue reading

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 …

Continue reading

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 …

Continue reading

www.lostpassword.com offers easy to use password unlocking utilities for Excel 2000 and earlier, as well as server other commercial programs, e.g. Act!, Lotus 1-2-3, etc. We’ve successfully used their 1-2-3 and Word modules to recover password protected documents.

Reading a Lotus 1-2-3 spreadsheet in Excel isn’t always as easy as it should be. If you’re using Excel 5.0 or 7.0, you’ll have to save your 1-2-3 file in either the WK1 or WK3 formats. Excel 97 or 2000 can usually read the WK4 format directly. However, if you’re using 1-2-3 97, you’re out …

Continue reading

The Microsoft web site has an article that describes how to improve the calculation speed of your worksheets.

Use the following custom number format #,###, (4 number signs, two commas) to display 15,000,000 as 15,000 (just be sure to include the words 000’s omitted somewhere on your spreadsheet)!

Enter a date into any cell, and then choose Format and Cells. Choose Custom on the Number tab. Enter mmmm into the Type field. Use mmm to abbreviate the month, or use ddd and dddd to display days of the week.