Feedback / ideas for optimizing this VBA code for speed

Originally Posted by Bishonen

Dont use variant if its not needed.

Or just don't declare variables if you're not going to use them.

Also, you don't need to use Evaluate to get the result of simple expressions.

This is very interesting. But I'm stuck on the line in the For i-1 to 8 section:

.Range("HM_Input_" & Chr(64 + i)).Value = HM_Input(Period, i)

I'm getting a "Type Mismatch" error, and the right side of the equal sign appears to be the culprit: Watching that expression yields a Type Mismatch that I don't quite understand.

I guess I'm confused by the "HM_Input(Period, i)" expression to begin with. "HM_Input" has already been defined as a variant variable. And it was given a very workable (and the correct one from my Watch window) value just 3 lines prior. So what's the "(Period, i)" for in the expression? I tried running it with that part stripped off in order to just try to assign its recently acquired value to the location. But now the error that comes back as "Application-defined or object-defined error".

Sorry, the workbook is company-confidential, so I'm not at liberty to let it out. I otherwise like your approach a lot, and am hoping to use it.

Oh, and the HM_Input(1, i) also got a type mismatch error

There are no errors in the input values.

In order to preserve confidentiality, I've extracted the two relevant sheets and uploaded the file. I've changed the formulas in the TEMs sheet (the hourly calculator) to reflect a simplified algorithm to track results instead of the confidential calculations. The relevant named ranges are identical. However, I've added "WhatPeriod" just so I can track this test version.

You can see the type of results I'm looking for in the first three rows in the Outputs section of the Periodic sheet. I've inserted your code as the workbook's only macro. I am still getting the same error result either with HM_Input(Period, i) or HM_Input(1, i): type mismatch.

I'm still confused as to why this error is occuring. Your help is really appreciated!!

"HM_Inputs and HM_Outputs ranges actually only refer to one cell " - yes, that was an artifact left over from the previous method. If I had them referring to the entire 8760 row range, would that have made the difference for your previous code?

So this latest version works great in the original workbook. The execution time is still 20 minutes. I still like the programming exellence of your method, and I've wanted to learn to use variable arrays for awhile, so thanks for giving me a practical entry.

But, is this as good as it gets? 20 minutes to complete? Is there any other kind of coding approach you would suggest I explore?

Do you know if the major process-time-consumer is the worksheet calculation; or is it the act of transfering the results?

I'm inclined to try to put the results to an array and write it out at the end, as you suggest. I do only have one result that gets transfered from one period to the next, but I suspect I could still perform that function within the array variable - right? But all this assumes that the major time-suck is writing/transfering results from the TEMs calculations to the Periodic table.

However, if the worksheet calculation is mostly what's slowing it down, then the array strategy wouldn't be worth the time/effort to figure it out. I don't think I can put the TEMs calcluations in code because of they way I need to use them to graphically display the dynamics of the energy system. This also allows me to change the system structure and calculations (and visually keep track of what I'm doing), which I will need to do on more than a rare occasion.

I've now attached a stripped down version of the TEMs (all calculations are real, but all labels and system drawings are removed for confidentiality) which is where all the periodic calculations are done for this macro. You'll note I only allow the manual calc of that sheet in each period, not the entire workbook. As you can see, it has the three dynamic inputs, and a bunch of other static inputs. You can also see how the math is all linear, and the formulas aren't too complex - all fairly simple stuff. The macro is also included (limited to six periods) and is functional.

Appreciate your insights here.