How to Create own VBA Functions in Excel

So far you all know what are functions.

Functions we can say that a set of formulas to perform task. We can further split them into two types

In-Built Functions

User Defined Functions

In Excel you can see in Ribbon FORMULAS tab, there are multiple function which you can directly use and create a set of formula to get the result. And these available function we can say as In-built Functions.

User can create custom functions as per requirement using VBA Coding technology and so we can say User Defined Functions.

We will take an example to creating a custom function to calculate Age as there is no in-built function, which will give you age from given dates.

 Writing function in VBA, you should follow universal rule

• Always provide a sensible name to your function.
• Never use the Keyword, Inbuilt function names to your procedure.
• Function name should start with letters, characters only.
• Spaces in name are supposed to filled by '_' special character.
• Never name the function by range name.

Let us start understanding the code step - by - step

Function FindAge(OrdDate as Date)

'Now check if the value in referenced cell is not blank.

    If OrdDate = 0 Then    'Determine if cells is empty

'If found return the result as "No Order Date Supplied..." in corresponding cell

        FindAge = "No Order Date supplied..."
    Else

'Perform calculation, I've used Select Case - End Select to check the parameters and return the calculated Age of supplied date.




        Select Case Month(Date) 

            Case Is < Month(OrdDate)
 
'Calculate the age from Year of today's Date - Year of OrdDate
                FindAge = Year(Date) - Year(OrdDate) - 1
 
            Case Is = Month(OrdDate)

'Check if day of today is greater or equal to day of OrdDate
                If Day(Date) >= Day(OrdDate) Then
                    FindAge = Year(Date) - Year(OrdDate)
                Else
                    FindAge = Year(Date) - Year(OrdDate) - 1
                End If
 
            Case Is > Month(OrdDate)
                    FindAge = Year(Date) - Year(OrdDate)
 

        End Select
    End If
 

End Function

Once you are done with writing complete function, you can see the name of function appears in intellisense list

Now you can use the function name as =FINDAGE() and referencing the cell that holds date. You can use in entire workbook, however you need to reference the cell which has dates.

Hope you understood the procedure to create your own function in VBA

Down load Practice workbook << User Defined Function >> from Google Drive

Subscribe to my blog for latest and unique articles on Automation.