This post is a corrollary to the Flag post. In that code, there is a For Next loop
|
1 2 3 4 5 6 7 |
For i = 1 To 3 .ColumnWidth = ((dFlagHoist * dUNIONFLY) / .Width) * .ColumnWidth With .Offset(0, 1) .ColumnWidth = ((dFlagHoist * (dFLAGFLY - dUNIONFLY)) / .Width) _ * .ColumnWidth End With Next i |
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
|
1 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Sub testwidth() Dim i As Long, j As Long With ActiveSheet.Range("a1") 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 Next j Next i End With End Sub |
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.
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
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
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)
With the Normal style set to Calibri 11pts, the – quite insane – formula to get the converting ratio (CharUnits per Point) at the vicinity of a given CharUnit is:
Ratio(CharUnit) = Min(9.00, 5.25 + ( 3.75 / CharUnit)).
It means the ratio remains the same for CharUnit = zero to 1, and then evolves following an f(x) = 1/x fashion to converge towards ratio = 5.25 for large values of CharUnit. For CharUnit values over 50, you are less than 2% away from 5.25.
This explains why for some values you only needed 2 loops instead of 3 to be close to the final ratio value.
The generic formula is: Ratio(CharUnit) = Min(MAX, A + ( B / CharUnit)).
You can calculate the parameters as follows:
– Measure the ratio for CharUnit=1. This is the value for MAX.
– Measure the ratio for a very large CharUnit value (>200?). This value is A.
– Then B = MAX – A.
Note it looks like Excel rounds the values of these parameters to the nearest 0.25. So to calculate A, I would always round down to the closest .25.
For example, with Calibri 11, Ratio(200.00) = 1053.75/200=5.27 -> rounded down to 5.25.
Then Ratio(1.00) = 9.00 = MAX, so B = 9.00 – 5.25 = 3.75.
Once you have your parameters, you can reverse the formula to get a CharUnit value for a given Width in points: do WidthInPts = ChartUnit * Min(MAX, A + ( B / CharUnit)).
Took me a whole afternoon to break the mysteries of the Range.Width getter!
My previous comment was sent too early.
Once you have your parameters, you can invert the formula to get a CharUnit value for a given Width in points:
ChartUnit = If(WidthInPts <= MAX, WidthInPts/MAX, (WidthInPts – B)/A)
Note Excel only keeps 2 digits for CharUnit values.
Really nice post. Thank You for that, it helped me a lot. I got one question – is that a some kind of joke or that zero width unit got a secret purpose? I’m just curious.
I have no idea why they chose the width of a zero as some standard measurement. They were probably trying to be user-friendly thinking that how many digits you would show would be a more intuitive measurement than points or some other computer-y thing that most people didn’t understand in the 1980s.
I believe the decision to lock the column sizes onto character width rather than points/pixels was taken so that, when you change the normal style (font and/or size), a value which fitted well within a cell would still fit well because the width would have been adjusted automatically.
I also believe the reason why the width in pixels is a read-only field is to indicate that this property is a result of the width in char units, no the other way around. For example, with a scenario where X = Y + Z, what happens if you change X? do you have Y changing or Z? At least here things are clear.
WHat really troubles me, is the kind of non-linear equation they rely upon to convert char widths to points. They could have also provided a built-in conversion function.
Good one @Ama.