How to convert numbers to strings in excel-CodePudding

I'm trying to create a replica of the MonthName() function in Visual Basic for Excel 2016 (this version doesn't seem to have it and I don't have control over updating it) and here's my code:

Function GetMonthName(ByVal MonthNum As Integer)

    Dim MonthNames(13) As String
        MonthNames(0) = Null
        MonthNames(1) = "January"
        MonthNames(2) = "February"
        MonthNames(3) = "March"
        MonthNames(4) = "April"
        MonthNames(5) = "May"
        MonthNames(6) = "June"
        MonthNames(7) = "July"
        MonthNames(8) = "August"
        MonthNames(9) = "September"
        MonthNames(10) = "October"
        MonthNames(11) = "November"
        MonthNames(12) = "December"
    

    GetMonthName = MonthNames(MonthNum)
    
End Function

I'm sure there's a better way to do this but I'm new to the language. At any rate, this doesn't work. I get a #VALUE! error when trying to put =GetMonthName(11) into the function bar as a test (It should return November). I'd like to know what I'm doing wrong. As I said earlier, I would use a standard library but I don't have that luxury.

EDIT: Putting NULL in quotes fixed it, but I don't understand why. I changed it to the empty string anyway but I still don't understand what that has to do with it.

CodePudding user response：

Solution English only

If you want to use Null you cannot declare the variable/array as String because Null is not a string (nor does it cast into a string). Either declare as Variant as well as the function.

Public Function GetMonthName(ByVal MonthNum As Long) As Variant

    Dim MonthNames() As Variant
    MonthNames = Array(Null, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
    
    GetMonthName = MonthNames(MonthNum)
    
End Function

Or use vbNullString which is ""

Public Function GetMonthName(ByVal MonthNum As Long) As String

    Dim MonthNames() As String
    MonthNames = Array(vbNullString, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
    
    GetMonthName = MonthNames(MonthNum)
    
End Function

Multi Language Alternative

Note that this will always return the English month names no matter what language the user uses on his computer. But you could also use the following which would give the month name according to the language setup of the computer it is running on.

Public Function GetMonthName(ByVal MonthNum As Long) As String
    
    GetMonthName = Format$(DateSerial(Year(Date), MonthNum, 1), "MMMM")
    
End Function

None-VBA Solution (Muli Language)

For a solution without VBA, you can write a full date into a cell and set the number format to MMMM. This will also show the month name of that date.

CodePudding user response：

If recommand the use of CHOOSE vba function.

Function GetMonthName(ByVal MonthNum As Integer)
    Dim MonthNames As String
    If MonthNum <= 0 Or MonthNum >= 13 Then 'You can return "" value
       GetMonthName = "Error" '--You can return "" value
    Else
       MonthNames = Choose(MonthNum, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
       GetMonthName = MonthNames
    End If
End Function