CODEWITHSUNDEEP
vbaexcelexcel-2010
Enrique Paullada
Enrique Paullada

Reputation: 53

My macro freezes after an undefined amount of iterations

I have a macro that checks if some names on column Q appear on column A (which is ordered alphabetically) and prints them out on column S if they do. However, every time I run it it freezes after an undefined amount of iterations (never on the same amount of iterations) so it's really hard to know what's going on. If I run it with a breaking point and press F5 for each iteration it doesn't freeze, the thing is I have thousands of names to compare and I really don't want to press F5 that many times.

Here's my code:

Sub test()
    Range("Q2").Select
    analizados = 0
    falsos = 0
    Do Until IsEmpty(ActiveCell)
        id1 = ActiveCell.Value
        primera = Left(id1, 1)
        Range("A2").Select
        Do While Not ActiveCell.Value Like "" & primera & "*"
            ActiveCell.Offset(1, 0).Select
        Loop
        Do While ActiveCell.Value Like "" & primera & "*"
            If id1 = ActiveCell.Value Then
                Range("S2").Select
                ActiveCell.Offset(falsos, 0).Select
                ActiveCell.Value = id1
                falsos = falsos + 1
                Exit Do
            End If
            ActiveCell.Offset(1, 0).Select
        Loop
        analizados = analizados + 1
        Range("Q2").Select
        ActiveCell.Offset(analizados, 0).Select
    Loop
End Sub

Thank you

Upvotes: 2

Views: 87

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

As you noticed, it's not actually frozen. It's just that Excel can't keep up with updating the screen as fast as you're bombarding it with "something has changed on the active sheet" events, and at one point it gives up and lets the macro complete without bothering with refreshing - at least that's how I understand it (might not be exactly what's going on though).

Try this:

Sub Test()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    '...
    '(rest of your code)
    '...

CleanExit:
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume CleanExit
End Sub

Basically you tell Excel to not even bother with repainting itself until you're done: this should greatly speed up your loops.

You may want to combine this with different settings for Application.Calculation and Application.Cursor, too; and for a better UX you could use the status bar to tell the user to wait a little:

Sub Test()
    On Error GoTo ErrHandler
    Application.StatusBar = "Please wait..."
    Application.ScreenUpdating = False
    '...

    '...
CleanExit:
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Exit Sub

Upvotes: 2

Related Questions