How to Use Excel’s Built-In VBA Functions

Excel VBA provides numerous built-in functions. Some of these Excel VBA functions take arguments, and some do not.

Excel VBA function examples

Here, you find a few examples of using VBA functions in code. In many of these examples, the MsgBox function displays 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 dialog box and also returns a value.

Displaying the system date or time

The first example uses VBA’s Date function to display the current system date in a message box:

Sub ShowDate()
  MsgBox "Today is: " & 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 type an empty set of parentheses, the VBE promptly removes them.

To get the system time, use the Time function. And if you want it all, use the Now function to return both the date and the time.

Finding a string length

The following procedure uses the VBA Len function, which returns the length of a text string. The Len function takes one argument: the string. When you execute this procedure, the message box displays your name, and the number of characters in your name.

Sub GetLength()
  Dim MyName As String
  Dim StringLength As Long
  MyName = Application.UserName
  StringLength = Len(MyName)
  MsgBox MyName & " has " & StringLength & " characters."
End Sub

Excel also has a LEN function, which you can use in your worksheet formulas. The Excel version and the VBA function work the same.

Displaying the name of a month

The following procedure uses the MonthName function, which returns the name of a month. MonthName uses one argument: an integer between 1 and 12.

Sub ShowMonthName()
  Dim ThisMonth As Long
  ThisMonth = Month(Date)
  MsgBox MonthName(ThisMonth)
End Sub

This procedure uses the Month function to get the current month (as a value), and this value is assigned to the ThisMonth variable. The MonthName function then converts the value to text. So if you run this procedure in April, the message box displays the text April.

Actually, the ThisMonth variable isn’t required. You can get the same effect with this expression, which uses three VBA functions:

MonthName(Month(Date))

Here, the current date is passed as an argument to the Month function, which returns a value that’s passed as an argument to the MonthName function.

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:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE"
  MsgBox FileLen(TheFile)
End Sub

Notice that this routine hard-codes the filename (that is, explicitly states the path). This isn’t a good idea. The file may 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 selection on the worksheet (as a string):

Sub ShowSelectionType()
  Dim SelType As String
  SelType = TypeName(Selection)
  MsgBox SelType
End Sub

The selection could be a Range, a Picture, a Rectangle, a ChartArea, 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.

Excel 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.

Discovering Excel VBA functions

How do you find out which functions VBA provides? Good question. The best source is the Excel VBA system. Another way is to type VBA, followed by a period. You get a list of items. Those with a green icon are functions. If this feature isn’t working, choose VBE’s Tools → Options, click the Editor tab, and place a check next to Auto List Members.

There are over 140 different functions available in VBA. Some are so specialized and obscure, you’ll never need them. Others, however, are quite useful for many applications.

For complete details on a particular Excel VBA function, type the function name in a VBA module, move the cursor anywhere in the text, and press F1.