Create a Function

In Excel, we already have a lot of functions. Still we have the opportunity to create more functions. When we do create a new function, it is probably with the intention to use it in many or all of our Excel Workbooks.

In order to create a new function, there are a number of conditions which need to be met:

1. The function code is written in VBA
2. This code is placed in a module of the project window
3. We must save the code as an add-in.

This all seems a lot harder than it actually is.

As an example, I create a function that calculates a discount of 10% if an associated number exceeds 100.

Let me start by writing the code in a module of the VBA project window.

To open the VBA window, we click this button in the “Developers” tab in the ribbon. You must already know this. Earlier version users should also know how to open it.

Double click “Module1” (1) in the Project Window.
If no modules are present in the project window, click the “Insert Module” button (2) in the toolbar.

Then we type the code in the code window.

This is the code we type:

Function Discount (Qty, Price)

When we create a function, we always start with the Function code. Then we give a function name, in this case I choose Discount. Next I open the brackets and type two arguments, separated by a comma, which must be completed. Then I close the brackets again.

‘Calculate a 10% discount when the condition is satisfied

This is a comment line, not code

If Qty > 100 Then

Discount = Quantity * Price * 0.1
Else
Discount = 0
End If

This is pretty straightforward. If the number is greater than 100, then the discount is equal to the number times 0.1 times Value or 10%. If this is not the case, then the discount is 0.

‘Rounding to two decimal places

Another line of comments, not code

Discount = Application.Round (Discount, 2)

With this line of code, the discount will be rounded to 2 decimal places

End Function

This is the end of the function.

Attention!
For all users of any version of Excel, the VBA code should always be in English. Whether you’re working with a Dutch version, a French version or Chinese version, it does not matter, the VBA code should always be in English.
Also note that I’m talking about the code, not the names you assign for Discount, Quantity or Price.

Next we have to save this code as an add-in

Save a Function as Add-in

Let me first explain what an add-in is. An add-in is a hidden workbook that consists mainly of code. The purpose of an add-in is that this code is saved in a workbook, and it automatically opens every time you open a workbook.

You can use it on each computer where an add-in is installed.

In the 2007 and 2010 versions of Excel, these files were saved with the extension “.Xlam” and in earlier versions with the extension “.XLA”.

To save an add-in file, choose “Save As …” and choose “Excel-Add-In (*.xlam)”.

In earlier versions of Excel, you use “Excel 97-2003 Add-In (*. Xla)”.

Next, open the “Excel Options” window. For the 2010 version you can find it under the backstage view. For the 2007 version you can find it under the Office Button. Choose the “Add-Ins” tab. In this tab you will see the newly created add-in. Click the “Start” button. In previous versions you can find this under the “Tools” button and the “Add-Ins”.

In the “Add-Ins”, select the add-in created, that is Discounts and click the OK button.

For your information:

The Discount.xalm file is added to the downloadable files.

If you want to use it, you must have it installed on your computer in Excel

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats