Excel 2007: Making Excel better with UDF's

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
ShumsExcel & VBA ExpertCommented:
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?
Determine the Perfect Price for Your 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 with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:
Maybe your boss needs to read this highly relevant post on Linked In
ouestqueAuthor 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.

*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.
ouestqueAuthor 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, Developing Solutions LLCCommented:
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.
ShumsExcel & VBA ExpertCommented:
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))
    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:

Open in new window

Check below screen shot:
UDF_IndexMatchNow converting this one formula into 60 sheets would be difficult.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
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.
Ejgil HedegaardCommented:
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.
ouestqueAuthor Commented:
Thank you! Very interesting ideas!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.