Assigning a variable a permant value till it is reassigned

[brettvba]

Can this be done without a const??

[daleyman]

declare the variable, instead of using:

Dim varname as integer

use:

Public varname as integer

This will assign it as a public variable and it will hold its value until it is changed.

[brettvba]

I have got public at the moment which is fine while in a proceedure but when the proceedure ends the value does to.

[daleyman]

works for me, i assume you are not hoping it will hold its value after the workbook is closed?

[brettvba]

no I only want it to hold till the workbook is closed and the value is introduced at the start under workbook open.

[daleyman]

i see what you mean, obviously doesn't like declaring publics in workbook or worksheet modules. if you put the public declaration line into a seperate module it works though.

[brettvba]

So when the work book opens it assigns the value eg wkbname = car

and then any proceedure you run wkbname will = car public just doesn't seem to hold the value past any proceedure.

[daleyman]

yes i have tested this you are right, if the public declaration is in the workbook module with the workbook_open code.

However, leave this workbook_open code where it is and put the public declaration in a new module (right click the workbook in the project browser of the VBA gui and select "insert > module" to get one of these)... i have tested and it works this way.

[brettvba]

But public declaration is only so it can be shared and that works fine but when one macro is run and that assigns a variable a value it stays until that macro ends and then the value is empty!

[daleyman]

cut and paste the code so i can see, or send me the workbook: david@monnetqos.com

Thanks.