Below we will look at a program in Excel VBA which creates a User Defined Function that uses the Array function to return the names of the months.
User defined functions need to be placed into a module.
1. Open the Visual Basic Editor and click Insert, Module.
2. Add the following code line:
The name of our Function is MONTHNAMES. The empty part between the brackets means we give Excel VBA nothing as input.
3. The Array function allows us to assign values to a Variant array in one line of code.
MONTHNAMES = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
4. Don’t forget to end the function.
5. Now you can use this function, just like any other Excel function, to return the names of the months. Select twelve horizontal cells, enter the function =MONTHNAMES() and press CTRL + SHIFT + ENTER.
Note: you cannot delete a single month. To delete the months, select the range A1:L1 and press Delete. This function is only available in this workbook.