topic Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA? in .NET Forum

How to do a calculation by a formula line the evaluate method in EXCEL VBA?

swaywood — Sat, 17 Feb 2018 10:41:40 GMT

Hi:

I have a string like '(1-2+3)/4*5^2', How to calculate the fomula by some exist dll or class?

I found excel vba's evaluate function can do this, but does not work for every computer, I don't know the reason.

If you know other dll has this method, please tell me , thanks.

or you have other way to solve this?

  /// <summary>
  /// 利用Excel VBA Evaluate
  /// </summary>
  /// <param name="str"></param>
  /// <returns></returns>
  public static double Cal(this string str)
  {
   string dllName = System.Reflection.Assembly.GetExecutingAssembly().Location.ToString();
   var app = new Excel.Application();
   FileInfo info = new FileInfo(dllName);
   Excel.Workbook workbook = app.Workbooks.Add(info.Directory.FullName + "\\Cal.xlt");
   Excel.Worksheet worksheet = workbook.Worksheets.Add() as Excel.Worksheet;
   var result = worksheet.Evaluate(str);
   app.DisplayAlerts = false;
   app.Quit();
   double data = double.Parse(result.ToString());
   return data;
  }

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

_gile — Sat, 17 Feb 2018 12:43:30 GMT

Hi,

You can try this:

        static double Calc(string expr)
        {
            bool wasOpen = false;
            dynamic xlApp;
            try
            {
                xlApp = Marshal.GetActiveObject("Excel.Application");
                wasOpen = true;
            }
            catch
            {
                try { xlApp = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application")); }
                catch { throw; }
            }
                var book = xlApp.Workbooks.Add();
                var sheet = book.ActiveSheet;
            try
            {
                sheet.Cells[1, 1] = "=" + expr;
                return sheet.Cells[1, 1].Value;
            }
            catch { return double.NaN; }
            finally
            {
                book.Close(false);
                book = null;
                if (!wasOpen)
                {
                    xlApp.Quit();
                    Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                }
            }
        }

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

ActivistInvestor — Sat, 17 Feb 2018 13:53:51 GMT

If you don't want to have a dependence on Excel (which is not guaranteed to be installed on every computer your code runs on), you can use any number of open-source libraries for expression parsing and evaluation.

@swaywood wrote:
Hi:

I have a string like '(1-2+3)/4*5^2', How to calculate the fomula by some exist dll or class?
I found excel vba's evaluate function can do this, but does not work for every computer, I don't know the reason.
If you know other dll has this method, please tell me , thanks.

or you have other way to solve this?

  /// <summary>
  /// 利用Excel VBA Evaluate
  /// </summary>
  /// <param name="str"></param>
  /// <returns></returns>
  public static double Cal(this string str)
  {
   string dllName = System.Reflection.Assembly.GetExecutingAssembly().Location.ToString();
   var app = new Excel.Application();
   FileInfo info = new FileInfo(dllName);
   Excel.Workbook workbook = app.Workbooks.Add(info.Directory.FullName + "\\Cal.xlt");
   Excel.Worksheet worksheet = workbook.Worksheets.Add() as Excel.Worksheet;
   var result = worksheet.Evaluate(str);
   app.DisplayAlerts = false;
   app.Quit();
   double data = double.Parse(result.ToString());
   return data;
  }

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

swaywood — Sun, 18 Feb 2018 02:57:03 GMT

Hi @_gile:

dynamic can not be used in a framework 3.5 program?

microsoft csharp.dll's framework is 4.0+?

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

_gile — Sun, 18 Feb 2018 08:38:22 GMT

@swaywood wrote:

Hi @_gile:

dynamic can not be used in a framework 3.5 program?

microsoft csharp.dll's framework is 4.0+?

Yes the dynamic type came with the .NET Framework 4.0.

For prior Framework versions, to avoid referencing the COM libraries, you can use Late Binding with refelection.

        static object Calc(string expr)
        {
            bool wasOpen = false;
            object xlApp;
            try
            {
                xlApp = Marshal.GetActiveObject("Excel.Application");
                wasOpen = true;
            }
            catch
            {
                try { xlApp = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application")); }
                catch { throw; }
            }
            object books = xlApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, xlApp, null);
            object book = books.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, books, null);
            object sheet = book.GetType().InvokeMember("ActiveSheet", BindingFlags.GetProperty, null, book, null);
            try
            {
                object range = sheet.GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, sheet, null);
                object cell = sheet.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, range, new object[2] { 1, 1 });
                cell.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, cell, new object[1] { "=" + expr });
                return (double)cell.GetType().InvokeMember("Value", BindingFlags.GetProperty, null, cell, null);
            }
            catch
            {
                return null;
            }
            finally
            {
                book.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, book, new object[1] { false });
                book = null;
                if (!wasOpen)
                {
                    xlApp.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, xlApp, null);
                    Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                }
            }
        }

