Column Widths In Points

This post is a corrollary to the Flag post. In that code, there is a For Next loop

This loop attempts set the column width in points. While row heights are measured in points (1/72 of an inch), column widths are measured as the number of zeros that fit in the column. The zeros are of the Normal style regardless of the font or style of the range. If your Normal style is Arial 10, then the default column width is 8.43, i.e. you can fit 8.43 zeros in the cell.

For the flag program, as well as many other applications, the column width needs to be set in points. The Width property shows the width of a range in points, but is read-only. To use the ColumnWidth property, which is read-write, and set the width in points, the obvious answer is something like this

Ideally, this should set the ColumnWidth property such that the Width property will equal 400. It doesn’t. I read a newsgroup post by Stephen Bullen that said that doing it twice gets you closer to the target width. As you get closer to the target width, the ratio of ColumnWidth to Width becomes more accurate for that width. I don’t know why the ratio changes, but it does.

Here’s the condensed version of a little test I ran:

which produced these results

***** 200 *****
1 35.14 188.25
2 37.29 199.5
3 37.43 200.25
***** 400 *****
1 70.29 372.75
2 75.43 399.75
3 75.43 399.75
***** 600 *****
105.43 557.25
2 113.57 600
3 113.57 600

Basically, what I found was that in most cases two iterations got the number within .25 of the target width. However, in some cases three times would be necessary. It seems that in all cases, the third iteration gets as close as it will ever get, although I didn’t try any widths greater than 1000. I wasn’t able to predict if the end result would be .25 less than, .25 greater than, or exactly the target width.

Thanks Vince, for the comment.

4 thoughts on “Column Widths In Points

  1. Thank you, Dick!

    I would have never figured that out on my own. I’m going to have to play around this now.

    Thanks again,

    Vince

  2. I ran into this myself again recently, and found three times was the best you’ll get. After that it either bounces around the target, or occasionally it just goes haywire. For the app I was building, it was better for me to aim a couple percent wider than the target, and live with it.

    When you adjust chart plot area dimensions, or format a chart with lots of series, sometimes you have to rerun that a few times, too.

    – Jon

  3. I may have found a way to rescale columns to user-determined widths with a called macro (note that I’m using inches instead of cms…if you want cm input, I’m sure calls to CentemetersToPoints would work):

    Sub ChangeSelectedColWidthIn()
    ‘ ChangeSelectedColWidthIn Macro
    ‘ Macro recorded 11/03/2005 by M P
    inWidth = Application.InputBox(prompt:=”Enter Width (in inches)”, Title:=”Set Width of Selected Columns”, Type:=1)
    oldWidthPts = Range(“A1?).Width
    Range(“A1?).ColumnWidth = 255
    scaleFactor = Range(“A1?).Width / 255
    Range(“A1?).ColumnWidth = oldWidthPts / scaleFactor
    Selection.Columns.ColumnWidth = Application.InchesToPoints(inWidth) / scaleFactor
    End Sub

    Note that this example will rescale the “A” column. If you don’t use the 255 scaling factor bit, you’ll get significant resizing of the A column and less accurate scaling elsewhere. I assume it’s due to round-off.

    if you have any improvements/amendments to my code, please don’t hesitate to contact me. I suppose it’d be best to play around with the ZZ column rather than A, but I don’t know if that’d mess with set print areas;)

    Papa Alpha Golf Echo Lima at Charley Sierra dot wisc dot edu

    (got lazy at the end there:P)

Leave a Reply

Your email address will not be published. Required fields are marked *