OpenEuphoria

Re: Excel

• Posted by Mike777b Oct 10, 2010
• 1579 views

Well, it has been another year or so, so I thought I'd try again. I really, really have my sights set on using Euphoria rather than another language (like the C (or C# or C++?) based xlw project, which does what I want but now I find out doesn't support anything other than 32 bit architecture).

To remind folks what this thread is about, without them having to read the whole thing:

I'm not looking for an activex object, just a "simple" add-in. .net shouldn't enter the discussion, as it doesn't apply to an add-in of this kind. What I'm looking for is the ability to put a simple call in an excel cell like =MyFunction(a1,a2) such that MyFunction runs some euphoria compiled code using the values found in cells a1 and a2.

I mentioned the xlw project last year. That subsystem is intended to build an xll which can be used by Excel directly as I described. Since then, I've found another mechanism which might make what I want to implement in Euphoria easier to emulate. The Visual C++ 2008 Express Edition (freely downloadable) has a template for creating DLL's that "almost" works the way I want. Since it creates a DLL (rather than an XLL) it can't be directly called by a worksheet function, but VBA can be used to create a wrapper that will make it work the same way.

From what I've gathered (and I *have* tested it, and it works on my machine), the following code/changes are all that are necessary to create a DLL which works as described further below:

Run VC++2008EE and create a Win32 Project (which is one of the pre-installed templates).

While creating the Win32 Project, check the "DLL" box.

Add a main.cpp file and an export.def file, which contain something like the following:

main.cpp
======== 
//____________________________________________ 
__declspec( dllexport ) double MyFunction(double a, double b) 
{  
     return a + b; 
} 
//____________________________________________ 
 
export.def 
========== 
LIBRARY MyDLL 
EXPORTS 
MyFunction @1 

Then change the following properties in the IDE before compiling:

1) Change the "Calling Convention" for MyDLL to "__stdcall (/Gz)" (found by highlighting MyDLL in the Solution Explorer, then selecting View|Property Pages|Configuration Properties|C/C++|Advanced - it is the first item)

2)Change the "Module Definition File" for MyDLL to ".\export.def" (found by highlighting MyDLL in the Solution Explorer, then selecting View|Property Pages|Configuration Properties|Linker|Input - it is the fourth item)

Then compile (Build|Build MyDLL).

You can move the resultant MyDLL.dll to anywhere.

In Excel, insert a Module through the VBA window (alt-F11 from the worksheet will open the VBA window) and in the Module include the following:

Option Explicit
 
Public Declare Function MyFunction Lib "c:\MyDLLs\MyDLL.dll" _ 
  (ByVal a As Double, ByVal b As Double) As Double 
   
Function aMyFunction(a As Double, b As Double) As Double 
    aMyFunction = MyFunction(a, b) 
End Function

Then you can insert in any cell the following and excel will process it:

=aMyFunction(a1, a2)

I still think that an xll would be preferable (probably a lot faster, because I'm sure the VBA interface slows things down dramatically), but at the least I would be able to do a majority of the heavy lifting in Euphoria which has to be a lot faster than C++.

Staying optimistic.

Mike