How to do a calculation by a formula line the evaluate method in EXCEL VBA?
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;
}
}
}
Accepted solution
@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;
}
}
}
@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;
}
Accepted solution
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];
}
}
@_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.
