Determine UDF position

Dec 3, 2014 at 2:01 PM
Edited Dec 3, 2014 at 3:32 PM
Hi Govert (and many thanx for this addin),
I want to ask you if it's possible somehow to determine position of created Excel DNA UDF function?
I'm working on function that will return range result... something like:
public static class Functions
{

  [ExcelFunction]
  public static string FillDisntace(int distance)
  {
    If(IsLogged)
    {
        LogUser();
    }

    int rowIndex = ??? function.RowIndex;
    int columnIndex = ??? function.ColumnIndex;

    For(int i = rowIndex; i < rowIndex + distance; i++)
    {
        // Do stuff with cell at position [columnIndex, i ]
    }
  Return "Function with distance " + distance;
  }

}
I've tried to find it through ActiveCell but since I dont have reference to "this.Application" I tried to pass reference to static/singleton class on ThisAddIn_Startup, but inside UDF it's allways NULL.


Many thanks G.
Mastenka
Coordinator
Dec 3, 2014 at 8:44 PM
Edited Dec 3, 2014 at 8:44 PM
Hi Mastenka,

You can get hold of the calling cell(s) as an ExcelReference object (this wraps the C API form of a reference), by calling
var caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
An ExcelReference has 0-based row and column information, and you can get the address with a call to
XlCall.Excel(XlCall.xlfReftext, myReference, true)
From there you can construct a COM Range object. You'll need to get hold of the Application object with a call to
var xlApp = ExcelDnaUtil.Application;
From there you can either use the C# dynamic feature, or add a reference to the Microsoft.Office.Interop.Excel assembly and assign the result to a variable of type Application.

Note that Excel puts many limitations on what you can do with an ExcelReference (or Range) object when you are inside a UDF function. In particular, you cannot write to or format parts of the sheet from inside a UDF function - you need to run a macro to do that.

-Govert
Marked as answer by Mastenka on 12/16/2014 at 5:43 AM
Dec 8, 2014 at 1:32 PM
Hi Govert and thanks for your response!

I was able to locate sender through your method and also write values into the sheet! Thanks for your help!

But I still have a problem with accessing classes from other parts of Addin project.

May I ask you for litle bit wider explanation of how you can interact with rest of Addin project?
Since UDF execution run on the main thread as well as rest of the Addin project classes i dont understand why I cant access classes from same namespace/Addin. For instance if I create static/singleton class with random Id assigned in constructor those there is gonna be two different instances of those classes one used for UDF and second in Addin namespace/project.

So for instance if I want to interact from functions with static class which handles user management, database access, Ribbon menu etc... how could I do that? I've tried to access those classes from [ExcelFunction(IsMacroType=true)], but same result. Code look like this:
public class UserDefinedFunctions
{
        [ExcelFunction(IsVolatile = false, Description="Lancelot get data function")]
        public static void LancelotGetData(string key, string from, string toEx, string obc)
        {
            //Lazy user authentication
            if (!UserManager.Instance.Login())
            {
                return;
            }

            string[] values = Controller.Instance.GetEnifData(key, from, toEx, obc);    
            SetLancelotData(values);        
        }

        [ExcelFunction(IsMacroType=true)]
        private static void SetLancelotData(string[] values)
        {
            var caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
            for(int i = caller.RowFirst; i <= caller.RowFirst + values.Length; i++)
            {
                //Insert value to cell
            }
        }
}
Thanks Govert!
Mastenka
Dec 17, 2014 at 2:09 PM
Please does anyone have any idea how from Excel DNA function work with rest of the Addin?
The nearest I could get was write request into file and check from the rest of the Addin this file to use it as a bridge over functions ?


Thanks
Mastenka
Coordinator
Dec 17, 2014 at 3:04 PM
Hi Mastenka,

I'm not sure I understand the problem.
Inside an Excel-DNA add-in, all the code can access any static fields, both from inside UDF functions and any other classes you might have.

Perhaps you are loading both an Excel-DNA add-in and a VSTO add-in?
If so, the easiest would be to consolidate everything into the Excel-DNA add-in.

Otherwise, you need some form of remoting to communicate between the two add-ins.
The link between the two add-ins could then be made through the AddIn.Object property in the Excel COM object model (which you'll have to set somewhere...).

-Govert