Create Excel VBA User Defined Function (UDF / custom function)

April 24, 2022March 1, 2021 by Abhishek Pandey

In this article, you learn how to build User Defined Function (Custom function) in Excel.

In Excel there are many inbuilt functions but sometimes we need output which can not be achieved by available inbuilt functions or may be very complex using inbuilt functions.

In such scenarios, you can build your own function known as User Defined Function (UDF) using VBA. User Defined Function also known as Custom Functions.

What is a function in VBA?

In VBA functions is code which outputs or returns some value by processing data. This output value can be a string, number or an array.

A VBA function may or may not have parameters.

This is in contrast to Subroutine or Procedure which performs some type of actions on a give object.

Max is example of inbuilt function in Excel. Behind the scene there are code lines which takes numbers and do processing on these numbers to return or output Max number.

For making text bold you press CTRL+B. Now here Subroutine or Procedure comes into play. When you press CTRL+B that Subroutine runs to make selected text bold.

In short in VBA context Functions returns value and Subroutine or Procedures performs action on an object.

Why create VBA User Defined Functions?

Most of the time you can get your required output using inbuilt Excel functions or combination of inbuilt Excel functions.

It is not wise to reinvent the wheel when there is an inbuilt function to suite your need.

But, there are cases where you need to build your own custom functions or VBA UDF. These cases are-

There is not inbuilt Excel function (direct or indirect/complex formula) which can give required output.

Example:- Currently there is not inbuilt Excel function for converting numbers into words like for 1 you want One output, for 22 you want Twenty Two output. For this type of scenarios you need to built VBA User Defined Function in Excel.

You can achieve your required output by using complex formula (combination of more than one inbuilt functions) or using an array formula. But they demand certain level of intellect effort or mastery over subject ,also time consuming, not very user friendly,easily transportable and readable.

For this type of scenarios also you can built Excel VBA User Defined Function. By building VBA UDF complexity and logic is taken care at behind in the code. And user is left with much more cleaner and readable direct function.

Example:- There is no inbuilt CircleArea functions in Excel. Yes, you can output circle radius like the below image in Excel.

But, what below image shows user have to know Circle area formula also by seeing only =(22/7)*A2*A2 one can not say what the output means.

Imagine you have formula like this =CircleArea(A2) where user have to input radius value only. Tell me which is more user friendly, I hope you got my point.

Structure or parts of VBA functions

In VBA a typical function starts with a Function keyword after that function name (FunctionName) is written with () brackets . At the end, End Function keyword is present which indicates end of that functions.

Between Function and End Function you write code line or logic in specific sequence.

It must be noted that in that there must be a line which assigns value to a function name. In the below image that line is FunctionName = someValue.

someValue can be a variable or expression or another function or literal hard value.

Function myFavNumber()
  myFavNumber = 6
End Function

The above code section is one of simplest form of function. Above functions name is myFavNumber and takes no arguments , outputs a number 6.

If you notice carefully in line myFavNumber = 6, myFavNumber keyword is same as function name.

Now at place of 6 you can write 3+3 or 3*2 or any variable name whose values is 6 or any function like myNum() which outputs 6.

Various styles (variants) of writing a VBA function

Since you already know output data type is a number hence you can declare output data type like below. Integer is one of the numeric data type.

Data type can be Boolean, Integer, Byte, Long, Double, Single, String, Date or Array.

Function FunctionName() as dataType

If you do not mention data type then it will Variant data type Implicitly. So Function myFavNumber() is same as Function myFavNumber() as Variant

Function myFavNumber() As Integer
  myFavNumber = 6
End Function

You can specify parameters mandatory and optional like below. Inputs are separated by commas.

Two mandatory inputs

Function FunctionName(Input1 as dataType, Input2 as dataType) as dataType

One optional input

Function FunctionName(optional Input1 as dataType) as dataType

One mandatory and one optional input

Function FunctionName(Input1 as dataType, optional Input2 as dataType) as dataType

You can make functions private to limit its use scope, by writing Private before Function keyword.

Private Function FunctionName()

Private Function myFavNumber() As Integer
  myFavNumber = 6
End Function

Creating simple User Defined Function in VBA

Creating Excel User Defined Function is easy, you just have to take care of some of the points.

VBA User Defined functions are little bit different from non VBA function, VBA UDF function code need to be placed at specific place in VBA IDE.

Where to write User Defined Function to be able to use in a cell

You must place code of VBA User Defined Function (UDF) in Standard Module only, to be able to use in a cell.

If you place VBA UDF code other than Standard Module then you can not use it a cell of Excel sheet.

To create a Standard Module open VBA IDE by pressing ALT+F11.

In VBA IDE go to Insert tab and click on Module to create a new standard module. Now copy paste below code section in that module.

Make sure to save as your workbook either in .xlsb (binary) or .xlsm (macro) format.

Note:- You can create one or more number of Modules (standard module). You can group or organize same type of function in a specific module like Functions related to Text modification in one module and Functions related to numbers in other module.

Note:- Also no two VBA UDF can have same name whether in same standard module or in different standard module of same workbook.

You can also change default module name to something else which best describes type of functions it has. Example if a module has Text modification related functions then you can name a module to something like TextModify.

LINK Changing worksheet code name or module name in VBA IDE

Also never make VBA User Defined Function Private, if you make it private then you can not use that function in a cell.

So writing it in a Standard Module and Keeping it public is two main criterias for creating VBA User Defined Function in Excel.

Create your own VBA custom function named CircleArea

Do exactly below steps as said
Create one Standard module in VBA IDE
Copy paste below code section in that standard module

