11. VBA Functions

11.1 VBA Functions Compared to Object Methods

You probably have used at least one function (Sum) in Excel, possibly without even knowing that it was a function. A function is like a method of an object in that it performs some operation, but there are differences. A function always returns a value. Also, a method is a member of an object, so when you call a method you refer to the object that has the method. For example, Range("A1").Clear calls the Clear method on the Range object to delete the contents and formatting of Cell A1. A VBA function is a feature of the VBA language, so you just use the function's name to call it. For example, Now() returns the system's date and time.

Another way to look at this is to think of a function as an operator, like "+" or "/". As with arithmetic operators, a value is returned, which can be assigned to a variable or used anywhere a value can be used. But instead of using a symbol, a function is a verbal operator. As with a method of an object, the definition of a function gives the function's name, its return value, and its parameters, if the function has any. Function parameters work in the same way as parameters of an object method.

11.2 Use of Parentheses

The general rule in VBA is that when an operation yields a return value, the arguments passed must be in parentheses. This rule holds true for functions.

For object methods, this rule is true if you are assigning the return value to a variable. The Workbooks.Add method is an illustration. This method returns a Workbook object representing the new workbook, but we do not have to assign that returned object to a variable. The new workbook simply becomes the active workbook. If we do not assign the return value to a variable, any arguments passed to the method are not in parentheses (using parentheses in this case will generate a VBA error). If we do assign the return value to a varible, any argument passed must be enclosed in parentheses.

If a VBA function has no parameters, the parentheses after the name of the function are empty, and it is optional to include empty parentheses in such a case. Using parentheses even when empty can be helpful in identifying the term as a function, and we will do so in this tutorial.

11.3 Using the Return Value of a Function

In the examples below we will use the functions Left, Right, MsgBox, Now and InputBox. You might want to look at the Help articles on these functions. Briefly, Left operates on a string and returns a certain number of characters from the left part of the string, Right does the same thing but from the right of the string, MsgBox displays a message and returns a value that identifies the button clicked to dismiss the message, Now returns the date and time as shown by the computer's system clock, and InputBox displays a small dialog with one edit field, into which the user can enter text; when the dialog is dismissed the text entered is returned.

x = "Hello, world"

y = Left(x, 5)

'(1)

z = "Goodbye, cruel " & Right(x, 5)

'(2)

MsgBox("The current date/time is " & Now())

'(3)

vResponse = MsgBox("Is " & Now() & " the current date/time?", vbYesNo)

'(4)

vUserName = InputBox("Please Enter Your Name") '(5)

In the examples above,

(1) assigns "Hello" to the variable y (an example of passing a variable as an argument to a function);
(2) assigns the string "Goodbye, cruel world" to variable z;
(3) displays a message box with the prompt "The current/date time is " followed by the date and time from the system clock;
(4) Displays a message box asking if the date and time from the system clock are correct, with "Yes" and "No" buttons, and assigns a value identifying which button was clicked to the variable vResponse;
(5) displays a dialog with the prompt to the user to enter his/her name, and that value is assigned to variable vUserName when the dialog is dismissed.

The examples above illustrate that you can use a function's return value wherever a value of that data type can be used. So, a function expression can be assigned to a variable, which would then contain the return value of the function, as in example (1) above. A function expression can be used as part of another expression to be assigned to a variable, as in example (2) above. A function expression can be used as an argument, or part of an argument passed to another function, as well, as in examples (3) and (4) above.

The MsgBox function is unusual in that you do not have to use its return value (as in example (3) above), provided you pass it only the one required argument, the prompt. If you use any of the optional arguments, as in example (4) where we pass the VBA enumeration for "Yes" and "No" buttons, you must use the return value of the function. MsgBox is a useful function and has a number of parameters, so you should read the Help article on this function.

11.4 VBA Enumerations

As explained earlier in this tutorial, an enumeration is a verbal representation of a number that is one of a limited set of permitted values. In addition to enumerations in the Excel object model, the VBA language itself has enumerations. VBA language enumerations have the prefix "vb" as in example 4 above. The enumerations available for particular parameters will be given in the Help article for the function.

11.5 My Fundamental VBA Functions

VBA has many built-in functions, and VBA Help has definitions and examples for all the functions of VBA. Listed below are the functions I have used most often (optional parameters have been omitted). I use Excel mainly for data analysis and reports; people who use Excel for other applications would probably have a different set of VBA functions they use most often.

String (text) functions:

Left(original text, number of characters)
Returns a portion of a string from the left.

Right(original text, number of characters)
Returns a portion of a string from the right.

Mid(original text, starting position, number of characters)
Returns a string of characters from inside the original string.

Len(string)
Returns the number of characters in a string.

Trim(string)
Removes spaces from the beginning and end of a string.

LCase/UCase(string)
Returns a string converted to all lower or all upper case.

Instr(string to be searched, string to search for)
Returns the starting position of the string to search for within the string being searched,
returns 0 if not found.

Replace(original string, substring to be replaced, replacement substring)
Returns the original string in which the substring to be replaced has been replaced by the replacement substring.

Date/Time functions:

Now()
Returns the current date and time (based on system clock).

Today()
Returns the current date (based on system clock).

DateAdd(interval to be added, number of intervals to be added, date to be added to)
Returns a date that is the number of intervals after the date to be added to.

DateDiff(interval used to calculate, beginning date, ending date)
Returns the number of the intervals between beginning date and ending date.

User interaction:

InputBox(prompt in the dialog box, title on the dialog box, default value)
Displays a dialog box with the title and prompt specified, for the user to enter a value.
That value is returned by the function; if the user enters no value the default value is returned.

MsgBox(prompt in the dialog box, which buttons to include on the dialog box, title on the dialog box)
Displays a dialog box with the title and prompt specified,
returns a number representing which button the user clicked to dismiss the dialog.