Page 200 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

'Calculate row number
strRow = Format(intDayOfWeek + 41, "00")
'Set title column
With Range("C" & strRow)
.Formula = rgeDay(intDayOfWeek, 0)
.Interior.ColorIndex = 36
.Font.Italic = True
End With
'Loop through each time period
For intHours = 1 To 12
sglTotal = 0
For intWeek = 1 To intWeeks
sglTotal = sglTotal + rgeMonth(intWeek, intHours)
Next intWeek
Chapter 8
'Set average and format
With Range(Chr$(Asc("C") + intHours) & strRow)
.Formula = sglTotal / intWeeks
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00)"
End With
Next intHours
'Add average total for weekday
With rgeMonth(1, 1).Offset(36, 13)
.Formula = "=Sum(R[0]C[-13]:R[0]C[-2])"
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00)"
End With
Next intDayOfWeek
End Sub

Inside Out

Measuring the Dimensions of a Cell
Change the dimensions of a cell, either by adjusting the width or the height, by using the
Format, Column, Width or the Format, Row, Height commands. Both commands display a
dialog box that shows the current value and lets you enter a new value. What neither box
explains is the unit of measurement being used to determine the size.
For row height, it’s fairly simple. By default all rows are set to AutoFit. This setting means
the row will increase in size to match either the largest-size font that’s entered on that row
or all the lines of a multi-line entry. Row height is measured in points (1 point = 1/72
inches), the same as font size. The AutoFit option will add a couple of points as padding to
help ensure the entire text is visible.

Because there is no standard method of measuring width, Microsoft had to devise its own
method. The width of a cell within Excel is done by calculating the average number of digits
0 through 9 that will fit in the cell using the standard font (usually 10 point Arial). Unfortu­
nately, it is not an exact measurement. The number of digits that are visible will vary
depending upon the Zoom level of the worksheet. Figure 8-4 shows a worksheet with the

174