can variables pass values

[Don]

I've written a program that starts a sub procedure each
minute (which is started with the application.ontime
event) that takes a value from a cell and puts it into
the next cell down a column. The problem is that it works
well as long as I use active cells, which I can't use.

THE QUESTION IS: can I pass the value of a global
variable from one procedure to the next or does the
variable return to zero or garbage when it goes out of
scope from one procedure to another? I really need to
pass an index value from one procedure to the next.
Any ideas at all? Declaring static seems to not work as
in VB.

 

[Harald Staff]

Hi

See if this is of help:

Sub test()
Call Macro1(1)
End Sub

Sub Macro1(ByVal X As Long)
MsgBox "Macro1 says " & X
X = X + 1
Call Macro2(X)
End Sub

Sub Macro2(ByVal X As Long)
MsgBox "Macro2 says " & X
X = X + 1
If X < 10 Then
Call Macro1(X)
Else
Call Macro3(X)
End If
End Sub

Sub Macro3(ByVal X As Long)
MsgBox "Final result " & X, , "Macro 3 finishes up"
End Sub

 

[Tom Ogilvy]

A global variable is visible by all routines in a project - so it isn't
passed.

At the top of a general module, outside any procedure declare the variable
as

Public MyCell as Range

or whatever is appropriate for your variable

for your question, if a variable goes out of scope, it doesn't exist. If
you refer to it in a procedure where it is out of scope, you create a new
variable with the same name which will have a value of empty since it will
be an uninitialized variant. (unless you have option explicit at the top,
then it will raise an error).

 

[jaf]

Hi Don,
When it's out of scope it's gone.
Static works the same. If you exit and restart your VB app. aren't statics
reset?

A solution would be to find the cell each time.
Is the next cell down blank?

Sub Tester2()
Sheets("xlconstants").Range("d1").Activate 'start at the top of column d
Selection.End(xlDown).Select 'move to the last cell containing data
ActiveCell.Offset(1, 0).Activate 'move down one more
MsgBox ActiveCell.Row 'we are here
End Sub