Using XLM macro’s

[David Looney]

I’m using ExecuteExcel4Macro PAGE.SETUP and I’m trying to use the SCALE argument to set the page to fit 1 page wide by any length long. Help says to pass an array which I can’t figure out how to do.

Help says set SCALE to TRUE to fit 1 page by 1 page which works fine. Using a number i.e. 50 sets scale to 50% which works fine. How do I pass an array in XLM? Below is what help says.:

HELP FILE
For worksheets and macros, you can specify the number of pages that the printout should be scaled to fit. Set scale to a two-item horizontal array, with the first item equal to the width and the second item equal to the height. If no constraint is necessary in one direction, you can set the corresponding value to #N/A.

Scale can also be a logical value. To fit the print area on a single page, set scale to TRUE.

 

[Kenneth Hobson]

Argument13 is what you would set in the dialog method.

'head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr, orient, paper_size, scale,
'pg_num , pg_order, bw_cells, quality, head_margin, foot_margin, Notes, Draft
Sub Test1()
  Application.SendKeys "{ENTER}", False
  Application.Dialogs(xlDialogPageSetup).Show _
        Arg1:="&""Arial,Bold""&18SampleTable�@", _
        Arg3:=0.590551181102362, _
        Arg4:=0.590551181102362, _
        Arg5:=0.866141732283465, _
        Arg6:=0.275590551181102, _
        Arg9:=True, _
        arg10:=True, _
        Arg11:=2, _
        Arg12:=8, _
        Arg13:=Array(50, 10), _
        Arg18:=0.590551181102362, _
        Arg19:=0.511811023622047
End Sub

 

[Kenneth Hobson]

This looks bad, replying to myself...

Anywho, use {}'s to define an array for the parameter 13, scale.

Sub Macro1_Version4()
    Dim St As String
    St = "PAGE.SETUP(, , " & _
                     "1.5, 1.5, 1.5, 1.5" & _
                     ", 0, False, False, False, 1, 1, True, 1, 1,False, , " & _
                     "1, 1" & _
                     ", False)"
'or using scale other than 1 to 1 as the True above.
    St = "PAGE.SETUP(, , " & _
                 "1.5, 1.5, 1.5, 1.5" & _
                 ", 0, False, False, False, 1, 1, " & _
                 "{5,1}, 1, 1,False, , " & _
                 "1, 1" & _
                 ", False)"
    Application.ExecuteExcel4Macro St
End Sub

 

[David Looney]

Are you crazy? Please reply to yourself as often as needed, you just SOLVED the problem, many, many thanks. I had just decided that passing an array must be done differently in the old xlm days so I did a search and found this note:

Arrays s/b passed as a string like ‘{1,2,3}’

I was just beginning to play with that when I seen you had posted the answer!

Thanks!

 

[Kenneth Hobson]

Since we are just passing a string, one can do this if one wants to dim the array and set the values first.

Sub Macro1_Version4()
    Dim St As String, a As Variant
    a = Array(5, 3)
    St = "PAGE.SETUP(, , " & _
                     "1.5, 1.5, 1.5, 1.5" & _
                     ", 0, False, False, False, 1, 1, " & _
                     "{" & Join(a, ",") & "}" & ",1, 1,False, , " & _
                     "1, 1" & _
                     ", False)"
    Application.ExecuteExcel4Macro St
End Sub