Working with Numeric Expressions
Numeric expressions are what normally think of when use the generic term "expression." Whether it's calculating gross margin, figuring out commissions, or determining the monthly payment on a loan, many expressions perform some kind of number crunching. You saw VBA's arithmetic operators earlier in this tutorial. This section adds to that by giving you a quick look at VBA's built-in math and financial functions.
VBA's Math Functions
The operands you use in your numeric expressions are usually numeric literals or variables declared as one of VBA's numeric data types. However, VBA also boasts quite a few built-in math functions that your expressions can use as operands. These functions are outlined in Table below.
VBA's Math Functions| Function | What It Returns |
| Abs(number) | The absolute value of number. |
| Atn(number) | The arctangent of number. |
| Cos(number) | The cosine of number. |
| Exp(number) | e (the base of the natural logarithm) raised to the power of number. |
| Fix(number) | The integer portion of number. If number is negative, Fix returns the first negative integer greater than (that is, closer to 0) or equal to number. |
| Hex(number) | The hexadecimal value, as a Variant, of number. |
| Hex$(number) | The hexadecimal value, as a String, of number. |
| Int(number) | The integer portion of number. If number is negative, Int returns the first negative integer less than (that is, further from 0) or equal to number. |
| Log(number) | The natural logarithm of number. |
| Oct(number) | The octal value, as a Variant, of number. |
| oct$(number) | The octal value, as a String, of number. |
| Sgn(number) | The sign of number. |
| Sin(number) | The sine of number. |
| Sqr(number) | The square root of number. |
| Tan(number) | The tangent of number. |
| Rnd(number) | A random number between 0 and 1, as a Single. You use the optional number as a "seed" value, as follows: |
| number | What It Generates |
| Less than 0 | The same number every time (varies withnumber). |
| Equal to 0 | The most recently generated number. |
| Greater than 0 | The next random number in the sequence. |
Note: The random numbers generated by Rnd are only pseudo-random. In other words, if you use the same seed value, you get the same sequence of numbers. If you need truly random numbers, run the Randomize statement just before using Rnd.This initializes the random number generator with the current system time. Here's an example:
Randomize myRandomNumber = Rnd()
The need for random numbers comes up quite a bit in programming. However, instead of random numbers between 0 and 1, you might need to generate numbers within a larger range. Here's the general formula to use to get Rnd to generate a random number between a lower bound and an upper bound:
Int((upper - lower) * Rnd + lower)
For example, here's some code that generates a random 8-digit integer:
Randomize fileName = Int((99999999 - 10000000) * Rnd + 10000000)
VBA's Financial Functions
VBA has quite a few financial functions that offer you powerful tools for building applications that manage both business and personal finances. You can use these functions to calculate such things as the monthly payment for a loan, the future value of an annuity, or the yearly depreciation of an asset.
Although VBA has a baker's dozen financial functions that use many different arguments, the following list covers the arguments you'll use most frequently:
- rate-The fixed rate of interest over the term of the loan or investment.
- nper-The number of payments or deposit periods over the term of the loan or investment.
- pmt-The periodic payment or deposit.
- pv-The present value of the loan (the principal) or the initial deposit in an investment.
- fv-The future value of the loan or investment.
- type-The type of payment or deposit. Use 0 (the default) for end-of-period payments or deposits and 1 for beginning-of-period payments or deposits.
For most financial functions, the following rules apply:
- The underlying unit of both the interest rate and the period must be the same. For example, if the rate is the annual interest rate, you must express nper in years. Similarly, if you have a monthly interest rate, you must express nper in months.
- You enter money you receive as a positive quantity, and you enter money you pay as a negative quantity. For example, you always enter the loan principal as a positive number because it's money you receive from the bank.
- The nper argument should always be a positive integer quantity.
Table show lists all VBA's financial functions.
The Built-In Financial Functions in VBA| Function | What It Returns |
| DDB(cost,salvage,life,period,factor) | The depreciation of an asset over a specified period, using the double-declining balance method. |
| FV(rate,nper,pmt,pv,type) | The future value of an investment or loan. |
| IPmt(rate,per,nper,pv,fv,type) | The interest payment for a specified period of a loan |
| IRR(values,guess) | The internal rate of return for a series of cash flows. |
| MIRR(values,finance_rate,reinvest_rate) | The modified internal rate of return for a series of periodic cash flows. |
| NPer(rate,pmt,pv,fv,type) | The number of periods for an investment or loan. |
| NPV(rate,value1,value2...) | The net present value of an investment based on a series of cash flows and a discount rate. |
| Pmt(rate,nper,pv,fv,type) | The periodic payment for a loan or investment. |
| PPmt(rate,per,nper,pv,fv,type) | The principal payment for a specified period of a loan. |
| PV(rate,nper,pmt,fv,type) | The present value of an investment. |
| Rate(nper,pmt,pv,fv,type,guess) | The periodic interest rate for a loan or investment. |
| SLN(cost,salvage,life) | The straight-line depreciation of an asset over one period. |
| SYD(cost,salvage,life,period) | Sum-of-years' digits depreciation of an asset over a specified period. |
