Width Height Top and Left Properties

These properties return values for the entire range, in points. For instance, the Top property returns the distance, in points, from the top of row 1 to the top of the first (leftmost) area of the range.

Note that when applied to a column, the Width property returns the width, in points, of the column. However, the relationship between Width and ColumnWidth can seem a bit strange at first.

For instance, the following code shows that a column of ColumnWidth 1 has Width 9.6 but a column of ColumnWidth 2 has Width 15. (In my case, the Normal style is 10 point Arial.) However, if the ColumnWidth property really measures the width of a column in units and the Width property really measures the width of the same column in points, then doubling one of these properties should double the other!

Columns("A").ColumnWidth = 1

MsgBox Columns("A").Width ' Displays 9.6

Columns("A").ColumnWidth = 2

MsgBox Columns("A").Width ' Displays 15

Columns("A").ColumnWidth = 10

MsgBox Columns("A").Width ' Displays 58.2

Fortunately, a little high-school algebra reveals the truth here. It appears that the Width property includes padding on the far right and the far left of the entire group of characters (next to the column boundaries). To support this conclusion, let's do a little algebra, which you can skip if it upsets you.

Assume for a moment that the Width property includes not just the sum of the widths of the ColumnWidth characters, but also an additional p points of padding on each side of the entire group of characters. Thus, the formula for Width is:

Width = 2*p + ColumnWidth*w where w is the true width of a single "0" character, in points. Thus, plugging in the values from the first two examples in the previous code gives:

Subtracting the first equation from the second gives:

Substituting this into the first equation and solving for p gives:

Thus, the formula for a Normal style of 10 point Arial is:

Now, for a ColumnWidth of 10, this gives:

Eureka! (Check the third example in the previous code.)

Thus, we have verified (but not really proved) that the Width property measures not just the width of each character but includes some padding on the sides of the column—in this case 2.1 points of padding on each side.

0 0


Post a comment