Set Variable by Name

28 Dec 2020: for further thoughts on this subject see Set Variable By Name Revisited.

29 July 2017: custom function updated to simplify the clearing of existing vars.

16 April 2017: custom function has been updated to auto-interpret dates (rather than treating them as sequential division problems)… e.g., to recognize “4/16/2017” as a date, as opposed to “4 ÷16 ÷ 2017”.

Recently, in the midst of various reporting and charting projects, I’ve found myself wishing for an easy way to “dynamically instantiate” one or more variables… in other words, set them by name, with the name determined programmatically, as opposed to being hard coded.

This issue has come up from time to time on this blog over the last five years or so, most notably in Dynamic Variable Instantiation, where I observed that…

…and then proposed an unwieldy mass of gobbledygook as a workaround, e.g.,

Actually, the preceding isn’t too bad for occasional use, especially if the value you’re passing to the variable is as simple as the GetSummary string shown above.

But as I began using this methodology on a more regular basis, and as the values I wanted to pass to the variable grew in complexity, it eventually dawned on me that a custom function would make my life easier… and not finding anything out there that exactly fit my needs, I located a viable candidate on Brian Dunning’s site, and proceeded to modify it.

I decided to call it SetVarByName, and it’s installed in this bare-bones file if you’d like to take it for a test spin: SetVarByName.zip

My goals for the CF

• simple to implement and use
• standalone — not part of a framework
• allow zero and negative repetitions (in addition, of course, to positive reps)
• allow $ and $$ variables to be defined (default to $ if unspecified)
• “value” automatically interpreted as either a static text string or as runtime code
• provide an easy way to clear a variable

Definition

One of Vaughan Bromfield’s improvements to the original CF was to wrap the main portion in EvaluationError — which returns a zero when there are no obvious problems with your syntax. I took the idea a step further and added a separate EvaluationError test to the initial Let declaration, facilitating the CF’s ability to guess whether you’re passing it a static text string or runtime code that needs to be interpreted.

Static Value Example

Result:

Runtime Code Example

Explanation:

• $counter = 2
• “$col_” & $counter = “$col_2”
• $col_2 = 12
• $col_1 = 3
• $col_2 / $col_1 = 4

Overriding Automatic Detection of Runtime Code

If, for some odd reason, you need to override the automatic detection of run-time code, then use a combination of Quote and, if necessary, escaped quotation marks, e.g., this…

…yields this:

The point being, with this CF, you save the syntactical contortions for once-in-a-blue-moon scenarios, whereas most of the time it does what you want with a minimum of fuss.

At any rate, it’s making my life easier, and we’ll see it in action next time around when we begin a multi-part series on Virtual List Reporting.

Finally, I want to say thank you to Rob Russell, Howard Schlossberg, Geoff Gerhard and Darren Terry, who provided useful feedback and suggestions regarding the custom function.