There are more mathematical, financial, and statistical functions than could possibly be covered in one short section. For the most part, these functions use operands and operators to evaluate numeric data. This section doesn't attempt to completely cover each, but rather provides a succinct review of those most often used. #### The `Abs` Function The `Abs` function returns the absolute value of a numeric value. This function uses the form
Abs(number) where `number` is any valid numeric expression or a literal numeric value. The returned value is the same as `number`, but without the sign. For instance, the function
Abs(intValue) where `intValue` is an `Integer` variable that stores the value 3, returns the `Integer` value of 3. #### The `Int` Function The `Int` function returns the integer portion of a numeric value. This function takes the form
Int(number) where `number` is required and can be any valid `Numeric` data type. The `Int` function does no rounding; it simply removes the fractional portion of `number`, if one exists. For instance, the function
Int(10.9) returns 10, not 11. If `number` is negative, `Int` returns the first negative integer less than or equal to `number`. For instance, the function
Int(-10.9) returns 11. When converting values, consider using `CInt` instead of `Int`, but keep in mind that the two functions aren't entirely interchangeable. The `Int` function won't convert `number`'s data type. Although `CInt` is often the better choice, it won't return the same results. Consider the task and use the most appropriate function. #### The `Rnd` Function Use the `Rnd` function to return a random number. This function's optional argument is any valid numeric expression. The function takes the form
Rnd[(seed)] where `seed` determines the range of the returned random number as follows: When `seed` is less than 0, `Rnd` returns the same random number. When `seed` is greater than 0, `Rnd` returns the next number in the sequence of random values as determined internally. When `seed` equals 0, `Rnd` returns the most recently generated random number. When `seed` is omitted, `Rnd` returns the next number in the sequence of random values, as determined internally. TIP Sometimes you must work with values that don't generate random values as required. For instance, suppose you're working with values that are less than 0, but you don't want to generate the same value over and over. When this is the case, use the `Randomize` statement to reset the internal `seed` value so the `Rnd` function can return seemingly unique random values that don't repeat. ##### A Mathematical Function Example The first example used the `CDate` function to convert a `Variant` data type to a valid `Date` data type. You might also remember that the result wasn't a whole number. You can display a whole number by adding an `Int` function to the equation. To do so: Return to the `Chapter5` module and find `GetDate()`.
Comment out the `MsgBox` statement by selecting that statement and clicking the Comment Block tool on the Edit toolbar. Or, simply insert an apostrophe character (`'`) at the beginning of the statement.
Enter the following new code
MsgBox Int(Now - varDate)
With the insertion point in the `Sub` procedure, press F5.
Enter 3/1/2004 in the input box and click OK. This time, the message box displays a whole number, as shown in Figure 5.9. Click OK to clear the message box.
##### Figure 5.9. Adding the `Int` function improves your message's display.
#### The `Ddb` Function The `Ddb` function returns a `Double` data type that represents the depreciation of an asset for a specific time period using the predefined double-declining balance method. This function takes the form
Ddb(cost, salvage, life, period[, factor]) where `cost` is a `Double` value or expression that represents the asset's initial cost, and `salvage` is a `Double` value that specifies the asset's value at the end of `life`, which is a `Double` value that expresses the length of the asset's life. Likewise, `period` is a `Double` that expresses the period for which the depreciation is to be calculated. The optional `factor` argument is a `Variant` that determines the rate at which the asset balance declines. When omitted, the double-declining method is assumed (although documentation doesn't seem to support any other values). #### The `FV` Function When you need to calculate the future value of an annuity, use the `Fv` function. This function returns a `Double` value and uses the form
Fv(rate, nper, pmt[, pv [, type]]) where `rate` is a `Double` value that specifies the interest rate per period, `nper` is an Integer value that specifies the number of payment periods in the annuity, and `pmt` is a `Double` value that specifies the payment made each period. The two optional arguments, `pv` and `type`, are `Variant` data types that represent the present value and whether payments are due at the start or the end of the period, respectively. #### The `IPmt` Function Calculate the interest payment for a given period of an annuity using the `IPmt` function. This function returns a `Double` and takes the form
IPmt(rate, per, nper, pv[, fv[, type]]) where `rate` is a `Double` that states the period's interest rate, `per` is any numeric expression that specifies the period for which the payment is to be calculated, `nper` is a `Double` that represents the total number of payments, and `pv` is any valid numeric expression that specifies the present value. The two optional arguments, `fv` and `type`, are `Variant` data types that indicate the future value or cash balance after the final payment and whether the payments are due at the start or the end of the period. #### The `NPer` Function The `Nper` function calculates the number of periods for an annuity as a `Double`. Use the form
NPer(rate, pmt, pv[, fv [, type]]) where `rate` is a `Double` that represents the interest rate per period, `pmt` is a `Double` that specifies the payment made each period, and `pv` is a `Double` that identifies the present value. The two optional arguments, `fv` and `type`, are `Variant` data types that identify the future value of the series of payments and whether the payments are due at the start or the end of the period. #### The `Pmt` Function Use the `Pmt` function to calculate the payment for an annuity as a `Double`. This function takes the form
Pmt(rate, nper, pv[, fv[, type]]) where `rate` is a `Double` that represents the interest rate per period, `nper` is an Integer that specifies the total number of payments, and `pv` is a `Double` that identifies the present value. The two optional arguments, `fv` and `type`, are `Variant` data types that identify the future value of the series of payments and whether the payments are due at the start or the end of the period. #### The `PPmt` Function Use the `PPmt` function to calculate the principal payment for an annuity as a `Double`. This function takes the form
PPmt(rate, per, nper, pv[, fv[, type]]) where `rate` is a `Double` that represents the interest rate per period, `per` is a `Double` that represents the period for which a payment is to be calculated, `nper` is a `Double` that specifies the total number of payments, and `pv` is a `Double` that identifies the present value. The two optional arguments, `fv` and `type`, are `Variant` data types that identify the future value and whether payments are due at the start or the end of the period. #### The `Rate` Function The `Rate` function calculates the interest rate per period for a loan or annuity. This function returns a `Double` and requires the form
Rate(nper, pmt, pv[, fv[, type[, guess]]]) where `nper` is a `Double` that specifies the total number of periods, `pmt` is a `Double` that specifies the payment amount per period, and `pv` is a `Double` that identifies the present value. The three optional arguments, `fv`, `type`, and `guess`, specify the future value, determine whether the payments are due at the start or the end of the period, and present an estimate of the function's resulting value. The first two optional arguments, `fv` and `type` are `Variants`; `guess` is a `Double`. #### The `Syd` Function The `Syd` function calculates the sum-of-years' digits depreciation of an asset for a specific period. This function returns a `Double` and uses the form
Syd(cost, salvage, life, period) where `cost` is a `Double` that specifies the asset's initial cost, `salvage` is a `Double` that indicates the asset's value at the end of its useful life, `life` is a `Double` that identifies the length of the asset's useful life, and `period` is a `Double` that represents the period for which the depreciation is being calculated. ##### A Financial Function Example Access users often forget that the financial functions even exist and reach for Excel when they need to analyze some figures. Depending on your needs, Excel might very well be the best tool for the job, but Access uses the same functions it just presents the data differently. Let's suppose you want to take out a small business loan to purchase new computer equipment so you can expand the business. A flexible function procedure can calculate your monthly payments on any loan. You plug in the numbers and VBA does the work. To create this function, return to the VBE and the `Chapter5` module (or open a new module if you like). Then, complete the following steps: Enter the following procedure
Public Function CalPayment(rate As Double, _ nper As Integer, pv As Double) As Currency CalPayment = Int(Pmt(rate / 12, nper, pv)) End Function NOTE The code in the accompanying example includes an underscore character (_) at the end of the first line. That's what is known as the line continuation character. Use this character at the end of a line when you want to continue a line of code to the next line. Including this character lets VBA know that the current line and the next are part of the same statement and should be evaluated as such. In the Immediate window, enter the following statement and press Enter:
?CalPayment(.07, 48, 10000)
Figure 5.10 shows the approximate monthly payment (without fees) of $240. Are you curious how VBA knew to display the amount as a currency? Look at the function's first line
Public Function CalPayment(rate As Double, _ nper As Integer, pv As Double) As Currency ##### Figure 5.10. A quick procedure can calculate a monthly payment. The `As Currency` phrase returns the function's value as a `Currency` data type. The `Int` function forces a whole number. A quick function procedure can help you determine whether to go loan shopping. Simply pass different values to the function to change the payment amount. Perhaps you think you can get a better interest rate and consequently, borrow more money. If this were a task you were going to incorporate into your database, you'd create a form to gather all those values, but for now, the VBE does the trick without any special interface objects. |