The .NET Framework 3.5 brings extension methods.

Using the following ones allows to avoid all the GetType().InvokeMember(...) stuff, and make the upper code more readable.

    public static class LateBindingextension
    {
        public static object Get(this object obj, string propName, params object[] parameter) =>
            obj.GetType().InvokeMember(propName, BindingFlags.GetProperty, null, obj, parameter);

        public static void Set(this object obj, string propName, params object[] parameter) =>
            obj.GetType().InvokeMember(propName, BindingFlags.SetProperty, null, obj, parameter);

        public static object Invoke(this object obj, string methName, params object[] parameter) =>
            obj.GetType().InvokeMember(methName, BindingFlags.InvokeMethod, null, obj, parameter);
    }

The code becomes:

        static object Calc(string expr)
        {
            bool wasOpen = false;
            object xlApp;
            try
            {
                xlApp = Marshal.GetActiveObject("Excel.Application");
                wasOpen = true;
            }
            catch
            {
                try { xlApp = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application")); }
                catch { throw; }
            }
            object book = xlApp.Get("Workbooks").Invoke("Add");
            object sheet = book.Get("ActiveSheet");
            try
            {
                object cell = sheet.Get("Cells").Get("Item", 1, 1);
                cell.Set("Value", "=" + expr);
                return (double)cell.Get("Value");
            }
            catch
            {
                return null;
            }
            finally
            {
                book.Invoke("Close", false);
                book = null;
                if (!wasOpen)
                {
                    xlApp.Invoke("Quit");
                    Marshal.ReleaseComObject(xlApp);
                    xlApp = null;
                }
            }
        }

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

ActivistInvestor — Sun, 18 Feb 2018 22:48:53 GMT

Above I write that you could use a third-party library to evaluate math expressions.

Well, I'm not sure what I was thinking when I wrote that, but you don't need any third party library (or Excel) to do that.

using System;
using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.Runtime;
using Autodesk.AutoCAD.EditorInput;
using Autodesk.AutoCAD.Internal.Calculator;


public static class CalcExample
{
   [CommandMethod("MYCALC")]
   public static void MyCalcCommand()
   {
      var doc = Application.DocumentManager.MdiActiveDocument;
      var ed = doc.Editor;
      var pso = new PromptStringOptions("\nExpression: ");
      pso.AllowSpaces = true;
      var psr = ed.GetString(pso);
      if(psr.Status == PromptStatus.OK)
      {
         try
         {
            var result = Evaluate(psr.StringResult);
            ed.WriteMessage("\nResult: {0}", result);
         }
         catch(System.Exception ex)
         {
            ed.WriteMessage(ex.Message);
         }
      }
   }


   public static double Evaluate(string expression)
   {
      if(string.IsNullOrWhiteSpace(expression))
         throw new ArgumentException(nameof(expression));
      CalcEngine engine = CalcEngine.GetEngine();
      CalcValueType valueType = new CalcValueType();
      valueType.Type = ValueTypeEnum.RealType;
      CalcResult calcResult = new CalcResult(valueType);
      if(engine.EvaluateExpression(expression, null, calcResult))
      {
         return calcResult.Result.R;
      }
      throw new ArgumentException("Failed to evaluate expression: " + expression);
   }
}

@swaywood wrote:
Hi:

I have a string like '(1-2+3)/4*5^2', How to calculate the fomula by some exist dll or class?
I found excel vba's evaluate function can do this, but does not work for every computer, I don't know the reason.
If you know other dll has this method, please tell me , thanks.

or you have other way to solve this?

  /// <summary>
  /// 利用Excel VBA Evaluate
  /// </summary>
  /// <param name="str"></param>
  /// <returns></returns>
  public static double Cal(this string str)
  {
   string dllName = System.Reflection.Assembly.GetExecutingAssembly().Location.ToString();
   var app = new Excel.Application();
   FileInfo info = new FileInfo(dllName);
   Excel.Workbook workbook = app.Workbooks.Add(info.Directory.FullName + "\\Cal.xlt");
   Excel.Worksheet worksheet = workbook.Worksheets.Add() as Excel.Worksheet;
   var result = worksheet.Evaluate(str);
   app.DisplayAlerts = false;
   app.Quit();
   double data = double.Parse(result.ToString());
   return data;
  }

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

_gile — Mon, 19 Feb 2018 08:01:18 GMT

@ActivistInvestor it seems to me CalcEngine is a third party library.

Anyway, form AutoCAD, we can use the geomcal.crx to evaluate arithmetic expressions (see >>here<< for expression reference)

        [CommandMethod("CALC")]
        public static void Calc()
        {
            var ed = Application.DocumentManager.MdiActiveDocument.Editor;
            var pso = new PromptStringOptions("\nEnter the expression: ");
            pso.AllowSpaces = true;
            var psr = ed.GetString(pso);
            if (psr.Status != PromptStatus.OK)
                return;
            var result = Evaluate(psr.StringResult);
            if (result == null)
                ed.WriteMessage("\nInvalid expression");
            else
                ed.WriteMessage($"\nResult: {result}");
        }

        public static object Evaluate (string expression)
        {
            var linker = SystemObjects.DynamicLinker;
            if (linker.GetLoadedModules().IndexOf("geomcal.crx") == -1)
                linker.LoadModule("geomcal.crx", false, true);
            var args = new ResultBuffer(
                new TypedValue((int)LispDataType.Text, "c:cal"),
                new TypedValue((int)LispDataType.Text, expression));
            var result = Application.Invoke(args);
            return result?.AsArray()[0].Value;
        }

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

SENL1362 — Mon, 19 Feb 2018 08:49:21 GMT

ClearScript, Microsofts freeware product, is my best of choice to add expressions to the C# program.

You can add simple expressions, but also add you're own Objects to use in calculations.

This also adds C# functionality to PowerShell.

But it is supported from .NET 4.0 onwards

See below for a few MS samples.

https://microsoft.github.io/ClearScript/Tutorial/FAQtorial.html

And my ow usage

using Microsoft.ClearScript.Windows;
...

using (var vbScript = new VBScriptEngine())
{
var result = vbScript.Evaluate("(1-2+3)/4*5^2");

//ClearScript(VBScript) does not support IIF, therefore we supply this as a function
vbScript.Execute("Function IIf(bClause, sTrue, sFalse)\n If CBool(bClause) Then\n IIf = sTrue\n Else\n IIf = sFalse\n End If\n End Function");
 
vbScript.AddHostObject("propVals", requiredPropertyValues);
// now we can evaluate 'user formulars' like 
//  Custom.Gemeente=discTek.Gemeente | planDoc.Custom.Plaats + iif(len(planDoc.Custom.Gemeente) > 0," gem. " & planDoc.Custom.Gemeente,"")

...

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

_gile — Mon, 19 Feb 2018 08:52:51 GMT

With the geomcal, it would be more efficient to move the loading part to a static constructor or to the IExtensionApplication.Initialize() method.

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

_gile — Mon, 19 Feb 2018 11:02:00 GMT

using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using Autodesk.AutoCAD.Runtime;

[assembly: CommandClass(typeof(AutoCAD2014Plugin.Commands))]

namespace AutoCAD2014Plugin
{
    public class Commands
    {
        static Commands()
        {
            SystemObjects.DynamicLinker.LoadModule("geomcal.crx", false, true);
        }

        [CommandMethod("CALC")]
        public static void Calc()
        {
            var ed = Application.DocumentManager.MdiActiveDocument.Editor;
            var pso = new PromptStringOptions("\nEnter the expression: ");
            pso.AllowSpaces = true;
            var psr = ed.GetString(pso);
            if (psr.Status != PromptStatus.OK)
                return;
            var result = TryEvaluate(psr.StringResult);
            if (result.HasValue)
                ed.WriteMessage($"\nType: {(LispDataType)result.Value.TypeCode} Value: {result.Value.Value}");
            else
                ed.WriteMessage("\nInvalid expression");
        }

        public static TypedValue? TryEvaluate(string expression) =>
            Application.Invoke(new ResultBuffer(
                new TypedValue((int)LispDataType.Text, "c:cal"),
                new TypedValue((int)LispDataType.Text, expression)))?.AsArray()[0];
    }
}

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

ActivistInvestor — Mon, 19 Feb 2018 12:06:13 GMT

@_gile wrote:
@ActivistInvestor it seems to me CalcEngine is a third party library.

@_gile, It seems to me that you're mistaken.

CalcEngine is what the Calculator UI and other components (like Data Extraction) depend on to access geomcal.arx.

It also implicitly loads geomcal.arx on first use.

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

ActivistInvestor — Mon, 19 Feb 2018 12:27:30 GMT

ClearScript is an embedded scripting engine, not a mathematical expression engine.

Math expression engines don't support things like conditional branching, control flow/looping, etc.

It would be a bit of overkill for simply evaluating math expressions.

@SENL1362 wrote:
ClearScript, Microsofts freeware product, is my best of choice to add expressions to the C# program.
You can add simple expressions, but also add you're own Objects to use in calculations.
This also adds C# functionality to PowerShell.

But it is supported from .NET 4.0 onwards

See below for a few MS samples.
https://microsoft.github.io/ClearScript/Tutorial/FAQtorial.html

And my ow usage
using Microsoft.ClearScript.Windows;
...

using (var vbScript = new VBScriptEngine())
{
var result = vbScript.Evaluate("(1-2+3)/4*5^2");

//ClearScript(VBScript) does not support IIF, therefore we supply this as a function
vbScript.Execute("Function IIf(bClause, sTrue, sFalse)\n If CBool(bClause) Then\n IIf = sTrue\n Else\n IIf = sFalse\n End If\n End Function");
 
vbScript.AddHostObject("propVals", requiredPropertyValues);
// now we can evaluate 'user formulars' like 
//  Custom.Gemeente=discTek.Gemeente | planDoc.Custom.Plaats + iif(len(planDoc.Custom.Gemeente) > 0," gem. " & planDoc.Custom.Gemeente,"")

...
 

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

_gile — Mon, 19 Feb 2018 12:23:41 GMT

Sorry my mistake, i didn't see the Autodesk.AutoCAD.Internal.Calculator using directive.

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

SENL1362 — Mon, 19 Feb 2018 12:30:17 GMT

LOL, and then use Excel for simple arithmetrics.

You're absolutely right when only used for simple calculations, but in my experiences it didn't stop with that.

Sometimes the values aren't just numberes any more, but Legenda properties etc.

To support that i needed tools like grep, awk etc. Furtunately MS developed ClearScript for short dynamic expressions.

For more lengtly operations, like a bunch of convertion rules to apply on drawings, i convert the rules to c#, compile and run these progs. Al in just factions of seconds.

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

ActivistInvestor — Mon, 19 Feb 2018 12:47:02 GMT

Using Excel for evaluating simple math expressions is definitely overkill, and probably the worst-possible way to solve that problem.

If the OP needs only to evaluate math expressions, AutoCAD has that ability without having to depend on additional libraries. AutoCAD's calculator engine also supports vector math, which ClearScript and most other general-purpose math expression engines don't support.

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

ActivistInvestor — Tue, 20 Feb 2018 01:25:41 GMT

I neglected to mention that In the example code I posted above, angles are expressed as degrees rather than radians. The CAL command and the (cal) LISP function also express angles in degrees, and I'm not sure there's any way to change that.

But since that's considered non-standard for math expressions in general (e.g, System.Math, LISP, C/C++, etc.), the following revision of the Evaluate() method from the above code will interpret and return angles as radians rather than degrees. So, while the CAL command or (cal) lisp function returns 45.0 for the expression 'atan(1.0)', the revised Evaluate() method shown below will return pi/4.0:

public static double Evaluate(string expression)
{
   if(string.IsNullOrWhiteSpace(expression))
      throw new ArgumentException(nameof(expression));
   CalcEngine engine = CalcEngine.GetEngine();
   CalcValueType valueType = new CalcValueType();
   valueType.Type = ValueTypeEnum.RealType;
   CalcResult calcResult = new CalcResult(valueType);
   CalcContext context = new CalcContext();
   context.AngleUnits = AngleUnitsEnum.Radian;
   if(engine.EvaluateExpression(expression, context, calcResult))
   {
      return calcResult.Result.R;
   }
   throw new ArgumentException("Failed to evaluate expression: " + expression);
}

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

swaywood — Fri, 23 Feb 2018 14:03:19 GMT

Hi @ActivistInvestor

Thank you very much.

Do you have more help doc of CalcEngine?

I don't know how to write the 'string expression'.

In my case i will use '+,-,*,/,^(Power),Sqrt,Sqr,(),Pi'

best wishes

swaywood

Re: How to do a calculation by a formula line the evaluate method in EXCEL VBA?

ActivistInvestor — Fri, 23 Feb 2018 19:27:43 GMT

@swaywood wrote:
Hi @ActivistInvestor

Thank you very much.
Do you have more help doc of CalcEngine?

I don't know how to write the 'string expression'.

In my case i will use '+,-,*,/,^(Power),Sqrt,Sqr,(),Pi'

best wishes
swaywood

The CalcEngine is what evaluates expressions for AutoCAD's calculator (CAL and QUICKCALC Commands).

You can see the docs for the calculator for the expression syntax, and keep in mind that the second version of the code I posted above uses radians rather than degrees (as the CAL and QUICKCALC command use).

With the second version of the Evaluate() method posted above,

These functions use radians in their argument:

sin(angle)
cos(angle)
tang(angle)

And, these functions return an angle in radians:

asin(real)
acos(real)
atan(real)

There is also another major difference between the CalcEngine and the (C:CAL) and (cal) LISP functions, which is in how integers are handled.

Using the (CAL) (or C:CAL) lisp function:

Command: (cal "50000+12000")
-3536

Using the CAL command:

Command: CAL
>> Expression: 50000+12000
62000

Using the MYCALC command from the example posted above:

Command: MYCALC
Expression: 50000+12000
Result: 62000

Well, I guess they forgot to mention that. 8)