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.

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).
--
Regards,
Tom Ogilvy

Don wrote in message
...
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.

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

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Don" skrev i melding
...
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.

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


--

John

johnf202 at hotmail dot com


"Don" wrote in message
...
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.