Personal tools
User menu

Knowledge Base/VBA/User Defined Functions

From Thalesians

Jump to: navigation, search

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.

  • This page was last modified on 1 August 2007, at 23:21.
  • This page has been accessed 7,958 times.