Knowledge Base/VBA/User Defined Functions
Creating user defined functions in Excel
The user can extend Excel's collection of built-in functions, such as SUM, AVERAGE and IF with user defined functions. Just like the built-in functions, these can be used in cell formulae.
- Open a new spreadsheet.
- Press Alt+F11 to open the Visual Basic Editor.
- Insert a new module (Insert > Module).
- Edit the new module. Enter the following code:
Function Hello(name As String) Hello = "Greetings, " & name End Function
- Save the spreadsheet (Ctrl+S).
- Switch to the main spreadsheet window. Try typing the following formula in a cell:
You will see "Greetings, Paul" in that cell.
In a similar fashion one can create more complicated functions.
To make user defined functions available to other workbooks, you can save them as an add-in.
- Switch to the main spreadsheet window (not Visual Basic Editor).
- Open the "Save As..." dialog (File > Save As).
- In "Save as type" select "Microsoft Office Excel Add-In (*.xla)"
- Enter an appropriate file name (e.g. hello.xla) and click "Save".
To load an add-in, follow the following steps:
- Select Tools > Add-Ins...
- In the Add-Ins dialog box, click on "Browse..."
- Browse to your *.xla file (e.g. hello.xla).
- Make sure the add-in is ticked in the "Add-Ins available" list box (by default, it will be).
- Click "OK" to close the Add-Ins dialog.
The functions from your *.xla file are now available for you to use.
- This page was last modified on 1 August 2007, at 23:21.
- This page has been accessed 7,958 times.