Thread: Odd behavior on margin change

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

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 wrote:

> 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,

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

"Jon Peltier" wrote:

> 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 wrote:
>
> > 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
>