Passing Values

[Information Hog]

How can you code a module to pass a value from one Private sub to a Public sub or the other way around? More to the point is once the variable has the value how do you retrieve the value?

[instruite]

the simplest way is to define the variable as a public variable
Then its value can be read and modified by any sub

[Rowan]

An example:

Private Sub start()
Dim myVar As Integer
myVar = 5
Call NextBit(myVar)
End Sub

Sub NextBit(Var1 As Integer)
MsgBox "Variable value is " & Var1
'or
'activesheet.range("A1").value = var1
'etc
End Sub

The sub NextBit takes a variable of integer type called Var1. When the
Private sub runs it passes a value to NextBit which is set to the value of
Var1. You can then retrieve this value by referencing the variable.

Hope this helps
Rowan

"Information Hog" wrote:

>
> How can you code a module to pass a value from one Private sub to a
> Public sub or the other way around? More to the point is once the
> variable has the value how do you retrieve the value?
>
>
> --
> Information Hog
> ------------------------------------------------------------------------
> Information Hog's Profile: http://www.excelforum.com/member.php...o&userid=21508
> View this thread: http://www.excelforum.com/showthread...hreadid=398019
>
>

[Bob Phillips]

There are a number of ways.

The easiest and worst IMO is to use a public variable throughout.

Second way is to pass a variable ByRef (the default)

Public Sub Macro1()
Dim myVar As Long

myVar = 5
MsgBox myVar
Macro2 myVar
MsgBox myVar
End Sub

Public Sub Macro2(ByRef arg As Long)
arg=22
End Sub

Another way, and my preferred option, is to use functions

Public Sub Macro1()
Dim myVar As Long

myVar = 5
MsgBox myVar
myVar = Func1(myVar)
MsgBox myVar
End Sub

Function Func1(arg As Long) As Long
Func1 = arg * 5
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Information Hog"
<[email protected]> wrote in
message news:[email protected]...
>
> How can you code a module to pass a value from one Private sub to a
> Public sub or the other way around? More to the point is once the
> variable has the value how do you retrieve the value?
>
>
> --
> Information Hog
> ------------------------------------------------------------------------
> Information Hog's Profile:
http://www.excelforum.com/member.php...o&userid=21508
> View this thread: http://www.excelforum.com/showthread...hreadid=398019
>

[mike]

Not to be trite or anything but is sounds like you don't have a grasp
of scope (lifetime of a variable). Any VB programming book should
address this subject which is too lengthy for a post.

[Peter T]

Who is this addressed to. If to Bob as it appears, please explain how you
would improve on his succinct yet complete response. Also explain in what
way you consider Bob does not have a grasp of the scope of variables.

Regards,
Peter T

PS Friendly advice - read Bob's response carefully before replying !

"mike" <[email protected]> wrote in message
news:[email protected]...
> Not to be trite or anything but is sounds like you don't have a grasp
> of scope (lifetime of a variable). Any VB programming book should
> address this subject which is too lengthy for a post.
>

[mike]

Your correct, of course! I knew who the comment was address to, not Bob
but the original poster. I should have replied to "Hog", not Bob.
Sorry for the confusion, Bob.
Since "Hog" seemed to indicate a lack of knowledge on simply getting a
value from a variable, I felt the best advice was to recommend becoming
more familiar with the basics. While Bob's response is good, "Hog" also
needs to have an understanding of where variables should reside and
why.

mike