Passing Global Variables by Value

[Ed Song]

I want to create public variables, but always want to pass the current value of the variable from one procedure to another. For Example:

-------------------

Option Explicit

Public X as Long
Public Y as Long
Public Z as Long


Sub A ()

X = 1
Y= 2
Z = X + Y

Call B

Worksheets (1).Range("A1").Value = Z

End Sub

Sub B ()

X = 2
Y=3
Z = X + Y

End Sub

----------------

In Cell A1 I want the value of 5 printed not the value of 3. Thus, I want the variables to be passed by value and not by reference. I want variables to pass by value globally.

Is this an appropriate procedure?

-----------

Option Explicit

Public X ByVal as Long
Public Y ByVal as Long
Public Z ByVal as Long


Sub A ()

X = 1
Y= 2
Z = X + Y

Call B

Worksheets (1).Range("A1").Value = Z

End Sub

Sub B ()

X = 2
Y=3
Z = X + Y

End Sub

----------------

 

[RickXL]

Hi,

If the variables can only be accessed by value, how do they get set?

 

[Ed Song]

I'm too much of a novice to know the meaning of your question. Doesn't the following statements set the variables?

Option Explicit

Public X ByVal as Long
Public Y ByVal as Long
Public Z ByVal as Long

As an example, look at the Variable X. Isn't the statement setting X as a global Long variable, to be passed by value everywhere in the program or am I using an invalid procedure?

 

[shg]

You aren't passing those values at all; they are available to to all procedures in the module because they are declared at module level (outside of all procedures). They are further available to all modules in the project because they are declared as Public.

Passing ByVal or ByRef is established (generally) in the signature of the procedure:

Sub mySub(ByVal X as Long, ByRef Y as Long)

For scalar variables, passing ByRef means you pass the memory address of the variable, which allows the procedure that receives it to modify the variable itself. Passing ByVal means that you pass a copy of the variable.

See Help for Understanding Scope and Visibility

 

[Ed Song]

So if I set up the variables as Pub X as long, Pub Y as Long then in the code I only need

Sub mySub(ByVal X, ByRef Y) because I've already declared the variables as Longs? or does VBA require me to repeat the command in the signature of the procedure?

 

[shg]

Suggest you step through Main while looking in the Immediate window and make sure you understand why each sub does what it does.

Option Explicit

Dim Bob             As Long

Sub Main()
  A
  Debug.Print Bob
  
  B
  Debug.Print Bob
  
  C Bob
  Debug.Print Bob
  
  D Bob
  Debug.Print Bob
  
  E Bob
  Debug.Print Bob
  
  F (Bob)
  Debug.Print Bob
End Sub

Sub A()
  Bob = 1
End Sub

Sub B()
  Dim Bob As Long
  
  Bob = 2
End Sub

Sub C(ByRef Joe As Long)
  Joe = 3
End Sub

Sub D(ByVal Joe As Long)
  Joe = 4
End Sub

Sub E(Joe)  ' passing ByRef is implicit, data type Variant is implicit
  Joe = 5
  Stop ' and look at Joe in the Locals window; it's Variant/Long, not Long
End Sub

Sub F(ByRef Joe As Long)
  Joe = 6
End Sub

 

[Ed Song]

Thanks, I'll study what you have done. The big problem is going to be passing the values of the main subroutine to the other subroutines. I'm not sure this can be done, and perhaps the program will have to be done on one module and one subroutine. How many lined of code in Excel 2010 can a module have and what is the maximum lines a subroutine can have? My program has the form:


Sub Main (signature?)
t=0

Do
Lines of Code (values calculated here must be passed on to A)
A (called variables)
Do While (Condition)
B (called variables)
C (called variables)

Lines of Code
Loop
Lines of Code
t = t+1
Loop Until t = 41

Print Output on Worksheet

End Sub

Sub A (signature)
Lines of Code
End Sub

Sub B (signature)
Call A (called variables)
Lines of Code
End Sub

Sub C (signature)
Call B (called variables)
Lines of Code
End Sub

The difficulty with my program is that every time we go through the outer loop sub A must reset back to its initial state, thus variables must be passed by reference. However, when we enter the inner loop, the current value must be passed on Sub B, and the values of Sub B must be passed on to Sub C.

 

[shg]

A module must be exportable to a .bas file < 64K in size. Other than that, there is no limit to the number of lines in a procedure, though 200 lines, IMO, is approaching overly long for a good design.

I think you're missing the basic notion of call by value and call by reference, and the relatively orthogonal concept of scope and visibility. Illustrating that was the point of my last post.

There are occasions where the use of public variables is appropriate. I wouldn't say that they are rare, but public variables make code difficult to debug and maintain, and they are way overused, IMO.

 

[shg]

A module must be exportable to a .bas file < 64K in size.

Actually, I think the limitatation may be that a procedure can't compile to a size larger than 64K. But I know of no way to determine how long a compiled procedure is.

But for reasonably factored code, the size limit is not an obstacle.

 

[Ed Song]

Yes, I'm having difficulty understanding your point. To illustrate what I'm trying to do, I modified your example to:

Option Explicit

Dim Bob As Long

Sub Main(ByVal Ann As Long)
Ann = 10
Debug.Print Ann

A
Debug.Print Bob

B
Debug.Print Bob

C Bob
Debug.Print Bob

D Bob
Debug.Print Bob
Debug.Print Sue

E Bob
Debug.Print Bob

F (Bob)
Debug.Print Bob
End Sub

Sub A()
Bob = 1
End Sub

Sub B()
Dim Bob As Long

Bob = 2
End Sub

Sub C(ByRef Joe As Long)
Joe = 3
End Sub

Sub D(ByVal Joe As Long, ByVal Sue as Long)
Joe = 4
Ann1 = Ann
Sue = Joe + Ann

End Sub

Sub E(Joe) ' passing ByRef is implicit, data type Variant is implicit
Joe = 5
Stop ' and look at Joe in the Locals window; it's Variant/Long, not Long
End Sub

Sub F(ByRef Joe As Long)
Joe = 6
End Sub

When I opened up the immediate window from your program I got as output 1, 1, 3, 3, 5, 5 I believe I understand why I got the sequence. When I open up the immediate window on my program I get a blank. I was hoping to get 10, 1, 1, 3, 3, 14, 5, 5

So what don't I understand. As I mentioned in a previous post, my program requires the main subroutine to pass on variables to the other subroutines, too.