View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Brian Brian is offline
external usenet poster
 
Posts: 8
Default Margins and cell widths

On 06/02/2016 18:17, GS wrote:
I would be grateful for any advice on how to set margins and cell
widths in Excel so that when the document is printed the printed
document reflects, accurately, the settings made.

I have tried using VBA and also the ribbon facilities, but in both
cases my settings do not accord with a subsequent print except to the
extent that the larger the setting the larger the width.

Kind regards

Brian


Some suggestions...

Setting a zero margin will always result a default 0.25 margin;
This is built into your printer so not much you can do about this
unless you can send commands to it to do otherwise.

Set header/footer margins to ALWAYS be less than top/bottom margins;

Do all settings in PrintPreview so Excel displays page boundaries after
closing that window. Now you can...

Adjust column widths to suit your desired layout for the displayed page
boundaries.

Note that setting Print_Area will affect page boundaries, so you need to
establish what range of columns you want to fit on a printed page. I
always set width of the 1st col to the right to 1 or 2 so I know where
the right limit is if the page boundaries from PrintPreview haven't been
set yet; -usually after opening the file, and before previewing the 1st
time during a session.

When designing forms, note that FontSize affects RowHeight unless you
manually specify row height for specific rows. My default font is Arial
8pt just because I like that font, and that size gives me 30 columns of
screen real estate at the default col width. This, of course, varies
with display size; the above is for my 17" laptop w/1920x1200
resolution. This 13" laptop only gives me 24 cols at 1366x768
resolution. (Zoom=100%)

I do a lot of authoring in Excel and following these guidelines has
always given me predictable, reliable printing results.
HTH



Thanks Garry for your comments. Very helpful.

I have done some more experiments and my results are as follows:-

If the margin is set at Mmm (using the ribbon) then the actual margin is
(M - 3)mm.

If the column width is set at Wmm, then the actual column width
is (1.8 x W)mm.

The column width can be set either using the ribbon or using
"Worksheets("Trial").Columns("A").ColumnWidth" in VBA. Each method
produces the same results.

If printing is done showing the column of row numbers, then the data
columns are, in effect, shifted to the right a little bit (about 5 or
6mm using a 10pt font).

The margin setting corresponds to what you said about margins being
reduced by 0.25, but the column width setting is a mystery.

The net result is that I can now set margins and columns widths using
VBA fairly accurately.

Thanks for your help.

Regards

Brian