Month Names

 

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:

Function MONTHNAMES()

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.

End 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.

Result:

Month Names in Excel VBA

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.

6. If you have Excel 365 or Excel 2021, select cell A1, enter the function =MONTHNAMES() and press Enter. Bye bye curly braces.

Dynamic Array Formula

Note: the MONTHNAMES function, entered into cell A1, fills multiple cells. Wow! This behavior in Excel 365/2021 is called spilling.