Blog

The VBA (Visual Basic for Applications) language provides users with an extensive set of Built-in Functions. These functions are used for performing numerical calculations, converting data, processing text, handling errors, and many other operations.

In this tutorial from Iran VBA, we will review the complete list of VBA’s built-in functions along with explanations, examples, and results so you can use them effectively in your own projects.

Definition

Built-in or intrinsic functions are those whose implementation and optimisation have been handled by the VBA language compiler and are made available to the programmer.

Built-in functions are embedded within the VBA compiler to increase speed, simplicity, and efficiency, taking on common operations that might be used by most programmers during development. For example, the built-in Sin function is included in the compiler to calculate the sine.

Built-in functions perform operations and return a value, similar to a Function procedure, with the key difference being that the architecture of built-in functions is handled by the compiler. Furthermore, built-in functions in VBA are not categorised as procedures, meaning they will not perform any operation until they are placed within a procedure.

VBA supports a wide range of built-in functions, which we will explore below.

1. Conversion Functions

Conversion functions in VBA are used to change the data type or value into another format. These functions are very useful when working with text inputs or mixed data.

2. Mathematical Functions

VBA’s mathematical functions are used for calculations such as sine, logarithm, absolute value, and generating random numbers. Some of the most commonly used ones are listed below.

3. Data Type Conversion Functions

In VBA, you sometimes need to change the data type of a variable (e.g., from string to number). The functions in this section are designed for this purpose.

4. String Functions

String functions in VBA are used for working with text (String). Common operations include trimming, searching, replacing, and calculating string length.

Combined Example:

' Remove extra spaces from text and display the final length
Dim txt As String
txt = "  Iran VBA  "
MsgBox Len(Trim(txt))  ' Output: 8

5. Date & Time Functions

Date and time functions in VBA are used to retrieve or calculate dates and times, find the difference between two dates, and format time values.

Example:

