Variable Indirection

Hi Guys

I am writing a fairly complex app in Excel. Part of this is to have what I have called "Calc Plans" which are a series of steps to achive a users required calculations. Basically I have a loop which consumes a list of instructions. Now I've got 99% working - I can

Calc ranges/sheets
Call methods on requsted sheets
call public subs

However the last piece of the puzzle is to call methods on class instances. Now I can indirect the method name via CallByName if I use the actual variable name which is the instance of the class, but I cant figure out how to indirect my reference to my class - and I REALLY dont want to build a big case statement to this.

SO what I want to achive is something like


[VBA]
dim oClassInstance

set oClassInstance = getVariableByName("glb_oRiskCache")
CallByName oClassInstance, "MyMethod".....

[/VBA]

"glb_oRiskCache" is a global variable that holds an instance of one of my classes - all my classes have a similar interface (BTW anyone got a good link to using interfaces in VBA?)

What I need is the "GetVariableByName" function.


Any thoughts/ideas very greatfully received
:banghead: :banghead: :banghead:

Use Application.Run, for example

Application.Run "book2.xls!test_msgbox", 4, 8

Dont know if I'm being dumb - but how does that help me with getting the value of a global variable?

Probably me, I didn't really understand all of what you were saying, but I thought you were trying to run procedures using a variable rather than a big select.

No - can do that, it's calling a method on a class instance (in a global variable) - Thanks anyway

I have re-read your original post and I still don't think I get it, but let's try to clarify.

Are you saying that you have a global variable that holds a class instance, and you want to call a method within the currently refrenced class? If so, isn't it as simple

[vba]
Dim glb_oRiskCache As Object

Set glb_oRiskCache = New Class1
MsgBox glb_oRiskCache.MyMethod

Set glb_oRiskCache = New Class2
MsgBox glb_oRiskCache.MyMethod
[/vba]
as an example.

Wherever you are identifying glb_oRiskCache (as a string presumably) you could create the class instance there and then.

Another alternative is to create a collection class for the classses.

Right - we're getting closer. So your second suggestion is one of two options I am considering. I have a series of caches

glb_oRiskCache
glb_oTradeCache
glb_oMarketDataCache
glb_oQueryNames
etc....

They all have a method "Refresh" I want to be able to call that refresh method on any of the caches without harcoding the cache name.

Rengineering my code so that all my global caches are now in a dictionary does not exactly fill me with glee, so my probable work around is to have a wrapper function that takes a string as input and returns the correct object. However, either of these work-arounds will require more maintenance and requires more "hard coding" than I would like - so if you get any clever ideas let me know

cheers & ttfn

Okay, let me get my head around it and see if I can think of anything.

First off, let's agree that there is no (direct) way of calling the class by variable name. You can do that with userforms, but this is via the forms collection, there is no classes collection. Shame, but there you are.

Big problem with the obvious alternatives is the amount of logic that you will need to include. But is that such a bad thing? You will only do it once, you can test it and prove it in isolation, and forget it. I assume (though reading it I think you may be) that you don't think there will be classes not catered for by the selection logic, after all when you add a new class, you can add to to the selector.

By cache, you mean class?

Another question, if all these classes have the same interface, is the logic still so different that separate classes are needed?

Hi

Well I am hoping (weakly) that we DONT have to agree to that - but I'm darned if I can find it.

implementation of work-around is not huge

when I say "cache" I mean an object / class instance

Some objects are exactly the same class some are very different classes, I dont have any worries about my class implementations, just how to access the objects - even if they were all one single class, there would still be dozens of instances of this same class and it wouldnt help the solution any (would it?).


Many thanks for your input

No, I think that if your classes are logically very different just with exposing a common interface, I would agree that separate classes is a (far) better way to go. Even if some are the same, I think you would have a powerful intellectual argument for keeping them separate when you have other classes that are different, it will aid clarity, and therefore maintenance.

As an aside, you mention ... either of these work-arounds will require more maintenance and requires more "hard coding" than I would like ... What is this hard-coding, and would it be eased if you stored that in a data worksheet and retrived it dynamically?