User Defined functions

Add-in Express™ Support Service
That's what is more important than anything else

User Defined functions
How do I create an Excel user defined function in ADX .NET 
Kennington


Guest


Am having a complete brain-blank on this - I want to create a UDF just as I would in VBA - so a Public Function returning the relevant value to a cell in an Excel spreadsheet.

There's obviously more to it than just creating a Public Function in VB.net but I just can't seem to make that mental leap!

a link to an example would work great (can't seem to google anything up on this either)

Cheers,
Rich.
Posted 03 Apr, 2005 06:49:15 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Hi Rich.

You can download an example here:
http://www.add-in-express.com/projects/excelrange.zip
Posted 04 Apr, 2005 10:14:30 Top
Timour


Guest


Ok I see that it is possible to create a UDF and make it available in Excel. My concern is the following: Can I edit the name of the Excel functions category, in which I will put my UDFs? Can I also edit the descriptions of the functions and the parameters for my UDFs that are shown in Excel's "Add function" dialog?

Can I please have an example of that?

Thanks!
Posted 19 Apr, 2005 05:06:11 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Hi Timour.

Unfortunately I found the following article: http://www.kbalertz.com/kb_Q285337.aspx.

There is the following quotation in this article:
Each Automation Add-in has its own category in the Excel Function Wizard. The category name is the ProgID for the Add-in; you cannot specify a different category name for Automation Add-in functions. Additionally, there is no way to specify function descriptions, argument descriptions, or help for Automation Add-in functions in the Function Wizard.
Posted 19 Apr, 2005 13:03:14 Top
Timour


Guest


That's strange.. I found and installed an Add-in for Excel called ZRandom - http://www.zrandom.com/zrand/, and all is has is a dll, which is connected to Excel - there is no wrapper in VBA. It has all its functions available in a custom category (which by the way is not ProgID.Class), and it has description of all functions and parameters. How did they make it possible? :( :?:
Posted 19 Apr, 2005 14:16:58 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Timour, I think this is an XLL add-in (DLL for Microsoft Excel) that implements and exports specific methods.
Now ADX doesnt't support XLL. You can read about XLL in the "Microsoft Office Development with Visual Studio" acticle from MSDN.

See also:
http://blogs.msdn.com/eric_carter/archive/2004/12/01/273127.aspx
Posted 19 Apr, 2005 15:25:44 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Hi Rich.

Probably I was mistaken and you need an example of an Excel Automation add-in. If so you can download this example here:
http://www.add-in-express.com/projects/excelformula.zip
Posted 19 Apr, 2005 15:41:04 Top
Richard Freeman




Posts: 3
 Joined: 2005-03-13
Ok - that's great, thanks Sergey - I'll give this a go over the weekend, but it seems to make sense.

Any thoughts what the performance will be like versus a standard vba UDF? I'm kind of assuming what you win on the pre-compilation you more than lose on the interop/.net overhead.

Might have to do some benchmarking methinks, though for me the convenience/power of using .net is a worth a lot of performance degradation.

Rich (Kennington)
Posted 29 Apr, 2005 03:41:37 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Hi Rich.

Sure, you will lose some time on loading .NET dll to the memory but afterwords the .NET code will be executed quickly enough. I agree with you that using .NET you have more advantages than using standard VBA.
Posted 29 Apr, 2005 12:12:36 Top