Call a function or sub procedure in VBA

When you write VBA procedures code looks like this:


Sub ExampleCallFunction()
    Dim dtToday As Date
    'call custom Function Tomorrow'
    dtToday = Tomorrow
    'call built-in Sub MsgBox - inside prompt built-in the function FormatDateTime is called'
    MsgBox Prompt:="Tomorrow will be " & FormatDateTime(Expression:=dtToday, _
                              NamedFormat:=vbGeneralDate), Buttons:=vbOKOnly + vbInformation
End Sub

Function Tomorrow() As Date
    Tomorrow = DateAdd(Interval:="d", Number:=1, Date:=Date)
End Function

This page explains various aspects of calling a sub or function procedure.

Note
The menu on the right shows completed function calls with fragments (Code VBA add-in)

It does not make a difference if you call a built-in or a custom procedure.

Related
Creating a Sub or Function procedure
Tomorrow code in fragment menu.png

Call a Sub

To call a Sub procedure from another procedure, type the name of the procedure and include values for any required arguments. You usually call Sub procedures to achieve side effects:

  • Ask user attention: Beep
  • Write to a log table: ErrorHandle Err, Erl(), "Module1.Test"

The first Sub is built-in, the second would be a custom Sub you wrote.

Call a Function

The normal use of a function is to assign the value returned by the function to a variable. To use the return value of a function, assign the function to a variable and enclose the arguments in parentheses. An example is calling the built-in Date function:


Dim dtToday As Date
dtToday = Date

If the functions return type is an object instead of a built-in type it is preceeded by the keyword Set, e.g. in Excel VBA:


Dim rngActiveCell  As Range
Set rngActiveCell = ActiveCell

You may also call a function in arguments to functions. In below fragment (Make First Character Uppercase) Len is used inside IFF amongst others.


Dim str As String: str =IIf(Len(str) > 0, UCase(Left(str, 1)) & Mid(str, 2), "")

Another use of function is as a position in an conditional expression, e.g.:


If Not rngActiveCell Is Nothing Then

Type conversion during assigment

One feature of VB that makes it easy to use is that it is not strict about types. VB will automatically try to interpret what you assign so that it can continue. In the example below, even though types don't match, VB will interpret: The String "True" is converted to True, the Integer 0 us converted to False.


Dim boo As Boolean
boo = "True"
boo = 0

Returning multiple values

If you want your procedure to returning multiple values, for example a function TopLeftCoords. Now there are two approaches:

  1. Use arguments to return one or more values
  2. Create a custom data type and use that as the functions return type

Use arguments to return one or more values


Sub TopLeft(X As Long, Y As Long)
    X = 5
    Y = 6
End Sub

Sub TopLeftDemo()
    Dim lngX As Long
    Dim lngY As Long
    TopLeft lngX, lngY
    MsgBox Prompt:="X=" & lngX & ";" & "Y=" & lngY, Buttons:=vbOKOnly + vbInformation
End Sub

Create a custom data type and use that as the functions return type

Note: if you want to try the below code, at least the Type definition needs to be in a standard module.


Public Type Coords
    X As Long
    Y As Long
End Type

Function TopLeft() As Coords
    With TopLeft
        .X = 5
        .Y = 6
    End With
End Function

Sub TopLeftDemo2()
    Dim crds As Coords
    crds = TopLeft
    MsgBox Prompt:="X=" & crds.X & ";" & "Y=" & crds.Y, Buttons:=vbOKOnly + vbInformation
End Sub

Call a Function using arguments position

The most commonly used way of calling a function is using arguments position. E.g. in the following code 


Dim strReplaced As String
strReplaced = Replace(strTableName, "ab", strReplaced)

If you see such code, or have to write it, a recurring question is what it actually does. Ok, you know it does a replace. But which string is being replaced in where by what? And will it replace 'aB' by 'ac'? To see what's what you can put the cursor inside the function call and insert a space, which opens a tooltip, see below. A more definite way however is to use explicit arguments.

call function

Call a Function using Explicit Arguments

When using the Code VBA add-in using default settings you will call procedures with explicit arguments in which case the code looks like - image below shows the VBA » String functions menu and tooltip telling what the procedure does.

An example of calling a function is with Explicit Arguments:


strReplaced = Replace(Expression:="AaBb", Find:="ab", _ 
            Replace:="ac", Start:=1, Count:=-1, Compare:=vbBinaryCompare)
Code VBA add-in replace function menu

Because this function has multiple arguments, when the menu item is selected, a dialog opens allowing you to select from the available variables for each argument - or just type a string. The text below the argument area explains what how to use the current argument. The bottom area tells what code will be inserted.

Code VBA add-in replace function