从一个UDF Excel单元格地址 [英] Get the excel cell address from a UDF

问题描述

我已经创建了一个自动化添加为Excel，如C＃类库，它包含一个UDF包装实现（我不使用VSTO）
中的UDF是这样的：

I have created an Automation Add-in for Excel,implemented as a c# class library, which contains a UDF wrapper.(I do not use VSTO) The UDF looks like this:

string foo(string data){

  //Do some work on the data passed
  string result;
  return(result);
}

有我隐式获得该单元的地址的方式，其中本公式输入的，不传递任何额外的参数？一种方法是尽快外接负载挂钩事件侦听器的工作簿，并捕获事件作为该单元的值的变化;但是我正在寻找替代的。

Is there a way for me to implicitly obtain the address of the cell where this formula was entered, without passing any additional parameters? One way would be to hook an event listener to the workbook,as soon as the add-in loads, and capture the events as the cells' value change; But I am looking for an alternative to that.

谢谢，

推荐答案

你可以尝试Globals.ThisAddIn.Application.Caller，它返回一个包含电池的Excel.Range。也许这样的事情来获得Excel.Application

you can try Globals.ThisAddIn.Application.Caller, it returns an Excel.Range containing the cell. Maybe something like this to get Excel.Application

public class InteropHelper
{
    public static void GetReferences(ref Microsoft.Office.Interop.Excel.Application _Application, ref Microsoft.Office.Interop.Excel.Workbook _Workbook)
    {
        EnumChildCallback cb;
        // First, get Excel's main window handle.
        int hwnd = (int)Process.GetCurrentProcess().MainWindowHandle;

        // We need to enumerate the child windows to find one that
        // supports accessibility. To do this, instantiate the
        // delegate and wrap the callback method in it, then call
        // EnumChildWindows, passing the delegate as the 2nd arg.
        if (hwnd != 0)
        {
            int hwndChild = 0;
            cb = new EnumChildCallback(EnumChildProc);
            EnumChildWindows(hwnd, cb, ref hwndChild);

            // If we found an accessible child window, call
            // AccessibleObjectFromWindow, passing the constant
            // OBJID_NATIVEOM (defined in winuser.h) and
            // IID_IDispatch - we want an IDispatch pointer
            // into the native object model.
            if (hwndChild != 0)
            {
                const uint OBJID_NATIVEOM = 0xFFFFFFF0;
                Guid IID_IDispatch = new Guid(
                     "{00020400-0000-0000-C000-000000000046}");
                Microsoft.Office.Interop.Excel.Window ptr = null;

                int hr = AccessibleObjectFromWindow(
                      hwndChild, OBJID_NATIVEOM, IID_IDispatch.ToByteArray(), ref ptr);
                if (hr >= 0)
                {
                    // If we successfully got a native OM
                    // IDispatch pointer, we can QI this for
                    // an Excel Application (using the implicit
                    // cast operator supplied in the PIA).
                    _Application = ptr.Application;
                    _Workbook = _Application.ActiveWorkbook;
                }
            }
        }
    }

    [DllImport("Oleacc.dll")]
    public static extern int AccessibleObjectFromWindow(
          int hwnd, uint dwObjectID, byte[] riid,
          ref Microsoft.Office.Interop.Excel.Window ptr);

    public delegate bool EnumChildCallback(int hwnd, ref int lParam);

    [DllImport("User32.dll")]
    public static extern bool EnumChildWindows(
          int hWndParent, EnumChildCallback lpEnumFunc,
          ref int lParam);


    [DllImport("User32.dll")]
    public static extern int GetClassName(
          int hWnd, StringBuilder lpClassName, int nMaxCount);

    public static bool EnumChildProc(int hwndChild, ref int lParam)
    {
        StringBuilder buf = new StringBuilder(128);
        GetClassName(hwndChild, buf, 128);
        if (buf.ToString() == "EXCEL7")
        {
            lParam = hwndChild;
            return false;
        }
        return true;
    }
}

这篇关于从一个UDF Excel单元格地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！