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.
|The menu on the right shows completed function calls with fragments (Code VBA add-in)|
- Call a Sub procedure
- Call a Function procedure:
- assign a value to a variable that is not an object
- Assign to an object variable
- use in expressions
- Call a Function using arguments position
- Call a Function using Explicit Arguments
- Type conversion (coercion)
- Returning multiple values
It does not make a difference if you call a built-in or a custom procedure.
|Creating a Sub or Function procedure|
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:
- 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
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:
- Use arguments to return one or more values
- 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 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)
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.