Why is dynamic public variable returning blank?

[Lewzerrrr]

Title

I'm trying to use MyUser and MyDate in many modules but it just returns blank.

Public MyUser As String
Public MyDate As String


Sub GetMyUser()


    Dim MyUser As String
    
    MyUser = Application.WorksheetFunction.Proper(Application.UserName)


End Sub


Sub GetMyDate()


    Dim MyDate As String
    
    MyDate = Format(Now(), "DD-MM-YYYY HH:MM")


End Sub


Sub Test()


    MsgBox MyDate
    MsgBox MyUser


End Sub

 

[RoryA]

Both the subs that set the variables have declared their own local variables, so the public variables are never set, even if you have called them before the Test routine.

 

[Fluff]

Beaten2it

 

[Lewzerrrr]

Can you help out with the resolution?

 

[RoryA]

Remove the the Dim lines from the subs that set the values.

 

[Jaafar Tribak]

Can you help out with the resolution?

Remove the lines :
Dim MyUser As String and Dim MyDate as String.

Also, make you sure the Public Module variables are declared in a Standard Module.

 

[Lewzerrrr]

Remove the lines :
Dim MyUser As String and Dim MyDate as String.

Also, make you sure the Public Module variables are declared in a Standard Module.

Yes they are in a standard module, I removed the Dim statements but still returning blanks in a messagebox.

If I call the procedures inside sub then it works fine, is it possible to not call the procedure but use the variable set?

Public MyUser As String
Public MyDate As String


Sub GetMyUser()


    MyUser = Application.WorksheetFunction.Proper(Application.UserName)
    
End Sub


Sub GetMyDate()
    
    MyDate = Format(Now(), "DD-MM-YYYY HH:MM")


End Sub


Sub Test()

    MsgBox MyDate
    MsgBox MyUser

End Sub

 

[RoryA]

No. It sounds like you actually want functions to me:

Function GetMyUser() As String

    GetMyUser = Application.WorksheetFunction.Proper(Application.UserName)
    
End Sub


Function GetMyDate() As String
    
    GetMyDate = Format(Now(), "DD-MM-YYYY HH:MM")


End Sub


Sub Test()

    MsgBox GetMyDate
    MsgBox GetMyUser

End Sub

 

[Jaafar Tribak]

Yes they are in a standard module, I removed the Dim statements but still returning blanks in a messagebox.

If I call the procedures inside sub then it works fine, is it possible to not call the procedure but use the variable set?

Public MyUser As String
Public MyDate As String


Sub GetMyUser()


    MyUser = Application.WorksheetFunction.Proper(Application.UserName)
    
End Sub


Sub GetMyDate()
    
    MyDate = Format(Now(), "DD-MM-YYYY HH:MM")


End Sub


Sub Test()

    MsgBox MyDate
    MsgBox MyUser

End Sub

You need to call the 2 procedures to set the variables values .. How are you expecting the variables to be set ?