DLL Library function that returns an array

Apr 26, 2011 at 5:59 PM

Dear, Govert.

I am trying to create an excel function that returns an array of doubles and would like each Array element to be displayed in consequent rows of a column.

I read your response to some similar question posted in Discussion like this one.

My Actual UDF looked like this:

public static double[,] GetSeries()
{
	double[,] a = new double[2,2];
	a[0,0] = 0;
	a[0,1] = 1;
	a[1,0] = 2;
	a[1,1] = 3; 
	return a;
} 

 However, you said only  one- and two-dimensional arrays of type object are supported (types object[] and object[,]).

so I changed it to :

public static object[,] GetSeries()
{
	object[,] a = new object[2,2];
	a[0,0] = 0;
	a[0,1] = 1;
	a[1,0] = 2;
	a[1,1] = 3; 
	return a;
} 
Then, I pressed ctrl+shift+enter but it only shows the first element of the array. It does not display all the elements in the array.
Could you please help?
Thanks in Advance.
Regards,
Thomas
Coordinator
Apr 26, 2011 at 6:15 PM

Hi Thomas,

Both double[,] and object[,] are supported by Excel-DNA, and both your functions work correctly on my machine - I get the 0,1,2,3 displayed when entered into a 2x2 range.

Are you sure you are entering the functions as array functions correctly (select a 2x2 region and then enter the formula and press Ctrl+Shift+Enter)? Is your formula displayed with curly brackets when you are done? Do you get an Excel error message (You cannot change part of an array.) when you try to enter another value into one of the array cells?

I cannot imagine another reason why it might not work.

Regards,

Govert

Apr 26, 2011 at 6:42 PM

It is not still working.

It only shows the first element of the array. I selected the 2x2 region and then entered the formula and press Ctrl+Shift+Enter but it does not work.

I do not get any error msg and it seems that the function successfully returned the array but only shows the first element.

The formula displayed is not with curly brackets.

Do you have any idea?

Apr 26, 2011 at 6:45 PM
Edited Apr 26, 2011 at 6:49 PM

Just tried out your function – after translating to vba:

 

<ExcelFunction(Category:=csIXPCategory)> _
    Public Shared Function GetSeries() As Object(,)
        Dim a As Object(,)
        ReDim a(2, 2)
        a(0, 0) = 0
        a(0, 1) = 1
        a(1, 0) = 2
        a(1, 1) = 3
        Return a
    End Function

And it returns this in excel

0 1 0

2 3 0

0 0 0

I have to mark my functions as Shared and to add the excelfunction attribute to the declaration to get them to appear in excel.

This may be what it missing. Simply declaring them as public doesn’t work.

Ian

Apr 26, 2011 at 6:50 PM
Edited Apr 26, 2011 at 6:55 PM

I believe there is nothing wrong with my function as I already have a button that displays all the elements of the array.

I just checked that the button click shows all the elements of the array.

However, my concern is that I want to implement this functionality within my UDF. So, whenever user just types formula and presses enter, it should automatically show every elements of the array.

Do you know how to implement this? 

Thanks in Advance,

Thomas 

Apr 26, 2011 at 7:09 PM
Edited Apr 26, 2011 at 7:10 PM

Thank you so much for your help

CTRL + SHIFT + ENTER worked perfect to display the elements in the array!

However, my concern is that, I want to implement this functionality within my UDF using C#. So, whenever user just types formula and presses enter, it should automatically show every elements of the array.

(There should be no need to select 2X2 cell).

Could you please assist?

Thank you so much!

Regards,

Thomas

Coordinator
Apr 26, 2011 at 7:11 PM

Hi Thomas,

This has nothing to do with Excel-DNA - you first function was perfect.

