ColumnWidths, Characters & Pixels

[Bart]

Dear all,

Adding Column Widths is somehow a very inaccurate matter. I tried to
measure the Width of 5 columns and make an other Column as wide as the
Widths of these 5 columns together. The column appeared to be less
wide.
It seems that the measurements aren't linear. Though I found out that
there's a relation between Characters and Pixels (Characters with the
Standard Font and Size).

m1 (Characters) | m2 (Pixels)
1 12
5 40
10 75
20 145
50 355
100 705

P = 7 * C + 5

P = number of Pixels
C = number of Characters

For example in my case I found the following measurements in
Characters (and Pixels):

2.86 (25 Pixels) +
15.86 (116 Pixels) +
15.86 (116 Pixels) +
15.86 (116 Pixels) +
12.29 (91 Pixels) +
20.14 (146 Pixels)
=82.87 Characters

The sum of these round figures is 82.87 Characters. (They are round
figures as they are when I read them as a Property of a Column.)
The sum of Pixels (not round figures) is 610.

Since the values of the ColumnWidth are round figures the calculation
isn't accurate (and so you might say non-linear). Calculating the
number of Characters with the number of Pixels by using the formula
stated above you get:

610 Pixels ≡ 86.43 Characters,

and not 82.87 Characters as stated above.

To work around this problem is by calculating in Pixels instead of
Characters. First convert the ColumnWidths to Pixels, do the
calculation and then convert it back to Characters. Like this e.g.:

Dim intPixels As Integer
Dim intTotalColumnWidth As Integer
Dim sngTotalColumnWidth As Single

For i = 1 To 3
intPixels = CInt(myRange.Columns(i).ColumnWidth * 7! + 5!)
intTotalColumnWidth = intTotalColumnWidth + intPixels
Next i

sngTotalColumnWidth = CSng(intTotalColumnWidth - 5) / 7!

myRange.Columns(1).ColumnWidth = sngTotalColumnWidth


Does someone have any other suggestions to obtain the ColumnWidth in
Pixels or calculate with ColumnWidths?

Bart

 

[Guest]

First, column widths were not designed for engineering calculations or
anything, they are rounded, you can convert them to pixels and back, but they
will still be rounded. Two things, first, why do you need to base
calculations on the width of cells? and secondly, try looking into just plain
..width, gives points and will likely be a little more accurate than
columnwidth.

 

[Guest]

Check out this link...

http://support.microsoft.com/default.aspx/kb/214123

 

[Bart]

John, Thanks for your interest.
The plain .Width property I tried seems to be a different value than
that of the Width in Pixels. And an other thing is: this .Width
property is read-only.
The fact that I make these calculations is as following. I'm abusing
Excel by trying to store great amounts of text in a Worksheet (more
than those 1024 characters that Excel can handle to show when using
'Wrap Text' and 'AutoSize'. See 'Excel specifications and limits'.) It
subsequently appeared that I should have used Access or at least Word
for what I'm trying to build.
Enfin, I build a Subroutine to place every line of text in a different
row. The text wrapping is done semi-automatic. In a cell as Wide as
the merged cells area I insert word by word. Because 'Wrap Text' and
'AutoSize' are enabled the CellHeight will grow when necessary. I
track this happening and remove the last added word and start a new
line.
In Merged Cells is where I want to put the text. On Merged Cells 'Wrap
Text' doesn't work, so I want to do the job in a single cell with the
same Width. So I have to measure the ColumnWidth of the Merged Cells.
At first when I just added the separate ColumnWidths of the Cells
which the Merged Cells covers, I noticed the Text Wrapping was done
incorrect: the line wasn't fully extended. When doing some debugging I
found out about these inaccurate measurements in Characters as state
in my previous Post.

I don't understand why Microsoft chose to use the Character unit,
since virtually everybody uses proportional Fonts. What meaning does
this unit has to us?

Bart

 

[Bart]

Thanks Jim Thomlinson, That put some extra light on the matter after
having read the VBA help files.

Bart

 

[Bart]

Sorry, I mixed up. This whole thing hasn't to do anything with round
figures (those should do). It is the offset in the .ColumnWidth that
makes all the trouble: - 5 / 7.

Bart