XLLs: A New Way To Optimize

I developed an Xll library with some functions, it works very well with Excel 2000-2003, When receiving Beta 1, the functions didn't work at all, with Beta2 they work better but not correctly !! in fact I use in the function the excel VLookup, I discovered that my Xll function works only if my range is less than 15000 lines !! if more it returns nothing !!! is this a Beta problem or Excel 2007 Problem ?? Thanks in advance

M. Cadoret

ncadoret@bolka.fr

Hello - we continue to fix bugs over time, but it is hard to comment on this specific issue without a bit more information.  If you could email the file to xlfiles@microsoft.com, I would be happy to get a tester to take a look and see whether the bug has been fixed or not.

Thanks.

Potentially dangerous. While there may be no dependencies in the same workbook, what about other open workbooks with external references into the workbook making the XLL function call? Or do you actually mean none of the open workbooks have such dependencies? And while maybe I should know the answer to this, what about picture links?

Not dangerous at all so long as your function returns #N/A when it does not return a valid result.  This is just like the situation when one of the inputs (e.g. an uncalced range) is not ready and so the XLL function needs to bail out early.

Actually let me change my "not dangerous" comment.  I don't think this optimisation is worth implementing, almost ever.  If I need the boost of avoiding calculation, I want to leave it to a user to decide if my function should recalc, and I can do that a number of simple ways from the spreadsheet level.

It would be far more interesting to change the recalc engine to not calc cells that are not relevant to the desired result.  Prune the dependency tree of anything not needed for the results (visual or otherwise) that we are calculating.  (parallel example: intelligent optimisation in functional programming)

That would mean that if a cell is visible, then it and all cells it depends on are calculated correctly.  A similar incremental mechanism could be possibly done with all cells whose vales are copied from within VBA.

Then if you have sheets of not normally looked at debugging calculations, they would only be calced when you are looking at them, and so they could be left in the sheet even when you are not debugging.

Harlan: You are correct. This optimization is not always relevant, and it can be done only in cases where the UDF is used in a well-known way, in certain solutions. E.g. if you are selling an addin (as opposed to an in-house solution), you should probably shy away from it.

Partigo: Interesting idea, thanks!