Microsoft Office Tutorials and References
In Depth Information
19.3.14 Consolidate Method
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:
9.6 = 2*p + 1*w
15 = 2*p + 2*w
Subtracting the first equation from the second gives:
5.4 = w
Substituting this into the first equation and solving for p gives:
p = 2.1
Thus, the formula for a Normal style of 10 point Arial is:
Width = 4.2 + ColumnWidth*5.4
Now, for a ColumnWidth of 10, this gives:
Width = 4.2 + 10*5.4 = 58.2
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.
19.3.14 Consolidate Method
This method combines (or consolidates) data from multiple ranges (perhaps on multiple
worksheets) into a single range on a single worksheet. Its syntax is:
RangeObject .Consolidate( Sources , Function , _
TopRow , LeftColumn , CreateLinks )
Sources is the source of the consolidation. It must be an array of references in R1C1-style
notation. The references must include the full path of the ranges to be consolidated. (See the
following example.)
Search JabSto ::




Custom Search