Feedback / ideas for optimizing this VBA code for speed

Hey all,

I could use some feedback on ways to optimize a bit of VBA code (below) for speed. I have written a fairly complex model in a single workbook that analyzes and reports the thermodynamics and economic performance of a renewable energy system. The workbook has something like 40 sheets, but for this question, only two are relevant: TEMs and Periodic. The TEMs sheet contains an hourly snapshot of the thermodynamics of the system. It currently has 3 dynamic inputs, which change every hour. It also has about 20 or so static inputs (static in the sense that they don’t change during the program run, but may change from run to run). Some of the static parameters are in the sheet, and some come from other sheets in the workbook. I need to generate a table of up 24 output values on the hourly basis, which means it has 8760 rows for a year’s worth of data. The Periodic sheet contains the 8760 rows of inputs, and it’s also what receives the 8760 rows of outputs.

The VBA code below is what I’ve written, which after a number of versions, I think I have stripped it down to the barest essentials. Formerly, I used copy/paste commands, but I think this method of direct cell writing – on what is essentially a big data movement exercise – is faster. The process is simple: put the input data into TEMs, recalculate only that sheet, put the resulting output data into Periodic. Then transfer an ending value (one of the outputs) into the next period’s starting value (now it’s an input), and do it again 8760 times.

Currently, the program takes about 20 minutes to complete. In a previous version, when I was tracking only about 14 outputs, it took about 3 to 4 minutes. This is when no other programs are running, and Excel is left as the primary window. The overall workbook is about 3.5 MB in size. I’m using a 1 year old Lenovo Thinkpad, running Win7 Pro 64 bit, with 6GB RAM. The processor is an Intel(R) Core(TM) i7-2720QM CPU operating at 2.20GHz (4 processor cores).

Any suggestions you have to speed this up would be appreciated. Ideally, I’d like to see this program run in 1-2 minutes or less. Alternatively, if you think this is the most optimal code, I’d appreciate hearing that too, as I am far from an expert on this stuff.

HERE's THE CODE

Sub TEM_Hourly_Model()

'TimeStamping feature for total time calc
Sheets("Periodic").Range("F3").Value = Evaluate("NOW()")

Dim HM_Input_A As Variant
Dim HM_Input_B As Variant
Dim HM_Input_C As Variant
Dim HM_Input_D As Variant
Dim HM_Input_E As Variant
Dim HM_Input_F As Variant
Dim HM_Input_G As Variant
Dim HM_Input_H As Variant
Dim HM_Output_A As Variant
Dim HM_Output_B As Variant
Dim HM_Output_C As Variant
Dim HM_Output_D As Variant
Dim HM_Output_E As Variant
Dim HM_Output_F As Variant
Dim HM_Output_G As Variant
Dim HM_Output_H As Variant
Dim HM_Output_I As Variant
Dim HM_Output_J As Variant
Dim HM_Output_K As Variant
Dim HM_Output_L As Variant
Dim HM_Output_M As Variant
Dim HM_Output_N As Variant
Dim HM_Output_O As Variant
Dim HM_Output_P As Variant
Dim HM_Output_Q As Variant
Dim HM_Output_R As Variant
Dim HM_Output_S As Variant
Dim HM_Output_T As Variant
Dim HM_Output_U As Variant
Dim HM_Output_V As Variant
Dim HM_Output_W As Variant
Dim HM_Output_X As Variant

Dim Period As Integer
Dim Period_start As Integer
Dim Period_end As Integer
Period_start = 1
Period_end = 8760
Application.CutCopyMode = False
Application.Calculation = xlCalculationManual

For Period = Period_start To Period_end
   'Counter on the worksheet (shows progress)
   Sheets("Periodic").Range("F7").Value = Period
