Setting column width on different computers

[Gleam]

I have a program which checks workbooks and corrects those errors which it
can correct. One check is on the column widths: Column A should be 1.43
characters wide and column G should be 8.86 characters wide. This works fine
on 99% of the computers where it is used but we have one or two where if you
change the width of column A to 1.43 it goes to 1.44, and for column G if you
set it to 8.86 it goes to 8.89.

This means that the next time the check is run, the workbook is deemed a
failure.

The difference appears to be the screen resolution: on the computers where
it works the resolution is 96x96 dpi and on one of the duff ones it is 120 x
120.

If I set the column to the maximum 255 characters, the width in points is
1790 on the OK computers and 1841.6 on the duff one.

Is there a way to calculate what Excel will set the column width to for a
given screen resolution? Then I could measure the resolution and check if the
column width is OK.

Can one change the resolution? If so, will this be for all programs or just
for Excel? (or for this spreadhseet?)

 

[Dennis Tucker]

It seems to me that the resolution of the column widths are to fine. You
should limit your column widths to only one decimal point precision. So
instead of 1.43 use 1.4. Or instead of 8.86 use 8.8.

 

[Gleam]

Thank you for the suggestion. I have done a small test and I think your idea
will work for column widths with a whole number of characters, but not for
any with decimal places.
In all the following results the set width was 4.4 characters (Arial size 10
as normal font) Two PCs were used with screen resolutions of 96 and 120 dpi.
Excel 2003.
Set by 96, read by 96 w=4.43
Set by 96, read by 120 w=4.33
set by 120 read by 120 w=4.44
set by 120 read by 96 w =4.57
Range 4.33 to 4.57. If the widths were read to one decimal place the range
would be 4.3 to 4.6.

I have set the width to a value where the read value is the same as the set
value in 96 resolution. (4.43 in the above example) If I then apply a
tolerance of 0.15 on the read column width I am OK on both 96 and 120 PCs.
(i.e. if the read width is within plus minus 0.15 characters of the required
value it is acceptable.)

I have a similar problem with the row height. A tolerance of 0.05 works for
this.

Incidentally I selected the "Notify me of replies" box but never received a
notification.

 

[Gord Dibben]

If you want really fine tuning

You can use VBA to set height and width in mm

Ole Erlandson has code for setting row and column dimensions.

http://www.erlandsendata.no/english/index.php?d=envbawssetrowcol


Gord Dibben Excel MVP

 

[Gord Dibben]

Note: dimensions in mm would not be resolution-related so would be same on
all computers.


Gord