Word Document Properties

Chapter 4: Storing and Modifying Information
09_046500 ch04.qxp 12/5/06 5:54 PM Page 99

---

value for the next part of the 
Output
string construction, so the 
Sgn
function
returns 
-1
. Finally, the code uses the 
Abs
function to remove the sign from
MyInt
and calls on 
Sgn
once again to show that the value is indeed positive.
The Math Functions help topic contains more information on the math func-
tions that VBA supports. The Derived Math Functions help topic contains
additional examples of how to combine the math functions to produce spe-
cific results.
Using currency values for 
money calculations
Money usually requires special handling on a computer because you don’t
want to introduce rounding or other errors. Even small incremental errors
can result in large errors if they accumulate over time. The 
Currency
data
type provides special handling for money calculations but at a slight perfor-
mance hit because the 
Currency
data type requires additional memory and
processing cycles.
Along with the 
Currency
data type, VBA provides a number of special func-
tions for calculating common monetary values. These are the same special
functions available to you in your Excel spreadsheet. For example, you still
have access to the 
Pmt
function. The main concern when working with 
monetary values in a VBA program is to ensure that you use the 
Currency
data type as needed. Listing 4-12 shows how the 
Currency
data type and the
Pmt
function work together.
Listing 4-12
Working with Monetary Values
Public Sub ShowPayment()
‘ Create the required variables. All non-monetary
‘ values use the Double type to ensure accuracy. The
‘ monetary values use the Currency data type.
Dim Rate As Double
Dim Periods As Double
Dim PresentValue As Currency
Dim FutureValue As Currency
‘Calculate the monthly payment on a mortgage.
Rate = 0.005 ‘ 6 Percent divided by 12
Months
Periods = 60 ‘ 5 years
PresentValue = 120000 ‘ $120,000.00 loan
MsgBox CStr(Pmt(Rate, Periods, PresentValue)), _
100