Month Names in Excel VBA

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.

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.

Result:

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, simply select cell A1, enter the function =MONTHNAMES() and press Enter. Bye bye curly braces.

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