Solver Macro VBA Loop Calculation Extremely Slow

[StarryEyes16]

Hi,
I am using Solver to iteratively find best fit solutions for rather complex polynomial equations. I’ve created 3 macros by recording VBA code for loop calculation of desired “variable”. I am only a beginner and don’t have time or need to become proficient.

I have 8760 rows (8760 hrs/year) of data used in the calculations. Additional to the 3 main macro loops above, I’ve created 4 other Solver macros. These don’t require loop calculations – single data set only, and are not a problem. I’ve also created and use many User-Defined-Formulas.

The problem is that although the macros work, running them is extremely slow. After stripping away non-Solver related columns and focusing only on those containing the Solver Variables, Objectives and Constraints, my attempt to run all 8760 hrs took over 15 hours, and I did not get the right answer! Running 500 hrs took 7.5 minutes. So, if I ran all 500 hr chunks until I reach 8760 hrs, the total time would be a minimum 2.19 hrs. Re-assembling these 500 hour chunks back into my original 8760 hour spreadsheet will also be time consuming. Here is a summary of my experimental runs:

Range Size: 100 Hrs; Elapsed time Range, min = 1.4 min; Equiv Run 8760, Hrs = 2.00
Range Size: 250 Hrs; Elapsed time Range, min = 3.6 min; Equiv Run 8760, Hrs = 2.07
Range Size: 500 Hrs; Elapsed time Range, min = 7.5 min; Equiv Run 8760, Hrs = 2.19
Range Size: 1000 Hrs; Elapsed time Range, min = 18.0 min; Equiv Run 8760, Hrs = 2.63
Range Size: 2000 Hrs; Elapsed time Range, min = 50.3 min; Equiv Run 8760, Hrs = 3.67
Range Size: 3000 Hrs; Elapsed time Range, min = 107 min; Equiv Run 8760, Hrs = 5.23
Range Size: 4000 Hrs; Elapsed time Range, min = 176 min; Equiv Run 8760, Hrs = 6.42
Range Size: 8760 Hrs; Elapsed time Range, min = 917 min; Equiv Run 8760, Hrs = 15.28

Is there some method to drastically reduce the run time (to say under ½ hr) for the entire 8760 hours without requiring breakdown into smaller hourly ranges? Or, is there some coding which will run these 500 hr chunks concurrently (i.e, 501 to 1000, 1001 to 1500, etc.), and each with a runtime of approx. 7.5 minute (see above)? I’ve tried recommendations found on internet, such as keeping all cell references and formulas on same worksheet, to improve speed, but still not enough. Is what I’m requesting beyond the scope of Excel 2013? I have an HP EliteBook with Intel Core i5 processor, 8 GB RAM.

Below is an example of the code for one of the 3 macro loops.

Sub MACRO3a1_Dewpoint_from_VapPress_RA()
' MACRO3a1 find best fit dewpoint yielding expected VapPressSat
Application.ScreenUpdating = False
SolverReset
'Use SolverReset to eliminate old constraints and allow creation of new constraints.
    
  Dim i As Integer
    For i = 1 To 1
    
   SolverOk SetCell:=Range("AX" & i + 28), MaxMinVal:=2, ValueOf:=0, ByChange:=Range("AY" & i + 28), _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    '   The 1 in "MaxMinVal:=1" means maximize value
    '   The 2 in "MaxMinVal:=2" means minimize value
    '   The 3 in "MaxMinVal:=3" means Match a specific value
   
   SolverAdd CellRef:=Range("AX" & i + 28), Relation:=2, FormulaText:=Range("AW" & i + 28)
   SolverAdd CellRef:=Range("AY" & i + 28), Relation:=1, FormulaText:=Range("AT" & i + 28)
    '   The 1 in "Relation:=1" means less than or equal "<="
    '   The 2 in "Relation:=2" means equal "="
    '   The 3 in "Relation:=3" means greater than or equal ">="
    
   SolverOptions AssumeNonNeg:=False 'this allows negative results for wet-bulb value
   
   SolverSolve (True)
   SolverReset
   Next i
Application.ScreenUpdating = True
End Sub

I’ve also included a macro which runs these 3 macro loops and presents the runtime for each. This macro also includes 4 other Solver macros, which are the single data set runs mentioned above. (Note: These 4 macros solve in under 5 seconds total)

Sub CalculateRunTime_Seconds()
'PURPOSE: Determine how many seconds it took for code to completely run
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim StartTime As Double
Dim Time2a As Double
Dim Time2b As Double
Dim Time3a As Double
Dim Time3a1 As Double
Dim Time3b As Double
Dim Time4a As Double
Dim Time4b As Double
Dim SecondsElapsed As Double
Dim SecondsElapsed2a As Double
Dim SecondsElapsed2b As Double
Dim SecondsElapsed3a As Double
Dim SecondsElapsed3a1 As Double
Dim SecondsElapsed3b As Double
Dim SecondsElapsed4a As Double
Dim SecondsElapsed4b As Double

'Remember time when macro starts
  StartTime = Timer

MACRO2a_Dewpoint_from_VapPress_RA
    Time2a = Timer
MACRO2b_WetBulb_from_HumRat_RA
    Time2b = Timer
MACRO3a_HumRat_from_VapPress_MA
    Time3a = Timer
MACRO3a1_Dewpoint_from_VapPress_RA
    Time3a1 = Timer
MACRO3b_WetBulb_from_HumRat_MA
    Time3b = Timer
MACRO4a_Dewpoint_from_VapPressSat_SA
    Time4a = Timer
MACRO4b_WetBulb_from_HumRat_OA
    Time4b = Timer

'Determine how many seconds code took to run
    SecondsElapsed2a = Round(Time2a - StartTime, 2)
    SecondsElapsed2b = Round(Time2b - Time2a, 2)
    SecondsElapsed3a = Round(Time3a - Time2b, 2)
    SecondsElapsed3a1 = Round(Time3a1 - Time3a, 2)
    SecondsElapsed3b = Round(Time3b - Time3a1, 2)
    SecondsElapsed4a = Round(Time4a - Time3b, 2)
    SecondsElapsed4b = Round(Time4b - Time4a, 2)

  SecondsElapsed = Round(Timer - StartTime, 2)

'Notify user in seconds
    MsgBox "2a ran in " & SecondsElapsed2a & " seconds." & Chr(10) & Chr(13) & _
    "2b ran in " & SecondsElapsed2b & " seconds." & Chr(10) & Chr(13) & _
    "3a ran in " & SecondsElapsed3a & " seconds." & Chr(10) & Chr(13) & _
    "3a1 ran in " & SecondsElapsed3a1 & " seconds." & Chr(10) & Chr(13) & _
    "3b ran in " & SecondsElapsed3b & " seconds." & Chr(10) & Chr(13) & _
    "4a ran in " & SecondsElapsed4a & " seconds." & Chr(10) & Chr(13) & _
    "4b ran in " & SecondsElapsed4b & " seconds." & Chr(10) & Chr(13) & Chr(10) & Chr(13) & _
    "Total time " & SecondsElapsed & " seconds.", vbInformation
    
    
 ' MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

End Sub

Thanks in advance,
Bill