Creating Functions in Microsoft Excel

Leave a reply

Many people will use Excel for years and never be familiar with some of the more advanced features. For example, there are ways to develop your own functions and even programs inside Excel using Visual Basic Application (VBA). This powerful tool is sitting there waiting to make life easier for Excel users. This post will explain how to create your own functions using Visual Basic Application in Microsoft Excel.

Load the Developers Tab

First, you need to add the developer’s tab to your ribbon. To do this, do the following

  1. Click on File->Options->Customize ribbon
  2. Then click “customize main ribbon” and click on the developers checkbox as shown below

Setup for the Function

Now that the developer’s tab is available. We need to click on it and then click on visual basic, as shown below.

Once you click on “Visual Basic,” you will see the following

To make a function, we need to add a module. This is done by clicking

  • Insert->Module

You will see the following

The Function

We are going to make a simple function that calculates the area of a triangle. The code is only three lines and is shown below.

Function area(base As Double, height As Double) As Double
area = (base * height) / 2
End Function

Line one is declaring the function and giving it the name “area.” Inside the parentheses, we have two parameters, “base” and “height.” Each of these parameters is a data type called double in VBA, which can be used to store decimals. The function itself is also a double data type as this is outside the parentheses.

Line two shows the actual formula for the area of a triangle.

Lastly, we finish the function by adding “End Function.” Generally, VBA does this for you automatically.

Test the Function

Now we can see if the function works by going to Excel. You don’t have to save the function or anything like that in this example. Go to excel and call the formula, place two numbers inside the parentheses, separated by a comma, and press enter. Below is an example.

Below is the output after press enter

Success

Conclusion

This was a simple example of how to make a function in Excel using VBA. There is so much more to explore and learn, but this is a great way to get started.

Leave a Reply