User-defined function for estimating coefficient of performance

Question

I am using hourly weather data to estimate performance of commercial refrigeration systems under various parameters. The formula for estimating co-efficient of performance (COP) of these systems is repeated for each hour.

I defined a formula to do this calculation:

Public Function estimatedCOP(dischargeTemp As Double, suctionTemp As Double)
Dim a As Double, b As Double, c As Double, d As Double, e As Double, f As Double
a = 9.12808037
b = 0.15059952
c = 0.00043975
d = -0.09029313
e = 0.00024061
f = -0.00099278
estimatedCOP = a + b * suctionTemp + c * suctionTemp * suctionTemp + _
 d * dischargeTemp + e * dischargeTemp * dischargeTemp + _
 f * suctionTemp * dischargeTemp
End Function

The function is repeated twice in a column with 8,760 rows (each hour of the year).

This gives the expected result, but takes a long time to calculate and update (as it's repeated ~17,000 times). How can I improve the performance of this function?

• Ryan Wildry: Hi, there are a few things that can probably be optimized in this function if appropriate. 1) use constants if the values of a-f if they are static. 2) provide a return type for the function, it is variant right now. That being said, those are pretty minor. This is just basic math ops, this should be fast (less than 1 second) for 10,000 items. What are you doing with the result of the function, writing it a worksheet? I suspect that might be where the slowdown is.
• LShaver: @RyanWildry it's used a in a column with 8760 rows.
• Ryan Wildry: Ahh, I gotcha. Try reworking the update as a sub and update the column by storing the results of the function to an array, then dump those values to the sheet in one shot. This should be faster.

Answer 1

You are using the Double data type for all variables. I think this is a good thing since it should avoid conversions. But it is probably too large for your purpose.

From the doc:

Double (double-precision floating-point) 8 bytes -1.79769313486231E308 to -4.94065645841247E-324 for negative values

Source: Data type summary

Perhaps using the single data type is enough ?

Single (single-precision floating-point) 4 bytes -3.402823E38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E38 for positive values

You can try and compare results.

For reference a response I made on another topic, that includes comments about data types: Adding qty If duplicates keys found VBA

---

It is unfortunate that the variable names (a..f) are not descriptive and meaningful. That makes logic errors harder to spot since the variables look all the same. Surely all those values have a meaning ?

---

Something else: if you are doing calculations in a loop and assuming this is in Excel, you can suspend automatic recalculation at the beginning of your procedure:

Application.Calculation = xlManual

Turn it back on when you are done:

Application.Calculation = xlAutomatic

Answer 2

You can reduce the number of operations from 5 additions, 8 multiplications to 5 additions, 5 multiplications using this (incorporating the advice to use constants, declared at module level):

 Const a = 9.12808037
 Const b = 0.15059952
 Const c = 0.00043975
 Const d = -0.09029313
 Const e = 0.00024061
 Const f = -0.00099278
 
 Public Function estimatedCOP(dischargeTemp As Double, suctionTemp As Double)
 ' estimatedCOP = a + b * suctionTemp + c * suctionTemp * suctionTemp + _
 ' d * dischargeTemp + e * dischargeTemp * dischargeTemp + _
 ' f * suctionTemp * dischargeTemp
 
 estimatedCOP = (e * dischargeTemp + d + f * suctionTemp) * dischargeTemp + (c * suctionTemp + b) * suctionTemp + a
 End Function

Optimization like this nearly always looks ugly. That's why I've left the original formula as a comment.