 # 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
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