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.
An option dialog box will open. Click on the Developer check box (it is under “Customize the Ribbon Main Tabs”).
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”.
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.
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:
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.
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
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.
When you click on the command button, this is the result.
Leave a Reply