Excel VBA User-Defined Functions
User-Defined Functions
One of the advantages of VBA is that you can create your own
functions using macros. These functions can be called and used as other
functions in excel and use them. You
can create your own functions in VBA and then use them just like you use
Excel’s built-in functions, such as IF, AVERAGE. Once the custom or user-defined
function is created, one needs to know only the function name and its
arguments.
Few practices which are followed in UDF are as follows:
• Giving data types of arguments is not always necessary, but it is considered
a good practice
• You can look for the functions you have created by going to ‘User Defined
Functions’ in the ‘Formula’ toolbar and change their description so that
whenever you open this particular excel file, they are readily available for
your use.
Steps to declare the Syntax
Function Name (arguments,….) Name= Functionality End Function
• Declaring a custom function starts with the keyword ‘Function’ and ends with
‘End Function’
• Following keyword Function, we write the name of the function – in our case
we have named the function as Name
• Now, the functions will have some arguments which will be declared in the
brackets. It is optional.
• In the next line, we show how to perform the calculation or the functionality of the function.
Sharing UDFs
The extension or location where you store a User Defined Function affects the sharing capability of the file. The commonly used storage extensions are as follows:
- Personal.xlsb— Onestores a UDF in Personal.xlsb if it is just for their use and won’t be used in a workbook opened on another system.
- Workbook— One stores a UDF in the workbook if it needs to be distributed to many people.
- Add-in— Onedistributes a UDF via an add-in if the workbook is to be shared among a select group of people.
- Template— One store a UDF in a template if it needs to be used to create several workbooks, and the workbooks are distributed to many people.
Example 1: Let’s suppose you want to calculate the area of a circle – now area of a circle is ?r2 where the value of ? is 3.14 and r is the radius of the circle –
• Click on ‘Visual Basics’ and then click on ‘Module’
• In the program editor, you will write –
'function name is Area 'Here we just have one argument which is radius - double 'if you may recall is one of the data types we 'have in VBA - so we are telling VBA that this value can be too large Function Area(Radius As Double) ' the area is going to be calculated by ? r2 where the value of ? is 3.14 and r is radius which we want the end-user to fill in Area = 3.14 * Radius * Radius End Function
Output
Now go back to excel and call the function as you normally call any other function i.e., by using equal to the operator and the name of the function = AREA (Cell containing radius or value of radius can be entered manually).
Click on enter. You will notice, as per the function, the area has been calculated.
Example 2: Write a macro to define a UDF for adding two numbers in Excel.
• Click on ‘Visual
Basics’ and then click on ‘Module’.
• In the program editor, you will write –
'function name is ADD . 'Arguments are placed in parentheses after the name of the function. This example has two arguments: Number1 'and Number2 . 'Integer defines the variable type of the result as a whole number Function Add(Number1 As Integer, Number2 As Integer) As Integer 'ADD = Number1 + Number2 is the result of the function that is returned Add = Number1 + Number2 End Function
Output
In the Excel Sheet, call the function by using equal to the operator and the name of the function = ADD (Cell containing number 1 value that can be entered manually as well, Cell containing number 1 value).
Click on Enter, and you will get the sum of two numbers. This will work similarly, unlike Excel built-in SUM function.
Example 3: Write a macro using UDF to set the Current Workbook’s Name and File Path in a Cell
'No arguments are used in this function Function FullName() As String FullName = ThisWorkbook.FullName End Function
Output
