Mathematica Functions from VBA

Question

I'm trying to call Mathematica functions from VBA (Excel) using .NETLink without success.

(Yes, I know Mathematica Link for Excel exists. And yes, I know the proper thing to do is buy it. However, it's not listed as compatible with v9, and on top of that, Episoft's website hasn't been updated since the end of 2011, so I'm not sure I want to rely on that for a long-term solution.)

Reading the .NET/Link API documentation is akin to hieroglyphics, and the information out there on how to hook up the two is pretty sparse.

I've narrowed it down to these items (after studying the SimpleLink example program included with Mathematica.

• Reference needed to NETLink .dll file (located under FileNameJoin[{$InstallationDirectory, "SystemFiles", "Links", "NETLink"}])
• Using MathKernel.Compute() and MathKernel.Results() as main functions to send data back and forth.

When I set this up in VBA, I get the ever-present error "Can't find DLL entry point".

Public Declare Sub MMACompute Lib "C:\Program Files\Wolfram Research\Mathematica\9.0\SystemFiles\Links\NETLink\Wolfram.NETLink.dll" Alias "MathKernel.Compute" (expr As String)
Public Declare Function MMAResults Lib "C:\Program Files\Wolfram Research\Mathematica\9.0\SystemFiles\Links\NETLink\Wolfram.NETLink.dll" Alias "MathKernel.Result" ()


Sub test()

  MMACompute ("2+2")
  Debug.Print MMAResults()

End Sub

Any experience out there in getting this set up correctly? (Win7 x64, Office 2007 32-bit, MMA v9.01)

Answer 1

I've got VBA calling Mathematica functions. It's not without issues, but maybe some other smart people here can help with the hiccups.

First things first:

The .dll that Mathematica includes with its installation for .NETLink is not COM-compatible, meaning that VBA cannot find entry points into the dll functions. To get around this, .NET must be installed on your computer, and you use a command-line program included with .NET called regasm.exe to convert the dll to a COM-compatible type library (.tlb). This can then be referenced by VBA and the included functions can be viewed within the VBA object browser.

To do this:

• Open command prompt and navigate to the .NET install location (mine is at C:\Windows\Microsoft.NET\Framework\vXXXX\
• Run regasm.exe "location_of_dll.dll" /tlb: "location_of_new_tlb.tlb". See here.

Now, when in VBA Editor, add a reference to this .tlb. The .NETLink functions should now be available.

Here is the function I wrote to execute a computation. It is adapted from the SampleLink program.

Public Function EVAL(inputstring As String)
    Dim Results As Variant

    Dim ml As Wolfram_NETLink.MathKernel
    Set ml = New Wolfram_NETLink.MathKernel
    ml.AutoCloseLink = True
    ml.CaptureGraphics = True
    ml.CaptureMessages = True
    ml.CapturePrint = True
    ml.GraphicsFormat = "Automatic"
    ml.GraphicsHeight = 0
    ml.GraphicsResolution = 0
    ml.GraphicsWidth = 0
    ml.HandleEvents = True
    ml.Input = inputstring
    ml.LinkArguments = Null
    ml.PageWidth = 60
    ml.ResultFormat = Wolfram_NETLink.ResultFormatType_OutputForm
    ml.UseFrontEnd = True
    Results = ml.Compute()
    Results = ml.Result
    EVAL = Results
End Function

So this works once. After which you must quit the kernel to execute another computation.

The basics are presented here, but with a little help this could be a viable interface between Excel and Mathematica.