Excel VBA Function Tutorial: Return, Call, Examples

What is a Function?

A function is a piece of code that performs a specific task and returns a result. Functions are mostly used to carry out repetitive tasks such as formatting data for output, performing calculations, etc.

Suppose you are developing a program that calculates interest on a loan. You can create a function that accepts the loan amount and the payback period. The function can then use the loan amount and payback period to calculate the interest and return the value.

Why use functions

The advantages of using functions are the same as those listed for subroutines: they break a long program into manageable parts, they can be reused from anywhere in the project, and a descriptive name documents what the code does. The Excel VBA Subroutine tutorial covers those benefits in full.

Rules of naming functions

The naming rules are also identical to those for subroutines. A function name cannot contain a space, must begin with a letter or an underscore, and cannot be a reserved VBA keyword such as Function, Private, or End.

VBA Syntax for declaring Function

Private Function myFunction (ByVal arg1 As Integer, ByVal arg2 As Integer)
    myFunction = arg1 + arg2
End Function

HERE in the syntax,

How to Return a Value and Set the Function Data Type

A Function has one job that a Subroutine does not: it hands a value back. Two details control that value, and both are easy to miss.

The first is the assignment. VBA has no Return statement. Instead you assign the result to the function’s own name, which is why the line reads myFunction = arg1 + arg2. If that assignment never runs, the function silently returns an empty value rather than raising an error, so every branch of the code must set it.

The second is the return type. The declaration above ends at the closing bracket, so the function returns a Variant. Adding an As clause after the brackets fixes the type, which is faster, uses less memory, and lets the compiler catch a mismatch.

💡 Tip: Use Exit Function to leave early once the return value is set, in the same way Exit Sub leaves a subroutine.

Function demonstrated with Example:

Functions are very similar to the subroutine. The major difference between a subroutine and a function is that the function returns a value when it is called. While a subroutine does not return a value, when it is called. Let’s say you want to add two numbers. You can create a function that accepts two numbers and returns the sum of the numbers.

1. Create the user interface
2. Add the function
3. Write code for the command button
4. Test the code

Step 1) User interface

Add a command button to the worksheet as shown below

Set the following properties of CommandButton1 to the following.

Your interface should now appear as follows

Step 2) Function code.

1. Press Alt + F11 to open the code window
2. Add the following code

Private Function addNumbers(ByVal firstNumber As Integer, ByVal secondNumber As Integer)
    addNumbers = firstNumber + secondNumber
End Function

HERE in the code,

Step 3) Write Code that calls the function

1. Right click on the btnAddNumbers command button
2. Select View Code
3. Add the following code

Private Sub btnAddNumbers_Click()
    MsgBox addNumbers(2, 3)
End Sub

HERE in the code,

Step 4) Run the program, you will get the following results

Download Excel containing above code

Download the above Excel Code

The button above calls the function from VBA code. A function can also be called from the worksheet itself, with no button at all.

How to Use a VBA Function in a Worksheet Cell

A function written in VBA can be typed into a cell exactly like SUM or VLOOKUP. Excel calls this a user defined function, or UDF, and it is the reason many people learn functions before subroutines. Three conditions must be met.

• Place it in a standard module: Insert, Module in the editor. A function stored behind a worksheet or in ThisWorkbook is not visible to the formula bar.
• Declare it Public: The example above uses Private, which hides it from Excel. Public is the default, so simply removing the keyword is enough.
• Return a value, change nothing: A UDF cannot format cells, delete rows, or write to another cell. Excel blocks those actions and the cell shows #VALUE!.

The function below converts a temperature and can be used anywhere on the sheet.

Public Function CelsiusToF(ByVal Celsius As Double) As Double
    CelsiusToF = (Celsius * 9 / 5) + 32
End Function

Save the workbook as a macro-enabled .xlsm file, then type =CelsiusToF(A1) into any cell. The result updates whenever A1 changes, and the name appears in the formula autocomplete list under the User Defined category. Because the workbook now contains macros, anyone opening it must enable content before the formula returns a value rather than #NAME?.

Common VBA Function Errors and How to Fix Them

Four problems account for most functions that compile but return the wrong answer.

• The function returns Empty or 0: The result was never assigned to the function name, or one branch of an If statement skips the assignment. Set the return value on every path.
• #NAME? in a worksheet cell: The function is Private, sits in a sheet module instead of a standard module, or the workbook was saved without macros enabled.
• Overflow with Integer arguments: The example uses As Integer, which stops at 32,767. Change both parameters and the return type to Long for any real data.
• A changed argument surprises the caller: Omitting ByVal makes VBA pass the variable itself, so the function can alter the caller’s value. Write ByVal unless that effect is wanted.

FAQs