Using XLM macro’s

D

David Looney

Board Regular
Joined
Aug 12, 2006
Messages
60
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
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
 
Last edited:
Upvote 0
Note, if this is not working:
a = Array("50","10")

Try:
a = Array(50,10)
 
Upvote 0
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)"
 
Upvote 0
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:
Code: 
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
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Macro to Autofit Cols Page Preview
Replies
6
Views
113
Logit
Logit
A
  • Question Question
VBA for setting up the printing pages per sheet
Replies
0
Views
582
Aurum1
A
M
  • Question Question
Scaling a “Check Point” x “% used” data table to fit a smaller range.
Replies
0
Views
148
merman777
M
S
  • Question Question
margin settings are off when using other computer
Replies
2
Views
418
smking204
S
R
  • Question Question
Functions with XLM macros are blocked
Replies
1
Views
936
MARK858
MARK858

Forum statistics

Threads
1,224,564
Messages
6,179,543
Members
452,924
Latest member
JackiG

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top