How to Create Vba Function in Excel

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:

VBA own function table

Click ALT + F11 to open the Visual Basic Editor.

VBA own function editor

Go to Insert > Module.

VBA own function module

Now you will get a screen like this:

VBA own function ready to go

This is the area where you need to enter your functions.

See also  What are the most common bugs in VBA code?

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

VBA own function code

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.

VBA own function works

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.

VBA own function formula bar

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.

See also  How to Record a Macro in Excel

Click on cell E5 and enter the formula =areaCircle(B5). Cell E5 will have a value of 78.53975.

VBA own function completed

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.