Filtering causes UDF Recalculation

[btk92]

Dear Experts,

i have an issue with an User Defined Function. I coded a function that works like an VLOOKUP, but can handle more than one criteria.
My Worksheet contains some defined tables, my function searches them for 2-3 criteria and gives me a result (other column, same row).
I call the function this way: =mysearchfunction(ResultColumn, criteria1, criteria1column, criteria2, criteria2column…).
criteria1, criteria2 etc. are Strings, the columns-parameters are Ranges.

My Problem:
Everytime I filter something, ALL cells which contain =mysearchfunction are being recalculated.
My Workbook contains about 200 Columns and 4000 rows, so the calculation takes too much time.

Is there a way to prevent Excel from recalculating all these cells?
They are just filtered, the values don’t change and my function is independent from filtered values (filtered rows are searched, too – that’s why I wrote an own function instead of using vlookup or index…).
For example: Is there a way to modify the filter functions from excel, so that not all cells are being flagged as “dirty / recalculation needed”?

Sorry for my English and thanks in advance for your help :-)

Kind regards!

[tim201110]

upload your file (small example) please

[btk92]

Hi tim, thanks for your reply!!

i made a (small / clean) demo file and attached it here.
The formula is in the Query table (Colum "I").

Everytime i filter the catalogue (lookup table) the formula gets recalculated and i want to avoid that (without setting calculation mode to manual).
You can check it by creating a breakpoint in the function searchnew and filter the catalogue - everything gets recalculated which is not necessary:-(

[MrShorty]

Off the top of my head, I cannot readily think of a way to influence Excel's recalculation engine, except for the global "set calculation to manual" setting.

You could conceivably set calculation to manual, then have an event procedure that will recalculate certain portions of the spreadsheet when needed. The .Calculate method in VBA can apply to workbook, worksheets, or even ranges https://msdn.microsoft.com/en-us/lib.../ff834613.aspx I'm not sure that this is really better or not.

I think I would focus efforts on improving the search algorithm. Right now, your UDF is using a basic, slow, "brute force" top down linear search algorithm. For example, your sample data is quite well suited to a binary search algorithm. Recognizing the additional performance penalty that a UDF incurs, I might try to do this using Excel's built in lookup functions with a much faster binary search.

1) In A4, enter =CONCATENATE(B4,C4) to join the two criteria into a single text string. Copy down (including the query table below).
2) Your sample data are already sorted in ascending order. I don't know if this is true for your actual data. If needed, sort the lookup table by column A. (At this point I will assume that every entry in column A of the lookup table is unique and you are not worried about duplication here).
3) In D19, enter =VLOOKUP(A19,$A$4:$D$12,4,TRUE). Copy down as needed.

(Note that you can use the structured table references in these as well. I just don't know or use them, so I used the normal A1 notation for references)

With the performance improvements of a built in function over a UDF and a binary search over a linear search, I would expect this to perform much faster than your UDF solution.

If you wanted to stick with your UDF solution, a binary search should be programmable into VBA.

[tim201110]

i'd use a trigger in D17
what does UDF calculate?

[btk92]

Wow, thank you guys!

@ MrShorty:
I was afraid of that... Too bad, there is no way to influence the recalculation triggers? :-(
I will try to develop a procedure that will recalculate certain areas / portions.
Maybe there is s way to select the whole "used range" or "specialcells (celltype formula)" except my UDF and recalculate them. Or i use worksheet_change event and fire a recalculation only for the changed column.
But fore sure it would be better to influence the recalculation triggers - filter event should not fire a recalculation.

Thanks for your advice to use a binary search. At first i used the .Find function from excel it self instead of the for each loop. Unfortunately that didn't work for filtered entrys (they havent been found).
But i will implement a binary search as soon as i solved the filter recalculation problem

To your other approach:
I have to avoid those "help-columns" (that i would need for concatenate for building a "multi-criteria" vlookup) - so thats no option for me
On the other hand is my data neither sorted in ascending order nor are the entrys in column A in my lookup table unique.
But thanks anyway!!!




@ timm201110:
Nice approach with the trigger - if i set D17 to 0 no calculations are done, even if i filter my lookup table.
But: After resetting the trigger cell back to 1, ALL cells are recalculated. i need to avoid that because it takes 2-3 minutes in my case
And: If D17 is set to 1 and i filter in the upper table, excel recalculates all fields. So this is not really a solution for me
calculation has to be started ONLY in case that the data actually changed, not if a filter has been set.