variable name based on calculated info & iteration formula

[Evan]

Hi,
I'm writing a formula that will automate excels goal seek
functionality. That is, I want to type a formula =iterate(C2, 25.2),
which will return a value in the called cell, so that the value in C2
gets closer to 25.2. The calculation option will be set to iterate,
so that the sheet continues to calculate until some convergence
parameter is met.

In order to do this I need to declare several static variables or
names to store the past values of the calling cell, C2, and a
convergence parameter. Because this function could be called from
many cells in the same worksheet, the variable names need to be
different for each cell that it is called from.

So, I need to be able to do something like this:
stringName = "hello" & "world"
static [stringName] as single
helloworld = 5#

and then have the variable helloworld hold the value indefinitely

Alternatively, If I could do
Names.Add Name:=strName RefersTo:=3#
Names(strName).Value = 5#

And then be able to recall the value as Names(strName).Value, that
would work.
I have not been able to do either of these successfully.
If anyone could suggest a workaround, or the correct syntax for either
of these methods (I don't think they can be used though), that would
be very helpful.

 

[Stephen Bullen]

Hi Evan,

In order to do this I need to declare several static variables or
names to store the past values of the calling cell, C2, and a
convergence parameter. Because this function could be called from
many cells in the same worksheet, the variable names need to be
different for each cell that it is called from.

Neat idea. I would put the elements to store each time into a variant
array, then store those arrays in a Dictionary object (from the
Microsoft Scripting Runtime, scrrun.dll), keyed on the full address of
the calling cell.

Ideally, we'd put then in a UDT instead, but Excel VBA doesn't like us
storing UDTs in Dictionaries.

I liked the idea so much, I just wrote a function to do it. I'll
happily share it if you like, but I thought I'd check in case you
wanted to try it yourself first.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[Evan]

Stephen,

Thanks very much for your help. I've sent an email to the address on
your web page, so that you can email me the source code that you wrote.
I'm sure that it will be helpful in getting me started.