How to Create User Defined Functions in Excel VBA

User Defined Functions in Excel VBA
In Excel VBA , We can create our own Functions known as User Defined functions , as Excel Built-In Functions to perform several tasks easily.

Creating user-defined functions allows you to write simpler code instead of having to use complicated formulas that come with MS Excel,thus allows you to accomplish a job easier. The user-defined functions can be entered into any cell or on the formula bar of the spreadsheet just like entering the built-in formulas of the MS Excel spreadsheet.

Syntax of User-defined Function :
Public Function FunctionName (Arg As DataType,……….) As DataType
or
Private Function FunctionName (Arg As DataType,……….) As DataType


* Public indicates that the function is applicable to the whole project while Private indicates that the function is only applicable to a certain module or procedure.

Here :
'Arg' is the Argument Variable that you are passing into the Function.
In order to create a user-defined function in Excel VBA, you need to go into the Visual Basic Editor in MS Excel Spreadsheet.
In the Visual Basic Editor, click on Insert on the menu bar to insert a module into the project, as shown in Figure.

---------------------------------------------------------------------------------------------------------------------

Example - I :

Let us see the below Example where we are creating a User-Defined-Function called "Grade".

The grade will automatically compute examination grades based on the marks that a student obtained. The code is shown below:

Function Grade(Marks As Integer) As Variant

Select Case Marks

Case Is >= 85

Grade = "A"

Case Is >= 65

Grade = "B"

Case Is >= 50

Grade = "C"

Case Is >= 35

Grade = "D"

Case Is < 35

Grade = "E"

End Select

End Function

Now you can see the  User-Defined-Function called "Grade" in Worksheet Functions and it gives the results like as below:

Result :

-----------------------------------------------------------------------------------------------------------------------

Example - II :

Let us see the below Example where we are creating a User-Defined-Function called "YoY_Growth" to calculate the YoY Growth Percentage.

Function YOY_Growth(X As Double, Y As Double)

YOY_Growth = Format((Y - X) / X, "0%")

End Function

Result :

Thanks, TAMATAM