Exploring VBAs Built-in Functions

2017-11-03 09:05:03

Exploring VBA s Built in Functions

Problem

You need to get familiar with VBA's built-in functions and use them in your code, but you're not sure where to begin.

Solution

Check out the Visual Basic Language Reference in the VBA help guide. There's a section in the language reference entitled "Functions," which includes an alphabetically organized list of built-in functions. For your convenience, I've summarized some of the more commonly used functions for scientific and engineering calculations in Table 2-2.

Table 2-2. Some useful VBA fuctions
Function	Comment
`Abs(number)`	Returns the absolute value of the number argument. The data type returned is the same as that of the argument.
`Atn(number)`	Returns the arctangent of the number argument. The returned data type is a `Double`. The result is in radians from -p/2 to p/2.
`Cos(number)`	Returns the cosine of the number argument, which represents an angle in radians. The return type is a `Double` in the range from -1 to 1.
`Exp(number)`	Returns e raised to the `number` power. The return type is a `Double`.
`Int(number)`	Returns the integer part of `number`, where `number` is a decimal number. The return type is an `Integer`. `Int` essentially truncates the number. To round a number use the `Round` function.
`Log(number)`	Returns the natural logarithm (to base e) of `number`. The return type is a `Double`. To calculate the base 10 log of a number, use the expression `Log(number) / Log(10`).
`Round(number)`	Returns `number` rounded to the nearest integer. The return type is an `Integer`.
`Round(number,` `n)`	Returns `number` rounded to the nearest decimal place specified by `n`. The return type is a `Double`.
`Sgn(number)`	Returns the sign of `number`. `Sgn` returns -1 if number is negative, 1 if number is positive, and 0 if `number` equals zero.
`Sin(number)`	Returns the sine of `number`, which represents an angle in radians. The return type is a `Double` in the range from -1 to 1.
`Sqr(number)`	Returns the square root of `number`. The return type is a `Double`. Taking the `Sqr` of a negative number causes an error.
`Tan(number)`	Returns the tangent of `number`, which represents an angle in radians. The return type is a `Double`.

Discussion

You can call VBA functions in your own expressions, as illustrated in Example 2-16.

Example 2-16. Calling VBA functions

num1 = 1 - cos(1.571) num2 = 1 - Atn(a/b)

You need not hardcode arguments as shown in the first statement. You can use an expression like that shown in the second statement as an argument to a function so long as the expression evaluates to an acceptable value permitted by the particular function.

As discussed in Recipe 1.10, Excel has a great many useful functions that you can use in conjunction with the VBA functions discussed here. You can actually call Excel's built-in functions from within a VBA procedure. See Recipe 2.13 for more information on calling Excel functions from VBA code. You have to be a little careful when working with both VBA and Excel functions. VBA and Excel both have some functions that perform the same (or a similar) operation, but in some cases functions with the same name are different, and this can be confusing. For example, both Excel and VBA have a Log function; however, the VBA version of the function returns the base e logarithm while the Excel version returns the base 10 logarithm unless you specify otherwise. This is just something to keep in mind when mixing functions.

Some programming languages have a function that raises a number to some exponent. VBA does not; however, you can use the ^ operator to raise a number to an exponent just as you do when raising a number to a power in an Excel formula. For example, the expression num^3 raises the variable num to the third power. You can also include an expression as the exponent, as in num^(k*1.3). See Recipes 1.8 and 1.9 for more information.

Exploring VBA s Built in Functions

Категории