locked
effective way to check for a condition of an Exycel worksheet RRS feed

  • Question

  • Hello,

    we have a shared Addin written in C# which uses the Excel.Interop.

    No we want to check whether the actual worksheet has a cell with a formula of the form

    = MyFunc(….)

    Currently we use the find method on the used range  for this

    range1 = urange.Find(What: "=Myfunc(", LookIn: Excel.XlFindLookIn.xlFormulas, LookAt: Excel.XlLookAt.xlPart);

    return range1 != null;

    (urange being the the used Range of the worksheet).

    No we have a special worksheet wich a used range A1:CA331103

    which has no formulas at all, just Text or empty cells.

    In this case the find lasts 15 seconds, although I disable already events and screen updating.

    Is there a faster way ?

    TIA

     Hendrik Schmieder

    Tuesday, July 17, 2018 8:39 AM

Answers

  • IF MyFunc is a custom UDF, then you could use code in MyFunc that creates a list of calling cell addresses in a storage array - or if you only need one address, a string variable.
    • Marked as answer by h_schmieder Thursday, December 6, 2018 10:17 AM
    Wednesday, July 18, 2018 1:18 PM

All replies

  • 15 seconds for Excel to check the values of 26,157,137 cells seems pretty fast already. Perhaps your code can search only part of the usedrange?
    Tuesday, July 17, 2018 5:35 PM
  • Hello h_chmieder,

    Since you are trying to find a formula, what about using Range.SpecialCells(xlCellTypeFormulas) to narrow the range for finding?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 18, 2018 5:34 AM
  • I also first thought of this,

    but then I thought of the other extreme a worksheet with the same used range, but all cell contains a formula.

    Range.SpecialCells(xlCellTypeFormulas) would then return a range object of the same size and size it contains

    26,157,137 cells this would take additional noticable time

    If there's a cell with "=Myfunc(" then find will stop on the first occurrence, but if not find have to check every cell.

    best regards

       Hendrik Schmieder

    Wednesday, July 18, 2018 11:58 AM
  • IF MyFunc is a custom UDF, then you could use code in MyFunc that creates a list of calling cell addresses in a storage array - or if you only need one address, a string variable.
    • Marked as answer by h_schmieder Thursday, December 6, 2018 10:17 AM
    Wednesday, July 18, 2018 1:18 PM
  • Yes,

    MyFunc is a custom UDF (isn't any UDF custom ?) defined inside a XLL.

    This looks promising, but I must check what happens if the last MyFunc cell is deleted from the sheet.

    best regards

      Hendrik Schmieder

    Wednesday, July 18, 2018 1:24 PM
  • What I meant was custom to you rather than custom to an add-in whose code you can't access. And is wasn't clear if the MyFunc was a stand-in for a specific worksheet function that you are using.
    Wednesday, July 18, 2018 2:24 PM
  • Sorry for delay.

    I was very busy.

    The implantation of the idea was a little bit tricky to avoid false positive or false negatives.

    But currently there aren't any complaints.

    with best regards

      Hendrik Schmieder

    Thursday, December 6, 2018 10:16 AM