MS-Excel / Functions and Formula

---

What Is a Function?

A function is a type of procedure. A function differs from a subroutine (subprocedure) in that a function always returns a value and a subroutine doesn't. And in common practice, a function almost always takes one or more arguments. Although subroutines can be written to take arguments, most programmers don't write their code this way.

So, to sum up, here are the key difference between functions and subroutines:

• Subroutines These never return values and are rarely sent arguments. Subs are also generally self-contained.
• Functions These communicate more with code outside their own, accepting incoming data from arguments, processing that data somehow, and sending back a result.

You'll often use functions that are built into VBA. Typically, you feed information into a built-in function by sending it arguments. The built-in function then processes that info and returns a value for you to use. But you can also create your own functions in the Code window if you wish.

Built-in functions are so essential to VBA. You used the Rnd function to generate random numbers to fill an array named intArray and the Int function to turn the random numbers into integers:

intArray(i) = Int(Rnd * 10)

Rnd is one of the rare functions that does not have to take one or more arguments. (Rnd can take one optional argument, but the previous example doesn't use it.)

Int, on the other hand, requires an argument - the number or expression that it's to turn into an integer. The argument in this example is supplied by the expression Rnd * 10. Here the Rnd function returns a value that the Int function uses; the Int function then returns a value to the procedure, which uses it to populate a subscript in the array.

An argument is a piece of information that VBA uses with a function, method, or command. You can tell when arguments are optional because they're enclosed within brackets. Because they are optional, you can provide or omit the arguments displayed in the brackets. For example, the full syntax for the Rnd function looks like this:

Rnd([number]) As Single

The brackets indicate that the number argument is optional, and the As Single part of the syntax denotes that the value returned is of the Single data type. Different functions return different data types suited to their job: Many functions return a Variant, but yes/no functions, such as the IsNumeric function, return a Boolean value, either True or False.

When necessary, VBA may convert the result of a function to a different data type needed by another function in the expression.

If any pair of brackets contains two arguments, you have to use both of them at once. For example, the MsgBox function displays a message box. The syntax for the MsgBox function is as follows

MsgBox(prompt[, buttons] [, title][, helpfile, context])

Here, prompt is the only required argument: buttons, title, helpfile, and context are all optional. But notice that helpfile and context are enclosed within a single set of brackets instead of each having its own pair, meaning that you need to use either both of these arguments or neither of them; you cannot use one without the other.

Using Functions

To use a function, you call it (or invoke it) from a subprocedure or from another function. To call a function, you can use a call statement, either with the optional Call keyword or by just using the name of the function. Using the Call keyword allows you to search for all calls in your project by searching for "call " (call followed by a space). However, using the Call keyword may seem like overkill for everyday functions; programmers rarely use it.

The syntax for the Call statement is as follows:

[Call] name [argumentlist]

Here, name is a required String argument giving the name of the function or procedure to call, and argumentlist is an optional argument providing a comma-delimited list of the variables, arrays, or expressions to pass to the function or procedure. When calling a function, you'll almost always need to pass arguments (except for those few functions that take no arguments).

The brackets around the Call keyword indicate that it is optional. If you do use this keyword, you need to enclose the argumentlist argument in parentheses. In most cases, it's easier to read the code if you don't use the Call keyword when calling a function.

For example, the following statement calls the MsgBox function, supplying the required argument prompt (in this example, it's the string Hello, World!):

MsgBox "Hello, World!"

You could use the Call keyword instead, as shown in the following statement, but there's little advantage in doing so:

Call MsgBox "Hello, World!"

Note that the MsgBox function is one of the few with which you can omit the parentheses around the argument list.

You can assign the result returned by a function to a variable. For example, consider the following code fragment. The first two of the following statements declare the String variables strExample and strLeft10. The third statement assigns a string of text to strExample. The fourth statement uses the Left function to return the leftmost 10 characters from strExample and assign them to strLeft10, which the fifth statement then displays in a message box:

Dim strExample As String
Dim strLeft10 As String
strExample = "Technology is interesting."
strLeft10 = Left(strExample, 10)
MsgBox strLeft10

If you prefer, you can assign the result of each function to a variable, as in this next example. Here the first string variable, str1, is assigned the leftmost 13 characters from the string This is Pride and Patriotism. So after its code line executes, str1 holds the value This is Pride. Then str2 is assigned the rightmost 5 characters from str1, resulting in Pride.

Dim str1 As String
Dim str2 As String

str1 = Left("This is Pride and Patriotism", 13)
str2 = Right(str1, 5)

MsgBox str2

However, after you become accustomed to working with functions, you can collapse them in various ways in your code. Instead of assigning the result of a function to a variable, you can insert it directly in your code or pass it (as an argument) to another function. This is a common shortcut. Take a look at the following statement. It does the same thing as the previous example but collapses the code into one line, avoiding the use of variables altogether:

MsgBox Right(Left("This is Pride and Patriotism", 13), 5)

This statement uses three functions: the MsgBox function, the Left function, and the Right function. (The Right function is the counterpart of the Left function and returns the specified number of characters from the right side of the specified string.)

When you have multiple sets of parentheses in a VBA statement, the code is executed starting from the innermost pair of parentheses and working outward. This is the same way that nested parentheses are handled in math.

So, in the previous example the Left function is evaluated first, returning the leftmost 13 characters from the string: This is Pride (the spaces are characters too). VBA passes this new string to the Right function, which in this case returns the rightmost five characters from it: Pride. VBA then passes this second new string to the MsgBox function, which displays it in a message box.