How to create & use User-Defined Function (UDF)?

Usually there is already built-in function, which can meet your requirements. But sometimes this is not exactly what You needed or it’s not exist. In this article I will show You how to create and use User-Defined Function (UDF) in VBA.

Built-in function

In most cases there are already some functionalities in the programming language or in the working environment.
Let’s take for example simple function MonthName. To use this You need to pass the number and the function returns the name of the month connected with the month – 1 for January, 2 for February, 3 for March etc. And this was all I wanted – I needed to add the name of the previous month to the name of the report. So I used:

Dim nameOfMonth As String
nameOfMonth = MonthName(Month(Now) - 1)

Unexpected result

It was good, until my college told me, that it returns the name of the month, but not in English, even though English was the language of the Office pack.

For me it was weird and funny, because I would not thought any moment that built-in VBA function will return the value in other language than English.

Anyways… the fastest solution to deal with the problem and to be sure that names WILL be in English was to write my own function.

User defined function (UDF)

I decided to go with simple Select Case, list down all the numbers from 1 to 12, in case of other return Unknown.

Function GetMonthName(monthNumber As Integer) As String
    
    Select Case monthNumber
        Case 1
            GetMonthName = "January"
        Case 2
            GetMonthName = "February"
        Case 3
            GetMonthName = "March"
        Case 4
            GetMonthName = "April"
        Case 5
            GetMonthName = "May"
        Case 6
            GetMonthName = "June"
        Case 7
            GetMonthName = "July"
        Case 8
            GetMonthName = "August"
        Case 9
            GetMonthName = "September"
        Case 10
            GetMonthName = "October"
        Case 11
            GetMonthName = "November"
        Case 12
            GetMonthName = "December"
        Case Else
            GetMonthName = "Unknown"
    End Select
    
End Function

After creation of GetMonthName I can use it in the code and can be sure about the result.

UDF as Excel function

What is more, I can also call this function like Excel formula typing its name in the formula bar.

The other way to find created by user functions is to go to Formulas tab and click on Insert Function.

There find and select User Defined from the drop down list and You’ll see all the UDF written by user in the box below.

Create your function now!

So in case of lack of needed function or just for fun – create your own User Defined Function in VBA! Try with the easy one, like return Hello World, to more complex functions with many parameters to start. It is really helpful solution, especially for those who mainly use Excel and its formulas – sometimes additional one can turn the table!

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

Leave a Reply

Your email address will not be published. Required fields are marked *