Making variable accessible to other macros

[wuhuie]

Was wondering how to make a variable defined in one macro available for use in other macros?

For example, if the variable myMonth = InputBox("Input trade date month as MM") in Macro A, how do I use the variable myMonth in Macro B without having to redefine it again in Macro B?

 

[Chitosunday]

put this in top of the module before the start of any sub
or function
public mymonth

 

[wuhuie]

If as advised, I were to put myMonth = InputBox("Input trade date month as MM") at the top of the module before the start of any sub or function, I would face a compile error : invalid outside procedure message.

 

[Chitosunday]

Just add code "Public mymonth" on top of the module and not
relocate the existing code

 

[wuhuie]

Thanks!

Actually I have another question.

=> If I have defined the following in my macro:

myDay = InputBox("Input trade date day as DD")
myMonth = InputBox("Input trade date month as MM")
myYear = InputBox("Input trade date year as YY")

=> then I create the following formula in my macro:

ActiveCell.Formula = "=DATE("myYear","myMonth","myDay")"

=> I get a compile error: syntax error message. I get the same error message also when I remove the "" around myYear, myMonth and myDay.
What is wrong?

 

[al_b_cnu]

Hi,

dont you want something like this:

Public MyDate
Sub xxx()
Dim sTitle As String

sTitle = ""
Do Until IsDate(MyDate) = True
    MyDate = Application.InputBox(prompt:="Please enter trade date", _
                                  Title:=sTitle, _
                                  Default:=MyDate)
    If MyDate = False Then
        MsgBox "Macro abandoned at user request"
        Exit Sub
    End If
    sTitle = "Incorrect date entered"
Loop

Selection.Value = Format(MyDate, "dd-mmm-yy")
End Sub

 

[wuhuie]

I need the date (DD), month (MM) and year (YY) to be separately defined as myDate, myMonth and myYear respectively as I need to use each of the variables in different ways in the macro.

My problem is how to create a date that looks like 16-Nov-05 from the myDate, myMonth and myYear variables. In other words, how do I convert the myMonth (MM) into month in words?

 

[al_b_cnu]

Hi,

But if you input a date, firstly the user only has to reply to 1 input box, & secondly the date is automatically validated (e.g. checks for 32nd January)

you can extract individual elements using the functions Year, Month, Day & MonthName, try this code:

Sub xxx()
Dim myMonth, myYear, myDay
myDate = Application.InputBox("Date?")
If myDate = False Then Exit Sub

myYear = Year(myDate)
myMonth = Month(myDate)
myDay = Day(myDate)
MsgBox myYear & " " & myMonth & " " & myDay & " " & _
        MonthName(myMonth) & _
        MonthName(myMonth, True)
End Sub

 

[Chitosunday]

ActiveCell.Formula = "=DATE(" & myYear & "," & myMonth & "," & myDay & ")"

 

[wuhuie]

Many thanks to all for your help!