Make your own worksheet functions and addins with Excel VBA

Excel has many worksheet functions like e.g. SUM(), AVERAGE() etc. This page shows how to make your own worksheet functions, that work in the same fashion.

It also shows, how to save them as an addin, so you can use them in all your workbooks.

It can be very useful to make special worksheet functions for calculations or operations you make regularly, if they are not included in Excel's standard functions.

At work I have made worksheet functions for e.g. chemical calculations with formulas that are difficult to remember.

And I have saved the functions in an addin, so I can use them in all my workbooks.

The only thing to remember is, that if I share a workbook with a colleague, he must either have the addin installed, or I must copy the functions to a VBA module in the workbook. Otherwise the functions will not work.

Below is a sample function, which can be used as a worksheet function. Calculating the area of a rectangle isn't spectacular, but the example shows how a function uses the arguments you pass to it - in this case length and width.

Function RECTANGLEAREA(Length As Double, Width As Double) As Double
'Calculates and returns the area of a rectangle
RECTANGLEAREA = Length * Width
End Function

The function's name is RECTANGLEAREA, just like Excel's standard functions have names.

Length and Width are declared as Double (data type). If you don't declare a data type, Variant is default. In this case the value returned by the function is also declared as a Double (a number with possible decimals).

If you copy the function above to a VBA module, activate a worksheet and select "Insert function" (SHIFT+F3) and select the category "User defined," you'll see RECTANGLEAREA on the list. When you select it the function wizard pops up:

Now you can either click a cell with the length value or write a cell address or type a value.

The advantage of using a cell address is of course that the calculated area will update automatically, if the length value changes.

A short description of the function

On the image above you can see a text saying: "Der er ingen hjælp til rådighed". This is Danish for "No help available," but we can change that.

Choose "Macros" from the menu (ALT+F8). A dialogue will pop up with a list of your macros (if any), but it will not show functions.

However, if you write the function's name, in this case RECTANGLEAREA, the buttons will become active and will look something like this (Danish Excel 2003):

Click "Settings" and write a line or two with a description of the function - e.g. "Calculates the area of a rectangle". Then it will look like this, when you call the function:

Using Excel's functions

To some extent you can use Excel's built-in worksheet functions in your own functions. In the function below, we use the worksheet function Pi to calculate the area of a circle.

Function CIRCLEAREA(Radius As Double) As Double
'Calculates the area of a circle
CIRCLEAREA = Application.WorksheetFunction.Pi * Radius ^ 2
End Function

More complicated functions

So far the examples have been very simple, so to make up for that you get two with a bit more "juice".

The first, DISCOUNTPRICE, calculates the discount and price as a function of the number of items sold. Up to 100 items offers no discount, up to 200 items will give a 2% discount, up to 300 a 4% discount, up to 400 a 6% discount and higher 8% discount.

The arguments passed to the function are the price with no discount and the number of items sold.

Function DISCOUNTPRICE(Price_no_discount As Double, Number As Long) As Double
Select Case Antal
   Case Is < 100
      DISCOUNTPRICE = Price_no_discount
   Case 100 To 199
      DISCOUNTPRICE = Price_no_discount * 0.98
   Case 200 To 299
      DISCOUNTPRICE = Price_no_discount * 0.96
   Case 300 To 399
      DISCOUNTPRICE = Price_no_discount * 0.94
   Case Is > 399
      DISCOUNTPRICE = Price_no_discount * 0.92
End Select
End Function

Notice that VBA uses "." as decimal separator, even if your country settings use comma.

In the last example a measured gas flow in m3/h is converted to normal cubic meters at 1013 mbar, 0° C, dry and 10% oxygen.

Function NM3_DRY_10pct_O2(M3, Temperature, mbar, O2, H2O) As Double

NM3_DRY_10pct_O2 = M3 * mbar / 1013 * (273.15 / (273.15 + Temperature)) * _
(1 - H2O / 100) * (20.9 - O2) / 10.9

End Function

In the function's formula are some constants, namely pressure at 1013 mbar, 0° Celcius = 273.15 Kelvin and 20.9% oxygen in the air we breathe (at least most of the time).

Remember!

A worksheet function in a cell can only return a value (or text). It can not change anything else like e.g. cell colour.

And take care that your function names are unique. If two functions have the same name, you are asking for trouble.

Make an addin

If you have made some worksheet functions (or macros/procedures) that you use often, you can save save them as an addin, so you can use them in all your workbooks.

The way I do it is to copy the functions to a VBA module in an empty workbook and then save the workbook as an addin.

You just pick "Save as" and select the right file type (Excel addin). Excel will automatically save it to the right folder in your user profile.

In the VBA editor you can protect your code with a password, if you like, but beware that there are programs out there designed to crack Excel passwords.

Next step is to activate the addin. In Excel 2003 you select "Functions" and "Addins" and select from the list.

In newer versions of Excel you select "Files", "Settings" and "Addins". Near the bottom of the page is a button next to a drop down list. Select Excel addins and click the button to get a list of available addins.

If you want to modify the addin at a later stage, you can do that in the VBA editor and save the changes.