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.
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)
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!