Excel offers a large number of built-in functions that you can use to perform many different tasks easily and quickly. But at least once, you might have felt that the built-in functions provided by Excel are not enough for you to perform some complicated task.
In such cases, you will have to write your own functions, and this is done using VBA. VBA stands for Visual Basic for Applications. Moreover, whenever you have to use a block of code repeatedly, it is advisable to create a function so that you can reuse the code easily.
For example, you want to calculate the area of different shapes like rectangle, square, circle, triangle etc. If you check the built-in formulas, you will not find any formula for the area calculation. Let’s do it by creating a function.
Create a VBA function
Open Excel and save your file as vbafunction.xlsm. Make sure you save the file as a Microsoft Excel Macro-Enabled Worksheet. Otherwise, the function you are going to create will not work.
Type “Shape” in A1, “Side1” in B1, “Side2” in C1, “Side3” in D1, “Area” in E1, “Triangle” in A2, “Rectangle” in A3, “Square” in A4 and “Circle” in A5. You can format the cells A1, B1, C1, D1 and E1 and make them bold. Now your screen will look like this:
Click ALT + F11 to open the Visual Basic Editor.
Go to Insert > Module.
Now you will get a screen like this:
This is the area where you need to enter your functions.
Enter function code
Enter the following code in the space provided:
Function AreaRectangle(Height As Double, Width As Double) As Double
AreaRectangle = Width * Height
End Function
Function AreaTriangle(Side1 As Double, Side2 As Double, Side3 As Double) As Double
Dim p As Double
p = (Side1 + Side2 + Side3) / 2
AreaTriangle = Sqr(p * (p – Side1) * (p – Side2) * (p – Side3))
End Function
Function AreaSquare(Side As Double) As Double
AreaSquare = Side * Side
End Function
Function AreaCircle(Radius As Double) As Double
AreaCircle = 3.14159 * Radius * Radius
End Function
Save these functions by clicking the Save icon or by going to File –> Save vbafunction.xlsm or by clicking CTRL + S.
Click ALT + F11 to go back to your Excel sheet.Type 5, 6 and 7 in the cells B2, C2 and D2. Type 10 and 8 in the cells B3 and D3. Type 10 in the cell B4. Type 5 in the cell B5.
Click on cell E2 and go to the formula bar and enter =ar. When you enter the first few lines of the name of your function, you will find that your function is listed along with other Excel built-in functions.
Select your function from the list or enter the function name completely as =areaTriangle(B2,C2,D2) and press the Enter key. Here, B2, C2 and D2 are the cells that contain the three sides of the triangle. Now cell E2 will contain the value of 14.69694.
Click on cell E3 and enter the formula =areaRectangle(B3,C3). Cell E3 will have a value of 80.
Click on cell E4 and enter the formula =areaSquare(B4). Cell E4 will have a value of 100.
Click on cell E5 and enter the formula =areaCircle(B5). Cell E5 will have a value of 78.53975.
Here, you have created simple mathematical functions to calculate the areas of different shapes. Likewise, you can create more complicated functions. You can even use built-in Excel functions in your functions and make the code reusable.
