This post is a corrollary to the Flag post. In that code, there is a For Next loop
For i = 1 To 3
.ColumnWidth = ((dFlagHoist * dUNIONFLY) / .Width) * .ColumnWidth
With .Offset(0, 1)
.ColumnWidth = ((dFlagHoist * (dFLAGFLY - dUNIONFLY)) / .Width) _
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
With ActiveSheet.Range("a1") .ColumnWidth = 400 / .Width * .ColumnWidth End With
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:
Dim i As Long, j As Long
For i = 200 To 600 Step 200
.ColumnWidth = 8.43
Debug.Print "***** " & i & " *****"
For j = 1 To 3
.ColumnWidth = i / .Width * .ColumnWidth
Debug.Print j, .ColumnWidth, .Width
which produced these results
|***** 200 *****|
|***** 400 *****|
|***** 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.