OpenEuphoria

2. Re: Excel

• Posted by Michael J. Sabal <m_sabal at ??hoo.com> Jan 07, 2008
• 1683 views
• Last edited Jan 08, 2008

How much functionality are you looking for?  I have a lib that can read 
documents saved in 97/2K/XP format, and one that can save sequences into
HTML tables with an xls extension (hey, it works for the most part).  I only
needed the most minimal feature set, so I haven't gotten around to completing
it.  If you're interested, I can email you off list.

Michael Sabal

4. Re: Excel

• Posted by Mike777 <anon4321 at gm?il.com> Jan 08, 2008
• 1698 views

Appreciate the offer, but I was thinking more along the lines of a classic excel
add-in.  Something that would allow my to type something like:

=myfunction(cell1, cell2, cell3) and then have it do its magic.

Magic in this case is something that is not written in VBA (although it
certainly could be) but instead is compiled into an excel add-in and exposes the
functions therein to the excel cells.

Mike

6. Re: Excel

• Posted by Greg Haberek <ghaberek at ??ail.com> Jan 08, 2008
• 1688 views

Mike777 wrote:
> 
> I'm not sure I need to go the ActiveX route.  My understanding is that you
> only
> need/want ActiveX is you want to go outside of the Excel framework.  For
> example,
> if you want to write to or read directly from the file system.  If all you
> want
> to do is memory bound, I think an Add-in can be compiled as a special form of
> DLL.  I remember some KB articles on it the last time I looked, which was
> quite
> a while ago (I told you it wasn't a rush!).  Now that I've at least posted
> here,
> I'll see if I can't find something that lays out the requirements as far as
> how the DLL has to be built.

Writing an Office Add-in is tough at best. I wrote one for Outlook in VB 2005
Express (.NET) and it was rather hairy. Lots of .NET and/or COM code is required.
I think it may be possible with EuCOM, but Microsoft abstracts so many things, I
wouldn't know where to begin.

-Greg

8. Re: Excel

• Posted by Matt Lewis <matthewwalkerlewis at gmail.c?m> Jan 08, 2008
• 1745 views
• Last edited Jan 09, 2008

Juergen Luethje wrote:
> 
> Maybe writing a Eu wrapper for DispHelper
>    http://disphelper.sourceforge.net/
> would be a good starting point. Just an idea ...

I don't think so.  It looks like a C/C++ analog to EuCOM, but wouldn't help
you to make COM objects from euphoria code.  I think that the simplest
way to do that might be to write a regular euphoria dll, and then wrap
it with the ActiveX object written in C++ or something.

Matt

10. Re: Excel

• Posted by Mike777 Feb 04, 2009
• 1753 views

I figured this time I would ask first! Has anybody a good link/reference for 
building a DLL that can function as an Excel Add-in?  Not critical.  Not rush. 
Just something I have my sights set on. 
 
Thanks 
 
Mike 

It has been a while since I asked the above question, so I figured I would ask again. Still have my sights set on doing this someday. Maybe somebody who wasn't around a year ago has something they can add to this discussion.

I'm not looking for anything other than a simple template, but I do need to develop something that would be compatible with old versions of Excel , like Excel 97.

So that new readers don't need to read the entire thread, I'll summarize:

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,a3) such that myfunction runs some euphoria compiled code using the values found in a1, a2 and a3.

Always optimistic,

Mike

12. Re: Excel

• Posted by Mike777b Oct 10, 2010
• 1580 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

14. Re: Excel

• Posted by mattlewis (admin) Oct 11, 2010
• 1573 views

Must....resist.....

 
public function MyFunction(atom a, atom b) 
	atom c = a * b 
	return c 
end function   
 
 

One issue with this is that he wants to pass a double. Euphoria callbacks can't handle doubles being natively passed. They can only take 31-bit integers (or Euphoria Atoms or Sequences).

The desired signature is:

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

If you translate (I did this on Linux, but should be similar), you should get some thing like :

$ euc -makefile-full -dll vb.e 
 
// inside vb.c: 
int _1MyFunction(int _a_144, int _b_145) 
{ 
    int _c_146 = 0; 
    int _0, _1, _2; 
     
 
    /** 	atom c = a * b*/ 
    if (IS_ATOM_INT(_a_144) && IS_ATOM_INT(_b_145)) { 
        if (_a_144 == (short)_a_144 && _b_145 <= INT15 && _b_145 >= -INT15) 
        _c_146 = _a_144 * _b_145; 
        else 
        _c_146 = NewDouble(_a_144 * (double)_b_145); 
    } 
    else { 
        if (IS_ATOM_INT(_a_144)) { 
            _c_146 = NewDouble((double)_a_144 * DBL_PTR(_b_145)->dbl); 
        } 
        else { 
            if (IS_ATOM_INT(_b_145)) { 
                _c_146 = NewDouble(DBL_PTR(_a_144)->dbl * (double)_b_145); 
            } 
            else 
            _c_146 = NewDouble(DBL_PTR(_a_144)->dbl * DBL_PTR(_b_145)->dbl); 
        } 
    } 
 
    /** 	return c*/ 
    DeRef(_a_144); 
    DeRef(_b_145); 
    return _c_146; 
    ; 
} 

