How to make array function to create sequence month from numbers in vba excel?

Question

I would like to make some array function to help me create report, suppose I have number=4 then I intend to make it sequence become 1,2,3,4 then lastly those numbers will be converted to "January", "February","March", "April". I've tried to created this function but nothing works.

Function numtomonth(num As Integer)
r As Long
a As Integer
h As Long

For a = 1 To num
r(a) = a
If r = 1 Then r = "January"
If r = 2 Then r = "February"
If r = 3 Then r = "March"
If r = 4 Then r = "April"
If r = 5 Then r = "May"
If r = 6 Then r = "June"
If r = 7 Then r = "July"
If r = 8 Then r = "August"
If r = 9 Then r = "September"
If r = 10 Then r = "October"
If r = 11 Then r = "November"
else r="December"
end IF
Next a
h = r

End Function

I would be grateful if could help me. Thanks

Answer 1

I think most of these answers are great, especially the language conversion ones. You get a thumbs up from me because I learned something. Unfortunately the title doesn't reflect conversion, so people looking for that in the future won't find it here.

The actual question from Ahmad was how to make an array of month names from numbers 1 through 12.

There is already a built in function for that in excel, which I relayed to him as a comment. Ahmad is an inexperienced vba programmer so I think the best way to teach him about built in arrays is simply:

Sub testMonthNameArray()

    For r = 1 To 13
        If r > 0 And r <= 12 Then
            MsgBox MonthName(r)
        Else
            Beep
            MsgBox r & " is not a valid number for months."
        End If
    Next r

End Sub

Answer 2

Already some great answers, but for months and abbreviations in different languages the following would work as a base to allow adding further languages. language abbreviations can be found here: http://library.princeton.edu/departments/tsd/katmandu/reference/months.html

Const langEnglish As Long = 1
Const langFrench As Long = 2
Const langGerman As Long = 3
Const langSpanish As Long = 4

Sub TestMonth()
    Dim Arr() As String: Arr = MonthArray(langSpanish, True)
    MsgBox Arr(3)
End Sub

Private Function MonthArray(Language As Integer, Optional Abbrev As Boolean) As Variant
    If IsMissing(Abbrev) Then Abbrev = False

    Dim V() As String
    If Abbrev <> True Then
        Select Case Language
        Case 1
            V = Split("January,February,March,April,May,June,July,August,September,October,November,December", ",")
        Case 2
            V = Split("Janvier,Février,Mars,Avril,Mai,Juin,Juillet,Août,Septembre,Octobre,Novembre,Décembre", ",")
        Case 3
            V = Split("Januar,Februar,März,April,Mai,Juni,Juli,August,September,Oktober,November,Dezember", ",")
        Case 4
            V = Split("Enero,Febrero,Marzo,Abril,Mayo,Junio,Julio,Agosto,Septiembre,Octubure,Noviembre,Diciembre", ",")
        End Select
    Else
        Select Case Language
        Case 1
            V = Split("Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec", ",")
        Case 2
            V = Split("Janv,Févr,Mars,Avril,Mai,Juin,Juil,Août,Sept,Oct,Nov,Déc", ",")
        Case 3
            V = Split("Jan,Feb,März,Apr,Mai,Juni,Juli,Aug,Sept,Okt,Nov,Dez", ",")
        Case 4
            V = Split("Enero,Feb,Marzo,Abr,Mayo,Jun,Jul,Agosto,Sept,Oct,Nov,Dic", ",")
        End Select
    End If
    MonthArray = V
End Function

Answer 3

to do the months in a different language you can use Application.WorksheetFunction.Text with the correct language code, a list of the codes are found here: http://excelribbon.tips.net/T011782_Specifying_a_Language_for_the_TEXT_Function.html

This will fill the array with the russian months:

Function NumToMonth(num As Integer) As Variant()
Dim a As Integer
Dim temp() As Variant
ReDim temp(1 To num) As Variant
If num > 12 Then Exit Function

For a = 1 To num

    temp(a) = Application.WorksheetFunction.Text(DateSerial(2017, a, 1), "[$-0419]mmmm")
Next a

NumToMonth = temp
End Function

Just change the 0419 with the proper code for the proper language.

Answer 4

If you need to get English month names, the MonthName function eliminates the need for that array.

If you need to map strings to some key, a Dictionary is what you need (note, the key must be a string, but you can use CStr(number) as a key), from the Microsoft Scripting Runtime library:

Private Function GetMappedValueFor(ByVal key As String) As String
    Static values As Scripting.Dictionary
    If values Is Nothing Then
        'initializes on first call only
        Set values = New Scripting.Dictionary
        values.Add "1", "Value for Key 1"
        values.Add "2", "Value for Key 2"
        values.Add "3", "Value for Key 3"
        values.Add "4", "Value for Key 4"
        values.Add "5", "Value for Key 5"
        values.Add "6", "Value for Key 6"
        '...
    End If
    GetMappedValueFor = values(key)
End Function

If the index is the key, this could work too - especially useful if your fiscal years don't start in January:

Dim values = "March,April,May,June,July,August,September,October,November,December,January,February"
Debug.Print Split(values, ",")(n) 'where n is the 0-based index you want

---

Your code has a number of problems though:

• Lack of indentation makes it hard to follow which instructions are under which code block.
• Inline If {condition} Then do not constitute an If block, so they don't need an End If token.
• Because there's no If block, the Else token will confuse the compiler.
• r is declared as a Long but used as an array in r(a) = a. That won't compile either.
• a is never assigned so it's always 0
• h is never referred to, and the function's return value is never assigned, so the function will always return an empty Variant.
• Function signature should declare a return type.
• You're treating every iteration differently; seems the loop could be outright eliminated.

Answer 5

You have already the MonthName function in VBA.

If you have a reason to create your own array, try this:

Function numToMonthArray(ByVal num As Integer) As String()
    ReDim ret(1 To num) As String
    For num = 1 to num
        ret(num) = MonthName(num)
    Next
    numToMonthArray = ret
End Function

Besides, MonthName has also an optional Boolean parameter Abbreviate to get you the abbreviation of the name; i.e.

ret(num) = MonthName(num, True)

will get you "Jan", "Feb", etc...