Visual Basic, At wits end..formula execution

Herbi
#1 / 10

At wits end..formula execution

Greetings All !!

In our app for the petroleum distribution industry there are gobs and gobs
of locally specific taxing calculation needed when a petroleum product is
sold.

They range from Federal, State, Local, Sales Tax, ... special environmental
taxation that make up to 60% of the retail price of your fuel.

The problem is that these formula vary widely from state to state that
involve multiplication, addition, division and subtraction.

Is there a way to allow the user to enter the formula into a text box then
later calculate the results under program control?

--Herbie

Wed, 18 Jun 1902 08:00:00 GMT

mike_hill..
#2 / 10

At wits end..formula execution

I don't know what you're using for a database, but if it's oracle,
here's an example using ADO and a stored procedure:

CREATE OR REPLACE
FUNCTION CALC_FORMULA
(
Formula_i IN VARCHAR
)
RETURN NUMBER
IS
Result_o NUMBER(16,5) := 0;
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,
'SELECT ' || Formula_i ||
' FROM DUAL',
DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(cur,
1,
Result_o);
rc := DBMS_SQL.EXECUTE(cur);
rc := DBMS_SQL.FETCH_ROWS(cur);
DBMS_SQL.COLUMN_VALUE(cur,
1,
Result_o);
DBMS_SQL.CLOSE_CURSOR(cur);
RETURN Result_o;
END;

public sub test()

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim sConnectionString as String

Set con = New ADODB.Connection
Set cmd = New ADODB.Command
con.Open sConnectionString

cmd.ActiveConnection = con
cmd.CommandText = "{? = CALL CALC_FORMULA (?)}"

cmd.Parameters.Append cmd.CreateParameter("Result_o", adDouble,
adParamOutput, 20, Null)
cmd.Parameters.Append cmd.CreateParameter("Formula_i", adVarChar,
adParamInput, 20, "7 * 8 + 1")

cmd.Execute
MsgBox (cmd.Parameters(0))

end sub

Good Luck,

-Mike

Quote:

> Greetings All !!

> In our app for the petroleum distribution industry there are gobs and
gobs
> of locally specific taxing calculation needed when a petroleum
product is
> sold.

> They range from Federal, State, Local, Sales Tax, ... special
environmental
> taxation that make up to 60% of the retail price of your fuel.

> The problem is that these formula vary widely from state to state that
> involve multiplication, addition, division and subtraction.

> Is there a way to allow the user to enter the formula into a text box
then
> later calculate the results under program control?

> --Herbie

Sent via Deja.com http://www.deja.com/
Before you buy.

Wed, 18 Jun 1902 08:00:00 GMT

Bill Pfeife
#3 / 10

At wits end..formula execution

Try this:

http://www.ucalc.com/mathparser/index.html

Quote:

> Greetings All !!

> In our app for the petroleum distribution industry there are gobs and gobs
> of locally specific taxing calculation needed when a petroleum product is
> sold.

> They range from Federal, State, Local, Sales Tax, ... special
environmental
> taxation that make up to 60% of the retail price of your fuel.

> The problem is that these formula vary widely from state to state that
> involve multiplication, addition, division and subtraction.

> Is there a way to allow the user to enter the formula into a text box then
> later calculate the results under program control?

> --Herbie

Wed, 18 Jun 1902 08:00:00 GMT

ADP
#4 / 10

At wits end..formula execution

Herbie,

If you're really determined to give your users enough firepower to do
damage, consider embedding the MS Script Control into your app. Then your
users can give it formulae, or even bits of code, to execute at runtime.

--A

Wed, 18 Jun 1902 08:00:00 GMT

#5 / 10

At wits end..formula execution

Wed, 18 Jun 1902 08:00:00 GMT

Herbi
#6 / 10

At wits end..formula execution

--A,

Found the solution, Checkout:

http://www.ucalc.com/mathparser/index.html

--Herbie

Quote:

> Herbie,

> If you're really determined to give your users enough firepower to do
> damage, consider embedding the MS Script Control into your app. Then your
> users can give it formulae, or even bits of code, to execute at runtime.

> --A

Wed, 18 Jun 1902 08:00:00 GMT

#7 / 10

At wits end..formula execution

Wed, 18 Jun 1902 08:00:00 GMT

GraphMa
#8 / 10

At wits end..formula execution

IF you can rely on "Excel" being installed on a system, you can
reference Excel and use its' considerable ability to parse algebraic
expressions from inside your VB program.

You could do this without showing Excel to the user.
OR
You could present an Excel worksheet to the user via the OLE container
control
OR - my favorite
Just write your VB application from inside Excel.

Excel is my favorite math library - Graphman

Quote:

> Greetings All !!

