CODEWITHSUNDEEP
excelvba
Vandy
Vandy

Reputation: 3

Try to retrieve data from a called sub

First, my apologize because it's my first vba code. :)

I try to retrieve the Month Name in a sub when calling another one, which uses a case condition. But the Month Name variable stays empty.

Can you tell me why ?

Here is my code :

enter code here

Sub RetrieveMonthName(MoisDeFacturation As Integer)

Dim MonthName As String

Select Case MoisDeFacturation
   Case Is = 1
      MonthName = "Janvier"
   Case Is = 2
      MonthName = "Février"
   Case Is = 3
      MonthName = "Mars"
   Case Is = 4
      MonthName = "Avril"
   Case Is = 5
      MonthName = "Mai"
   Case Is = 6
      MonthName = "Juin"
   Case Is = 7
      MonthName = "Juillet"
   Case Is = 8
      MonthName = "Août"
   Case Is = 9
      MonthName = "Septembre"
   Case Is = 10
      MonthName = "Octobre"
   Case Is = 11
      MonthName = "Novembre"
   Case Is = 12
      MonthName = "Décembre"
   Case Else
      MsgBox ("Sortie du programme, le chiffre " & MoisDeFacturation & " que vous avez 
renseigné ne correspond pas à un mois de l'année")
      Exit Sub
End Select

End Sub


Sub AddAFacture()


Dim InvoicingMonth As Integer
Dim MonthName As String

InvoicingMonth = 5
RetrieveMonthName (InvoicingMonth)
MsgBox ("" & MonthName & "")


End Sub

Upvotes: 0

Views: 65

Answers (2)

Storax
Storax

Reputation: 12167

If you would like to use your function you need to return a value like that

Option Explicit

Function RetrieveMonthName(MoisDeFacturation As Integer) As String

    Dim MonthName As String   ' This varaible is locally scoped and not visible outside the function

    Select Case MoisDeFacturation
        Case Is = 1
            MonthName = "Janvier"
        Case Is = 2
            MonthName = "Février"
        Case Is = 3
            MonthName = "Mars"
        Case Is = 4
            MonthName = "Avril"
        Case Is = 5
            MonthName = "Mai"
        Case Is = 6
            MonthName = "Juin"
        Case Is = 7
            MonthName = "Juillet"
        Case Is = 8
            MonthName = "Août"
        Case Is = 9
            MonthName = "Septembre"
        Case Is = 10
            MonthName = "Octobre"
        Case Is = 11
            MonthName = "Novembre"
        Case Is = 12
            MonthName = "Décembre"
        Case Else
            MsgBox ("Sortie du programme, le chiffre " & MoisDeFacturation & " que vous avez renseigné ne correspond pas à un mois de l'année")
            Exit Function
    End Select
    ' retunr a value
    RetrieveMonthName = MonthName

End Function


Sub AddAFacture()

    Dim InvoicingMonth As Integer
    Dim MonthName As String

    InvoicingMonth = 5
    ' assin value to the locally scoped varaible MontNname (not visible in the function!)
    MonthName = RetrieveMonthName(InvoicingMonth)
    MsgBox ("" & MonthName & "")

End Sub

Of course it is easier to use functions coming with VBA resp. Excel which will give you the same result.

You only need this code

Sub AddAFactureA()

    Dim InvoicingMonth As Integer
    Dim mName As String

    InvoicingMonth = 5
        
    mName = MonthName(InvoicingMonth)
    
    MsgBox ("" & mName & "")

End Sub

As you used in your code a variable MonthName in your cocde you hid the VBA function MonthName in your code which means you prevented yourself from using it.

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36840

You no need to declare additional function. You can achieve it like below.

Sub AddAFacture()
Dim InvoicingMonth As Integer
Dim MonthName As String

InvoicingMonth = 5
MonthName= Format(DateSerial(Year(Date()),InvoicingMonth,1),"mmmm")
MsgBox MonthName

End Sub

Upvotes: 1

Related Questions