'   Sheets("Periodic").Range("F8").Value = Evaluate(Period / Period_end)   >>>not working properly, so leave inactive for now
  
   'Inputs: give input data to System Inputs
   Range("HM_Input_A").Value = Worksheets("Periodic").Range("HM_Inputs").Cells(Period, 1).Value
   Range("HM_Input_B").Value = Worksheets("Periodic").Range("HM_Inputs").Cells(Period, 2).Value
   Range("HM_Input_C").Value = Worksheets("Periodic").Range("HM_Inputs").Cells(Period, 3).Value
   'Range("HM_Input_D").Value = Worksheets("Periodic").Range("HM_Inputs").Cells(Period, 4).Value
   'Range("HM_Input_E").Value = Worksheets("Periodic").Range("HM_Inputs").Cells(Period, 5).Value
   'Range("HM_Input_F").Value = Worksheets("Periodic").Range("HM_Inputs").Cells(Period, 6).Value
   'Range("HM_Input_G").Value = Worksheets("Periodic").Range("HM_Inputs").Cells(Period, 7).Value
   'Range("HM_Input_H").Value = Worksheets("Periodic").Range("HM_Inputs").Cells(Period, 8).Value
   'Calculate the worksheet
   Application.Worksheets("TEMs").Calculate
   'Application.Calculate    'temporary global recalculation; after testing complete, final program should use worksheet-selective recalculation
   'Outputs
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 1).Value = Range("HM_Output_A").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 2).Value = Range("HM_Output_B").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 3).Value = Range("HM_Output_C").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 4).Value = Range("HM_Output_D").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 5).Value = Range("HM_Output_E").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 6).Value = Range("HM_Output_F").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 7).Value = Range("HM_Output_G").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 8).Value = Range("HM_Output_H").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 10).Value = Range("HM_Output_J").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 11).Value = Range("HM_Output_K").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 12).Value = Range("HM_Output_L").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 13).Value = Range("HM_Output_M").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 14).Value = Range("HM_Output_N").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 15).Value = Range("HM_Output_O").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 16).Value = Range("HM_Output_P").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 17).Value = Range("HM_Output_Q").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 18).Value = Range("HM_Output_R").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 19).Value = Range("HM_Output_S").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 20).Value = Range("HM_Output_T").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 21).Value = Range("HM_Output_U").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 22).Value = Range("HM_Output_V").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 23).Value = Range("HM_Output_W").Value
   Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 24).Value = Range("HM_Output_X").Value
   'Transfer Period-specific Ending Results as a starting value to the same parameter in the Next Period
   'This one is for ending Tank Volume, which is the starting value for next period
   Worksheets("Periodic").Range("HM_Inputs").Cells(Period + 1, 2).Value = Worksheets("Periodic").Range("HM_Outputs").Cells(Period, 2).Value

Next Period
Application.Calculation = xlCalculationAutomatic

'TimeStamping feature for total time calc
Sheets("Periodic").Range("F4").Value = Evaluate("NOW()")
Beep
End Sub

application.screenupdating = false

at the very begining.
Dont use variant if its not needed.

untested but maybe

Sub TEM_Hourly_Model()
   Dim wsPeriodic             As Worksheet
   Dim HM_Input               As Variant
   Dim HM_Output              As Variant
   Dim Period                 As Long
   Dim Period_start           As Long
   Dim Period_end             As Long
   Dim n                      As Long
   Dim i                      As Long

   Period_start = 1
   Period_end = 8760

   With Application
      .CutCopyMode = False
      .Calculation = xlCalculationManual
      .ScreenUpdating = False
   End With
   'TimeStamping feature for total time calc
   Set wsPeriodic = Sheets("Periodic")
   With wsPeriodic
      .Range("F3").Value = Now()


      For Period = Period_start To Period_end
         '###############################################################
         ' DO NOT SHOW PROGRESS ON A SHEET IF YOU WANT CODE TO RUN FAST!
         '###############################################################
         'Counter on the worksheet (shows progress)
         '.Range("F7").Value = Period
         '   Sheets("Periodic").Range("F8").Value = Evaluate(Period / Period_end)   >>>not working properly, so leave inactive for now

         'Inputs: give input data to System Inputs
         HM_Input = .Range("HM_Inputs").Rows(Period).Value
         HM_Output = .Range("HM_Outputs").Rows(Period).Value

         For i = 1 To 8
            .Range("HM_Input_" & Chr(64 + i)).Value = HM_Input(Period, i)
         Next i
         'Calculate the worksheet
         Application.Worksheets("TEMs").Calculate
         'Application.Calculate    'temporary global recalculation; after testing complete, final program should use worksheet-selective recalculation
         'Outputs
         For n = 1 To 24
            HM_Output(1, n) = Range("HM_Output_" & Chr(64 + n)).Value
         Next n
         .Range("HM_Outputs").Rows(Period).Value = HM_Output
         'Transfer Period-specific Ending Results as a starting value to the same parameter in the Next Period
         'This one is for ending Tank Volume, which is the starting value for next period
         .Range("HM_Inputs").Cells(Period + 1, 2).Value = HM_Output(1, 2).Value

      Next Period
      
      With Application
         .Calculation = xlCalculationAutomatic
         .ScreenUpdating = True
      End With
      'TimeStamping feature for total time calc
      .Range("F4").Value = Now()
      Beep
   End With
