Knowledge Base/VBA/User Defined Functions

 

From Quantitative Finance

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:

=Hello("Paul")

You will see "Greetings, Paul" in that cell.

In a similar fashion one can create more complicated functions.

Creating add-ins

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.

 
 

	Enter your search terms Submit search form
	Web www.bilokon.co.uk