How can I set a variable to be both Public and Static? I need it to work in
two separate procedures, and I also need it to keep a running tab each time
the macro is run.

Public variables are static in that they retain their values even if a
particular procedure has stopped running. To be Public the variable has to
be available to all modules at all times, so they exist and retain their
values as long as your project is running (which, for Excel, means as long as
your workbook containing the code is open). So nothing special should be
needed to do what you want to do.
--
- K Dales


"Mike" wrote:

How can I set a variable to be both Public and Static? I need it to work in
two separate procedures, and I also need it to keep a running tab each time
the macro is run.

Public variables declared at the regular code module level retain their
value until you do something to reset them, so there's no need to
declare them Static.

Try:

Public nTest As Long

Public Sub Test1()
MsgBox nTest
nTest = nTest + 1
End Sub

Public Sub Test2()
MsgBox nTest
nTest = nTest + 2
End Sub

Run Test1 and Test2 a few times...

Note that Public variables declared at the Class Module level retain
their values only as long as an instance of the class exists.

In article ,
"Mike" wrote:

How can I set a variable to be both Public and Static? I need it to work in
two separate procedures, and I also need it to keep a running tab each time
the macro is run.

This is what I needed. I didn't know the Subs themselves had to have Public
in front of them. That worked. Thanks.

"JE McGimpsey" wrote:

Public variables declared at the regular code module level retain their
value until you do something to reset them, so there's no need to
declare them Static.

Try:

Public nTest As Long

Public Sub Test1()
MsgBox nTest
nTest = nTest + 1
End Sub

Public Sub Test2()
MsgBox nTest
nTest = nTest + 2
End Sub

Run Test1 and Test2 a few times...

Note that Public variables declared at the Class Module level retain
their values only as long as an instance of the class exists.

In article ,
"Mike" wrote:

How can I set a variable to be both Public and Static? I need it to work in
two separate procedures, and I also need it to keep a running tab each time
the macro is run.

They don't - it's implied if you omit them.

In article ,
"Mike" wrote:

I didn't know the Subs themselves had to have Public
in front of them.