In the following case, the Application.InchesToPoints function was hardcoded to the inches value. The third version of Macro1 looks like this:

Sub Macro1_Version3()

With ActiveSheet.PageSetup

If .LeftMargin <> 108 Then .LeftMargin = 108 If .RightMargin <> 108 Then .RightMargin = 108 If .TopMargin <> 108 Then .TopMargin = 108 If .BottomMargin <> 108 Then .BottomMargin = 108 If .HeaderMargin <> 72 Then .HeaderMargin = 72 If .FooterMargin <> 72 Then .FooterMargin = 72 End With End Sub

You should see the difference on this one when you're not changing all the default margins.

Another option can reduce the runtime by over 95%! It uses the PAGE.SETUP XLM method. The necessary parameters are left, right, top, bot, head_margin, and foot_margin. They are measured in inches, not points. So, using the same margins that we have been changing already, a fourth version of Macro1 looks like this:

Sub Macro1_Version4() Dim St As String St = "PAGE.SETUP(, , 11 & _

", 0, False, False, False, 1, 1, True, 1, 1,False, , " & _ "1, 1" & _ ", False)" Application.ExecuteExcel4Macro St End Sub

The second and fourth lines of St correspond to these parameters. But there are some simple precautions to follow. First, this macro relies on XLM language, which is still included in Excel for backward compatibility, but we don't know when Microsoft will drop it. Second, be careful when setting the parameters of PAGE.SETUP because if one of them is wrong, the PAGE.SETUP is not executed and doesn't generate an error, which can possibly leave you with the wrong page setup.

0 0

Post a comment