1

Is there a reliable way in code to count calls to UDFs within a worksheet/workbook? Also, what are the alternatives to be notified if UDFs get added/removed/modified?

whatever
  • 2,492
  • 6
  • 30
  • 42

1 Answers1

0

The only reliable way I know of is to parse all the formulas looking for functions.
A function signature is always funcname immediately followed by (
Then you would have to eliminate built-in function if you just want UDFs

You can either build a custom UDF-only parser or start from a more generic one.

Usually for performance reasons you use usedRange.SpecialCells(xlCellTypeFormulas).FormulaR1C1 and eliminate duplicates before parsing.

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • Is usedRange.SpecialCells reliable? I remember having read discussions about it not returning reliably the correct number of used cells (e.g http://stackoverflow.com/questions/11886284/usedrange-count-counting-wrong) – whatever Apr 10 '17 at 13:00
  • Depends what you mean by Used - if you mean contains data or formulas (the real used range) then no it does not do that because it considers that cells that have previously been used or touched by formatting etc to be part of the used range. – Charles Williams Apr 10 '17 at 13:58
  • Ok, good observation, for me "used" means that .text, .value or .value2 are non null. A formatted empty (.text, .value or .value2 are null) cell is of no interest to me. – whatever Apr 10 '17 at 14:57
  • To be clear - UsedRange with SpecialCells does not give you nulls: it gives you multiple areas, 1 for each block of contiguous formulas. (In Excel 2007 there was a max limit of 8192 areas). – Charles Williams Apr 10 '17 at 16:06
  • Ok, so you get a range with one or more areas. How do you go from there to identifying cells containing your UDFs? Loop through them one by one? Any other more efficient approach? What about calling Application.WorksheetFunction.CountA(worksheet.Cells)? – whatever Apr 10 '17 at 16:27