Creating

Maybe your formulas are just getting too long and too complicated.
Long, complicated formulas are a nightmare to understand especially when they are returning the wrong answer.
It is recommended that you are familiar with Macros and are at least comfortable Recording Macros before trying to write your own custom functions.


What do I need to consider ?

Decide how many arguments you need to pass to the function
Also spend a bit of time to decide the datatypes and the possible ranges of these arguments.
Do you want to include error handling in your function. You may want to return an error value when certain parameters are entered.
Decide on the datatype that the function will return, whether an Integer, Long etc.


Where is the best place to keep it ?

Where is the best place to store your user defined functions.
Is your function only relevant to one workbook or would it be useful if was available to all your workbooks.
The big advantage with keeping your functions in an Excel add-in is that you will not have to precede the name of the function with the name of the workbook, where as you will if the function is stored in another workbook.
When creating functions they must be placed in a normal VBA module and not a worksheet code module.
These functions are created in Visual Basic. You can write a function that maintains its current value.
Storing them in your Personal.xls - Ensure that they are declared in a separate code module and not in a sheet module.


How do I use a User Defined Function ?

The exact syntax for your function will vary depending on how the user defined function has been saved.
There are a number of ways you can save and access your user defined functions:

1) Inside the Active workbook.

2) From Another workbook.

3) Referring to a referenced workbook.

4) Inside an Excel add-in.

For all these different scenarios we will be using the SHEETNAME function.


Inside the Active Workbook

The quickest way to add and use a user defined function is just to put the code into the corresponding workbook.
When user defined functions are put into the actual workbook you can access it just by using the name of the function.


 

The major disadvantage is that none of your other workbooks can access this function.
However this is the best approach if there is a chance the workbook will be emailed to someone else.


From Another Workbook

It is possible to use a user defined function that has been defined in another workbook.
The only draw back of this approach is that the other workbook must be open for this function to work.


 


Referring to a Referenced Workbook

You can save yourself from prefixing the user defined function with the name of the workbook by creating a permanent reference to the other workbook.
This can only be done from within the Visual Basic Editor.
Before you add the reference though you need to change the VBA Project name of the workbook that contains your functions.
Select the relevant project in the VBA Editor and select (Tools > VBAProject Properties).
Change the project name to something you will more meaningful which you will remember and press OK.


 

Select the project for the other workbook, i.e. the one you want to be able to use the functions in.
Select (Tools > References) and find your entry in the References dialog box.
Make sure this entry is ticked and press OK.


 

This will create a reference from your workbook to the workbook that contains the user defined functions.
You will see the corresponding entry added to the References node of that particular project.
Once this reference has been set up you can access it just by using the name of the function.


 


Inside an Excel Add-in

If the function has been saved inside an Excel add-in which is currently loaded then you can type the name of the function directly into a workbook as you would any other function.
For more information regarding add-ins please refer to the Creating an Excel Add-in page.


 


Problems and Errors

If a workbook contains a user defined function which cannot be found then #NAME? is displayed in the cell.
When you select this cell you will be able to see a "full" reference to the location of the user defined function the last time the workbook was saved.
If you are using an add-in and the user defined function is not being recognised prefix it with the full folder path.


 


Custom Worksheet Functions are inconsistent

Sometimes these functions are in lowercase, sometimes uppercase and sometimes mixed case.
The case that will be used seems to be determined by the case used the very first time the function is called from Excel.


How to Access a User Defined Function

You can access your user defined functions from:
(Insert > Function) dialog box. User defined function appear in the User Defined category by default.
Paste Function on the standard toolbar
where on the ribbon


Remember that functions will not appear in the (Tools > Macro > Macros) dialog box.
Your functions will only appear in the (Insert > Function) dialog box if they have been declared Public (this is the default if not specified).
Any functions declared as Private will not appear in the (Insert > Function) dialog box.
You should declare all your user defined functions as Public and any other subroutines or functions as Private.


Differences between a Macro and a Custom Function

A Macro does not require any arguments passed into it where as a custom function probably will.
A Macro uses the keyword "Sub" in its declaration, where as a function uses the keyword "Function".
A Macro allows you to execute a sequence of simple commands where as a function allows you to perform a sequence of calculations.
A User Defined Function cannot be recorded using the Macro Recorder.


There are a few limitations