End Sub

maybe change

HM_Input(Period, i)

to

HM_Input(1, i)

or provide a sample workbook for testing

HM_Input = .Range("HM_Inputs").Rows(Period).Value

creates an array from each row of the input range. the line in error should definitely be using the amendment I made because it's only one row each time

do you have any error values in that range?

it's because your HM_Inputs and HM_Outputs ranges actually only refer to one cell which was not at all clear from the code. change the code to

Sub TEST_Hourly_Model()
   Dim wsPeriodic             As Worksheet
   Dim HM_Input               As Variant
   Dim HM_Output              As Variant
   Dim Period                 As Long
   Dim Period_start           As Long
   Dim Period_end             As Long
   Dim n                      As Long
   Dim i                      As Long

   Period_start = 1
   Period_end = 6

   With Application
      .CutCopyMode = False
      '.Calculation = xlCalculationManual
      '.ScreenUpdating = False
   End With
   'TimeStamping feature for total time calc
   Set wsPeriodic = Sheets("Periodic")
   With wsPeriodic

      For Period = Period_start To Period_end
         Sheets("TEMs").Range("G9") = Period
         'Inputs: give input data to System Inputs
         HM_Input = .Range("HM_Inputs").Cells(Period, 1).Resize(1, 8).Value
         HM_Output = .Range("HM_Outputs").Cells(Period, 1).Resize(1, 24).Value

         For i = 1 To 3
            Sheets("TEMs").Range("HM_Input_" & Chr(64 + i)).Value = HM_Input(1, i)
         Next i
         'Calculate the worksheet
         '''''Application.Worksheets("TEMs").Calculate
         'Outputs
         For n = 1 To 24
            HM_Output(1, n) = Range("HM_Output_" & Chr(64 + n)).Value
         Next n
         .Range("HM_Outputs").Cells(Period, 1).Resize(1, 24).Value = HM_Output
         'Transfer Period-specific Ending Results as a starting value to the same parameter in the Next Period
         ''''(off for now)'''''.Range("HM_Inputs").Cells(Period + 1, 2).Value = HM_Output(1, 2).Value
      Next Period
      
      With Application
         .Calculation = xlCalculationAutomatic
         .ScreenUpdating = True
      End With
      'TimeStamping feature for total time calc
      Beep
   End With
End Sub

with the ranges being only one cell the variable was not holding an array-just a value-which is why the type mismatch arose. if the ranges had been tables it would have run ok as originally coded

I can't comment on speed without knowing the specifics of the actual workbook but I suspect you could store all the results in an array and write that out once at the end-but wasn't sure as you seem to use at least some of the output as input in subsequent calcs-or possibly do all the calculations in the code which might be faster. it sounds like a long time for so few rows though-perhaps calculating specific ranges only would assist

extend that sample file to the same number of rows as your real model-how fast does it run? it seems pretty quick to me with 1000 iterations even on a mac