Columnwidth change not correct

[Mike]

Excel 2003

When I run the program below, the debug shows: 50.58 and 50.57

I then physically measure the 6 columns and get 9.5 centimeters

and then measure column 10 and get 8.5 centimeters.

Does anybody know what's going on here?

-------------------------------------------------------------------------

sub doit

Dim widthOfSixColumns As Single

Dim columnTenWidth As Single

widthOfSixColumns = Sheets("sheet1").Columns(1).ColumnWidth * 6

Sheets("sheet1").Columns(10).ColumnWidth =
Sheets("sheet1").Columns(1).ColumnWidth * 6

columnTenWidth = Sheets("sheet1").Columns(10).ColumnWidth


end sub



Thanks

 

[Guest]

The following is copied from Help in xl2007. It might help explain the unit
of measurement which is used.

On a worksheet, you can specify a column width of 0 (zero) to 255. This
value represents the number of characters that can be displayed in a cell
that is formatted with the standard font (standard font: The default text
font for worksheets. The standard font determines the default font for the
Normal cell style.). The default column width is 8.43 characters. If the
column width is set to 0, the column is hidden.

Regards,

OssieMac

 

[Guest]

I have had another look at your post. I'm not sure now that I was on the
same wave length as yourself. What exactly are you trying to do? Your code
simply sets the width of column 10 to 6 times wider than column 1. What is it
about measuring 6 columns? The marginal difference in the 50.58 and 50.57 is
because it is not always possible to set the column width the exact
measurement. This even occurs when you manually set column widths; it simply
goes to closest allowable.

Regards,

OssieMac

 

[Guest]

Also you should realise that there is an allowance for more space before
leading characters and after last characters in each column than there is
between characters. Because the column widths are measured in the number of
character that will FIT in the column, a wider column does not have this
additional space multiple times.

Regards,

OssieMac

 

[Mike]

My point is: it says that they are equal 50.58=50.57 but they are N O T
9.5 cm <> 8.5 cm.

 

[Rick Rothstein \(MVP - VB\)]

When I run the program below, the debug shows: 50.58 and 50.57

My point is: it says that they are equal 50.58=50.57 but they
are N O T 9.5 cm <> 8.5 cm.

I see what OssieMac is saying. After you run his subroutine, column J (which
is number 10) will (supposedly) be set to six times the width of a
non-expanded column (say, column A, B, C, etc.). Take a ruler and physically
measure the width of column J after running his subroutine and then measure
the combined widths of columns A through F (that is, six contiguous,
non-expanded columns)... the two measurements won't be equal, they will be
noticeably different. I don't get as much variation as OssieMac does (may
have something to do with fonts; I have my sheet set to a font size of 12
instead of the default of 10), but I do get a measureable difference (4.8
inches for columns A through F and only 4.5 inches for column J). I am
guessing OssieMac's question is why don't the six contiguous column
physically measure the same as the one column that is six times as wide as
any single column; especially when Excel reports their width to be within
0.01 character units?

Rick