Microsoft Office Tutorials and References

In Depth Information

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.

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)