Build VBA expression from a string

[Bryhni]

Dear all, it's late at night where I am and my brain is not working anymore (if it ever was): I want to build and evaluate a VBA expression by reading in a string with a VBA expression from a spreadsheet cell. The purpose is to match up tags based on recognized patterns. Given a tag pattern is recognized, I want to see if I can manipulate the string until I can find a match in a range. Example: ' Cell A1 contains the string "Mid(Tag,7,12) & Right(Tag)" and Tag is a string variable used in the VBA Function. How can I evaluate a TestTag based on this string:

TestTag = Evaluate(Range("A1").Value)

... obviously doesn't work. If I coded all the different variants into the VBA code it would look like this:

TestTag = Mid(Tag,7,12) & Right(Tag)

.... which obviously does work. But it does not provide the clarity of a user-accessible table with all patterns and manipulation options laid out in a sheet. Any hints?

 

[djreiswig]

I think it is possible without the variables, but I'm not sure how to do it with the variable "Tag" in there.

 

[Bryhni]

Thanks, djreiswig! You nudged me in the right direction: By replacing the variable "Tag" with the value of Tag, a string, the expression became purely "formula" and could be evaluated:

TestPattern = Evaluate(Range("A1").Value) TestTag = Replace(TestPattern, "Tag", Chr(34) & Tag & Chr(34) TestTag=Evaluate(TestPattern)

Probably not the most elegant code, but it works.

 

[djreiswig]

Great. Glad it helped.