Dynamic Variable Name

[Jake]

I have a whole slew of variables that are similarly named:

for example

abcdMax = 10
efghMax = 20
ijklMax = 30 ...

I want to use the "Max" variable in a function based upon a string
that is sent to the function

The following code will not work, but I think it illustrates what I am
trying to do....


Function myFunction(incomingstring as String)

fourletters = Left(incomingstring, 4)

dynamicvariable = fourletters & "Max"

myFunction = Dateadd("m", dynamicvariable, "00:00")

End Function


In this code "dynamicvariable" is a string, whereas I want the value
of the variable with the same "name" as this string. I understand
that I could handle my task with a very long case statement, but I am
searching for more brevity. Any and all creative solutions are
appreciated!


Thanks,
Jake

 

[Tom Ogilvy]

Function myFunction(incomingstring as String)

fourletters = Left(incomingstring, 4)

dynamicvariable = fourletters & "Max"

myFunction = Dateadd("m", Evaluate(Range(dynamicvariable).RefersTo),
"00:00")

End Function

 

[Bob Phillips]

Use an array of possible values, and an array of dynamic values, find the
'incomingstring' in the former array, this gives you an index into the
latter array.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)