Writing this down after almost pulling my hair out in grappling with the correct syntax as it has been a long while since I coded macros in Excel. To start dabbling with macros in Excel 2013, go to File > Options > Customize Ribbon and tick the checkbox for the Developer tab on the right pane. You should see a new Developer tab on the Ribbon. Click on the Developer tab, Visual Basic, and you will see the list of the worksheets in the VBA Project on the left pane. Double-click on ThisWorkbook to start coding (if you double-click on a worksheet, the macros in there only apply to that worksheet).
Firstly, if you want to create an array of strings, use the following code. Somehow, the variable has to be declared as Variant instead of String in order for this to work. And note the brackets in the declaration.
Dim Months() As Variant
Months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
I wanted to create a simple procedure that will return an array of month names. So I created a Sub procedure with a Return at the end. No go. Found out that to return a value, I have to set the value to the name of the procedure. Errors. Finally, came across a page that mentioned the difference between a Function and a Sub, the former being used for procedures that return values. And here’s the final code:
Function GetMonths()
GetMonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
End Function
Hope this will help someone out there 🙂 Adhuc!