User defined functions through Automation in Excel 2003

[jpgreenwald]

Hi all,

Im trying to create user defined functions (ones that you can use in
the cells of excel) in python. I know that its a ...dumb... thing to
do since its a lot easier in other languages (vb,c#, vc..etc) but Im
stuck on seeing if I can get this to work. Currently I have written a
simple script:

class jtest:
_public_methods_ = ['Add2']
_reg_clsid_ = "{27A888AA-B5DB-44BE-BBA7-DECF3A1DF861}"
_reg_progid_ = "jtest.Excel"

def Add2(x, y):
return x+y

and compiled it to a dll with py2exe and created the registry entry in

HKCR\CLSID\{xxxGUIDxxx}\Programmable\

so that excel can pick it up and it loads no problem when selecting it
from the automation add-in menu. However thats all it does. I cant
seem to be able to call the function or do anything with it. Im sure
there is a lot more thats needed than what im doing but documentation
is scarce. Thanks for any help or insight that can be offered.

-Jesse

 

[jpgreenwald]

Ive been looking more into this subject and now have a few things to
add. Im using some c# code that works in doing what I want (adds the
function into excel):

using System;
using System.Runtime.InteropServices;

namespace jtest {
[ClassInterface(ClassInterfaceType.AutoDual)]
public class test{
public test(){
}
public double Add2(double x, double y) {
return v1 + v2;
}
}
}

I messed arround with it and without
"[ClassInterface(ClassInterfaceType.AutoDual)]" the same thing happens
that happened in python; the dll would load but the function is not
usuable. So reading some more I was *thinking* that it was the early
bound calls allowed by the AutoDual Interface that I cant seem to easy
replicate in python. Now I was *thinking* again that the only way to
replicate that would be to use a typelib but I do not know how to
generate that using a idl file I dont know how to create. Again I was
*thinking* that if I had a typelib I could use makepy and then gencache
so when I use py2exe the typelib would be used and early bound calls
would be used.

Am I completely Insane and following a completely wrong thought process
here? If not, how do I make a idl file (then use midl.exe ?), and then
make a tbl? Thanks again for your thought.

-Jesse

 

[Ian Parker]

Ive been looking more into this subject and now have a few things to
add. Im using some c# code that works in doing what I want (adds the
function into excel):

using System;
using System.Runtime.InteropServices;

namespace jtest {
[ClassInterface(ClassInterfaceType.AutoDual)]
public class test{
public test(){
}
public double Add2(double x, double y) {
return v1 + v2;
}
}
}

I messed arround with it and without
"[ClassInterface(ClassInterfaceType.AutoDual)]" the same thing happens
that happened in python; the dll would load but the function is not
usuable. So reading some more I was *thinking* that it was the early
bound calls allowed by the AutoDual Interface that I cant seem to easy
replicate in python. Now I was *thinking* again that the only way to
replicate that would be to use a typelib but I do not know how to
generate that using a idl file I dont know how to create. Again I was
*thinking* that if I had a typelib I could use makepy and then gencache
so when I use py2exe the typelib would be used and early bound calls
would be used.

Am I completely Insane and following a completely wrong thought process
here? If not, how do I make a idl file (then use midl.exe ?), and then
make a tbl? Thanks again for your thought.

-Jesse

Darn, I was interested in seeing an answer for this, although dismally
unable to contribute myself. Did I miss the responses?

Regards

Ian