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

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)

"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

???

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

In article ,
"Bob Phillips" wrote:

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.

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

--
Regards,
Tom Ogilvy

"JE McGimpsey" wrote in message
...
???

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

In article ,
"Bob Phillips" wrote:

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.

Ah...

In article ,
"Tom Ogilvy" wrote:

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

"Bob Phillips" wrote in message
...
Bura,

How would an add-in project get reset?


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


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)

"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

"Bura Tino" wrote:
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...

I have noticed the same behaviour.

Jens.
--
http://ManagedXLL.net/
Replace MSDN with my first name when replying to my email address!

"Bura Tino" wrote:

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

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.
--
http://ManagedXLL.net/
Replace MSDN with my first name when replying to my email address!

"Jens Thiel" wrote in message
...
"Bura Tino" wrote:

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

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.


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

http://ManagedXLL.net/
Replace MSDN with my first name when replying to my email address!

"Bura Tino" wrote:

know that Excel has a hidden per-instance name space that is accessible
to
DLLs only.

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


Use xlfSetName.

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

Jens.

--
http://ManagedXLL.net/
Replace MSDN with my first name when replying to my email address!

Here is some information on them:

http://www.cpearson.com/excel/hidden.htm
Hidden Name Space In Excel

Written by Laurent Longre, hosted on Chip Pearson's site.

--
Regards,
Tom Ogilvy


"Bura Tino" wrote in message
...

"Jens Thiel" wrote in message
...
"Bura Tino" wrote:

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

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.


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

http://ManagedXLL.net/
Replace MSDN with my first name when replying to my email address!

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


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/

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