I'd like to know if it's possible to use a VBA procedure to set a
variable or constant that will be used by another VBA procedure. I'd
like to do this without referencing a value in a worksheet cell.

Ben

wrote in message
oups.com...
I'd like to know if it's possible to use a VBA procedure to set a
variable or constant that will be used by another VBA procedure. I'd
like to do this without referencing a value in a worksheet cell.

Ben


Insert a module and declare something like

Public Myglobal As Long

You can also define a name that refers to your global value like this
ActiveWorkbook.Names.Add Name:="MyGlobal", RefersToR1C1:="=999"
This possibility can be useful if you want to preserve the value when you
exit Excel.
(without referencing a value in a worksheet cell)

/Fredrik

Hi Ben

Allow me to disagree with Fredrik: Do NOT use public variables unless you
really need them all over the place.

To pass stuff from one piece of code to another; include what you want
passed inside parentheses, like this:

Sub Test()
Call RemoteCode(12, "Yo da man!")
End Sub

Sub RemoteCode(L As Long, S As String)
MsgBox "Number is " & L & vbNewLine & _
"and the message is:" & vbNewLine & S
End Sub

HTH. Best wishes Harald


skrev i melding
oups.com...
I'd like to know if it's possible to use a VBA procedure to set a
variable or constant that will be used by another VBA procedure. I'd
like to do this without referencing a value in a worksheet cell.

Ben

Harald and Frederick,

Thanks for your replies. I didn't make myself clear. I'm not wanting to
call another procedure while the first one is still executing. I
actually want to modify code somewhere so that when I run the second
procedure it will use the constant that the first procedure set
earlier.

Ben

Ben,

You might find this helpful.

http://www.cpearson.com/excel/vbe.htm

Doug Glancy

wrote in message
ups.com...
Harald and Frederick,

Thanks for your replies. I didn't make myself clear. I'm not wanting to
call another procedure while the first one is still executing. I
actually want to modify code somewhere so that when I run the second
procedure it will use the constant that the first procedure set
earlier.

Ben

Hi Ben

Assuming that it's the same session (not closing/reopening excel), a public
variable will do:

Public MyConst As String

Sub MorningProcedure()
MyConst = "Good morning"
End Sub

Sub EveningProcedure()
MsgBox MyConst
End Sub

otherwise you must store things somewhere. A cell is a reasonable choice, an
external textfile or the registry is also possible.

It is of course possible to have a macro write and rewrite other macros. But
it's a little awkward and it can get unstable. Better write your second
macro so that it handles all potential scenarios and let it decide.

HTH. best wishes Harald

skrev i melding
ups.com...
Harald and Frederick,

Thanks for your replies. I didn't make myself clear. I'm not wanting to
call another procedure while the first one is still executing. I
actually want to modify code somewhere so that when I run the second
procedure it will use the constant that the first procedure set
earlier.

Ben