PGSystemTester

Testing Excel Let Function Speed

May 18th, 2020
205
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. 'works for file: https://1drv.ms/x/s!AiO7_3PtXmZ9gqdSf7C4NOtWDPdfeg?e=Mrz62E
  2. 'LinkedIN Article: https://www.linkedin.com/pulse/how-much-benefit-does-excels-new-let-function-offer-steven-rider
  3.  
  4. Sub MakeResults()
  5.     Const theLoopsToDo As Long = 8
  6.     Call TestPasteFormula(True, theLoopsToDo)
  7.     Call TestPasteFormula(False, theLoopsToDo)
  8. End Sub
  9.  
  10.  
  11. Private Sub TestPasteFormula(isLet As Boolean, LoopsToPerformRecalc As Long)
  12. Const theColumnForLet As Long = 6
  13. Const IncludePasteTime As Boolean = False
  14.  
  15. Dim ws As Worksheet, RLine As Long, theColoumn As Long
  16.  
  17. Set ws = ActiveSheet
  18. With ws
  19.  
  20. .Range("A:C").ClearContents
  21. DoEvents
  22.  
  23.  
  24. theColoumn = IIf(isLet, theColumnForLet, theColumnForLet + 1)
  25.  
  26. Dim trackTime As Double
  27.  
  28.  
  29. .Cells(1, theColoumn).Copy
  30. If IncludePasteTime = True Then trackTime = Now
  31. .Range("C:C").PasteSpecial xlPasteFormulas
  32.  
  33. Call GenerateNewNumbers
  34. DoEvents
  35.  
  36. Dim b As Byte
  37.  
  38. If trackTime = 0 Then trackTime = Now
  39. For b = 1 To LoopsToPerformRecalc
  40. Call GenerateNewNumbers
  41. DoEvents
  42. Next
  43.  
  44.  
  45.     trackTime = Round((Now - trackTime) * 24 * 3600, 0)
  46.  
  47.     RLine = .Cells(Rows.Count, theColoumn).End(xlUp).Row + 1
  48.     .Cells(RLine, theColoumn).Value = trackTime
  49.  
  50.     If isLet Then .Cells(RLine, theColoumn + 2).Value = LoopsToPerformRecalc
  51.  
  52. End With
  53.  
  54. End Sub
  55.  
  56.  
  57. Private Sub GenerateNewNumbers()
  58. Dim aVrt(), ws As Worksheet, x As Long, y As Long
  59.    
  60.     Set ws = ActiveSheet
  61.     ws.Range("A:B").ClearContents
  62.     aVrt = ws.Range("A:B")
  63.    
  64.     For y = LBound(aVrt, 2) To UBound(aVrt, 2)
  65.        
  66.         For x = LBound(aVrt) To UBound(aVrt)
  67.             aVrt(x, y) = VBA.Rnd() * 6120 Mod 8 + 1
  68.         Next x
  69.     Next y
  70.    
  71.     ws.Range("A:B") = aVrt
  72.     DoEvents
  73.  
  74. End Sub
RAW Paste Data