jyindc
02-10-2017, 02:24 PM
I have a UDF that is basically a series of index-match routines. This UDF performs 2 vlookups and one index/match per calculation, and is present in about 3000 cells. In Excel 2010 a full recalc takes about 5-8 seconds. In 2016, it's about 5-8 minutes, if it ever finishes. The code is same for both versions.
In researching this issue, I've found a lot of things seemingly unrelated to lookups that could affect speed in 2016. Would anyone have any ideas on what I could tweak to speed up the recalc speed?
Here's the basics of the UDF in case there is something that could be going wrong in the newest Excel version:
format of the UDF is =myUDF($A#)
1. get current column number and with find that value in col 1 of small table (15 rows) to return an offset value from col 2.
2. get the value from cell argument range, lookup that in a 400-row table to get a sheet and range where the result will be found -- also an offset from col 2 in that table.
3. index/match based on the column from lookup 1 and row from lookup 2
In 2016, the recalcs really bog down when the number of cells the UDF is in hits about 500. As mentioned previously, there's barely any delay in 2010.
Thanks in advance.
In researching this issue, I've found a lot of things seemingly unrelated to lookups that could affect speed in 2016. Would anyone have any ideas on what I could tweak to speed up the recalc speed?
Here's the basics of the UDF in case there is something that could be going wrong in the newest Excel version:
format of the UDF is =myUDF($A#)
1. get current column number and with find that value in col 1 of small table (15 rows) to return an offset value from col 2.
2. get the value from cell argument range, lookup that in a 400-row table to get a sheet and range where the result will be found -- also an offset from col 2 in that table.
3. index/match based on the column from lookup 1 and row from lookup 2
In 2016, the recalcs really bog down when the number of cells the UDF is in hits about 500. As mentioned previously, there's barely any delay in 2010.
Thanks in advance.