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 FunctionAfter 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!
