There are a lot of inbuilt functions that are available for our use in excel, however we may come across some scenarios where we want to calculate a number of our requirement and want to use it repeatedly
Example-
Suppose we want to calculate the result for a mathematical function (A+B)2.
We want to calculate this by using our own function in excel taking the inputs for A and B from excel cells or we may give the values of A and B by ourselves.
The cell B1 and B2 contains the value of A and B respectively that we want to use in our function.
Step 1
Press Alt+F11 to open the VB editor of excel or alternatively you can open it from the Developer ribbon.
You will get the VB editor as shown below
Step 2
Click on insert menu and insert a new module in which we will write our formula to be used in excel.
As you can see a new module “Module 1”is inserted in our VB editor.
Step 3
Now start writing the formula for (A+B)2 in the editor i.e. A2+B2+2*A*B.
Write the function as below.
To define a function you have to write the function formula under Public Function which are keywords and end the function with End Function which marks the end of the function.
Also we have to define the parameters or inputs that our function is going to take a and b in our case and the types of those inputs are integer as we want our formula to operate on integer number.
Myformula is the name that I have given to the function, the result after the calculation is going to be returned in Myformula variable only.
The returned variable should be having the same name of the function that we have defined.
Step 3
Now we will have to write a couple of line of code to do our calculation.
Step 4
Now start writing our formula in a cell and you will observe that our newly created formula is appearing.
Give the first argument as cell B1 and second argument as B2 with comma separating both.
Hit Enter and the result will be calculated by using our defined formula.
Hope this helped.