Remember that custom functions are designed to perform calculations and return a value. They are not to be used in place of macros.
Custom worksheet functions do have a few limitations.

5) They cannot change the contents of any other cells (only the active cell).

6) Function names must adhere to certain rules for example you can't use a name that resembles a cell address, for example B22.

7) They cannot copy and paste cells to other parts of a worksheet.

8) They cannot change the active worksheet.

9) They cannot apply formatting to the active cell.

If you want to change the cell formatting when different values are entered (or displayed), you should use either Conditional Formatting or use the Change Event method.

10) They cannot open additional workbooks.

11) They cannot be used to run macros the using Application.OnTime.

A custom function can display a message box or input box.
A custom function can make changes to cell comments.


Example - REVERSE

This user defined function returns the cell contents with all the characters reversed.

Public Function REVERSE(ByVal sCellContents As String) As String 

   If Application.WorksheetFunction.IsNonText(sCellContents) = True Then
      REVERSE = VBA.CVErr(xlCVError.xlErrNA)
   Else
      REVERSE = VBA.StrReverse(sCellContents)
   End If

End Function

For more details on this function, please refer to the REVERSE page.


MMULT and SUMPRODUCT - handle all the array computations so it is not necessary to worry about the specific dimensions of the array in the code or to process individual elements


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.

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


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.


Defining the Category

You must execute some VBA code when the workbook (or add-in) that contains the function is opened.
For more information refer to the Function Categories page.


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:

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


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.

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

Pressing F9, (Shift + F9) or (Ctrl + Shift + F9) will not recalculate worksheet functions unless they contain Application.Volatile or Application.Volatile(True).


fastexcel.wordpress.com/2011/05/25/writing-efficient-vba-udfs-part-1/
fastexcel.wordpress.com/2011/06/06/writing-efficient-vba-udfs-part-2/


Returning an Valid Error

It is possible to have your custom function return error values
In order to be able to return an error value from a custom function the datatype returned by the function must be Variant.
There will also be a slight performance loss by returning a Variant but returning a sensible error value might outweigh this.

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

If you try and return an error value from a function that does not return a Variant the function will return #VALUE.


# DIV/0!

VBA.CVErr(xlCVError.xlErrDiv0)  'Error Number 2007  


# N/A

VBA.CVErr(xlCVError.xlErrNA)  'Error Number 2042  


# NAME ?

VBA.CVErr(xlCVError.xlErrName)  'Error Number 2029  


# NULL!

VBA.CVErr(xlCVError.xlErrNull)  'Error Number 2000  


# NUM

VBA.CVErr(xlCVError.xlErrNum)  'Error Number 2036  


# REF!

VBA.CVErr(xlCVError.xlErrRef)  'Error Number 2023  


# VALUE!

VBA.CVErr(xlCVError.xlErrValue)  'Error Number 2015  


Returning Arrays

This example will accept a cell range and return an array of values plus 10


Public Function ReturnArray(ByVal oRange As Range) As Variant 
Dim myarray As Variant
Dim irow As Integer
   myarray = oRange.Value
   For irowno = 1 To Ubound(myarray,1)
      myArray(irowno,1) = myarray(irowno,1) + 10
   Next irowno
   ReturnArray = myarray
End Function


Excel > Cells & Ranges > VBA Code > Working with Arrays
Custom functions can also be used to significantly shorten your formulas. However custom functions are often much slower to calculate than the built-in functions.
Include screen shots for example
When your custom worksheet function is re-calculated it behaves just like an Excel worksheet function and is only re-calculated when any of its arguments are modified.
Include a log file example as well


Passing in Arrays

You can use ParamArray refer to MEDIANIFS
You can also use user defined worksheet functions in your regular VBA code.


Important

If you have written a custom worksheet function and do not want to precede the function name with the workbook name then save your workbook as an Excel addin (.xlam).
A custom function will usually have one or more "arguments" although it can be used to just return a value.
Custom Functions will always appear in lowercase in your worksheets.
Your custom function named cannot contain spaces but they can contain the underscore character. The preferred method though is to actually use capital letters at the start of each new word (e.g. "CapitalLetter").
Custom Functions are also known as User Defined Functions (or UDF's).
Use Excel worksheet functions where possible as these are always faster than equivalent VBA functions.
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).
You cannot include full stops in your VBA Project Names.
You cannot create a reference to an unsaved workbook.


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext