BET: Microsoft Excel Functions User Defined

Leading the way in Microsoft Office Development

Home

Excel

Word

Microsoft Excel > Functions User Defined > Writing Functions

< Previous | Next >

Step 1 - Write the Function

	Open up the Visual Basic Editor either by pressing (Tools > Macro > Visual Basic Editor) or by pressing (Alt + F11).
	Find the corresponding project for the workbook you are going to add the function to. Insert a new Code Module by selecting (Insert > Module).
	Remember to enter your subroutine as a Function and not as a procedure (i.e. Sub).
	Write your function. The simple function below returns a given character as a capital letter.
	Ensure they are declared as Public and written in an actual module as opposed to a worksheet module.

1
2
3
4
5
6
7

Public Function BET_CapitalLetter(ByVal sChar As String) As String
   If (Asc(sChar) >= 97) And (Asc(sChar) <= 122) Then
      BET_CapitalLetter = Chr(Asc(sChar) - 32)
   Else
      BET_CapitalLetter = sChar
   End If
End Function

Step 2 - Using the Function

	Any custom function will be added to the User Defined category by default but can be added to a specific function category if you want.
	Refer to them by workbook name and then function name
	Can create a reference to the workbook or create an Excel add-in to avoid entering the prefix.
	Have to prefix the function with Personal.xls. To get round this problem you can set a reference from your current workbook to the workbook that contains the custom functions.

	You can then use the function as normal (without the prefix). To create a reference open the VB Editor and select (Tools > References). Alternatively you could just create an Excel add-in.
	SS - References dialog
	These custom functions can then appear in the (Insert > Function) dialog box.

Step 3 - Defining the Category

	There is no direct way to add a custom function to a function category, other than User Defined.
	You must execute some VBA code when the workbook (or add-in) that contains the function is opened.
	The table below lists the category names with their corresponding numbers.

0	All (no specific category)	8	Logical
1	Financial	9	Information
2	Date & Time	10	Commands (normally hidden)
3	Maths & Trigonometry	11	Customizing (normally hidden)
4	Statistical	12	Macro Control (normally hidden)
5	Lookup & Reference	13	DDE / External (normally hidden)
6	Database	14	User Defined (default)
7	Text	15	Engineering (Analysis ToolPak)

You can define the category for your functions when the workbook or add-in is opened.

8
9
10

Private Workbook_Open()
Application.MacroOptions macro:="Macro Name", Category:=2
End Sub

Step 4 - Using the Function

	Sometimes when you create and use a custom worksheet function it will appear in lowercase, sometimes mixed case and sometimes upper case.
	It all depends on which case is used the first time you use the custom worksheet function.
	If you include Application.Volatile in your custom function then the function will be recalculated every time any value changes in the worksheet.
	It is possible to have your custom function return a true error value:

11
12
13

Public Function BET_Error() As Variant
BET_Error = VBA.CVErr(xlErrValue)
End Function

Step 5 - Forcing a Recalculation

You can force a custom worksheet function to recalculate whenever any cell in the worksheet is recalculated by placing a "Application.Volatile (True)" at the top of your function.

14
15
16
17
18
19
20
21
22

Public Function BET_CapitalLetter(ByVal sChar As String) As String
   Application.Volatile (True)
'you could also use "Application.Volatile True" or even just "Application.Volatile" since True is the default.
   If (Asc(sChar) >= 97) And (Asc(sChar) <= 122) Then
      BET_CapitalLetter = Chr(Asc(sChar) - 32)
   Else
      BET_CapitalLetter = sChar
   End If
End Function

Step 6 - Things to Remember

		It is not possible to add individual descriptions to each argument - or is it ??
		It is possible to display help for arguments of a custom function in the Paste Function dialog box. This can be done using the Object Browser and will be effective even if you convert the workbook to an add-in.
		Any custom worksheet functions that you write must be strictly "passive" and cannot change the active cell or apply formatting.
		To return a real error from a function, use CVErr() which converts an error number into the real error. There are built in constants for the common worksheet errors.
		You can write custom functions that return arrays (i.e. as in array formulas).

< Previous | Top | Next >

Leading the way in Microsoft Office Development

Step 1 - Write the Function

Open up the Visual Basic Editor either by pressing (Tools > Macro > Visual Basic Editor) or by pressing (Alt + F11).

Find the corresponding project for the workbook you are going to add the function to. Insert a new Code Module by selecting (Insert > Module).

Remember to enter your subroutine as a Function and not as a procedure (i.e. Sub).

Write your function. The simple function below returns a given character as a capital letter.

Ensure they are declared as Public and written in an actual module as opposed to a worksheet module.

Step 2 - Using the Function

Any custom function will be added to the User Defined category by default but can be added to a specific function category if you want.

Refer to them by workbook name and then function name

Can create a reference to the workbook or create an Excel add-in to avoid entering the prefix.

Have to prefix the function with Personal.xls. To get round this problem you can set a reference from your current workbook to the workbook that contains the custom functions.

You can then use the function as normal (without the prefix). To create a reference open the VB Editor and select (Tools > References). Alternatively you could just create an Excel add-in.

SS - References dialog

These custom functions can then appear in the (Insert > Function) dialog box.

Step 3 - Defining the Category

There is no direct way to add a custom function to a function category, other than User Defined.

You must execute some VBA code when the workbook (or add-in) that contains the function is opened.

The table below lists the category names with their corresponding numbers.

You can define the category for your functions when the workbook or add-in is opened.

Step 4 - Using the Function

Sometimes when you create and use a custom worksheet function it will appear in lowercase, sometimes mixed case and sometimes upper case.

It all depends on which case is used the first time you use the custom worksheet function.

If you include Application.Volatile in your custom function then the function will be recalculated every time any value changes in the worksheet.

It is possible to have your custom function return a true error value:

Step 5 - Forcing a Recalculation

You can force a custom worksheet function to recalculate whenever any cell in the worksheet is recalculated by placing a "Application.Volatile (True)" at the top of your function.

Step 6 - Things to Remember

It is not possible to add individual descriptions to each argument - or is it ??

It is possible to display help for arguments of a custom function in the Paste Function dialog box. This can be done using the Object Browser and will be effective even if you convert the workbook to an add-in.

Any custom worksheet functions that you write must be strictly "passive" and cannot change the active cell or apply formatting.

To return a real error from a function, use CVErr() which converts an error number into the real error. There are built in constants for the common worksheet errors.

You can write custom functions that return arrays (i.e. as in array formulas).