[...]
2) How have you achieved this in VBA?
The way I achieved this was to store the data (object) in a Collection
defined at the Global level of a Class in VBA. Each member of the
Collection can then be accessed via its own key and various operations/
tests can be performed on it.
IMHO, this is the key. You are doing it successfully in VBA, and while
that's not exactly the same as a .NET Framework application, it does
suggest you should be able to do the equivalent somehow in C#.
Going back to your first post, I'm wondering if we couldn't have helped
you better by simply taking you a little less literally. You are
describing your code as "the program". To most of us, that implies
you've got a standalone application running as its own process.
But, from your description of the VBA, it sounds as though it may be
you simply load a C# DLL into Excel and it remains loaded for each
execution. You don't have a "program" that ends. You just keep
calling the DLL.
If that's correct, then what you want to do is probably simple. Just
declare a field in an appropriate class (perhaps the main class
managing the whole thing) that has the "static" attribute. Then as
long as that DLL remains loaded, whatever you store in that field will
remain there.
If that doesn't work, then it might be useful to be more explicit about
the part of your description that applies to how your code is used.
Specifically, when you write "some Excel functions in C# that will be
wrapped in an XLL", what do you mean exactly?
What's an "Excel function in C#"? IMHO, an "Excel function" is mainly
a built-in function, and alternatively you can extend Excel by writing
a function in VBA. But once you leave that environment, a function
isn't an Excel function. It's some more general-purpose function that
you import into Excel. So, do you just mean you've written some
general-purpose function that you want to import into Excel? Or does
your code have some tighter connection to Excel that we're not thinking
of?
And what's "wrapped in an XLL"? Are you simply compiling your C# code
as a DLL suitable for use in Excel as an XLL? It's been awhile, but my
recollection is that an XLL is just a DLL renamed, accessed from Excel
through the usual VBA DLL import mechanism (I don't even recall how
XLL's worked with XLMs...that's too long ago, and surely it's not
relevant here
). Is that what you're doing? Or have you actually
written some non-C# wrapper for your C# DLL that proxies the C# DLL to
Excel?
[...]
Please understand that I am entirely self-taught at VBA and am just
learning C# now so I apologise if my mixing of VBA & C# is causing
anyone a headache
Well, if anything your mixing of VBA and C# takes you outside what's
likely the expertise of most of us. I can't speak for the others, but
even for me having had a fair amount of experience using VBA with
Office applications, I don't generally have the need to integrate my
VBA with external code, and haven't _ever_ had the occasion to need to
do it with C# code.
I'm not saying that no one here knows how to do things like that, but I
wouldn't expect it to be a commonly held piece of knowledge.
That said, we're generally fast learners.
If you can explain in
more detail the specifics of how you've integrated your C# code with
your Excel VBA code, it's possible that will help lead to a better
answer.
That is, of course, assuming that for some reason a static field in one
of your C# classes doesn't do the trick. And if it doesn't, it would
be helpful if you could explain why it doesn't. That would probably
help with our understanding of what it is exactly you're trying to do.
Pete