> In our app for the petroleum distribution industry there are gobs and
gobs
> of locally specific taxing calculation needed when a petroleum
product is
> sold.

> They range from Federal, State, Local, Sales Tax, ... special
environmental
> taxation that make up to 60% of the retail price of your fuel.

> The problem is that these formula vary widely from state to state that
> involve multiplication, addition, division and subtraction.

> Is there a way to allow the user to enter the formula into a text box
then
> later calculate the results under program control?

> --Herbie

Sent via Deja.com http://www.deja.com/
Before you buy.

Wed, 18 Jun 1902 08:00:00 GMT

CCS
#9 / 10

At wits end..formula execution

If I understand the ADO object model regarding stored procedures,

Then:
cmd.ActiveConnection = con
cmd.CommandText = "{? = CALL CALC_FORMULA (?)}"
cmd.Parameters.Append cmd.CreateParameter("Result_o", adDouble,
adParamOutput, 20, Null)
cmd.Parameters.Append cmd.CreateParameter("Formula_i", adVarChar,
adParamInput, 20, "7 * 8 + 1")

will parse your sp string as {? = CALL CALC_FORMULA(?,?,?)}

leaving params(0) and (1) uninitialized.

Let ADO do what ADO does:

cmd.ActiveConnection = con
cmd.CommandText = "CALC_FORMULA"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("Result_o", adDouble,
adParamOutput, 20, Null)
cmd.Parameters.Append cmd.CreateParameter("Formula_i", adVarChar,
adParamInput, 20, "7 * 8 + 1")

Regards,
Charles Storm.

Quote:

>I don't know what you're using for a database, but if it's oracle,
>here's an example using ADO and a stored procedure:

>CREATE OR REPLACE
>FUNCTION CALC_FORMULA
>(
> Formula_i IN VARCHAR
>)
>RETURN NUMBER
>IS
> Result_o NUMBER(16,5) := 0;
> cur integer;
> rc integer;
>BEGIN
> cur := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cur,
> 'SELECT ' || Formula_i ||
> ' FROM DUAL',
> DBMS_SQL.V7);
> DBMS_SQL.DEFINE_COLUMN(cur,
> 1,
> Result_o);
> rc := DBMS_SQL.EXECUTE(cur);
> rc := DBMS_SQL.FETCH_ROWS(cur);
> DBMS_SQL.COLUMN_VALUE(cur,
> 1,
> Result_o);
> DBMS_SQL.CLOSE_CURSOR(cur);
> RETURN Result_o;
>END;

>public sub test()

>Dim con As ADODB.Connection
>Dim cmd As ADODB.Command
>Dim sConnectionString as String

> Set con = New ADODB.Connection
> Set cmd = New ADODB.Command
> con.Open sConnectionString

> cmd.ActiveConnection = con
> cmd.CommandText = "{? = CALL CALC_FORMULA (?)}"

> cmd.Parameters.Append cmd.CreateParameter("Result_o", adDouble,
>adParamOutput, 20, Null)
> cmd.Parameters.Append cmd.CreateParameter("Formula_i", adVarChar,
>adParamInput, 20, "7 * 8 + 1")

> cmd.Execute
> MsgBox (cmd.Parameters(0))

>end sub

>Good Luck,

>-Mike

>> Greetings All !!

>> In our app for the petroleum distribution industry there are gobs and
>gobs
>> of locally specific taxing calculation needed when a petroleum
>product is
>> sold.

>> They range from Federal, State, Local, Sales Tax, ... special
>environmental
>> taxation that make up to 60% of the retail price of your fuel.

>> The problem is that these formula vary widely from state to state that
>> involve multiplication, addition, division and subtraction.

>> Is there a way to allow the user to enter the formula into a text box
>then
>> later calculate the results under program control?

>> --Herbie

>Sent via Deja.com http://www.deja.com/
>Before you buy.

Wed, 18 Jun 1902 08:00:00 GMT

J Fren
#10 / 10

At wits end..formula execution

All the suggestions sound viable - but the concept of allowing the
Users to generate VB Code gives me the jitters.

Personally I would create a library of ActiveX EXE calculation
routines and allow the users to pick the routine they want in a
configuration module. Rates and text can be configurable.

That way you just send out another small module each time a new and
weird formula turns up.

Quote:

>Greetings All !!

>In our app for the petroleum distribution industry there are gobs and gobs
>of locally specific taxing calculation needed when a petroleum product is
>sold.

>They range from Federal, State, Local, Sales Tax, ... special environmental
>taxation that make up to 60% of the retail price of your fuel.

>The problem is that these formula vary widely from state to state that
>involve multiplication, addition, division and subtraction.

>Is there a way to allow the user to enter the formula into a text box then
>later calculate the results under program control?

>--Herbie

Wed, 18 Jun 1902 08:00:00 GMT