UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Add A User Defined Function To Excel    
Add A User Defined Function To Excel

Sometimes the built-in functions in Excel aren't quite enough for you to do a specific task. That's where User Defined Functions can be helpful. You can write them yourself in Visual Basic for Applications (VBA), and let them perform the task you need using all of the tools that VBA offer.

This article assumes you already have a function already written, but you don't know how to make it available for use in your workbook.

You can attach a User Defined Function to a workbook, or you can make it available to you all of the time.

Option 1: Only One Workbook

Article Information
Excel 2003 and Prior

Tools/Macro/Visual Basic Editor (Alt-F11)

Excel 2007 and Later

Developer Tab/Code Group/Visual Basic (Alt-F11)

Let's say you have a really specialized function that only needs to be used in one workbook.

1. Open that workbook, then go to your Visual Basic Editor.
2. Hit CTRL-R to open the Project Explorer, if necessary.

Look for the VBAProject that has the name of the file you want to include your function with.

3. Click the "+" next to the project name to see Excel Objects inside that project.
4. Right-click one of the Objects, and select Insert->Module from the pop-up menu that appears.

A new code module will be added to the project. Double-click the module to view it in the main window of the editor.

5. Place the code for your User Defined Function in this code module.

Note: If you want to rename this new code module, just remember that you should not name the module with the same name as the function.

6. Click the Save button on the toolbar to save the workbook.

At this point, your User Defined Function should be available by clicking the Insert Function button (The one with fx on it) next to your formula bar. Select "User Defined" for the category, and your function should be visible.

Option 2: Available Always (On This Computer, Anyway)

Let's say the function is something more broadly useful that you may want to use on a regular basis. In that case, you should save it in your PERSONAL.XLSB file, which opens in the background every time you open Excel.

1. Open that workbook, then go to your Visual Basic Editor.
2. Hit CTRL-R to open the Project Explorer, if necessary.
If you don’t have a project named “PERSONAL.XLSB” in your list of projects:
A. Go back to your main Excel window
B. Click “Record Macro” on the Developer tab of the ribbon
C. Select “Personal Macro Workbook” for the “Store macro in” combo box
D. Click OK
E. Click on two different cells in the current workbook
F. Click “Stop Recording” on the ribbon
G. Return to the editor window – you should now have PERSONAL.XLSB
(Basically this process records a 'dummy' macro into your PERSONAL.XLSB file. Since you don't have that file, this process will force Excel to create one for you.)
3. Click the "+" next to PERSONAL.XLSB to see Excel Objects inside that project.
4. Right-click one of the Objects, and select Insert->Module from the pop-up menu that appears.

A new code module will be added to the project. Double-click the module to view it in the main window of the editor.

5. Place the code for your User Defined Function in this code module.

Note: If you want to rename this new code module, just remember that you should not name the module with the same name as the function.

6. Click the Save button on the toolbar to save the workbook.

At this point, your User Defined Function should be available by clicking the Insert Function button (The one with fx on it) next to your formula bar. Select "User Defined" for the category, and your function should be visible.

Exporting and Importing UDFs

It is a good idea to put each UDF in its own module or to bundle similar UDFs into a module.

In the VB editor, select the module you wish to export.

In the Properties Window change the name of the module to something that you can remember. I usually keep the prefix “Mod.”

image:ModMyName.jpg

Then select File -> Export File

This will bring you to the directory where Excel is installed. I prefer to keep this folder “clean” so I store my modules in a separate directory and make a shortcut to that directory in the Excel folder.

image:ModExport.jpg

Navigate to the folder where you wish to keep your modules and click the save button.

To import a module, open the VB Editor and click on File -> Import File.

Navigate to the folder where you keep your modules. Select the module you want, and click on Open. The module will be installed in the new workbook.

You can build a library of modules for frequently used functions and even copy the bas files to other computers.

When designing modules, it is probably a good idea to design them so as to accept parameters so they can “plug and play” with the rest of your code without having to recode what is in the module itself.

For example, the following module is called SubSQL (substitute SQL). It has two subsections: SubSQL and SuperCat.

SuperCat is a user-defined function used on the spreadsheet that reads a an Excel table column (or any other range) and concatenates the contents into a SQL statement in a single cell. I usually give the cell containing the function a name like “SQL_Model.”

SubSQL refreshes the query with the newly-created SQL statement.

Since the two functions work hand-in-hand, I store them in the same module.

After loading this module, all I have to do to call a query anywhere else in the code (even in another module) is to use SubSQL query_name, command_string_name such as SubSQL, “qry_model”, “SQL_Model”

CODE

Sub SubSQL(ConnectionName As String, CommandString As String)

ActiveWorkbook.Connections(ConnectionName).ODBCConnection.CommandText = Range(CommandString)

ActiveWorkbook.Connections(ConnectionName).Refresh

End Sub

Function SuperCat(MyRange As Range) As String
Dim cl As Range
Dim SuperString As String

SuperString = ""

For Each cl In MyRange
   SuperString = SuperString & cl.Value & " "
Next

SuperCat = SuperString

End Function
Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 8,791 times.  This page was last modified 13:36, 8 November 2014 by dflak. Contributions by doctor9 and Glenn Lloyd  Disclaimers