ExcelDNA and Microsoft.Office.Interop.Excel

[Emmanuel GUIGNIER]

Hello everybody,

I have built an XLL application, based on ExcelDNA, with C# within Visual Studio Community 2017 and Excel 16.0.
The purpose is to migrate from VBA code to XLL (C#) in order to protect it.
My XLL functions work with WorkBook, WorkSheet, Range, Cells (value, formula, color, ...), Userform, and so on, like with VBA.

It works fine on my computer, but I am not sure that it will work on others.
Using Microsoft.Office.Interop.Excel 16.0 (present on my computer), the application will not work on computer having an ealier version of Excel (2013 or 2010).

In my code, I use the following lines :

using ExcelDna.Integration;
using XL = Microsoft.Office.Interop.Excel;
...
XL.Application app = (XL.Application)ExcelDnaUtil.Application;
XL.Workbook wb = app.ActiveWorkbook;
XL.Worksheet ws = wb.Worksheets[1];
XL.Range startCell = app.ActiveCell;
...

In other code I Found on the web, I saw some call to XlCall (ExcelDNA) :

double now = (double)XlCall.Excel(XlCall.xlfNow);
...
private static dynamic Excel;
Excel = ExcelDnaUtil.Application;
...

Today, I need your help to build an XLL application with good fundation.
I use ExcelDNA to build the XLL in VS2017, and some times ExcelAsyncUtil.QueueAsMacro() function, and that's all.
All other functions are based on Microsoft.Office.Interop.Excel !!

1. Do I need the Microsoft.Office.Interop.Excel to make my XLL application ?
2. Where can I found a documentation for beginner to understand how to use the XlCall and other functions, and the detail documentation ?
   I tried to found it on the web, but it was not accessible for me, although I am a good developer in VBA or C#.
3. Where can I found ExcelDNA examples using XlCall and working with Excel objects ?
   On the web, there are a few examples or breaked links.
   

Thanks for your help.

Best Regards.

Emmanuel (Lyon - France)

[Govert van Drimmelen]

Hi Emmanuel,

 

You say:

> It works fine on my computer, but I am not sure that it will work on others.
> Using Microsoft.Office.Interop.Excel 16.0 (present on my computer), the application will not work on computer having an ealier version of Excel (2013 or 2010).

It should in fact work fine on other computers. If your Reference to the Microsoft.Office.Interop.Excel assembly has the “Ember Interop Types” set to “true” then you don’t have to distribute anything extra for the COM interop to work.

You should find that, as long as you use the parts of the object model that are present in the older Excel versions, that the code runs on old version too – this is exactly like VBA. If you write VBA code in Excel 2016 it will work on Excel 2010 as long as you don’t use features that are new only for Excel 2016. But it sounds like you are using standard types like Workbook that will be compatible with old versions too.

 

So your first piece of code is good and should work under old versions too.

 

If you’re worried about compatibility, you can remove the reference to the version 16.0 object model, and add the NuGet package “ExcelDna.Interop” which includes only the Excel 2010 object model, so anything you see on the IntelliSense should already be present in Excel 2010. But you don’t need to do that for it to work, it just protects you from mistakenly using something that was added in the newest version.

 

For macros, the C API (which you use through the XlCall call) is more limited than the COM object model, since Microsoft hasn’t really updated it in 20 years.

There is an overview and links to the only help file that exists for the XlCall methods from here: https://github.com/Excel-DNA/ExcelDna/wiki/Excel-C-API

 

-Govert

[Emmanuel GUIGNIER]

Hy Govert,

Whouaaaaa ! You are the quickest man of the world !!!
Thanks a lot for your answer !

1. “Ember Interop Types” set to “true” : It is set yet. I will try my XLL tomorrow on another computer.
2. About XlCall, I understand that it is not necessary to use it while I use the COM model with the Microsoft.Office.Interop.Excel assembly. Is it OK ?
3. You suggest me to replace Microsoft.Office.Interop.Excel reference by “ExcelDna.Interop” reference. I understand that it is not necessary in my case. However, where can I found the ExcelDna.Interop ? I did not found it in the distribution.
4.  Regarding the XlCall methods, I have found them by the past. But It is obscure for me. I do not know where to found what. My actual knowlegde is based on VBA and the Excel COM model, and it is difficult for me to through this documentation and make link with my knowledge. Is there any examples ?

Thanks Govert.

Emmanuel

[Govert van Drimmelen]

ExcelDna.Interop is a NuGet package that adds a reference to the older version of Microsoft.Office.Interop.Excel.

 

For macros that run from a ribbon button or event handlers, the COM interface is more powerful than the C API and usually the better way to do it.

Excel-DNA internally uses the C API because that’s the best way to add user-defined functions to Excel. For your own add-in you typically don’t have  to use the C API, since Excel-DNA has implemented all the integration already.

 

There are very few examples of using the C API (XlCall methods) from C#. You can search on GitHub and StackOverflow to try to find some.

Here’s an example of using the XlCall methods to read and write from a range on a worksheet:

https://stackoverflow.com/a/3868370/44264

 

But it is indeed obscure. The reference for the C API is the Excel SDK: https://msdn.microsoft.com/en-us/library/office/bb687883.aspx together with the macro help files I link to from the GitHub page here: https://github.com/Excel-DNA/ExcelDna/wiki/Excel-C-API

If you have used the C API in C++ then using it from C# is very easy. Otherwise it is not easy.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Emmanuel GUIGNIER]

Hy Govert,

I am back :)
My XLL works fine on my computer (Windows 7, Excel 2016)

As said before, I tried my XLL on another laptop :

• Windows 10
• Excel 2010, 64 bits, 14.07190.5000
  

When I tried to charge my XLL manually into Excel, I have received a message like this (it is a traduction from french) :

"the ...xll The XLL is an invalid add-in" !!

Do you have any idea ?
Thanks a lot.
Emmanuel

[Govert van Drimmelen]

There are separate add-ins for the 32-bit version of Excel and the 64-bit version of Excel.

You should find both versions in the output directories, by default with the 64-bit ones named xxx64.xll

You need to load the add-in with the right bitness, else you'll see this message from Excel.

-Govert