Can Excel-DNA Help me here?

[HedgePig]

I recently stumbled across Excel-DNA, downloaded it and tried the first example - to create a user defined function from an Excel worksheet. This worked perfectly and was easy to implement.

But apart from allowing user-defined worksheet functions, what else can Excel-DNA do? Apologies for this ignorant question - I have read some of the postings in this group and well as the information on http://exceldna.codeplex.com and it seems that people are using it for far, far more than simple UDF's but it's not clear to me where Excel-DNA comes in here. I stumbled across Excel-DNA because I downloaded Visual Basic Express (2010) a few days ago - my first experience with .NET. I'm reasonably familiar with VBA and was looking for a way to write a DLL where I could access external functions from VBA. (I wanted something that was faster than VBA for calculations.) I did end up managing to run a simple example although it was messier than I thought and then ran into the problem that I could find a way of passing 2-dimensional arrays from VBA to my DLL.

Would Excel-DNA be able to help me here? Once again, apologies for the very basic question - but I would like to know what else Excel-DNA can do and exactly where it fits in. And if it can solve my immediate problem, that would be a bonus.

Regards
HedgePig

[Patrick O'Beirne]

To use VB express 2010 with ExcelDna, see
http://sysmod.wordpress.com/category/exceldna/
There's a downloadable doc with my experiences.
I'm updating it.
Migrating Excel VBA Add-in to VB.Net, ExcelDNA, NetOffice.docx


Any comments / improvement idea from others welcome

> --
> You received this message because you are subscribed to the Google
> Groups "Excel-DNA" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/exceldna/-/spCavpAt64kJ.
> To post to this group, send email to exce...@googlegroups.com.
> To unsubscribe from this group, send email to
> exceldna+u...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/exceldna?hl=en.

[Michael_F]

Apologies for this ignorant question - I have read some of the postings in this group and well as the information on http://exceldna.codeplex.com and it seems that people are using it for far, far more than simple UDF's but it's not clear to me where Excel-DNA comes in here.

Excel-DNA allows you to write Excel plugins in any of the popular .NET programming languages. It is a lightweight alternative to Microsoft's VSTO, with less version hassles, but just for Excel, not for the other Office applications.

Besides the fact that such a plugin integrates easily into the menus or ribbons of Excel, IMHO the real advantage is that is runs directly in-process. If you write a .NET program and access Excel "from outside", via "automation", you always have a interprocess-communication between Excel and the separate program whenever the program reads or writes to a cell or calls some other Excel functionality. A plugin does not have those interprocess communication, which makes it much, much faster. How fast depends, of course, on your case, but for my applications I often had a speed gain of about a factor of ~10 to 20.
 

I stumbled across Excel-DNA because I downloaded Visual Basic Express (2010) a few days ago - my first experience with .NET. I'm reasonably familiar with VBA and was looking for a way to write a DLL where I could access external functions from VBA. (I wanted something that was faster than VBA for calculations.) I did end up managing to run a simple example although it was messier than I thought and then ran into the problem that I could find a way of passing 2-dimensional arrays from VBA to my DLL.

Would Excel-DNA be able to help me here?

Given your DLL is written in an arbitrary .NET language, you can write easily an Excel DNA based plugin as a replacement for your VBA code, which is linked against that DLL and uses it directly.

Greetings

- Michael -

[HedgePig]

Hello "sysmod" and Michael

Thanks very much for your replies - much appreciated.

sysmod - I'm looking through the examples you mentioned and learning a lot although I still have a lot more to learn.

Michael "Given your DLL is written in an arbitrary .NET language, you can write easily an Excel DNA based plugin as a replacement for your VBA code, which is linked against that DLL and uses it directly."
Actually, at this stage I don't want to replace all my VBA code. What I want to do is read my input from within VBA and then call my DLL to do the number crunching. Finally, I'd write out results again in VBA. I'd be passing large amounts of data from VBA to the DLL and back again but the real time consuming part is the number crunching that I want the DLL to do. If I can get 10x increase in speed, I'd be very happy indeed and even 5X would be OK.

Once again, thanks for your help. I ahve some more ignorant but I'll post them separately (so as to target my ignorance!)

Regards
HedgePig

[Michael_F]

Actually, at this stage I don't want to replace all my VBA code. What I want to do is read my input from within VBA and then call my DLL to do the number crunching.

You don't have to replace all of your VBA code. An UDF with Excel-DNA creates an interface to call .NET code from Excel-VBA very easily. And whatever DLL you want to use, you can easily call the DLL functions, objects, classes etc. from your UDF. The pros and cons of other methods to accomplish the same task are listed here:

http://www.blog.methodsinexcel.co.uk/2010/08/16/why-excel-dna/

Take your pick.

Greetings

- Michael -

[HedgePig]

Thanks Michael for your help. But...er....how do I use the DLL functions/objects/classes etc from within VBA?

I tried creating a simple DLL from the example in http://www.blog.methodsinexcel.co.uk/2010/09/22/writing-an-excel-dna-function-using-an-ide/  (I used VB Express (2010) since I didn't want to deal with a new IDE as well.) I managed to create and compile the DLL. I then added the XLL file as an add-in from with Excel (not the the VBA IDE). I did initially have a compile error from within Excel DNA indicating a runtime version problem but manged to sort that out.

Once I managed to add-in the XLL, I can directly use the "HelloWorld" function from within Excel itself.
However, how I was expecting to have access to the class from the class from within VBA as well. (This is where I really want to have access to the code.)
However, if within VBA I type a line like
   Dim myobj As New MyFirstDNAAddIn
I get an error message "User-defined type not defined". What am I doing wrong?

I can directly call the function from with VBA by typing something like
   Msgbox(Application.Run("HelloWorld", "How are you?"))

[HedgePig]

Sorry replied before completing.....

I wanted to add that if I use something like Application.Run("HelloWorld", mystring)
then it seems that I cannot pass mystring by reference. This is also a "problem" in that ultimately, I want to call a VB function/subroutine that will pass back a lot of modified data.

If you do have any suggestions, I'd much appreciate it. I was hoping that Excel DNA would enable me to use VB.NET easily . However, it seems that most of the people using Excel DNA it are fairly familiar with .NET. I'm definitely not and right now my wall is missing a lot of bricks! Perhaps the target user of Excel DNA is not people like me so maybe I shouldn't be using it at all.

[Patrick O'Beirne]

On 05/09/2012 02:39, HedgePig wrote:
> I wanted to add that if I use something like
> Application.Run("HelloWorld", mystring)
> then it seems that I cannot pass mystring by reference. This is also a
> "problem" in that ultimately, I want to call a VB function/subroutine
> that will pass back a lot of modified data.

Threads to follow on this liist:
17-May-2012 [ExcelDna] Re: COM, API, Interop and UDFs?
6-Aug-12 [ExcelDna] Re: ComServer examples

I had hoped I could use Smurf's suggestion on XLLs:
>
Using declares will get you your answer = func(...) style. Func will
be whatever you declare your xll function will be known as in VBA as in:
Declare Function theVBAName Lib "thexll.XLL" Alias "theXLLName" (ByVal
int as long) As Long
<

so I tried

Declare Function AddThem Lib "TestDna.XLL" Alias "f0" (ByVal int1 as
long, ByVal int2 as long) As Long

BUT Govert says
>>
In general the VBA Declare style access will not work. The Excel-DNA
functions return XLOPER data structures to Excel (that's what
the 'Q's
mean in the registration string), and dealing with these in VBA
would
be a nightmare. The fact that it nearly seems to work is a red
herring. For functions that take doubles and return a double you
might
be able to pull it off with the 'IsExceptionSafe:=True' option
set on
the .NET side. But there is no chance of passing strings or objects
around that way.
The COM Server support is the right direction to look if you really
need to use the .NET code from VBA.



Excel-DNA can interact with Excel in different ways. The most basic is
the UDF and macros which are registered through the C API. These only
allow simple types like double, string, and object arrays object[,]
that contain simple types. Kevin's pointer is a good one as a
reference for this. These functions can be called from the worksheet,
or from VBA using Application.Run.

Excel-DNA can also expose itself as a COM server to VBA. In this
respect, Excel-DNA does not really add any power - you can register
and use a regular .NET library via COM in VBA, and with Excel-DNA
getting everything working the first time is probably harder. The
advantage is that you have single add-in for worksheet UDFs and
objects exposed to VBA. The keyword to search for in this group to
find more about Excel-DNA's support for VBA interop is "ComServer":
https://groups.google.com/group/exceldna/search?group=exceldna&q=comserver&qt_g=Search+this+group.
<<

[HedgePig]

Hello sysmod

Thanks for your help and pointers to what posts might be helpful.
As you suggested, I think that for what I'm wanting to do, Excel DNA probably doesn't add any extra convenience so I'll just continue to write what I want in VB.NET and register the dll using regasm, the add a reference to the resulting .tlb file. This approach is not so clean for deployment and I was hoping Excel DNA would help me get around this hassle - but it does seem to work, so I'll live with it.

By the way, I did find out that using Application.Run() in VBA, does not allow one to pass parameters by reference. So this problem isn't one that I could expect Excel DNA to overcome!

Regards
HedgePig