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.

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.


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

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.


-----Original Message-----
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.


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

.

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


-----Original Message-----
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.


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

.

Hi Jan

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

Much appreciated.


-----Original Message-----
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.


-----Original Message-----
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.


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

.

.

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.


Same method:

Public sAppname As String

Regards,

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