VBAs 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 4.5.

Table 4.5 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.

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.

¡^ 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()

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.

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 with number).

Equal to 0 The most recently generated number.

Greater than 0 The next random number in the sequence.

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)

0 0

Post a comment