0

Using Excel-Dna I'd like to search all open workbooks in an Excel instance for the number of occurrences of a particular formula.

My best hunch so far is that I need to XlCall the xlcFormulaFind function as documented here:

XlCall.TryExcel(XlCall.xlcFormulaFind, out result, "MyFunc", 1, 2, 1);

However, I just get an ExcelError returned, and am not sure how to dig deeper.

Any advice gratefully received.

MarkNS
  • 3,811
  • 2
  • 43
  • 60

1 Answers1

1

You're using the C API to call Excel. You can find the old help file for the macro features here: http://www.xceed.be/Blog.nsf/dx/excel-macro-function-help-file-for-windows-7

An alternative to the C API would be to use the COM Automation interfaces. From an Excel-DNA add-in, you get hold of the root Application object by calling ExcelDnaUtil.Application. Then the rest of the API is exactly as from VBA.

It looks like you're using FORMULA.FIND macro (xlcFormulaFind) correctly, and it worked fine when I tried it in this Excel-DNA command:

[ExcelCommand(ShortCut = "^G")] // Ctrl+Shift+G
public static void FindMyFunc()
{
    object found = XlCall.Excel(XlCall.xlcFormulaFind, "MyFunc", 1, 2, 1);
    if ((bool)found)
    {
        XlCall.Excel(XlCall.xlcAlert, "Found It!");
    }
    // else it would have shown a message already
}

Note the xlcFormulaFind returns a bool indicating whether the text was found or not. If a cell was not found, it shows a message and returns false.

You can't use the function from inside a worksheet function (UDF). If the UDF is not marked as IsMacroType=true the call the XlCall.Excel will fail (throwing an XlCallException or returning a fail code if you use XlCall.TryExcel). If you do mark the function as IsMacrotype=true the macro call succeeds, but always returns false.

Govert
  • 16,387
  • 4
  • 60
  • 70
  • Hi Govert, thanks for the advice, and moreover thanks for Excel-DNA - it's a great piece of software. I never actually got this to work, but I was very pressed for time so didn't get to explore too far, hopefully I'll get back to it sooner or later. In the end I just changed the workflow of my utility to remove the requirement. – MarkNS May 28 '14 at 18:49