using variables from other macro

[lezawang]

Hi
How can I make the second marco print out the values from the first macro?
for example I want to use the second marco to msg out x and y which are variable in first macro. Thank you so much.

Sub myinputbox()
    'to use msgbox
    Dim x As Integer
    Dim y As String
    x = InputBox("enter a number")
    y = InputBox("enter a name")
    
End Sub


Sub mymsgbox()
    MsgBox (x)
    MsgBox (y)
End Sub

 

[DanteAmor]

Try this

Sub myinputbox()
    'to use msgbox
    Dim x As Integer
    Dim y As String
    x = InputBox("enter a number")
    y = InputBox("enter a name")
    Call mymsgbox(x, y)
End Sub


Sub mymsgbox(x, y)
    MsgBox (x)
    MsgBox (y)
End Sub

 

[Eric W]

Or use global variables:

Global x As Integer
Global y As String


Sub myinputbox()
    'to use msgbox
    x = InputBox("enter a number")
    y = InputBox("enter a name")
    
End Sub

Sub mymsgbox()
    MsgBox (x)
    MsgBox (y)
End Sub

 

[Rick Rothstein]

Or use global variables:

Global x As Integer
Global y As String


Sub myinputbox()
    'to use msgbox
    x = InputBox("enter a number")
    y = InputBox("enter a name")
    
End Sub

Sub mymsgbox()
    MsgBox (x)
    MsgBox (y)
End Sub

Two notes about Eric's suggestion...

1) I would suggest using complex variable names that cannot easily be called accidentally from within some other unintended procedure.

2) You cannot Dim those global variables within any procedure that will make use of them otherwise they will not be global to that procedure.

 

[Eric W]

Two notes about Eric's suggestion...

1) I would suggest using complex variable names that cannot easily be called accidentally from within some other unintended procedure.

2) You cannot Dim those global variables within any procedure that will make use of them otherwise they will not be global to that procedure.

Both good points. Modern program design pretty much states that global variables should be used exceedingly rarely. Variables should be defined locally to the function or procedure, and communication between procedures should be via parameters in the Call statement, like Dante suggested. This allows each procedure to be treated as a "Black box", and you can code and test it independently of the other modules. That said, there are some legitimate times you could use a global variable.

Also as another way to handle your example, you could write the x and y variables to a location in the spreadsheet, and the second procedure could read them.

 

[lezawang]

Thanks all for the help. I did the Global x and y but did not work.
each macro would start with a line ------- and end with a line ----
when I start typing "Global x As Integer" and press enter
excel would move "Global x As Integer" above the line of the macro, like this


Global x As Integer
Global y As String
____________________________________________
Sub myinputbox()
'to use msgbox
x = InputBox("enter a number")
y = InputBox("enter a name")
End Sub
_____________________________________________
Sub mymsgbox()
MsgBox (x)
MsgBox
End Sub

 

[Eric W]

That's normal. Excel is showing that the global variables do not belong to either routine, but rather to all of them. Did you actually try to run your code?

 

[DanteAmor]

It is completely normal.
It should look like this:

By the way, the declaration of the variable x is not correct, since the inputbox returns a text, then if you capture a letter it will send you an error.
You should declare as string and then convert the value to a numerical value.


Finally, you need this for something specific or just as general knowledge. Because if it's for something specific, maybe we can help you with that problem.

 

[lezawang]

Thanks all again for the help. I did run the code above but msgbox was empty in both cases. I entered x=10 and y="hello". Thanks a lot

 

[DanteAmor]

First you must run this macro: inputbox and then this macro: mymsgbox