Variable scope

T

TonyM

Can anyone tell me what keyword I need to use to declare a
variable which will remain in scope while ever a workbook
is open. I know I've used this sometime ago but can't
think how I did it.

Thanks.
 
J

Jan Karel Pieterse

Hi TonyM,
Can anyone tell me what keyword I need to use to declare a
variable which will remain in scope while ever a workbook
is open. I know I've used this sometime ago but can't
think how I did it.
Click to expand...

In a normal module, declare a variable as public in the top of the
module. It will be available to each module in the project.

Public sAppname As String= "The Name Of This Application"

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
G

Guest

Hi Jan

Actually what I meant was if I declare a variable with
public scope and set it to an initial value which may then
change, how do I get the variable to retain the current
value while ever the workbook is open, ie outside of code
execution.

I could update a cell in Excel to do this but I'm sure
there is a way to do it within the code itself.

Thanks.
 
J

Jean-Yves

Hi,
I used the names collection to store a variable.
Not secured by it works

Set the value
ActiveWorkbook.Names.Add Name:="SdbNumber", RefersToR1C1:="=" & txtSdb
get the value

Dim strSDB As String
strSDB = Application.Names("SdbNumber").Value
txtSdb = Mid(strSDB, 2, Len(strSDB) - 1)

HTH
regards,

JY
 
T

TonyM

Hi Jan

Ignore the last post - what you said is exactly what I was
after. Didn't think it was so straightforward !

Much appreciated.
 
J

Jan Karel Pieterse

Actually what I meant was if I declare a variable with
public scope and set it to an initial value which may then
change, how do I get the variable to retain the current
value while ever the workbook is open, ie outside of code
execution.
Click to expand...

Same method:

Public sAppname As String

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
Top