VBA how to use a function within a sub ?

[jsmith2094]

Hey everyone,

I have created a function :

Function Name_of_month(a)

    Name_of_month = "Unknown"
    If a = 1 Then Name_of_month = "January"
    If a = 2 Then Name_of_month = "February"
    If a = 3 Then Name_of_month = "March"
    If a = 4 Then Name_of_month = "April"
    If a = 5 Then Name_of_month = "May"
    If a = 6 Then Name_of_month = "June"
    If a = 7 Then Name_of_month = "July"
    If a = 8 Then Name_of_month = "August"
    If a = 9 Then Name_of_month = "September"
    If a = 10 Then Name_of_month = "October"
    If a = 11 Then Name_of_month = "November"
    If a = 12 Then Name_of_month = "December"

End Function

what I need to do is to assign number to Column A from 1-12 then based on the cell value then display a mesgbox containing the month of the year so for example 1= january, 2= febuary and so on.....

I have attempted but think im way off lol

Sub Enumerate_all_months()

Dim A As Integer    ' Create whole number variable

Dim b As String

    For A = 1 To 12
                        
    Cells(A, 1) = A
                        
    Next
    b = Name_of_month(A)
    MsgBox b
    

End Sub

Any help would be apricated

 

[mohadin]

Hi
Try

Sub Enumerate_all_months()

Dim A As Integer    ' Create whole number variable

Dim b As String

    For A = 1 To 12
                        
    Cells(A, 1) = A
       b = Name_of_month(A)
    MsgBox b
    Next

End Sub

 

[RoryA]

You could probably shorten your function to something like:

Function Name_of_month(a)

    Name_of_month = "Unknown"
    On Error Resume Next
    Name_of_month = Format$(DateSerial(2000, a, 1), "mmmm")

End Function

then your msgbox needs to be inside the loop:

Sub Enumerate_all_months()

Dim A As Integer    ' Create whole number variable

    For A = 1 To 12
                       
    Cells(A, 1) = A
    MsgBox Name_of_month(A)                       
    Next

End Sub