Calling a function in Excel VBA

In this Excel VBA tutorial, you are going to learn how to create and call a VBA function in Excel. Excel provides the user with a large collection of ready-made functions. These functions are usually more than enough to satisfy the average user’s requirements.

If these functions aren’t enough, or if a user is looking for a new function, many more of these functions can be added to Excel by installing the various add-ins that are available.

Most calculations can be achieved with what is provided. However, sometimes you are doing a tedious task, and before long you find yourself wishing that there was a function that did a particular job, and you can’t find anything suitable in the list. That’s when you need a UDF, or a “user-tailored” function.

A UDF (User Defined Function) is simply a function that a user creates for him or herself with VBA. UDFs are often called “Custom Functions”. A UDF can be simple or very complex; depending on the user.

To use a VBA function, you must first create it. Follow these steps to create a VBA function and call it:

Note: You can skip steps 1 and 2 if the DEVELOPER tab is already open.

The ribbon

Right-click anywhere on the ribbon and select “Customize the Ribbon” from the drop-down menu.

Customize The Ribbon

An option dialog box will open. Click on the Developer check box (it is under “Customize the Ribbon Main Tabs”).

Add Developer Tab

The developer tab is now visible and is present next to the view tab on the top menu bar. Click on the Developer tab and select “View Code”.

See also  Using Scientific Notation in VBA

View Code

The code

A new window (Visual Basic Editor) will open which will have a dialog box in the center. You will write the code in the dialog box.

New VBA Code

Write the following code in the dialog box.

Function Area(x As Double, 
y As Double) As Double
Area = x * y
End Function

After writing the code, close the window by clicking on the cross(x) icon on the upper right side of the screen. Don’t worry, Excel won’t close.

Explanation: To create a function in Excel VBA, you use the keyword “Function” followed by the function name (this can be anything), followed by the declaration of variables and their types in curly braces, followed by the type of function, followed by what the function does, followed by “End Function” to close the function.

In this case, the function name is “Area”, the function type is donated by the data type “Double”, X and Y are variables, and their data type is Double. The function multiples the values of variables X and Y and stores them in the variable Area.

Here is an example:

VBA function as double

You can now call this function from somewhere else in your code by simply using the name of the function and giving a value for each argument (in this case, by giving the value of variable x and variable y).

The Button

Now place a command button on your Excel sheet by clicking on the developer tab, selecting “Insert” and then by selecting the command button icon in the ActiveX Controls.

Insert Command Button

Drag the command button onto the Excel worksheet. Double-click on the command button (make sure the Design Mode is on).A new window opens (Visual Basic Editor). Write the following line of code.

Dim z as Double
z=Area (3, 5) +2
MsgBox z

Create Function VBA code

Explanation: in the first line you have declared a variable z of data type Double. In the second line of code you called the function Area and passed the values of the argument (values of variables x and y) and added + 2 to it. In the third line, you are displaying the value of z in a message box.

See also  Multiple Loops in Excel Vba

When you click on the command button, this is the result.

Sheet Command Button