< Day Day Up > |
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 FunctionThe 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 FunctionThe 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 FunctionUse 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:
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 ExampleThe 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:
The Ddb FunctionThe 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 FunctionWhen 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 FunctionCalculate 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 FunctionThe 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 FunctionUse 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 FunctionUse 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 FunctionThe 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 FunctionThe 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 ExampleAccess 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:
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. |
< Day Day Up > |