Persistent data in an Add-in

B

Bura Tino

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
 
B

Bob Phillips

Bura,

How would an add-in project get reset?

Using a cell is bad (IMO) as it is encroaching on the user's domain (the
spreadsheet is his after all) and may get over-written by that user.

You could also consider workbook names as a storage device, the registry,
environment variables, or even a file.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

JE McGimpsey

???

A cell in an add-in's sheet belongs to the add-in. The user normally
would never see it, much less use it.

It's no more the user's domain than any other part of the add-in (though
to be fair, I certainly consider any add-in's code module part of *my*
domain!).
 
T

Tom Ogilvy

It think the original poster was speaking of a cell in an addin
Bob was speaking of a cell on the users worksheet
JE was back to the addin
 
J

Jens Thiel

Bura Tino said:
Is it better to use a global variable or a cell in an add-ins worksheet to
store a value?
Click to expand...

Since your other question was about C++ add-ins, you might be interested to
know that Excel has a hidden per-instance name space that is accessible to
DLLs only.

Jens.
 
B

Bura Tino

Jens Thiel said:
Since your other question was about C++ add-ins, you might be interested to
know that Excel has a hidden per-instance name space that is accessible to
DLLs only.

Jens.
Click to expand...


This is very interesting. Can you point me to more information?
 
J

Jens Thiel

Bura Tino said:
This is very interesting. Can you point me to more information?
Click to expand...

Use xlfSetName.

You can also persist binary data to workbooks in a hidden namespace.

Jens.
 
N

Norman Harker

Hi Bura Tino!

Storing a value in an Addin's worksheet?

I use this approach in the form of entering a UDF in uppercase in the
Addin's worksheet. It has a hidden advantage in that the case used for
entering the UDF then becomes the default case for that function. The
UDF can then emulates the inbuilt function approach whereby entering
lower case converts to uppercase. Otherwise Excel will use the case
used by the first use of a particular UDF in a particular Excel
Session.

Only trouble I have is that this approach appears to interrupt a
Workbook_Open event subroutine in a file that sits in XLSTART folder.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. The threat
of worms / viruses that exploit vulnerabilities addressed by these
patches is now high.
See:
http://www.microsoft.com/security/protect/
 
B

Bura Tino

Bob Phillips said:
Bura,

How would an add-in project get reset?
Click to expand...

Should I take this comment as an indicaiton that projects don't get reset?
What if an error occurs?
I have a feeling that that may lead to a reset...
 
J

Jens Thiel

Bura Tino said:
Should I take this comment as an indicaiton that projects don't get reset?
What if an error occurs?
I have a feeling that that may lead to a reset...
Click to expand...

I have noticed the same behaviour.

Jens.
 
B

Bill Renaud

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!).
 
Top