Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 'comparison of using LOOP versus an Array to edit a worksheet using vba.
- 'inspired from this question: https://stackoverflow.com/questions/56880195/excel-vba-swap-columns-with-range
- 'VBA, Loops, Arrays, Performance
- Sub speedTEST()
- ''RUN THIS MACRO ON A SHEET THA HAS DATA 2563 rows in columns a and b)
- Dim beginTIME As Date, i As Long
- Const tRials As Long = 9
- 'Loop Approach``````````````````````````````````````
- beginTIME = Now
- For i = 1 To tRials
- Call LoopApproach
- Next i
- 'Puts results in column e
- ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = DateDiff("S", beginTIME, Now) & " seconds for " & tRials & " trials using LoopApproach"
- 'Array appraoch``````````````````````````````````````
- beginTIME = Now
- For i = 1 To tRials
- Call ArrayApproach
- Next i
- 'puts result in column e
- ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = DateDiff("S", beginTIME, Now) & " seconds for " & tRials & " trials using ArrayApproach"
- 'Error404 approach```````````````````````````
- beginTIME = Now
- For i = 1 To tRials
- Call ErrorCode
- Next i
- 'puts result in column e
- ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = DateDiff("S", beginTIME, Now) & " seconds for " & tRials & " trials using ErrorCode"
- End Sub
- '``````````````````````````````````````
- Private Sub ArrayApproach()
- Dim hold_rng() As Variant
- Dim rowsToinclude As Long, WS As Worksheet
- Set WS = ActiveSheet '<--- make sure this is correct worksheet
- With WS
- rowsToinclude = 2563
- hold_rng = .Range("A1:A" & rowsToinclude)
- .Range("A1:A" & rowsToinclude).Value = .Range("B1:B" & rowsToinclude).Value
- .Range("B1:B" & rowsToinclude).Value = hold_rng
- End With
- End Sub
- '``````````````````````````````````````
- Private Sub LoopApproach()
- For i = 1 To 2563
- temp = Cells(i, 1).Value
- Cells(i, 1).Value = Cells(i, 2).Value
- Cells(i, 2).Value = temp
- Next i
- End Sub
- Sub ErrorCode()
- Dim i As Long
- Dim arrA As Variant, arrB As Variant
- Dim ValueA As Double, ValueB As Double
- 'Cahng if needed
- With ThisWorkbook.Worksheets("Sheet1")
- arrA = .Range("A1:A2563")
- arrB = .Range("B1:B2563")
- For i = 1 To 2563
- ValueA = arrA(i, 1)
- ValueB = arrB(i, 1)
- .Range("A" & i).Value = ValueB
- .Range("B" & i).Value = ValueA
- Next i
- End With
- End Sub
Add Comment
Please, Sign In to add comment