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.

 

[xenou]

I think you just need:

Dim a
a = Array("50","10")

This would be an array with two items, 50 and 10. You would pass it:
MethodThatNeedsAnArray(a)

Based on what the help file you quoted says, it looks like you could also use something like:
a = Array("50","#N/A")

AB

 

[xenou]

Note, if this is not working:
a = Array("50","10")

Try:
a = Array(50,10)

 

[Scott Huish]

Never used Excel4Macros myself, but maybe this like will help:

http://www.vbaexpress.com/forum/showthread.php?t=13903

 

[David Looney]

I’ve tried every which way to pass the array (including your suggestion) and nothing works.

Thanks for the link to vbaexpress but there was nothing there either.

Here are the arguments and below it a call that works. The True is the SCALE argument.

PAGE.SETUP(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)

Application.ExecuteExcel4Macro "PAGE.SETUP(, , 1.5, 1.5, 1.5, 1.5" & _
", 0, False, False, False, 1, 1, TRUE, 1, 1, False, , " & _
"1, 1, False)"

 

[Kenneth Hobson]

Typically, one would use WorkSheetFunction.Transpose to transpose an array for such things. It is more trouble than it is worth in this case I would guess.

Like yours:

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)"
    Application.ExecuteExcel4Macro St
End Sub

 

[David Looney]

The help file says to pass a two-item horizontal array for the SCALE argument so I'm not sure I follow you.

 

[iliace]

I think you'd use a variant array, and assign it to (1, "N/A") or transpose it - one way or the other should work.

 

[David Looney]

I’ve tried every combination under the sun, nothing works.

Dim a() As Variant
a = Array("1", "2")
a = Array(1, 2)
a = Application.WorksheetFunction.Transpose(a)
etc.

Application.ExecuteExcel4Macro "PAGE.SETUP(, , 1.5, 1.5, 1.5, 1.5" & _
", 0, False, False, False, 1, 1, a(), 1, 1, False, , " & _
"1, 1, true)"

I've tried passing array as a string, using option base 1 (and not) and a million other things...

 

[Kenneth Hobson]

You might try the Sendkeys method for xlDialogPageSetup.
http://www.mrexcel.com/archive2/8400/9334.htm