How can I evaluate a string into an object in VBA?

Question

In my previous question, How do I assign a value to a property where the property name is supplied at runtime in VBA?, I learned to use CallByName to set a property in a class at run time.

This time, however, I'm trying to figure out how to get an object at run time from a string.

For example, let's say I have a string with the following data: Worksheets("RAW DATA").Range("A1").QueryTable.

Here's what I might try to do where the data above is the input for strParam below:

Function GetObject(strParam As String) As Object
    GetObject = SomeFunction(strParam)
End Function

In this case, GetObject should return a QueryTable when evaluated against Worksheets("RAW DATA").Range("A1").QueryTable. Is there anything in VBA that could take the place of SomeFunction from the example above?

Not that I know of, but dude! Your code must be nuts to maintain!!! Are you sure Excel is the best tool for what you are trying to do??? — Christian Payne
– Christian Payne, Commented Nov 17, 2009 at 0:22
Excel's certainly not the best solution from a "developer-friendly" programming approach. However, the end solution must be in Excel and it's something I'm familiar with. In this question, I'm trying to store "initialization variables" outside of the actual code as much as possible. It's been a good lesson in i-think-i'm-wasting-too-much-time-trying-to-make-this-perfect. — Ben McCormack
– Ben McCormack, Commented Nov 17, 2009 at 0:51
I'm kind of intrigued...what exactly are you trying to do that you want to supply arbitrary VBA code at runtime? In your example it seems like you're just trying to get the QueryTable that goes with a given range, but that just means making the range address a parameter, not the whole VBA statement. You shouldn't need to go down the whole "soft coding" path... — jtolle
– jtolle, Commented Nov 17, 2009 at 1:05
I just saw your previous comment. Things like "Worksheets" and "Range" and "QueryTable" definitely fall firmly into the category of "source code" and not "initialization variables". — jtolle
– jtolle, Commented Nov 17, 2009 at 1:09
That's a very good point. I'm trying to automate part of the process involved with preparing a delivery manifest at our company. These have existed as Excel workbooks, so I'm writing code to read what order numbers are entered and auto-populate other fields with data from the database. I'm trying to keep it flexible and avoid "hard-coding" as much as possible (which works with simple types), but I think it's time to just get the job done. It's works fairly well anyway :) — Ben McCormack
– Ben McCormack, Commented Nov 17, 2009 at 1:44

omegastripes · Accepted Answer · 2017-08-14 10:42:12Z

7

Active Scripting Engine can help you. Instantiate ScriptControl ActiveX, use .AddObject() method to add reference to Excel's Application object to the script control's execution environment, set the third parameter to True to make all Application's members accessible too. Then just use .Eval() method to evaluate any property or method, which is the Application's member. The example below shows evaluation of Worksheets() property:

Sub TestQueryTable()
    Dim objQueryTable As QueryTable
    Dim strEvalContent As String
    strEvalContent = "Worksheets(""RAW DATA"").Range(""A1"").QueryTable"
    Set objQueryTable = EvalObject(strEvalContent)
    objQueryTable.Refresh
    MsgBox objQueryTable.Connection
End Sub

Function EvalObject(strEvalContent As String) As Object
    With CreateObject("ScriptControl")
        .Language = "VBScript"
        .AddObject "app", Application, True
        Set EvalObject = .Eval(strEvalContent)
    End With
End Function

If you are on 64-bit Office, this answer may help you to get ScriptControl to work.

edited Aug 14, 2017 at 10:42

answered Nov 3, 2015 at 19:53

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

William Over a year ago

This is great, but can it be used to instantiate custom-defined classes scoped to a specific Excel Workbook?

omegastripes Over a year ago

@William check this answer, it shows how to instantiate a class by name.

jtolle · Accepted Answer · 2010-10-15 01:47:07Z

0

This time you're out of luck. There is no VBA equivalent of eval (not in Excel anyway...there is in Access VBA).

(Application.Evaluate() evaluates strings as Excel expressions, not as VBA code.)

edited Oct 15, 2010 at 1:47

answered Nov 17, 2009 at 0:49

jtolle

7,1742 gold badges31 silver badges52 bronze badges

1 Comment

Ben McCormack Over a year ago

This may be a sign to stop trying to hack VBA and simply delivery a solution :)

SnakeWasTheNameTheyGaveMe · Accepted Answer · 2009-11-17 00:03:48Z

-1

There's the "Evaluate" method (or [ ] brackets). I don't think it will do exactly what you expect - as in run VBA code found in a string. You can look it up in the VBA help menu.

answered Nov 17, 2009 at 0:03

SnakeWasTheNameTheyGaveMe

2,5822 gold badges28 silver badges73 bronze badges

Collectives™ on Stack Overflow

How can I evaluate a string into an object in VBA?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related