Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Values
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Values
the simplest way is to define the variable as a public variable Then its value can be read and modified by any sub -- instruite I am B.E. Instrumentation and Control and write now working as Hardware design engineer in automotive electrconics industry ------------------------------------------------------------------------ instruite's Profile: http://www.excelforum.com/member.php...o&userid=26539 View this thread: http://www.excelforum.com/showthread...hreadid=398019 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Values
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Values
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" <Information.Hog.1u7b6a_1124773519.243@excelforu m-nospam.com wrote in message news:Information.Hog.1u7b6a_1124773519.243@excelfo rum-nospam.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Values
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Values
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" wrote in message oups.com... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Values
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
passing values to other sheets | New Users to Excel | |||
Passing values in Sheet to VBA | Charts and Charting in Excel | |||
Passing values to new app | Excel Programming | |||
Hyperklinks - passing values | Excel Programming | |||
Passing values between 2 subs ? | Excel Programming |