How to use and store custom functions in Excel

This tutorial delves into the practical application and efficient management of custom Excel functions (UDFs). We'll cover various usage scenarios, including embedding UDFs within worksheets and VBA code, and demonstrate how to streamline UDF access and storage using Excel add-ins.

Utilizing UDFs in Excel

Once your UDFs are tested, integration into Excel formulas or VBA code is straightforward. For example, the formula =GetMaxBetween(A1:A6,10,50) directly employs a UDF. UDFs can also be combined with standard Excel functions; for instance, =CONCATENATE("Maximum value between 10 and 50 is ", GetMaxBetween(A1:A6,10,50)) adds text to the UDF's output.

Similarly, =INDEX(A2:A9,MATCH(GetMaxBetween(B2:B9,F1,F2),B2:B9,0)) leverages a UDF within an INDEX/MATCH function.

Remember, UDFs are limited to returning values; they cannot execute other actions.

UDFs within VBA

UDFs seamlessly integrate into VBA macros. The following macro highlights the cell containing the maximum value (between 10 and 50) within the active column:

Sub MacroWithUDF()
  Dim Rng As Range, maxcase, i As Long
  With ActiveSheet.Range(Cells(ActiveCell.CurrentRegion.Row, ActiveCell.Column), Cells(ActiveCell.CurrentRegion.Rows.Count   ActiveCell.CurrentRegion.Row - 1, ActiveCell.Column))
    maxcase = GetMaxBetween(.Cells, 10, 50)
    i = Application.Match(maxcase, .Cells, 0)
    .Cells(i).Interior.Color = vbRed
  End With
End Sub

This macro utilizes the GetMaxBetween UDF.

Nested UDFs are also possible. For example, SpellGetMaxBetween, a UDF that uses both GetMaxBetween and SpellNumber (a hypothetical UDF converting numbers to text), demonstrates this capability.

Accessing UDFs Across Workbooks

Using UDFs across workbooks requires careful consideration. The simplest approach is to prepend the workbook name to the function call (e.g., =My_Functions.xlsm!GetMaxBetween(A1:A6,10,50)). Alternatively, consolidating all UDFs into a single workbook and copying them as needed is another option, though less efficient for large projects.

Creating and Using Excel Add-ins

The most effective method is creating an Excel add-in (.xlam). This offers several advantages: one-time connection, automatic loading, enhanced security, and easy sharing.

Add-in Creation and Connection

1. Create the Add-in: Save a new Excel workbook as an Excel Add-in (.xlam). The default location ( C:\Users\[Your_Name]\AppData\Roaming\Microsoft\AddIns) is recommended.

2. Connect the Add-in: In Excel Options, under Add-Ins, select "Excel Add-ins" and click "Go." Browse to and select your .xlam file.

3. Adding UDFs to the Add-in: Open the VBA editor (Alt F11), insert a module into your add-in project, and add your UDF code.

Remember to share the add-in file with anyone who needs to use your custom functions. This ensures consistent access and avoids the limitations of other methods.

The above is the detailed content of How to use and store custom functions in Excel. For more information, please follow other related articles on the PHP Chinese website!