Now come to sheet of an Excel workbook in which you have just pasted VBA UDF code to see the magic.

Note:- UDF must be written in standard module otherwise you can not use function in Excel cell.

Function CircleArea(radius As Long) As Long
  CircleArea = (22 / 7) * radius * radius
End Function

When you write =CircleArea in a cell this function appears in an intellisense list. Now just input radius value to get circle area for that radius.

In the above VBA UDF (User Defined Function) CircleArea is name of function.

radius As Long means it takes one input which is a number of long data type.

()As Long means, it outputs numeric long data type.

Private VBA function and public VBA function with Examples

The below video is about Public or Private Procedures in VBA but conceptually it is same for Public or Private Functions also.

Here I will discuss how, what, why to make a function public or private.

Public Function – By default any VBA function you create is public in nature. Here public means you can refer or call this function in Procedures or Functions other modules also along with parent module in which it is written.

Visually you can indentify any function as a public function when Private keyword is missing before Function keyword in the very first line.

Private function – To make a function private you just put Private keyword before Function keword in the very first line.

Private functions can only be referred or called in Procedures or Functions of the parent module in which it is written not in other modules. So its scope is limited to parent module only.

If make VBA UDF as private function then you can not use in an Excel cell or range.

Just experiment with CircelRadius UDF put Private keyword like the below, see whether you can use this function in a cell or not.

You will see private function is not be visible in intellisense list as you type, you have manually type entire function name to be able to use it.

Private Function CircleArea(radius As Long) As Long
  CircleArea = (22 / 7) * radius * radius
End Function

Why to make a VBA function public or private?

Here I will use analogy suppose you have a shop ( a module). There are various display racks or dispense machine (functions or subs) in which good are placed.

Customers (function or sub from other module) comes and interact (buying) with these goods persent in various racks also with counter person or cashier.

Also there are things where you don’t want customer interaction like cash vault,storage room (private function or sub) and you make arrangements for this.

Now it does not make sense to make these display racks as prohibited area for customer and cash vault and storage room accessible to customers.

Only you and your staff interact with cash vault and storage room.

Just like above analogy some functions or sub only used or called or referred within parent module only so you make them private, whereas some functions or sub used or called or referred by functions or sub of other modules so you keep them public.

Private keyword just clears intent that it is used by functions or procedures of a parent module only. So making changes to Private function will affects only functions or Sub of that modules only.

Also private functions or procedures are not visible in intellisense list of other module when typing.

In case of public function you have to be a bit more cautious while modifying it because it will affect functions or procedures other modules also when it get called or referred in other modules.

Many ways to use VBA functions

VBA functions are not only for creating User Defined Functions but serve other usages too. They can be used to support other functions or can be referred by other functions.

They also help in supporting logic flow in procedures.

Calling VBA functions in Procedures and Functions (same workbook)

You can call VBA functions in VBA Procedures for controlling logic flow using If – then or Case statements.

Below two code sections are there. First is VBA function named sheetExits which takes sheet tab name as input and outputs TRUE is sheet exist in the parent workbook (in which code is written) else FALSE if sheet does not exist.

Now second code section is VBA procedure named process , in this produre in If statement sheetExits is called or used for determining course of action based on function output value.

Now in the below case both function and procedure resides in the same module, hence there is no mention of module before function name.

But, suppose sheetExits is written in Thisworkbook module then to call this function in the procedure of another module you will call like this Thisworkbook.sheetExits(sheetName) . Here you will mention the module name with dot or period.

Function sheetExits(sheetName As String) As Boolean
   Dim sht As Worksheet
   On Error Resume Next
       Set sht = Worksheets(sheetName)
   On Error GoTo 0
   
   If sht Is Nothing Then
        sheetExits = False
   Else
        sheetExits = True
   End If
End Function

Sub process()
  Dim shtNm As String
  shtNm = "City"
  If sheetExits(shtNm) Then
    'code for doing things if sheet is already there
  Else
    'code when sheet is not present like create sheet
    Worksheets.Add
    ActiveSheet.Name = shtNm
  End If
End Sub

Using custom function in Excel sheet cell

You have already seen we can use any public VBA function written in a Standard Module as VBA User Defined Function (Custom formula). This VBA UDF we can use in Excel sheet Cell just like any built in Excel function.

Below code section is of VBA UDF function named Abbreviation. Just try to implement below function yourself and use it in an Excel sheet cell.

If you are not able to do then just click the below video link.

Abbreviation Function In Excel (VBA UDF for Short form | Abbreviation | Acronym)

Function Abbreviation(str As Variant) As String
  Dim i As Integer, newStr As String
  Dim wrdArr As Variant  ''Word Array after splitting
  
  newStr = ""
  wrdArr = Split(Trim(str), " ")
  For i = LBound(wrdArr) To UBound(wrdArr)
    newStr = newStr & Mid(wrdArr(i), 1, 1)
  Next i
  
  Abbreviation = UCase(newStr)
End Function

Calling VBA function and UDF of another workbook

Using other workbook UDF in a cell

You can use other workbook UDF function in differenct workbook cell. But make sure both workbooks are open.

Suppose in worbook named myBook.xlsb you have written UDF function named test.

Then you can use this test UDF in another workbook cell like below

=’myBook.xlsb’!test()

Calling another workbook function

You can call other workbook function like below

Application.Run(“‘myBook.xlsb’!myFavColor”)

Here myBook.xlsb is workbook name with extension and myFavColor is function name.

Make sure to keep open both workbook in which function is written and the workbook which is calling that function.

Leave a Comment Cancel reply