Excel 2007: Making Excel better with UDF's

ouestque
ouestque used Ask the Experts™
on
I inherited a bunch of workbooks that each contain over 60 sheets, each  containing over 100 thousand Index/Match formulas. It runs pretty slow...

The owner wants to increase performance, but loves his formulas that update on the fly. I was thinking, if I could give him a User Defined Function that runs faster than Index/Match, that would get him part of the way there. Is this possible? If so what would be the process/VBA code?

It would be pretty cool if we could create UDF's that are more efficient than what Excel provides.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
i can't conclude that UDF will definitely run faster or slower than Index/Match, a proper test need to be done but i think there are other dependencies. for example, even you create a UDF and use it into your worksheet, it doesn't mean it will run faster. one of the important concerns is that total number of formulas that were used in your worksheets.

to make your worksheets faster, you can try to reduce the number of formulas using in your worksheets. How can we reduce it? >> we can convert so of them to fixed values when it's feasible.
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Doesn't matter if you use UDF for Index/Match, processing will be slow as it should be to calculate over 60 sheets.

Please post your sample workbook, we can create a VBA instead of running 100 thousands INDEX/MATCH formulas.
NorieAnalyst Assistant

Commented:
There could be ways to make these workbooks more efficient without resorting to VBA.

Can you post some examples of these formulas?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Maybe your boss needs to read this highly relevant post on Linked In

Author

Commented:
Hey guys. I totally agree. Cleaning the file and creating VBA to do the calculations instead of formulas would be ideal. One macro can more efficiently yield results than a bunch of formulas.

Responses:
*Norie: A sample of Index/Match is: Index($A2:B$18,MATCH($A2,A$2:A$18,0),2)
*Shums: I would normally love to post his file, but I can't due to confidentiality.
*Ryan: Totally agree. Nonetheless, every cell in all of those sheets update when any other given cell changes. When he changes one value, everything else needs to update and he looks at each cell afterwards to make a decision. Also, about 50 people that know nothing about macro's and Excel make updates to this file and they will add new items/rows/columns anywhere at random that will be in a non-standard format.  Therefore, he wants me to increase efficiency in a way where he can always change the layout/formatting without needing support. It's racking my brains a little.

I guess my initial philosophy was, I could immediately meet his criteria, "Make it run faster without needing support", by creating a more efficient UDF. It would also be a great learning experience to see if it could be done.

Author

Commented:
Love the article Dale and I totally agree.
NorieAnalyst Assistant

Commented:
One macro can more efficiently yield results than a bunch of formulas.

Are you sure about that?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
The problem I have with formula hidden behind cells in a spreadsheet is that they cannot be documented, but UDFs can.

I can remember one spreadsheet I inherited which had a bunch of formulas (not all in the same column), interspersed throughout a worksheet, which were over 500 characters long, and the creator of the spreadsheet gave no explanation, anywhere in the workbook, of what they were supposed to do.  This spreadsheet was crazy and we eventually rewrote many of those formulas as UDFs, which we could document so people could understand what they were attempting to do.

I doubt that you are going to provide much speed improvement over the Excel functions, but if you have a bunch of those functions linked together, as described above, you might see some improvement.  You will definitely be able to understand better what is going on behind the curtain.
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
As per formula provided, you can create two dynamic named range and use index match udf.
This is just an example, Assuming you have ID's in Col A & Employee Names in Col B you and you need to have Employee Names based on ID:
Named Range for IDNumbers:
=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A$2:$A$1001))

Open in new window

Named Range for Employee's NameList:
=OFFSET(Sheet1!$B$2, 0, 0, COUNTA(Sheet1!$B$2:$B$1001))

Open in new window

Then UDF for Index Match:
Function MyMatch(LookupValue As Variant, RngReturn As Range, RngSearch As Range, Optional HorizontalMatch As Boolean) As Variant
If HorizontalMatch = True Then
    MyMatch = WorksheetFunction.Index(RngReturn, 0, WorksheetFunction.Match(LookupValue, RngSearch, 0))
Else
    MyMatch = WorksheetFunction.Index(RngReturn, WorksheetFunction.Match(LookupValue, RngSearch, 0), 0)
End If
End Function

Open in new window

Suppose you have drop drown for IDs in E1 and you need result in E2:
=MyMatch(E1,NameList,IDNumbers)

Open in new window

Check below screen shot:
UDF_IndexMatchNow converting this one formula into 60 sheets would be difficult.
UDF_Index_Match.xlsm
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
When entering a formula that uses Shums' UDF you would see this
Before.jpgand unless you looked at the code you might have no idea that the HorizontalMatch property was optional.

If you are interested in making a small adjustment so that it looks like this
After.jpgwhere it's clear that HorizontalMatch is optional then take a look at my Creating your own Excel formulas and doing the impossible article.
Since Index is so fast that you will not notice calculation, the issue is to reduce the number of match search.

If several columns use the same match, a helper column making the match once, can increase speed significantly.

If the UDF does the same as the build in function, it is typically slower, but sometimes it is possible to do the match another way.

With a long list, say 20,000 rows, the average search rows are 10,000.
If it is numbers (dates are also numbers) and the list is sorted, the UDF could look a the middle value of the range.
If that is greater than the search value, then look at the first half of the range, else the last half.
Repeating this 10-12 times will reduce the rows to less than 10, which can be searched fast.
So with less than 20 match search the result can be found.

With text match, a table could be made once, with the start and end point for the first, or first 2 letters.
Then the UDF could search that limited range.
That also require that the list is sorted.
Using that principle, with Indirect formulas for the range, could do something similar, also with numbers.

Author

Commented:
Thank you! Very interesting ideas!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial