Odd behavior on margin change

[John]

I have to loop through a significant number of charts applying numerous
changes in formats. The speed of operation is fast when I am operating my
computer stand-alone. However, when I am connect to the internet when
running my macros the speed slows down tremendeously and I can observe
significant activity on my wireless router and cable modem. The speed is
faster in the docking station at work. However, it is slower than when
stand-alone.

I narrowed down the calls that cause the slowdown and router and cable modem
activity. I want the page margins to be different than the defaults and
allow the user to set them. The following code causes the problem, which I
simplified for example:

Private Function FormatChart(oChart As Chart, ...other parameters)
With oChart
With .PageSetup
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.25)
End With
' other code
End With
' other code
End Function

It doesn't matter whether I enter a value in points or have the app
calculate it. The workbook is self-contained, not linked to another workbook.

Any ideas?

Thanks,
John

 

[Jon Peltier]

John -

Aha, now it's clear. Each chart requires four calls to the printer
driver, which is notoriously slow in Excel VBA. When your computer is
standalone, you're not hooked up to a printer, which presumably shortens
the communication loop.

The old XLM page.setup routine is much faster. I have a function I use
which runs rings around VBA's PageSetup. It builds the XLM property
string based on optional arguments supplied in the function call. It may
not be 100% debugged, but so far it's worked in a small number of my
projects. Let me know if you find a problem or think of enhancements.

' call page setup like this
bSuccess = XLM_PageSetup(<various arguments>)

' XLM Page Setup Function (watch the word wrap)
''==========================================================================
Function XLM_PageSetup(Optional HeaderL As String = "", Optional HeaderC
As String = "", _
Optional HeaderR As String = "", Optional FooterL As String = "", _
Optional FooterC As String = "", Optional FooterR As String = "", _
Optional MarginL As Double = 0.5, Optional MarginR As Double = 0.5, _
Optional MarginT As Double = 1, Optional MarginB As Double = 1, _
Optional MarginH As Double = 0.5, Optional MarginF As Double = 0.5, _
Optional PrtRCHead As Boolean = False, Optional PrtGrid As Boolean
= False, _
Optional CtrHoriz As Boolean = True, Optional CtrVert As Boolean =
False, _
Optional PgOrient As Long = xlLandscape, Optional PaperSize As
Integer = 1, _
Optional FitToOne As Boolean = True, Optional PrtScale As Long = 100, _
Optional FitPgsWide As Integer = -1, Optional FitPgsTall As Integer
= -1, _
Optional FirstPgNum As Variant = """Auto""", Optional PgOrder As
Integer = 1, _
Optional BW As Boolean = False, Optional PrtQual As String = "", _
Optional PrtNotes As Boolean = False, Optional PrtDraft As Boolean
= False, _
Optional ChtSize As Long = xlFullPage) As Boolean

Dim sPgSetup As String
Dim sScale As String

If Not ActiveChart Is Nothing Then
sScale = ""
ElseIf FitToOne Then
sScale = "TRUE"
ElseIf FitPgsWide > 0 Or FitPgsTall > 0 Then
sScale = "{" & IIf(FitPgsWide > 0, FitPgsWide, "#N/A") & "," &
IIf(FitPgsTall > 0, FitPgsTall, "#N/A") & "}"
Else
sScale = CStr(PrtScale)
End If

sPgSetup = """&L" & HeaderL & "&C" & HeaderC & "&R" & HeaderR & ""","
sPgSetup = sPgSetup & """&L" & FooterL & "&C" & FooterC & "&R" &
FooterR & ""","
sPgSetup = sPgSetup & MarginL & "," & MarginR & "," & MarginT & "," &
MarginB & ","
If ActiveChart Is Nothing Then
sPgSetup = sPgSetup & PrtRCHead & "," & PrtGrid & ","
Else
sPgSetup = sPgSetup & ChtSize & ","
End If
sPgSetup = sPgSetup & CtrHoriz & "," & CtrVert & ","
sPgSetup = sPgSetup & PgOrient & "," & PaperSize & "," & sScale & ","
sPgSetup = sPgSetup & FirstPgNum & ","
If ActiveChart Is Nothing Then sPgSetup = sPgSetup & PgOrder & ","
sPgSetup = sPgSetup & BW & "," & PrtQual & ","
sPgSetup = sPgSetup & MarginH & "," & MarginF & ","
If ActiveChart Is Nothing Then sPgSetup = sPgSetup & PrtNotes & ","
sPgSetup = sPgSetup & PrtDraft

XLM_PageSetup = Application.ExecuteExcel4Macro("PAGE.SETUP(" &
sPgSetup & ")")
' True if successful
End Sub
''==========================================================================

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[John]

Jon,

Thanks for the post. That did the trick. I suspected that it was trying to
access the printer but couldn't figure out a method to prevent it.

I used your code "as is" and included credit. It changed the settings that
I wanted. However, I did not test all the properties.

Thanks,
John