CODEWITHSUNDEEP
excelexcel-dna
MarkNS
MarkNS

Reputation: 4021

Find occurrences of an Excel formula with Excel-Dna

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.

Upvotes: 1

Views: 857

Answers (1)

Govert
Govert

Reputation: 16907

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.

Upvotes: 1

Related Questions