Working with column and page widths

[Craig.Leat]

Hi,

My A4 paper in portrait orientation has a width of 595 points. I have
manipulated the width of seven columns manually to have the largest
width that will just fit on the page. I then expect the sum of
LeftMargin, RightMargin and Columns("A:G").Width to equal my page
width, but it does not. My sum yields 570.45 not 595 points. Any ideas
on where the remaining 24.55 points have disappeared to?

Related to the above is another question: Do cell borders occupy
space?

And another question that has just come to mind: What effect might
font substitution on the printer side have on all this?

Many thanks

Craig

 

[Peter T]

Hi Craig,

The printable width is almost always less than the physical paper width. It
is possible with API's to get dimensions of all the non-printable margins,
or indeed with your own Excel/vba calculation returns the width (though not
margins). But unless you're desperate to always have that extra tad just
assume the 'printable width' is about 10mm or 30 points narrower than the
paper, .

Regards,
Peter T

 

[Craig.Leat]

Hi Peter

Thanks for the comments. I have always believed margins were measured
from the edge of the paper, not the edge of the printable area - might
I be wrong? I wrote a little routine (see below) that sets wide left
and right margins and then grows columns A and B until an automatic
page break appears between the two columns. At this width the columns
are just too wide to fit on the same page and so I would expect the
total width of the margins and two columns to be wider than the page.
The output of the routine shows otherwise. On my system, 591 pts
(including margins) is too wide to fit on a 595 pt page.

This is important to me, not because I want tiny margins, but because
I want to use *all* the space between the margins. Any ideas on what I
am overlooking?

Craig.


Private Sub GrowThoseColumns()
Dim increment, InitWidth, TotalWidth As Single
With Worksheets(1)
Application.ScreenUpdating = False
increment = 0
InitWidth = 5
.PageSetup.PaperSize = xlPaperA4
.PageSetup.Orientation = xlPortrait
.PageSetup.LeftMargin = 295
.PageSetup.RightMargin = 200
.Columns("A").ColumnWidth = InitWidth
.Columns("B").ColumnWidth = InitWidth
Do Until .Columns(2).PageBreak = xlPageBreakAutomatic
increment = increment + 0.01
.Columns("A").ColumnWidth = InitWidth + increment
.Columns("B").ColumnWidth = InitWidth + increment
Loop
TotalWidth = .PageSetup.LeftMargin + .PageSetup.RightMargin
+ .Columns("A").Width + _
.Columns("B").Width
Application.ScreenUpdating = True
response = MsgBox("Col A width = " & .Columns("A").Width & " pts"
& Chr(13) & _
"Col B width = " & .Columns("B").Width & " pts" & Chr(13) & _
"Total margin & col width = " & TotalWidth & " pts", vbOKOnly)
End With
End Sub

 

[Craig.Leat]

I have done some more testing with different page orientations,
margins and numbers of columns and I cannot identify any pattern in
the way XL decides how much of the available width between the margins
to use. I find it interesting that if the number of columns stays the
same, I change the margins and rerun the routine then XL reports
different page widths (calculated from sum of margins and column
widths).

I am led to conclude that the width between the margins can be
accurately determined, but XL is unlikely to use the full space
available. XLs determination of how much space to use is an
unfathomable black magic. The only way to achieve columns with the
maximum width possible is by trial and error along the lines of the
routine discussed above.

Craig

 

[Peter T]

When you say "I change the margins and rerun the routine then XL reports
different page widths", what order of discrepancy do you get, a few points
or something more significant.

In your earlier post -
"I have always believed margins were measured
from the edge of the paper, not the edge of the printable area - might
I be wrong?"

No I think you are right, tested a bit more and it seems I was wrong [that
Excel calcs margins from the printable area].

Regards,
Peter T

If you mean Excel's margins I always assumed they were measured from the
edge of the printable area

I have done some more testing with different page orientations,
margins and numbers of columns and I cannot identify any pattern in
the way XL decides how much of the available width between the margins
to use. I find it interesting that if the number of columns stays the
same, I change the margins and rerun the routine then XL reports
different page widths (calculated from sum of margins and column
widths).

I am led to conclude that the width between the margins can be
accurately determined, but XL is unlikely to use the full space
available. XLs determination of how much space to use is an
unfathomable black magic. The only way to achieve columns with the
maximum width possible is by trial and error along the lines of the
routine discussed above.

Craig

<snip>

 

[Craig.Leat]

Hi Peter

When you say "I change the margins and rerun the routine then XL reports
different page widths", what order of discrepancy do you get, a few points
or something more significant.

Example 1
With A4 paper, portrait orientation, all XL margins = 0 and eleven
columns with the widths optimized to fill the page, TotalWidth (as
calculated below) returns 531 points.

Example 2
With A4 paper, portrait orientation, all XL margins = 20 pts and
eleven columns with the widths optimized to fill the page, TotalWidth
(as calculated below) returns 566.5 points.

Example 3
With A4 paper, portrait orientation, all XL margins = 80 pts and
eleven columns with the widths optimized to fill the page, TotalWidth
(as calculated below) returns 571.75 points.

Remember the TotalWidth should be 595 points.

I calculate TotalWidth as follows:
TotalWidth = 0
For n = 1 To 11
TotalWidth = TotalWidth + .Columns(n).Width
Next n
TotalWidth = TotalWidth + .PageSetup.LeftMargin
+ .PageSetup.RightMargin

In your earlier post -
"I have always believed margins were measured
from the edge of the paper, not the edge of the printable area - might
I be wrong?"

No I think you are right, tested a bit more and it seems I was wrong [that
Excel calcs margins from the printable area].

Regards,
Peter T

If you mean Excel's margins I always assumed they were measured from the
edge of the printable area

XL appears to be inconsistent. My printer has a non-printable width of
about 7mm (measured with a ruler) on the left and right edges of a
portrait page. Setting XLs margins to zero the page border prints 7mm
from the edge of the page. However, if I set the margins to 20mm the
border prints at 20mm from the edge of the page not 27mm.

I couldn't find any explanation in my local XL help system.

Regards

Craig