A function performs a calculation and returns a single value. The SUM function adds the sum of a range of values. The same holds true for functions used in your VBA expressions: Each function does its thing and returns a single value.
The functions you use in VBA can come from three sources:
- Built-in functions provided by VBA
- Worksheet functions provided by Excel
- Custom functions that you (or someone else) write, using VBA
VBA provides numerous built-in functions. Some of these functions take arguments and some do not.
VBA function examples
Here are a few examples of using VBA functions in code. Note the use of the MsgBox function to display a value in a message box. Yes, MsgBox is a VBA function — a rather unusual one, but a function nonetheless. This useful function displays a message in a pop-up dialog box.
Displaying the system date
The first example uses VBA's Date function to display the current system date in a message box:
Sub ShowDate()
MsgBox Date
End Sub
Notice that the Date function doesn't use an argument. Unlike worksheet functions, a VBA function with no argument doesn't require an empty set of parentheses. In fact, if you provide an empty set of parentheses, the VBE will remove them.
To get the system date and time, use the Now function instead of the Date function. Or to get only the time, use the Time function.
Finding a string length
The following procedure uses the VBA Len function, which returns the length of a string. The Len function takes one argument: the string. When you execute this procedure, the message box displays 11 because the argument has 11 characters.
Sub GetLength()
Dim MyString As String
Dim StringLength As Integer
MyString = "Hello World"
StringLength = Len(MyString)
MsgBox StringLength
End Sub
Excel also has a function, which you can use in your worksheet formulas. The Excel version and the VBA function work the same.
Displaying the integer part of a number
The following procedure uses the Fix function, which returns the integer portion of a value — the value without any decimal digits:
Sub GetIntegerPart()
Dim MyValue As Double
Dim IntValue As Integer
MyValue = 123.456
IntValue = Fix(MyValue)
MsgBox IntValue
End Sub
In this case, the message box displays 123.
VBA has a similar function called Int. The difference between Int and Fix is how each deals with negative numbers.
- Int returns the first negative integer that's less than or equal to the argument.
- Fix returns the first negative integer that's greater than or equal to the argument.
Determining a file size
The following Sub procedure displays the size, in bytes, of the Excel executable file. It finds this value by using the FileLen function.
Sub GetFileSize()
Dim TheFile As String
TheFile = "c:MSOFFICEEXCELEXCEL.EXE"
MsgBox FileLen(TheFile)
End Sub
Notice that this routine hard codes the filename (that is, it explicitly states the path). Generally, this isn't a good idea. The file might not be on the C drive, or the Excel folder may have a different name. The following statement shows a better approach:
TheFile = Application.Path & "EXCEL.EXE"
Path is a property of the Application object. It simply returns the name of the folder in which the application (that is, Excel) is installed (without a trailing backslash).
Identifying the type of a selected object
The following procedure uses the TypeName function, which returns the type of the selected object (as a string):
Sub ShowSelectionType()
Dim SelType As String
SelType = TypeName(Selection)
MsgBox SelType
End Sub
This could be a Range, a ChartObject, a TextBox, or any other type of object that can be selected.
The TypeName function is very versatile. You can also use this function to determine the data type of a variable.
VBA functions that do more than return a value
A few VBA functions go above and beyond the call of duty. Rather than simply return a value, these functions have some useful side effects. Table 1 lists them.
Table 1: Functions with Useful Side Benefits
Function | What It Does |
MsgBox | Displays a handy dialog box containing a message and buttons. The function returns a code that identifies which button the user clicks. |
InputBox | Displays a simple dialog box that asks the user for some input. The function returns whatever the user enters into the dialog box. |
Shell | Executes another program. The function returns the task ID (a unique identifier) of the other program (or an error if the function can't start the other program). |
Discovering VBA functions
How do you find out which functions VBA provides? Good question. The best source is the Excel Visual Basic Help system. Table 2 contains a partial list of functions (minus some of the more specialized or obscure functions).
For complete details on a particular function, type the function name into a VBA module, move the cursor anywhere in the text, and press F1.
Table 2: VBA's Most Useful Built-in Functions
Function | What It Does |
Abs | Returns a number's absolute value |
Array | Returns a variant containing an array |
Asc | Converts the first character of a string to its ASCII value |
Atn | Returns the arctangent of a number |
Choose | Returns a value from a list of items |
Chr | Converts an ANSI value to a string |
Cos | Returns a number's cosine |
CurDir | Returns the current path |
Date | Returns the current system date |
DateAdd | Returns a date to which a specified time interval has been |
| added — for example, one month from a particular date |
DateDiff | Returns an integer showing the number of specified time intervals between two dates, for example the number of months between now and your birthday |
DatePart | Returns an integer containing the specified part of a given |
| date — for example, a date's day of the year |
DateSerial | Converts a date to a serial number |
DateValue | Converts a string to a date |
Day | Returns the day of the month from a date value |
Dir | Returns the name of a file or directory that matches a pattern |
Erl | Returns the line number that caused an error |
Err | Returns the error number of an error condition |
Error | Returns the error message that corresponds to an error number |
Exp | Returns the base of the natural logarithm (e) raised to a power |
FileLen | Returns the number of bytes in a file |
Fix | Returns a number's integer portion |
Format | Displays an expression in a particular format |
GetSetting | Returns a value from the Windows registry |
Hex | Converts from decimal to hexadecimal |
Hour | Returns the hours portion of a time |
InputBox | Displays a box to prompt a user for input |
InStr | Returns the position of a string within another string |
Int | Returns the integer portion of a number |
IPmt | Returns the interest payment for an annuity or loan |
IsArray | Returns True if a variable is an array |
IsDate | Returns True if an expression is a date |
IsEmpty | Returns True if a variable has not been initialized |
IsError | Returns True if an expression is an error value |
IsMissing | Returns True if an optional argument was not passed to a procedure |
IsNull | Returns True if an expression contains no valid data |
IsNumeric | Returns True if an expression can be evaluated as a number |
IsObject | Returns True if an expression references an OLE Automation object |
LBound | Returns the smallest subscript for a dimension of an array |
LCase | Returns a string converted to lowercase |
Left | Returns a specified number of characters from the left of a string |
Len | Returns the number of characters in a string |
Log | Returns the natural logarithm of a number to base e |
LTrim | Returns a copy of a string, with any leading spaces removed |
Mid | Returns a specified number of characters from a string |
Minute | Returns the minutes portion of a time value |
Month | Returns the month from a date value |
MsgBox | Displays a message box and (optionally) returns a value |
Now | Returns the current system date and time |
RGB | Returns a numeric RGB value representing a color |
Right | Returns a specified number of characters from the right of a string |
Rnd | Returns a random number between 0 and 1 |
RTrim | Returns a copy of a string, with any trailing spaces removed |
Second | Returns the seconds portion of a time value |
Sgn | Returns an integer that indicates a number's sign |
Shell | Runs an executable program |
Sin | Returns a number's sine |
Space | Returns a string with a specified number of spaces |
Sqr | Returns a number's square root |
Str | Returns a string representation of a number |
StrComp | Returns a value indicating the result of a string comparison |
String | Returns a repeating character or string |
Tan | Returns a number's tangent |
Time | Returns the current system time |
Timer | Returns the number of seconds since midnight |
TimeSerial | Returns the time for a specified hour, minute, and second |
TimeValue | Converts a string to a time serial number |
Trim | Returns a string without leading or trailing spaces |
TypeName | Returns a string that describes a variable's data type |
UBound | Returns the largest available subscript for an array's dimension |
UCase | Converts a string to uppercase |
Val | Returns the numbers contained in a string |
VarType | Returns a value indicating a variable's subtype |
Weekday | Returns a number representing a day of the week |
Year | Returns the year from a date value |
