Where am I called (what cell) ?

Jun 19, 2011 at 1:03 PM

How can I recover the calling location of a function ?

For example, if my function takes one argument but returns a matrix range,

don't i need to know where to return the range/region ?

 

Next: How can I return a range and values ...

Coordinator
Jun 20, 2011 at 11:22 AM
Edited Jun 20, 2011 at 10:07 PM

Hi - [sorry the message got garbled.]

For finding the caller you can use XlCall.xlfCaller. Search in the Google group (http://groups.google.com/group/excdeldna) for xlfCaller and you should find discussions and some examples.

One example is this:

Public Module Test

<ExcelFunction(IsMacroType:=True)> _ 
        Function ShowCallerInfo() 
                Dim Caller as ExcelReference 
                Dim BookAndSheetName as String 
                Dim RefText as String 

                Caller = XlCall.Excel(XlCall.xlfCaller
                BookAndSheetName = XlCall.Excel(XlCall.xlSheetNm, Caller) 
                RefText = XlCall.Excel(XlCall.xlfRefText, Caller) 

                ShowCallerInfo = "Caller Book and Sheet Name: " & BookAndSheetName & _ 
"; Full Reference: " & RefText 
        End Function 

End Module

For returning an array: You can return a either double[,] or object[,] from you UDF. In Excel you treat it as an 'array fomula' which you enter with Ctrl+Shift+Enter.

-Govert

 

Jun 21, 2011 at 2:23 AM

testing in c#

thanks...

 

but how do i return an arbitrary range

matrix/tensor ?

Coordinator
Jun 21, 2011 at 8:24 AM

Example of an array function:

public static class ArrayTest
{
    public static object[,] TestArray()
    {
        object[,] result = new object[2, 2];
        
        result[0,0] = "Hello";
        result[0,1] = "World";
        result[1,0] = 2.3;
        result[1,1] = 3.4;
 
        return result;
    }
}

This gets entered into as Excel as an "Array Formula" by selecting a 2x2 range of cells, entering the formula "=TestArray()", then pressing Ctrl+Shift+Enter, so that the formula displays as "{=TestArray()}".

-Govert

Jun 22, 2011 at 4:28 PM

testing... will post... thanks !

i thought I had to know where I was called from and write to a Range based on my calling location...

this is much neater.

Jun 22, 2011 at 4:31 PM

 

Hello

 

no it does not work.

how can i debug this ?

 

		[ExcelFunction ( Description = "TestArray" , Category = "test functions" , HelpTopic = "verifies that a UDF knows where it came from" )]
public static object [ , ] TestArray ()
{
object[,] result = new object [ 2 , 2 ];

result [ 0 , 0 ] = "Hello";
result [ 0 , 1 ] = "World";
result [ 1 , 0 ] = 2.3;
result [ 1 , 1 ] = 3.4;

return result;
}
Jun 22, 2011 at 5:55 PM

Poking at this now. I make the following observations:

 

I'm on Windows 7 32 bit. Office 2010 14.0.5138.4000 Office Pro Plus.

MS Visual Studio with VSTO. I'm working my way back through the XLCustomMarshal1.cs.

Your help most appreciated. I finding debug statements commented out.

I'm also finding that this is not working as expected:

		[ExcelFunction ( Description = "WhereAmITest1" , Category = "test functions" , HelpTopic = "verifies that a UDF knows where it came from" )]
public static String WhereAmITest1 (  )
{
ExcelReference eR=( ExcelReference ) XlCall . Excel ( XlCall . xlfCaller );

String HereIThinkIAm = String . Format ( "Col First {0} Last {2} Row First {3} Last {4}" ,
eR . ColumnFirst , eR . ColumnLast ,
eR . RowFirst , eR . RowLast );

Debug . WriteLine ( HereIThinkIAm );

return HereIThinkIAm;
}

 

 

 

Coordinator
Jun 23, 2011 at 3:33 AM

Here is a complete .dna file for the simple array formula.

Do the following:

* Make a text file containing the xml below, and call the file MyArrayTest.dna.

* Make a copy of Distribution\ExcelDna.xll from the Excel-DNA download next to the .dna file, and call it MyArrayTest.xll.

* File->Open the MyArrayTest.xll file in Excel.

* Select cells A1 to B2.

* Type in the formula "=TestArray()" and press Ctrl+Shift+Enter to input it as an 'array formula'.

* Check that the cells display Hello; World; 2.3 and 3.4.

It definitely works fine if entered correctly.

<DnaLibrary Language="C#">
using ExcelDna.Integration;
public class Test
{
    [ExcelFunction ( Description = "TestArray" , Category = "test functions")]
    public static object[,] TestArray()
    {
        object[,] result = new object[2, 2];

        result[0, 0] = "Hello";
        result[0, 1] = "World";
        result[1, 0] = 2.3;
        result[1, 1] = 3.4;

        return result;
    }
}
</DnaLibrary>

Your function using xlfCaller fails because the string.Format call is incorrect - you have {0}, {2}, {3} and {4} instead of {0} , {1}, {2}, {3}. The call to string.Format throws an exception which is returned as #VALUE in Excel.

-Govert

Jun 24, 2011 at 12:19 AM

I got it to work, but the 'input as array formula' upsets me.

You have to pre-allocate the answer region written by the formula ?

 

I find this really not what I want; i want to do matrix operations that can transpose,

take arbitrary values, and to stacks of values in different sheets.

 

How do I get out from underneath this requirement of outlining your output regions ?

I don't always know what size object will emerge from my function. I know by convention

you should not affect values outside your input 'footprint', but that is why i'm writing my own functions.

 

dB

 

Coordinator
Jun 25, 2011 at 4:30 PM

Hi dB,

Array formulas are an important feature of Excel. You can read a bit more here: http://www.cpearson.com/excel/ArrayFormulas.aspx, or by Googling a bit.

In most cases, if you want to write to arbitrary parts of a worksheet, a macro is a more appropriate implementation than a user-defined function. Writing beyond the caller's range will subvert Excel's calculation model, and is a bad idea. It is not only 'by convention' but actually enforced by Excel as much as possible.

However, there are some ugly hacks that do make it possible to implement this kind of thing. The ArrayResizer.dna example in the Excel-DNA distribution is an example of a self-resizing UDF htat I also discussed here last year: http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/.

-Govert

 

Jun 26, 2011 at 1:22 AM

I was populating the sheet with UDF and testing them in VS2010.

The array functions did not when I programatically populate the sheet.

THey do work when I enter them as an ArrayFormula.

 

I would make some functions test if they clobber anything, and make some options to

clobber/noclobber.

 

For example, returning an arbitrary length list of environment vars, or manipulating 3d tensors or

datasets makes the array formula function problematic.

 

For example, I have some applications to mung/manipulate lists of files in directories. I can't

call in advance the number of files returned for each directory. 

 

i will review your citation above.

 

I did read up on Array Formulas. I think it will make a nice kind of tensor if i can

get the omitted regions to -auto-fill to flesh out co-variant and conta-varient (super and sub pre-scripted tensors).

 

i would put in some protection for oversized arrays being returned.

 

People are sending me data in spreadsheets for analysis and i'm working on how best to analyze their data.

Is there a programatic way to pre-define a region to call a UDF ?

 

dB

Coordinator
Jun 26, 2011 at 11:18 AM

Hi,

As an example, you might have a macro that dumps all the files in a directory, which you activate by selecting a cell that contains a directory name and pressing a button on a ribbon tab. To most Excel users, this would be a more natural way of getting that list on the sheet, than entering a UDF that takes the directory name as argument and magically dumps data all over the sheet whenever it is called.

I normally try to discourage breaking Excel's dependency tree. But with Excel-DNA you can do any of this if you really want to. The restriction is put in place by Excel and the spreadsheet calculation mode, and if you want to subvert it Excel-DNA gives you a way to do so, as much as any other Excel add-in tool.

 

>Is there a programatic way to pre-define a region to call a UDF ?

I'm not sure what you mean. Could you perhaps expand a bit more carefully what you are asking here?

If you are looking for a programmatic way to enter a formula in a cell or region from a macro, the answer is 'yes'. There are different ways - either using the C API or using the COM Automation interface. If you know VBA, using the COM automation interface will be easiest. You can access the object model from your Excel-DNA macro, starting with the ExcelDnaUtil.Application helper to get the root Application object. I can try to help with an example if you explain where you are stuck.

-Govert