Calling a function in Excel
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.
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.
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”
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.
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:
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.
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
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.
Template
Further reading: Basic concepts Getting started with Excel Cell References