('Shared' in VB.NET is the equivalent of 'static' in C#. Only Shared/static methods are exported by Excel-DNA, so Ian is right that you need this. But you do not need the ExcelFunction attribute unless your .dna file has ExplicitExports="true".)

 

Excel will always display an array formula in {curly brackets}. Even if you type "=1+2" into a 2x2 range and press Ctrl+Shift+Enter, you should see {=1+2} in the fx textbox.

I think either your control key or your shift key is misbehaving. Though your messages seem to have lower and upper case letters in, so I'm guessing your Ctrl key is stuck, broken or hijacked by some other program. Maybe you can try both the left Ctrl key and then the right Ctrl key?

 

-Govert

Coordinator
Apr 26, 2011 at 7:15 PM

Mmmm.....

OK, so do you know what changed to make it work?

 

The automatic resizing is tricky. I wrote a post on how to do this here: http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/.

 

-Govert

Apr 26, 2011 at 7:17 PM
Edited Apr 26, 2011 at 7:19 PM

Thanks, Govert,

I will read it and let you know if I have further question

Thank you so much,

I really appreciate for your hardwork and your help.

Regards,

Thomas

Apr 27, 2011 at 3:47 PM
Edited Apr 27, 2011 at 3:50 PM

Dear, Govert.

I just checked that the automatic resizing works perfect!

However, as I am really new to Excel Add-in, I have 2 questions regarding the automatic resizing.  

1. As I have to understand how the automatic resizing works and I cannot read the code by debugging (I tried to debug it on VSTO 2010 but it does not seem to work as it is for excel), could you recommend any way to understand the code for the automatic resizing effectively? (This is the first time that I implemented Excel-Addin so, is there any way to read the code effectively like reading by debugging?)

 

 

2. I am wondering about the maximum number of rows and columns of the array that automatic resizing can take.                                                                                                                           

I saw that 

the maximum number of rows: 65536, maximum number of columns: 256 if Excel version < 12.0

the maximum number of rows: 1048576, maximum number of columns: 16384 if Excel Version > 12.0

Is this right? I have to know the maximum number of rows and columns of an array that Automatic resizing function can take.

 

 

I really appreciate for your hard work and this nice and great tool saved me a lot of time to implement Excel Add-in. Thank you so much,

Coordinator
Apr 27, 2011 at 6:00 PM

Hi,

1. I'm not sure why you are not able to debug. Some possibilities:

* You have enabled LoadFromBytes="true" in the .dna file.

* You are targeting .NET 2.0 from Visual Studio 2010 - then you might have the issue discussed here: http://groups.google.com/group/exceldna/browse_frm/thread/6767ac80f2bb3f11/0ed7258176144779?lnk=gst&q=debugging#0ed7258176144779 . To target .NET 4 you add a RuntimeVersion="v4.0" attribute to the .dna file:

<DnaLibrary RuntimeVersion="v4.0" >

   <ExternalLibrary ....... />

</DnaLibrary>

2. Yes that's right - those are the full Worksheet sizes under the respective Excel versions.

-Govert

Apr 27, 2011 at 6:51 PM

Dear, Govert.

Thank you for your response and I am so sorry about bothering you again.

I am using .Net 3.5 and  I just erased LoadFromBytes="true" from .dna file.

Here is my .dna file

<DnaLibrary>
                <ExternalLibrary Path="AddIn.dll" />
</DnaLibrary>

However, as I am using DLL Library function, how can I debug in this case? (I enabled the Add-in by building and copying the AddIn.dll  next to .dna and .xll file. Finally, I ran the .xll file to enable the add-in).

I set up unit testing set up in AddIn.dll to see how the code works but it catches exception whenever I debug using unit test('ExcelDna.Integration.XlCallException')

I also tried console application to debug but it did not work because of the same reason.

 

How did you debug, Govert ?

Thank you so much and I really appreciate for your help

Regards,

Thomas

Coordinator
Apr 27, 2011 at 7:10 PM

Instead of copying the Add.dll to the place where the .xll is, rather copy the .xll and .dna files into the bin\Debug directory next to the AddIn.dll.

Then set Excel as the program to start when debugging:

      Go to the project properties, then the Debug tab, then pick Start external program: c:\Program Files\Microsoft Office\Office12\Excel.exe.

      In Command-line arguments, you can also put the full path to the .xll, which will let Excel load the add-in on startup.

You can also attach the debugger to a running Excel instance.

      In this case you run Excel and load the add-in, then in Visual Studio you pick Debug -> Attach to Process. Pick the Excel.exe process, and select the debugger for Managed Code (v2.0,....).

-G

 

 

Apr 27, 2011 at 7:54 PM

Thanks, Govert.

I debugged using the first way you showed me.

(Go to the project properties, then the Debug tab, then pick Start external program: c:\Program Files\Microsoft Office\Office12\Excel.exe.

      In Command-line arguments, you can also put the full path to the .xll, which will let Excel load the add-in on startup.)

It works fine (Debugging opens a new worksheet and then enables the add-in). However, my concern is that I put the breakpoints to see what's happening in my UDF and Resize() function while debugging but the breakpoints do not seem to work.

Do you have any idea?

Thank you so much,

Thomas  

Coordinator
Apr 27, 2011 at 8:10 PM

I can just think that the Add.dll is not next to its .pdb, or that you have the VS 2010 issue I pointed to earlier.

-G

Apr 27, 2011 at 8:51 PM

Hi, Govert.

I just found out that it does not lock my .DLL even after I erased LoadFromBytes="true" from .dna.

I think this keeps causing the problem.

Before I added LoadFromBytes="true" to my .dna, it locked my .dll file but it does not anymore.

Do you have any idea?

Thank you so much and sorry about bothering you.

Regards,

Thomas