Query-Style Custom Function needs speed boost!

[cyber553]

I have created a custom function (GetData) that I use to pull data from a "database" (which I update in the Fin_Data tab). It works well, however the report I need requires that I use requires that I call this function in 10,000+ cells, and as a result the report takes over 4 hours to recalculate. I would really appreciate any help/advice/code-tweaks/changes that could be offered to cut down on this run-time, thanks in advance!



I've included the function below and attached a sample spreadsheet with report to facilitate/encourage tinkering. The data source is normally ~11,000 rows and the report itself is 1,000 rows and has an additional 50 columns but has been reduced to accommodate the file size limitations to upload.

Please Login or Register  to view this content.

[BrianB]

Your method is too complicated to check through in detail. I am very simple minded, you see

Looking at your final worksheet, it is very similar in layout to many of the reports I had to produce. I usually used pivot tables (sometimes more than one) with VLOOKUP() to transfer data to the final sheet.

This often needed extra calculated columns in the data to analyse the data to fit a PT.

Not knowing your organisation setup etc. ........... I usually handled monthly reporting - doing the calculations once and distributing just the final reports.

[cyber553]

Thanks Brian - I recognize that the data is very Pivot Table friendly, unfortunately my boss doesn't like how they look. While I also realize I could do SUMIFs based on a pivot, I'm still curious to know if anyone can think of a way to speed up my function (I was an engineer in college and still enjoy programming, so maybe now I'm just curious...).

[BrianB]

Your boss does not need to see a pivot table.
As stated, it is just used as a link to the properly formatted report.

[TBrooker]

I'm pondering that perhaps there is a workaround by reducing the number of cells you calculate from rather than changing the way you are doing so, however I'm not sure if its viable. I think this depends on whether or not you are normally modifying data or adding data.

If you are adding data my thinking is perhaps that you can have the code only calculate the newly entered data. This could possibly be done by having a line at the end of the code which writes the number of rows which contained data when you ran that code to a cell and then calling on that cell's value at the start of your code to say which row to start calculating from.

Then instead of summing/calulating all the data again e.g. A2+A3+A4+A5+A6 you add only the values from new data to the previously calculated values from the last time you ran the code e.g. (A2+A3+A4)+A5+A6.

I am not sure how much data you add each time but this could reduce times needed to calculate drasticly provided you are not for example writing all the data calculated from in one or two big sessions.

I'm a little better at understanding what I want to do and seeing novel workarounds than actually coding for them so I'm of little help there but hopefully there is some concept that can be drawn from this.

Tom

[cyber553]

Brian - I appreciate your idea, I've written some other macros and used your strategy of utilizing VLOOKUPs (I use SUMIFs as they provide a bit more flexibility) to re-work my report(s). This has helped a ton - reporting can now be accomplished in about 15 minutes (a 94% improvement in the time required) - and this method seems to scale very well!

To all those with a mind for code and who might be looking for a challenge, I'm still very curious if there might be ways to improve my GetData() function without introducing any new assumptions about how the data is sorted. I'm open to suggestions!