Page 234 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

sngResult = Application.WorksheetFunction.CountIf(.Cells, strCriteria)
MsgBox ("The number of cells that meet the criteria is " & sngResult & ".")
Case "Average"
sngResult = Application.WorksheetFunction.Average(.Cells)
MsgBox ("The average of the values is " & sngResult & ".")
Case "Mode"
sngResult = Application.WorksheetFunction.Mode(.Cells)
MsgBox ("The mode of the values is " & sngResult & ".")
Case "StDev"
sngResult = Application.WorksheetFunction.StDev(.Cells)
MsgBox ("The standard deviation of the values is " & _
sngResult & ".")
Case Else
MsgBox ("Unrecognized operation; please try again.")
End Select
End With
End Sub

Note You probably noticed how much work the With ActiveCell.CurrentRegion.
Columns(intColNumber) statement saved in the Summarize procedure. If it weren’t for
the With statement, the range reference code would have wrapped around every line, making
the code extremely hard to read. Always look for such opportunities to simplify your code.
Chapter 9
Performing Financial Calculations

One of the most common financial calculations you’ll be asked to perform is to determine
the monthly payment on a loan. For that calculation you use the PMT function, which has
the syntax:

PMT(rate, nper, pv, fv, type)

Table 9-3 describes the five arguments used with the PMT function.
Table 9-3. An In-Depth Look at the PMT Function’s Arguments
Argument Description
rate The interest rate, to be divided by 12 for a loan with monthly payments
nper The total number of payments for the loan
pv The amount loaned (pv is short for present value, or principal)
fv The amount to be left over at the end of the payment cycle (usually left
blank, which indicates 0)
type 0 or 1, indicating whether payments are made at the beginning or at the
end of the month (usually left blank, which indicates 0, or the end of
the month)

208
Part 4: Advanced VBA