Best way to do calculations with large data in VBA

[Ranmandaman]

Hi all,

I'm trying to automate some reports and the data set is pretty large. So far I've been using VBA to create pivot tables and doing the calculations from there. I'm now wondering whether there is a quicker way?

Using pivot tables is easy but when I have a lot of calculations it does slow down. I was thinking perhaps using arrays and dictionaries to do the calculations but not sure how I would do this? Are there any articles that'll help.

The calculations are basic eg calculate the count of each item/particular item or sum the cost of a item based on certain conditions etc

Thanks all!

[Kyle123]

How many calculations are you actually doing?

[Ranmandaman]

Maybe like 5-10 calculations but on a large data set and for a large number of records. So doing it the pivot way has been quite slow changing the report filters for each record

[Kyle123]

How do 5 array formulas fare?

[Ranmandaman]

Would like to have it all done automated via VBA


Heres an example of what I am doing. I am calculating the costs and time and count for every Priority Group.
Is the below code an efficient way to do this? I've created 3 dictionaries but Im not sure whether It can be done with just one?

Another thing I need to do is to count the number of Locations for each Priority Group (each Priority Group may have more than one location so I need to count each one)

Would it just be easier to use pivot tables with VBA rather than array/dictionaries?

Thanks

Please Login or Register  to view this content.