The easiest thing to do here is probably to write a wrapper function right below the above code:

double MyFunctionDbl( double ad, double bd ){ 
	object a, b, c; 
	double cd; 
	 
	a = NewDouble( ad ); 
	b = NewDouble( bd ); 
	 
	c = _1MyFunction( a, b ); 
	cd = DBL_PTR( c )->dbl; 
	DeRefDS( a ); 
	DeRefDS( b ); 
	DeRefDS( c ); 
	printf("MyFunctionDbl ad %f  bd %f cd %f\n", ad, bd, cd ); 
	return cd; 
} 

And then call that. I wrote this test program to use it:

include std/dll.e 
 
constant 
	VB = open_dll( "/home/matt/eu/test/vb.so" ), 
	MYFUNC = define_c_func( VB, "MyFunctionDbl", {C_DOUBLE, C_DOUBLE}, C_DOUBLE ) 
 
? c_func( MYFUNC, { 0.5, 0.25 }) 
? c_func( MYFUNC, {1, 1 }) 

Matt

16. Re: Excel

• Posted by mattlewis (admin) Oct 11, 2010
• 1575 views

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

Yes, if you're certain you'll only pass integers like that, you should be fine. Bigger integers, or floats or doubles, however, and you'll need to do something like what I showed.

$ euc -makefile-full -dll vb.e 
 
// inside vb.c: 
int _1MyFunction(int _a_144, int _b_145) 
{ 
   ... snipped ... 
} 

Yes, that was C. It was from translated euphoria code. You can adapt the wrapper code easily to call a different function. This basic code will work for anything that takes two doubles and returns a double. Other signatures are pretty easy, too.

It was meant just to show that it was working in general, and calling it from euphoria was the easiest way to do it. You'll have to try it yourself in VBA.

Matt

18. Re: Excel

• Posted by mattlewis (admin) Oct 11, 2010
• 1531 views

I've done something like this in the past, so I know that it's possible.

This message contains the translated version of the simple example (multiplying two atoms and returning the result). The first block (in the post) contains the translated version, which looks like this:

int _1MyFunction(int _a_144, int _b_145)  
{  
... 

That was simply what the translator created based on the euphoria code. My little wrapper function that I hand wrote in C is what's meant to be called by your Excel VBA code. It takes the 'native' double values, and turns them into Euphoria atoms, uses those to call your code, then releases the memory it reserved, and returns the value.

So, the basic idea here is to write your euphoria code, then translate, add the additional wrappers, and finally compile the dll so you can use it. By default, when you run the translator, it translates, compiles and cleans up after itself (deleting the c and obj files).

For this task, however, we need to edit the C files before we build it. I like to use the -makefile-full option. It translates your code, and writes out a makefile that your compiler / build system can use. When using Watcom (as I presume you are), it might look like this:

> euc -makefile-full -dll mikes_excel_code.e 
Translating code, pass: 1 2 3 4 5 6 7 8 9 10 11  generating 
 
19.c files were created. 
To build your project, type wmake -f mikes_excel_code.mak 
 
-- do your editing here... 
 
> wmake -f mikes_excel_code.mak 
 

And it will build your dll for you. I haven't tried this with Watcom lately, but you might need to specify this file for export by adding it to another generated file that defines all of the exported functions.

Matt

20. Re: Excel

• Posted by mattlewis (admin) Oct 11, 2010
• 1556 views

Strings coming from VB are a little weird. They're BSTRINGs. Basically, you receive them as a pointer. The length of the string is stored in the 4 bytes previous to the value of the pointer. The data itself is stored as wide characters. Take a look at EuCOM, which has some code for dealing with these, along with arrays, which you should try to avoid (because they're a PITA). I've actually used the code in EuCOM (about 9 or 10 years ago) to write a DLL that was called by Excel. The EuCOM code was mainly used for dealing with Strings and Arrays coming from VBA.

I don't know what Excel thinks "real numbers" are, but they're probably native doubles.

a = NewDouble( ad );  
b = NewDouble( bd );  
c = _1MyFunction( a, b );  

Yes, NewDouble() is the back end euphoria function that allocates a Euphoria atom that is stored as a double. In addition to the actual double, it also stores reference counting and cleanup information. The calls to DeRefDS() de-reference the objects, making sure that the memory they use is freed.

Matt