For Loop VBA Speed up by putting into an array

Question

I am trying to go through about 2000 lines worth of data and do a for loop and several if statements within each loop. It works, but right now it is painfully slow. I understand from my research that if I can put the data into an array and manipulate it there and then put the data back into the cells would be much faster but I could use some help on the coding to do so. Here is my code.

Sub EliminateVariance()

    Dim Old As Long
    Dim Older As Long
    Dim Oldest As Long
    Dim Current As Long
    Dim VarianceOld As Long
    Dim VarianceNew As Long
    Dim VarianceNew1 As Long
    Dim VarianceNew2 As Long
    Dim Month1 As Variant
    Dim SheetName As Variant
    Dim LastRow As Long
    Dim i As Long

    Month1 = InputBox("What is this month?")

    SheetName = Month1 & " SummaryByCust"

    Worksheets(SheetName).Activate

    LastRow = Cells(Rows.count, "B").End(xlUp).row

    For i = 3 To LastRow
        VarianceOld = Range("V" & i)
        Oldest = Range("I" & i)
        Older = Range("H" & i)
        Old = Range("G" & i)
        Current = Range("F" & i)

        If VarianceOld > Oldest Then
            VarianceNew = VarianceOld - Oldest
            Range("I" & i) = 0
            If VarianceNew > Older Then
                VarianceNew1 = VarianceNew - Older
                Range("H" & i) = 0
                If VarianceNew1 > Old Then
                    VarianceNew2 = VarianceNew1 - Old
                    Range("G" & i) = 0
                    If VarianceNew2 > Current Then
                        MsgBox ("Error: Deferred is greater than what it should be. Verify your numbers")
                    Else
                        Range("F" & i) = Current - VarianceNew2
                    End If
                Else
                    Range("G" & i) = Old - VarianceNew1
                End If
            Else
                Range("H" & i) = Older - VarianceNew
            End If
        Else
            Range("I" & i) = Oldest - VarianceOld
        End If
    Next i

End Sub

If it works but you want optimization, it should be posted on Code Review — Scott Craner
– Scott Craner, Commented Aug 3, 2018 at 16:34
@ScottCraner Good point, but if "painfully slow" means "so slow that it isn't really usable" then perhaps it doesn't really work in any practical sense. The borderline between Stack Overflow and Code Review can be fuzzy. — John Coleman
– John Coleman, Commented Aug 3, 2018 at 16:44
Each time that you specify a range it wouldn't hurt to use specify Range("").Value2 — Marcucciboy2
– Marcucciboy2, Commented Aug 3, 2018 at 16:45
@JohnColeman Agreed, but what the OP is asking is that we do a total rewrite on the existing code which is too broad for this site. If the question was more targeted then I would agree on this specific question. — Scott Craner
– Scott Craner, Commented Aug 3, 2018 at 16:45
@ScottCraner I agree that it is too broad for here and have just voted to close it for that reason. — John Coleman
– John Coleman, Commented Aug 3, 2018 at 16:46

Scott Craner · Accepted Answer · 2018-08-03 16:54:45Z

2

Here is an example on how to use Arrays:

Sub arrayEx()

'Set the range
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("A1:B20000")

'Bulk load the values from the range into an array
'Even if a single column this will create a 2D array
Dim rngArr As Variant
rngArr = rng.Value

'Loop the "Rows" of the array
Dim i As Long
For i = LBound(rngArr, 1) To UBound(rngArr, 1)
    'Do something with that array
    'when loaded from a range it is similar nomenclature to Cells: array(row,column)
    If rngArr(i, 1) = "A" Then
        rngArr(i, 2) = "B"
    End If
Next i

'overwrite the values in range with the new values from the array.
rng.Value = rngArr

End Sub

Try to adapt to fit your needs.

answered Aug 3, 2018 at 16:54

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SeanN430 Over a year ago

Thank you! That's exactly what I was hoping for. I'll do a rewrite and see what I can come up with

SeanN430 Over a year ago

Works like a charm! Thank you again.

Collectives™ on Stack Overflow

For Loop VBA Speed up by putting into an array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related