Dynamic code in VBA? "EXEC" function?

M

Maury Markowitz

I have a sort of one-off need to be able to type a formula into a database
field, as a string, and then execute it later. The formula would be something
like "DTE * 1.5 / 10", the first part of the string being an identifier. The
code would look up the number for DTE (it's not a variable) and then run the
formula.

In many languages there is an "EXEC" function for this purpose, but I can't
seem to find anything similar in the VBA docs.

Maury
 
D

Dirk Goldgar

Maury Markowitz said:
I have a sort of one-off need to be able to type a formula into a
database field, as a string, and then execute it later. The formula
would be something like "DTE * 1.5 / 10", the first part of the
string being an identifier. The code would look up the number for DTE
(it's not a variable) and then run the formula.

In many languages there is an "EXEC" function for this purpose, but I
can't seem to find anything similar in the VBA docs.
Click to expand...

The closest is probably the Eval() function. However, you're going to
have to look up the value of DTE and replace it in the string before
evaluating it.
 
T

tw

Why would you put a calculation in a field. If you need to do some kind of
calculation, why not do it in a query.
 
D

david epsom dot com dot au

What he means is that the query engine uses the same
VBA code evaluator as used by Eval, Run, and CallByName:

s = "[DTE] * 1.5/10"
sSQL = "update t set f = " & s & ";"
Codedb.Execute sSQL

Unfortunately, you can't combine the two methods:
'xxxxxx "Select eval(fldEval) from mytable" xxxxxx'
So if you want to use the query expression evaluator,
you still have to use code to create the expression,
just as you would with Eval or Run. However, even
better, you can use a user-defined function to embed
one evaluation into the other:

function MyEval(s)
MyEval = eval(s)
end function

sSQL = "select MyEval([dte] & "*" & [strMethod]) from t;"

The query evaluator will get the field values, do the
string concatenation, and call the user-defined function
for each record: MyEval("5 * 1.5/10"). The user-defined
function will evaluate the string and return a value
for each record: eval("5 * 1.5/10")

As a matter of interest, Eval and Run can't be used
directly in SQL because they are methods of the
Application object: CallByName is a method of the
VBA object, so, theoretically, you might be able to
directly use CallByName inside a query.

(david)
 
M

Maury Markowitz

david epsom dot com dot au said:
function MyEval(s)
MyEval = eval(s)
end function

sSQL = "select MyEval([dte] & "*" & [strMethod]) from t;"
Click to expand...

Ohhh, I like that.

Actually I considered using the SQL engine to drive this all, I've done
some dynamic SQL in the past. The problem is that I don't want the user to
have to type in a select statement. But that might not be an issue, following
the method you have above I could put the partial formula in the DB, fetch
the root price (DTE), combine the two in a string and presto.

Now I just have to come up with a reasonable way to encode this. 9 times
out of 10 the formula will be "get the price of the underlying stock and
multiply by x". It should be easy for the user to define "the underlying"
(easy as in that's the default). Perhaps I could use some sort of code, like
{IBM}, in the case where the price is based on some other instrument entirely.

Maury
 
Top