20
$\begingroup$

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)

$\endgroup$
8
  • $\begingroup$ i.stack.imgur.com/4Y6MK.png $\endgroup$ Apr 23, 2013 at 15:45
  • $\begingroup$ @belisarius Interesting. I called the folks at WRI and they made it clear in no uncertain terms that v3.5 of the Excel Link tool was not compatible with their latest and greatest version of MMA. $\endgroup$
    – kale
    Apr 23, 2013 at 15:49
  • $\begingroup$ Could it have to do with the fact that belisarius is using a 32-bit Mathematica while you're using a 64 bit one? Are .NET dlls specific to 32-bit or 64-bit systems? InstallableNET.exe comes in a 32 bit and a 64 bit flavour but there's only a single dll. $\endgroup$
    – Szabolcs
    Apr 23, 2013 at 23:42
  • $\begingroup$ @Szabolcs Which brings up another point... What is the InstallableNET.exe application used for? $\endgroup$
    – kale
    Apr 24, 2013 at 1:06
  • $\begingroup$ @kale It's used for NETLink. It's a MathLink program that is launched using Install when you run InstallNET[]. I'm almost certain it's not used for what you're using it here. $\endgroup$
    – Szabolcs
    Apr 24, 2013 at 1:30

1 Answer 1

16
$\begingroup$

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.

enter image description here

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.

$\endgroup$
2
  • $\begingroup$ If you don't mind me asking where did you put the ouput .tlb I ran the following but haven't gotten your code to work just yet. C:\Windows\Microsoft.NET\Framework\v4.0.30319>regasm.exe "C:\Program Files\Wolfram Research\Mathematica\10.0\SystemFiles\Links\NETLink\Wolfram.NETLink.dll" /tlb: C:\Program Files\Wolfram Research\Mathematica\10.0\SystemFiles\Links\NETLink\Wolfram.NETLink.tlb $\endgroup$
    – William
    Jan 15, 2016 at 18:40
  • $\begingroup$ @William It's been awhile since I've looked at this, but I believe regasm creates the type library and registers it. You should be able to Browse for it in the VBA references dialog box. Then you can call those functions I have referenced. I don't believe the type library has to be in a particular location. $\endgroup$
    – kale
    Mar 3, 2016 at 23:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.