Passing Variables through subs

[Djblois]

I know how to declare them publically but I would prefer to declare them only when I first need them and then pass them through the other subs. How do I do this???

[OBP]

You have to declare them as part of the Sub heading, it is what the brackets are for.Then when you call the sub, you have to pass the values that are in the sub header brackets.

[Ken Puls]

Does this help?

Sub MeFirst()
    Dim strMessage As String
    strMessage = "I was passed to another sub!"
    Call MeSecond(strMessage)
End Sub

Sub MeSecond(sMsg As String)
    MsgBox sMsg, vbOKOnly, "This message was sent to you by yourself!"
End Sub

Run MeFirst.

[mdmackillop]

In passing to a sub, the argument doen't need to be in brackets; when passing to a function, you need them.

 
Sub MeFirst()
    Dim strMessage As String
    strMessage = "I was passed to another sub!"
    MeSecond strMessage
    MsgBox MeThird(strMessage), vbOKOnly, "This message was sent to you by yourself!"
End Sub
 
Sub MeSecond(sMsg As String)
    MsgBox sMsg, vbOKOnly, "This message was sent to you by yourself!"
End Sub
 
Function MeThird(sMsg As String)
    MeThird = sMsg & vbCr & sMsg
End Function

[Bob Phillips]

In passing to a sub, the argument doen't need to be in brackets; when passing to a function, you need them.

Not correct, it is not whether it is a Sub or a Function

Sub MeFirst()
    Dim strMessage As String
    strMessage = "I was passed to another sub!"
    MeSecond strMessage
    MsgBox MeThird(strMessage), vbOKOnly, "This message was sent to you by yourself!"
End Sub
 
Function MeSecond(sMsg As String)
    MsgBox sMsg, vbOKOnly, "This message was sent to you by yourself!"
End Function
 
Function MeThird(sMsg As String)
    MeThird = sMsg & vbCr & sMsg
End Function

The determining factor on whether parentherses are required is if a function is part of an expression, or whether the procedure is invoked via a Call statement.

[Cyberdude]

I'm not sure that I understood the question, but I'd like to clarify that passing a variable as an argument doesn't necessarily pass the variable ... it's usually passing the variable's value.

Sub First
Dim XXX As String
XXX = "Hello"
Call Second(XXX)
End Sub

Sub Second(InputString As String)
Msgbox InputString
End Sub

Sub Second will display "Hello".

[Jan Karel Pieterse]

I'm not sure that I understood the question, but I'd like to clarify that passing a variable as an argument doesn't necessarily pass the variable ... it's usually passing the variable's value.

That is not entirely correct. In fact you should be using the keyword ByRef or Byval. If you use Byref, you pass a pointer to the variable. Use ByVal to pass just the content of the variable. And it also depends on whether you're passing an Object variable or a "normal" one.

[mdmackillop]

I think we really need an Article on this, if we ever get that area of the site up and running again!

[Djblois]

Thank you all and I agree it seems a complicated topic and not totally understood. So I think we need an Article or something from an expert

[compariniaa]

 Sub MeSecond(sMsg As String)

If I were to have used

Sub MeSecond(sMsg)

would Excel treat sMsg as a variant?

[Zack Barresse]

I'm actually working on a white paper for this, along with Excel addins (prior to 2007).

[Bob Phillips]

If I were to have used

Sub MeSecond(sMsg)

would Excel treat sMsg as a variant?

It would not treat sMsg as a Variant, it is implicitly declaring sMsg as type variant. So if you passed a parameter of any other type when calling the procedure, it would fail.

[Norie]

xld

Are you sure?

Sub MeSecond(sMsg)
    MsgBox TypeName(sMsg)
End Sub

Sub test()
Dim TestString As String
    TestString = "Test"
    MeSecond TestString
End Sub

Sub MeSecond(sMsg As Variant)
    MsgBox TypeName(sMsg)
End Sub

Sub test()
Dim TestString As String
    TestString = "Test"
    MeSecond TestString
End Sub

[Zack Barresse]

Norie, I believe Bob is talking about a parameter, not a data type.

[compariniaa]

It would not treat sMsg as a Variant, it is implicitly declaring sMsg as type variant. So if you passed a parameter of any other type when calling the procedure, it would fail.

I'm a little confused...what would be an example of a parameter?

[Cyberdude]

Bob lost me too when he referred to a "parameter". I would probably have said "pass a value". Would I have been incorrect?

[Zack Barresse]

I think what Bob was referring to was what you declare it as, compared to what you try to pass it as. Look at these three examples (yes, two are destined to fail, can you tell which ones?)..

Sub MeSecond(sMsg)
    MsgBox sMsg.Address & vbNewLine & TypeName(sMsg)
End Sub
Sub MeFirst()
    Dim r As Range
    Set r = Range("A1")
    MeSecond r
End Sub

Sub MeSecond2(sMsg As Range)
    MsgBox sMsg & vbNewLine & TypeName(sMsg)
End Sub
Sub MeFirst2()
    Dim s As String
    s = "test string"
    MeSecond2 s
End Sub

Sub MeSecond3(sMsg As Long)
    MsgBox sMsg & vbNewLine & TypeName(sMsg)
End Sub
Sub MeFirst3()
    Dim s(0) As Double
    s(0) = CDbl(Range("B2").Value)
    MeSecond3 s(0)
End Sub

[Bob Phillips]

xld

Are you sure?

Sub MeSecond(sMsg)
    MsgBox TypeName(sMsg)
End Sub

Sub test()
Dim TestString As String
    TestString = "Test"
    MeSecond TestString
End Sub[/vba] [vba]
Sub MeSecond(sMsg As Variant)
    MsgBox TypeName(sMsg)
End Sub

Sub test()
Dim TestString As String
    TestString = "Test"
    MeSecond TestString
End Sub

No clearly I am not. The first part of the statement was okay, the second part was complete garbage - well not complete garbage as a function does error if you try to pass a mis-matched data type, but of course variant can take any sub-type.

[Bob Phillips]

Bob lost me too when he referred to a "parameter". I would probably have said "pass a value". Would I have been incorrect?

I don't think so, but in my terminology, an argument is a property of a procedure, sub or function, whereas a parameter is the value passed into that argument, be it a value or a variable reference.

I find it useful to clearly differentiate between the variable (the parameter) being passed, and the procedure porperty (argument) being actioned.

[malik641]

That is not entirely correct. In fact you should be using the keyword ByRef or Byval. If you use Byref, you pass a pointer to the variable. Use ByVal to pass just the content of the variable. And it also depends on whether you're passing an Object variable or a "normal" one.

Just wanna ask something about this. When you call another procedure and the procedure uses arguements by reference, then if you change the variable in the called procedure, it should change the variable in the calling procedure, right?

Well I was playing with it and noticed that if you don't use the Call statement, the passing variable isn't changed.

Sub Test()
Dim msg As String
msg = "Hey"
Debug.Print msg
Call Change(msg)
'Change (msg)
Debug.Print msg
End Sub

Sub Change(ByRef msg As String)
msg = "What's up?"
End Sub

Try switching between the "Call Change(msg)" and the "Change (msg)", the statement without "Call" doesn't change the passed variable in the "Test" procedure.

I thought the Call statement was optional?