Leading the way in Microsoft Office Development

Step 1 - How do I use a User Defined Function ?

The exact syntax for your function will vary depending on how the user defined function has been saved.

There are a number of ways you can save and access your user defined functions:

1) Inside the Active workbook.

2) From Another workbook.

3) Referring to a referenced workbook.

4) Inside an Excel add-in.

For all these different scenarios we will be using the SHEETNAME() function.

Step 2 - Inside the Active Workbook

The quickest way to add and use a user defined function is just to put the code into the corresponding workbook.

When user defined functions are put into the actual workbook you can access it just by using the name of the function.

The major disadvantage is that none of your other workbooks can access this function.

However this is the best approach if there is a chance the workbook will be emailed to someone else.

Step 3 - From Another Workbook

It is possible to use a user defined function that has been defined in another workbook.

The only draw back of this approach is that the other workbook must be open for this function to work.

Step 4 - Referring to a Referenced Workbook

You can save yourself from prefixing the user defined function with the name of the workbook by creating a permanent reference to the other workbook.

This can only be done from within the Visual Basic Editor.

Before you add the reference though you need to change the VBA Project name of the workbook that contains your functions.

Select the relevant project in the VBA Editor and select (Tools > VBAProject Properties).

Change the project name to something you will more meaningful which you will remember and press OK.

Select the project for the other workbook, i.e. the one you want to be able to use the functions in.

Select (Tools > References) and find your entry in the References dialog box.

Make sure this entry is ticked and press OK.

This will create a reference from your workbook to the workbook that contains the user defined functions.

You will see the corresponding entry added to the References node of that particular project.

Once this reference has been set up you can access it just by using the name of the function.

Step 5 - Inside an Excel Add-in

If the function has been saved inside an Excel add-in which is currently loaded then you can type the name of the function directly into a workbook as you would any other function.

For more information regarding add-ins please refer to the Creating an Excel Add-in page.

Step 6 - Problems and Errors

If a workbook contains a user defined function which cannot be found then #NAME? is displayed in the cell.

When you select this cell you will be able to see a "full" reference to the location of the user defined function the last time the workbook was saved.

If you are using an add-in and the user defined function is not being recognised prefix it with the full folder path.

Step 7 - Things to Remember

You cannot include full stops in your VBA Project Names.

You cannot create a reference to an unsaved workbook.