PGSystemTester

Timing Loop Edits Versus Array

Jul 4th, 2019
222
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2.  
  3. 'comparison of using LOOP versus an Array to edit a worksheet using vba.
  4. 'inspired from this question: https://stackoverflow.com/questions/56880195/excel-vba-swap-columns-with-range
  5. 'VBA, Loops, Arrays, Performance
  6.  
  7. Sub speedTEST()
  8. ''RUN THIS MACRO ON A SHEET THA HAS DATA 2563 rows in columns a and b)
  9. Dim beginTIME As Date, i As Long
  10. Const tRials As Long = 9
  11.  
  12. 'Loop Approach``````````````````````````````````````
  13.    beginTIME = Now
  14.    
  15.     For i = 1 To tRials
  16.         Call LoopApproach
  17.     Next i
  18.    
  19.     'Puts results in column e
  20.        ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = DateDiff("S", beginTIME, Now) & " seconds for " & tRials & " trials using LoopApproach"
  21.  
  22. 'Array appraoch``````````````````````````````````````
  23.    beginTIME = Now
  24.     For i = 1 To tRials
  25.         Call ArrayApproach
  26.     Next i
  27.    
  28.     'puts result in column e
  29.        ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = DateDiff("S", beginTIME, Now) & " seconds for " & tRials & " trials using ArrayApproach"
  30.  
  31.  
  32. 'Error404 approach```````````````````````````
  33.    beginTIME = Now
  34.     For i = 1 To tRials
  35.         Call ErrorCode
  36.     Next i
  37.    
  38.     'puts result in column e
  39.        ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = DateDiff("S", beginTIME, Now) & " seconds for " & tRials & " trials using ErrorCode"
  40.  
  41.  
  42.  
  43. End Sub
  44.  
  45.  
  46. '``````````````````````````````````````
  47. Private Sub ArrayApproach()
  48.     Dim hold_rng() As Variant
  49.     Dim rowsToinclude As Long, WS As Worksheet
  50.    
  51.     Set WS = ActiveSheet '<--- make sure this is correct worksheet
  52.    
  53.     With WS
  54.         rowsToinclude = 2563
  55.         hold_rng = .Range("A1:A" & rowsToinclude)
  56.        
  57.         .Range("A1:A" & rowsToinclude).Value = .Range("B1:B" & rowsToinclude).Value
  58.         .Range("B1:B" & rowsToinclude).Value = hold_rng
  59.    
  60.     End With
  61.  
  62.  
  63. End Sub
  64.  
  65. '``````````````````````````````````````
  66. Private Sub LoopApproach()
  67.  
  68.     For i = 1 To 2563
  69.         temp = Cells(i, 1).Value
  70.         Cells(i, 1).Value = Cells(i, 2).Value
  71.         Cells(i, 2).Value = temp
  72.     Next i
  73. End Sub
  74.  
  75.  
  76.  
  77. Sub ErrorCode()
  78.  
  79.     Dim i As Long
  80.     Dim arrA As Variant, arrB As Variant
  81.     Dim ValueA As Double, ValueB As Double
  82.  
  83.     'Cahng if needed
  84.    With ThisWorkbook.Worksheets("Sheet1")
  85.  
  86.         arrA = .Range("A1:A2563")
  87.         arrB = .Range("B1:B2563")
  88.  
  89.         For i = 1 To 2563
  90.             ValueA = arrA(i, 1)
  91.             ValueB = arrB(i, 1)
  92.  
  93.             .Range("A" & i).Value = ValueB
  94.             .Range("B" & i).Value = ValueA
  95.         Next i
  96.  
  97.     End With
  98.  
  99. End Sub
Add Comment
Please, Sign In to add comment