Variable scope

[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.

 

[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.

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

 

[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.

 

[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

 

[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.

 

[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.

Same method:

Public sAppname As String

Regards,

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