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 = "&P of &N"
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = False
End With

Another factor is the default printer of your computer when the code runs. I usually set my default printer to CutePDF, which isn’t really a printer, but a program that writes (prints) to a PDF file. Using this default printer while running the PageSetup code chops mega-time off the execution.

One trick I used was to run this code in the Workbook_BeforePrint module. That way, running my macro to create a new report doesn’t execute the PageSetup code and therefore executes much, much, much, much, much faster.

The New PrintCommunication Property

In Excel 2010 I happen to record a macro for print settings and discovered a new page setup property:

Application.PrintCommunication

The Application.PrintCommunication property is new in Excel 2010. It specifies whether communication with the printer is turned on. The values are Boolean and Read/Write.

Using the PrintCommunication Property

Set the Application.PrintCommunication equal to FALSE, then run the Page Setup code, then set it back to TRUE and your code will run exponentially faster. However, there are two problems with using the Application.PrintCommunication property and both deal with compatibility.

Compatibility with Older Versions

Older versions of Excel don’t recognize this new property so you have to code around this fact to ensure backward compatibility. I checked the Excel 2010 version number by running some code in the VBA Editor’s Immediate window.

You can also check your Excel version by choosing File > Help and reading the screen.

Since the Application.version property returns a String value, the Val function is required to make use of it. My code now reads like this to achieve backward compatibility:
If Val(Application.Version) >= 14 Then Application.PrintCommunication = False
With ActiveSheet.PageSetup
' (all the ugly page setup code goes here)
End With
If Val(Application.Version) >= 14 Then Application.PrintCommunication = False

I checked to see how long it takes to run the VBA code to produce my report in three versions of Excel: 2010, 2007 and 2003.

Excel 2010 – run1 = 0.918 sec, run2 = 0.867 sec
Excel 2007 – run1 = 5.965 sec, run2 = 6.273 sec
Excel 2003 – run1 = 6.066 sec, run2 = 6.125 sec

My default printer at the time was a HP D110 and it took over 5 seconds longer to run the PageSetup code in Excel 2007 and 2003.

Compatibility with Excel 2011 for Mac

By now I’m happy with Excel for adding this new property, but reality sets in and I realize that Excel 2011 can run code too. I check to see what version number Excel 2011 for Mac happens to be, and it too is version 14.0.

So while I’m in the immediate window I check for the Application.PrintCommunication property and find out it doesn’t exist.

So I have to use the OperatingSystem property of the Application object to give me another condition.

Excel 2010 Operating System

Excel 2011 Operating System

I use the Left function to grab the first three letters of the operating system name.

Left(Application.OperatingSystem, 3) = “Win”

Then change my code to account for earlier Excel versions AND the Windows operating system as follows:

If Val(Application.Version) >= 14 And Left(Application.OperatingSystem, 3) = "Win" _
Then Application.PrintCommunication = False
' (my page setup code here)
If Val(Application.Version) >= 14 And Left(Application.OperatingSystem, 3) = "Win" _
Then Application.PrintCommunication = True

Using the PrintCommunication property of the Application Object will help speed up your code execution. However, you need to be aware of backward compatibility for earlier versions of Excel that don’t recognize the code, and with the Mac version of Excel 2011 that should have the same property, but doesn’t.

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 on a worksheet called MyData and the range A2:E10 is named myFoodData.

Inserting a row inside this range will automatically expand the reference for the Named Range, but normally a user would add data to bottom of the table in the first empty row.

My solution is event based. I write a simple subroutine.

Sub ShiftRangeAndRename()
Const n As String = "myFoodData"
Dim rng As Range
Set rng = Range(n).CurrentRegion
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
rng.Name = n
End Sub


Then reference it from the deactivate routine on the MyData worksheet.

Private Sub Worksheet_Deactivate()
Call ShiftRangeAndRename
End Sub

When a user goes to the MyData worksheet and updates data and returns to the main worksheet, the worksheet deactivate routine calls the routine to update the range reference and its associated name. This also works if they are deleting data, but that’s not common in this type of situation.