Calling a function in Excel

In this article we 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 function are usually more than enough to satisfy the average user requirement. If these functions aren’t enough or if a user is looking for a new function that many more of these function can be added in 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 create for him or herself with VBA. UDFs are often called “Custom Functions”. A UDF can be simple or a very complex it depends on the user.To call a VBA function we must first create it. Follow these steps to create a VBA function and call it:

Note: you can skip step 1 and step 2 if the DEVELOPER tab is already open

STEP-1

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

Customize The Ribbon

STEP-2

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

Add Developer Tab

STEP-3

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

View Code

STEP-4

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

New VBA Code

STEP-5

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 the 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 declaration of variables and their types in curly braces followed by type of function followed by what the function does followed by “End Function” to close the function.

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

Here is an example:

VBA function as double

You can now all 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).

STEP-6

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

STEP-7

Drag command button on to the excel worksheet. Double click on the command button (make sure the Design Mode is on).A new window open (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: the first line of you have declared a variable z of data type Double. In the second line of code you have called function Area and passed the values of the argument (values of variable x and y) and have added + 2 to it. In the third line you are displaying the value of z in a message box

STEP-8

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

Sheet Command Button

Template

You can download the Template here – Download

RELATED ARTICLESMORE FROM AUTHOR