We use cookies to improve our site and your experience. By continuing to browse on this website you accept the use of cookies. Find out more about our cookes policy.

How To Excel
User defined functions (UDFs) in Excel

Excel's User defined functions give the user the ability to create their own custom functions. These UDFs can then be used in the same way as normal formulae such as SUM().

UDFs can either be attached to a workbook or to your personal workbook.

How to create and use UDFs

  1. In Excel open the Visual Basic Editor (VBE). (Alt + F11).
  2. Within VBE insert a new module in either your personal workbook or the workbook you are working on.
  3. Enter your UDF code.
  4. Save in VBE and close VBE window.
  5. In the Excel workbook, type in your new formula.

Things to consider

  • If the UDF is attached to the workbook it will only be available in that woorkbook. 
  • Using UDFs in a workbook will flag the workbook as containing macros. This may be an issue for file sharing. 
  • If the UDF is saved in your personal workbook the functional will not be available to other users sharing the workbook containing the formula. 
  • UDFs are often slower than standard Excel formulae.

A collection of our favourite UDFs

Below is the code for some of our favourite UDFs:

IsFormula
=Function IsFormula(Check_Cell As Range)
'=IsFormula(A1)
IsFormula = Check_Cell.HasFormula
End Function

 

Eval
Function eval(ref As String)
'=Eval(A1)
Application.Volatile
eval = Evaluate(ref)
End Function

 

FText
Function FTEXT(f As Range)
'''''''''''''''''''''''''''''''''''
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=62
'=FTEXT(A1)
'''''''''''''''''''''''''''''''''''
If f.HasFormula Then
FTEXT = f.Formula
Else: FTEXT = f
End If
End Function

 

Countwords
Function CountWords(rRange As Range) As Long
Dim rCell As Range, lCount As Long
'''''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid.com'''''''''''''''''''
'Count whole words
'=CountWords(A1) OR =CountWords(A1:A10).
'''''''''''''''''''''''''''''''''''''''''
For Each rCell In rRange
lCount = lCount + _
Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1
Next rCell
CountWords = lCount
End Function 

 

Thoughts or questions inspired by this article? Call or email to share them with us.

Call +44 1225 350730

Email us