speed of program issue

[soce]

Alright, I will do my best to describe what it is that I am trying to accomplish and throw in some code as well but at the end of the day, I am looking for the fastest way to do this since run time is important to me.

I need to make a program that does a MASSIVE amount of number crunching and calculating energy lost/gained for buildings over an entire year. The inputs for this type of thing are huge. I have solar variables, temperature, wall types, tons of lookup tables based on wall types for radiation, convection conduction, etc..... the point being that this spreadsheet needs to be able to chug through 365 days of the year and lookup a ton of data pending on what type of data the customer types in for his case. I tried to use a bunch or arrays to minimize the amount of excel workbook space used. So, I reduced cell floor space but now have to swap in more data in and out of my program during the run time. In doing this I seemed to have greatly slowed everything down to the point of being unusable.

Does anyone understand enough about what I am saying to help me out? Basically, what is the best way to get a ton of data in and out of a spreadsheet that has to run through a big macro to chug a bunch of numbers? It seems to me, that building to MINIMIZE the amount of data swapping in and out of the cells seems to be the fastest. Therefore, instead of having a page that calculates something six times during one day, it is faster to build the page 6 times as big and have it only calculate once.... is this the case or am I missing something?

If I have totally lost you I am not surprised but if anyone does follow and can offer help I am all ears. Thanks.

[mrice]

Simple suggestion - use VBA to turn the screen updating off - this can significantly improve speed.

[shg]

As I suggested yesterday, you should also turn off calculation while swapping data in and out, and then calculate selectively (the workbook, a single sheet, or a single range) only as needed.

[soce]

ok, thx for the feedback. i already shut of the updating but the recalculating is very difficult because i need a lot of the equations recalculated every time.

[shg]

Right, but you can calculate from VBA only what you need computed (Excel knows how to handle dependencies), and only when needed. So if you're loading parameters, for example, you don't need to calculate until you have a coherent set loaded.

[mikerickson]

Reading and writing to a spreadsheet is much faster when done in bulk.

Please Login or Register  to view this content.

Is far faster than

Please Login or Register  to view this content.