VBA User Defined Function

What is a Function?

A function is a group of reusable code which can be called anywhere in your program. This eliminates the need of writing same code over and over again. This will enable programmers to divide a big program into a number of small and manageable functions.

Apart from inbuilt Functions, VBA allows us to write user-defined functions as well. This section will explain you how to write your own functions in VBA.

Function Definition

A VBA function can have an optional return statement. This is required if you want to return a value from a function.

For example, you can pass two numbers in a function and then you can expect from the function to return their multiplication in your calling program.

NOTE − A function can return multiple values separated by comma as an array assigned to the function name itself.

Before we use a function, we need to define that particular function. The most common way to define a function in VBA is by using the Function keyword, followed by a unique function name and it may or may not carry a list of parameters and a statement with a End Function keyword, which indicates the end of the function. The basic syntax is shown below −

Syntax

Add a button and add the below function

Function Functionname(parameter-list)
  statement 1
  statement 2
  statement 3
  .......
  statement n
End Function

Example

Add the below function which returns the area. Note that a value/values can be returned with the function name itself.

Function findArea(Length As Double, Optional Width As Variant)
   If IsMissing(Width) Then
      findArea = Length * Length
   Else
      findArea = Length * Width
   End If
End Function

Calling a Function

To invoke a function, call the function using function name as shown below −

The Output of the area would be displayed to the user.

← Previous Page

Next Page →