Speeding up VBA with the PrintCommunication Property in Excel 2010

I have an Excel file that creates different reports from a data table using a macro, and before Excel 2010 was released the “time-hog” was running VBA code for the print settings. Tell me you’ve added something like the following code and had your execution time slow exponentially. With ActiveSheet.PageSetup .CenterHeader = “&””Verdana,Bold””&12&A” .CenterFooter = …

Read more

How to Quickly Convert 1904 Dates to the 1900 Date System in Excel

Microsoft Excel’s handling of dates is a critical feature for accurate data management. However, discrepancies between the 1900 and 1904 date systems can lead to confusion and errors. This comprehensive guide will help you understand these systems and provide step-by-step instructions for adjusting dates correctly.

Documentation for VBA in Excel

A long time ago, in a far-away classroom I was seemingly forced to write copious amounts of documentation in a FORTRAN class just for a few lines of code. It didn’t help that I was just learning how to keyboard (type) and that I didn’t understand why pseudo code was also required by my nameless, …

Read more

Automatically Expand a Named Range in Excel

I usually put a name to each data table created for referencing information in in Excel, as in a Named Range. If you add data to the bottom of the table, the Named Range isn’t modified and any reference to it will fail to include the new information. Here’s a table of data I stuck …

Read more

VBA Help System Lacking in Excel 2011

Since never using the VBA Editor in Excel 2011 for Mac was a slight embarrassment, I thought to give it a whirl with a half-written function that was started in Excel 2010. I quickly found out the Editor’s windows have more of a free-floating style, but the programming looked to be quite the same. Until …

Read more

Excel ISNUMBER or IsNumeric Function

I don’t like it when Excel worksheet functions are different from their VBA counterparts. Makes for some aggravation. For example, I have a user generated data range that’s supposed to be made up of either numbers or empty cells. However, Excel users sometimes bump their keyboards inadvertently (when you hit the space bar) and things …

Read more

Hide or Show Names in Excel with VBA

If you’ve discovered how useful Names can be in a spreadsheet, you may also know they can get-in-the-way-if-you-have-too-many. Just check out the Name Box in the picture to the right. Each Name has a Visible property that’s set to TRUE by default. You can hide a Name by changing the Visible property to FALSE, but …

Read more

Hide Worksheets in Excel

There are some things you can do in the VBA Editor that won’t trigger the dreaded warning about opening a file with a macro. Hiding a worksheet so nobody can find it is one of those things. Excel worksheets have a visible property that can take on one of three values. xlSheetVisible xlSheetHidden xlSheetVeryHidden By …

Read more