Assigning a variable a permant value till it is reassigned

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
I have got public at the moment which is fine while in a proceedure but when the proceedure ends the value does to.
 
Upvote 0
works for me, i assume you are not hoping it will hold its value after the workbook is closed?
 
Upvote 0
no I only want it to hold till the workbook is closed and the value is introduced at the start under workbook open.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top