' Calculate age based on date of birth
Dim age As Integer
age = DateDiff("yyyy", #22/05/1990#, Date)
MsgBox "Your age: " & age

6. File & System Functions

File and system functions in VBA are used to check for the existence of files or folders, access system information, and working paths.

Example:

' Check if a file exists
If Dir("C:\Data\Report.xlsx") <> "" Then
    MsgBox "File exists."
Else
    MsgBox "File not found."
End If

7. Error Handling Functions

The Err object in VBA is used to access information about the last error that occurred. This object is automatically initialised when an error occurs and includes properties such as the error number (Err.Number), error description (Err.Description), and its source (Err.Source). Using Err.Clear clears the current error information, ready to receive the next error.

Sub Example_ErrorHandling()
    On Error Resume Next    ' Continue execution even if an error occurs
    Dim x As Integer
    x = 10 / 0              ' Cause an error (division by zero)

    If Err.Number <> 0 Then
        MsgBox "Error code: " & Err.Number & vbCrLf & _
               "Description: " & Err.Description & vbCrLf & _
               "Source: " & Err.Source, vbExclamation, "Error Management in VBA"
        Err.Clear            ' Clear the error for reuse
    End If
End Sub

VBA has several built-in functions for detecting and managing runtime errors. These functions are typically used with the On Error statement.

Example:

On Error Resume Next
Dim x As Integer
x = 10 / 0
If Err.Number <> 0 Then
    MsgBox "Error: " & Err.Description
    Err.Clear
End If

8. Other Built-in Functions

This section includes miscellaneous but useful functions that don’t fit into the previous categories, such as Format, Now, and IsNull.

MsgBox Function

• List arguments are separated using a comma (,).
• Except for the mandatory prompt argument, all other arguments are optional.
• The prompt argument can be a String (as in the table example) or a variable containing string data.
• The buttons argument configures the combination of buttons to be used in the message box.

InputBox Function

The return value of the built-in InputBox function is of String data type. Therefore, to store the information received from the user, you must declare a variable of type String.

Array Function

• List arguments are separated using a comma (,).
• The lower bound of an array is zero by default. You can change the lower bound to one using the Option Base 1 statement. Note that the Option Base statement can only be used at the module level. The example above assumes Option Base is set to 1.

LBound Function

• The dimension argument specifies which dimension’s lower bound is to be returned. Use 1 for the first dimension, 2 for the second, and so on. If omitted, the default is 1.
• The LBound function is used with the UBound function to determine the size of an array dimension. For example,
  UBound(A, 2) - LBound(A, 2) + 1
  calculates the size of the second dimension of array A.
• The Option Base setting can affect the return value of the LBound function.
• Using the To clause when declaring an array with any of the variable declaration statements (Dim, Private, Public, ReDim, Static) will cause the number specified in the declaration to be set as the lower bound. The value specified in the To clause takes precedence over the Option Base setting. (This is illustrated in the example).

Example

Dim Lower
Dim MyArray(1 To 10, 5 To 15, 10 To 20)     ' Declare a three-dimensional array with specified upper and lower bounds
Dim AnyArray(10)  ' Declare a one-dimensional array
Lower = Lbound(MyArray, 1)     ' Returns: 1.
Lower = Lbound(MyArray, 2)     ' Returns: 5.
Lower = Lbound(MyArray, 3)    ' Returns: 10.
Lower = Lbound(AnyArray)    ' Returns: 0 or 1. Depends on the Option Base setting.

UBound Function

• The dimension argument specifies which dimension’s upper bound is to be returned. Use 1 for the first dimension, 2 for the second, and so on. If omitted, the default is 1.
• The UBound function is used with the LBound function to determine the size of an array dimension. For example,
  UBound(A, 2) - LBound(A, 2) + 1
  calculates the size of the second dimension of array A.
• The Option Base setting does not affect the return value of the UBound function. Using the To clause when declaring an array with any of the variable declaration statements (Dim, Private, Public, ReDim, Static) will cause the number specified in the declaration to be set as the upper bound. The value specified in the To clause takes precedence over the Option Base setting. (This is illustrated in the example).

Example

Dim Upper
Dim MyArray(1 To 10, 5 To 15, 10 To 20)    ' Declare a variable containing an array
Dim AnyArray(10)
Upper = UBound(MyArray, 1)    ' Returns: 10.
Upper = UBound(MyArray, 3)    ' Returns: 20.
Upper = UBound(AnyArray)      ' Returns: 10.

Choose Function

• The index argument is numeric and must be between 1 and the number of choice arguments.
• The choice arguments can be any Variant expressions containing one of the possible choices.
• If the index argument is greater than the number of choices, the function returns Null.

CreateObject Function

• The class argument is the name of the application and the class of the object to be created. The syntax for this argument is  appname.objecttype and consists of two parts: appname (the name of the application providing the object) and objecttype (the type or class name of the object to create).
• The optional servername argument is the name of the network server where the object is to be created. (Use this if you want to create the object on a computer on the network).
• After an object is created by the function, the created object will have all the methods and properties of the referenced object of which it is an instance.

The process of connecting the object created by the CreateObject function happens at runtime. This is because to create an object using this function, a variable is first declared using the As Object clause, meaning the variable can hold a reference to any type of object; therefore, access to the object is only possible at runtime. This type of variable is called Late-Bound. The opposite type is early-bound. The reference of an early-bound variable is specified at declaration and after declaration can only contain the reference of the declared object, but it performs better than a Late-Bound variable.

VarType Function

• The built-in VarType function is used to determine the data type of a variable. If you use an object instead of a variable in the function argument, the function will return the data type of the object’s default property (Property).
• The table below shows the data type corresponding to the return value of the VarType function.

IsNumeric Function

The return value of the built-in IsNumeric function is True if the function argument is a number, otherwise False.

📚 Summary

In this article, we have familiarised ourselves with the most important VBA Built-in Functions. These functions are fundamental tools for building efficient and professional macros in Excel and other Office applications.

In future tutorials, we will cover String, Date & Time, File & System functions, as well as Error Handling functions in VBA in more detail.

Resources for Further Reading:

List of Built-in Functions on the Microsoft site