View Full Version : Variable Indirection
macleanb
02-28-2007, 01:01 AM
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
dim oClassInstance
set oClassInstance = getVariableByName("glb_oRiskCache")
CallByName oClassInstance, "MyMethod".....
"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:
Bob Phillips
02-28-2007, 03:15 AM
Use Application.Run, for example
Application.Run "book2.xls!test_msgbox", 4, 8
macleanb
02-28-2007, 04:09 AM
Dont know if I'm being dumb - but how does that help me with getting the value of a global variable?
Bob Phillips
02-28-2007, 04:49 AM
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.
macleanb
02-28-2007, 04:51 AM
No - can do that, it's calling a method on a class instance (in a global variable) - Thanks anyway
Bob Phillips
02-28-2007, 05:05 AM
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
Dim glb_oRiskCache As Object
Set glb_oRiskCache = New Class1
MsgBox glb_oRiskCache.MyMethod
Set glb_oRiskCache = New Class2
MsgBox glb_oRiskCache.MyMethod
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.
macleanb
02-28-2007, 05:42 AM
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
Bob Phillips
02-28-2007, 06:47 AM
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?
macleanb
02-28-2007, 06:59 AM
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
Bob Phillips
02-28-2007, 07:16 AM
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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.