Improve UDF speed

You should avoid volatile UDFs. Pass all the ranges you need to look at as direct arguments. Also, there is no point in trying to set calculation in a UDF.

What are you wanting to achieve with this function...please explain it's purpose...

A few comments and observations:

1) loops are slow, and your UDF has two loops. One good place for performance improvements is to understand what your loops are doing and see if there is a less expensive way to accomplish the same thing.
1a) The first loop looks like a basic, brute force, linear lookup, which will be very slow. Two parts to improving this. In my experience, Excel's lookup functions are faster than a VBA loop like this. When you can use the binary search/approximate match option available through these lookup functions, it is even faster. Many of Excel's functions are available to VBA through the WorksheetFunction object: https://msdn.microsoft.com/en-us/vba...n-visual-basic The help file includes an example of using the MATCH() function with a linear search option (3rd argument=0). You can get a binary search by using 1 as the 3rd argument. This alone should dramatically improve the performance of your UDF.
1b) The second loop looks like a basic conditional sum (SUMIF() or SUMIFS()). Again, you can use these two Excel functions, and I would expect to see some performance improvement.

2) You say that it needs to run against 250000 rows, but it is not clear exactly what that means.
2a) Does that mean that there will be 250000 rows in HHData * 9 function calls/row = 2.25E6 function calls? If so, you need to recognize that there is a significant overhead that is an inherent part of each UDF function call. This means that any VBA UDF, no matter how simple or optimized, is relatively slow. You can test with a very simple UDF like

Please Login or Register  to view this content.

Then call from the spreadsheet with =parrot(rand()). Make as many copies as you need to test this and see how long it takes to calculate (on my machine, 100000 copies of this function call took ~45 s to calculate, compared to instantaneous for 100000 copies of =RAND()). If your spreadsheet really needs that many calls to the function, then there will likely be significant performance gains by abandoning the VBA UDF and figure out how to do this with regular worksheet functions -- either nested or spread out over helper cells. As noted above, the right combination of MATCH() and SUMIFS() should replicate the UDF calculation without needing the UDF.
2b) Or does this mean the the lookup tables in RAG Times will be 250000 rows long each? Switching to a binary lookup with the built in MATCH() function should dramatically improve this, as a binary lookup does not require significantly more time to search a large list as a small list. If this is the scenario, then simply implementing 1a may be enough performance improvement for the large lookup tables.

Some ideas for you to consider.

As some have mentioned, the UDF you have provided may be possible to do with a worksheet function instead.

I have worked out a named range and a much simpler formula using built in functions that gets the same results.

I am testing it further to make sure it works on all columns.

EDIT:

Made a copy of your HHdata sheet called "HHData New". Left your old sheet as is with your functions. New sheet I used a formula that uses a named range. No macros required, same result. Should be much faster. I only applied my version to columns BO, BP, BQ. Same method can be used for other columns (creating a named range for the weekend and changing the named range in the function when moved over to columns BS, BT, BU).

Formula in BO2:

Formula:

Please Login or Register  to view this content.

Named range (DNOwd):

Formula:

Please Login or Register  to view this content.

I did set your 2 tables on RAG times as actual tables to use structured references (tblWday and tblWEnd).

If you have questions let me know. You likely should take a copy of your workbook, strip out the UDF code and the UDF's entered on sheet, if there is no other code save as xlsx and try my formula and compare to the copy with your UDF. Should be a pretty drastic difference in speed. Not saying it will be instant, 250k rows is alot, but should be a noticeable improvement.

Updated copy with an even simpler formula:

BO2:

Formula:

Please Login or Register  to view this content.

I have not re uploaded, just replace the formula in BO2 with this and fill over and down. If it gives you trouble I can update the sample I sent with revised formula