Hi Bura,
I believe global variables lose their value as soon as your code stops
running. Therefore, to maintain a variable between macros, I generally use
one of the 3 following approaches:
1. Save and retrieve it to/from the registry, if it is something that is
global across a lot of workbooks.
SaveSetting appname, section, key, setting
GetSetting(appname, section, key[, default])
2. If there are a lot of variables (several dozen or more), save them in a
separate workbook in a folder where your macro is saved, and use method #1
above to save the location of this workbook in the registry.
3. Save it in the user's workbook on a hidden worksheet. I generally add a
worksheet ("Scratch") to the template and use it to save all kinds of
constants and formulas to calculate labels for charts, etc. If your users
are running your macro on a workbook of their own that was not built from a
template that you provided, then methods #1 and #2 may be the only option.

I generally never make any changes at runtime to an add-in, since Excel
would then have to save the add-in every time that the user exits Excel. If
not done automatically, and the user is prompted to save and they say "No",
then the global variable is lost. Being prompted to save an add-in tends to
make a user think that they have been infected with a virus (since an add-in
is generally thought of as additional functionality, and everybody knows
that code is not supposed to change - unless it has been infected with a
virus, of course!).
--
Regards,
Bill


"Bura Tino" wrote in message
...
Hi,

Is it better to use a global variable or a cell in an add-ins worksheet to
store a value?

Using a global variable seems cleaner, but I'm concerned that the project
may get reset and the value would be lost.
Using a cell in a worksheet seems ugly, but so far seems to work for me.

Are there any hidden dangers in the latter approach?

Thank you,

Bura