VBA nested for loop crashes

Question

I have this function in VBA, everything works except when it reaches the For loop it just crashes and I have to close Excel and everything, not really sure how to fix it.

Private Sub checkDuplicates(x As Worksheet)
    Dim n As Integer, i As Long, j As Long

    Sort x
    addheader x, "checkDup."
    n = searchHeader(x, "checkDup.")
    x.Columns(n) = 1
    For i = 50 To 3 Step -1
        For j = 1 To 5
            If Not x.Cells(i, j) = x.Cells(i - 1, j).Value Then
                x.Cells(i, n) = 0
                Exit For
            End If
        Next j
    Next i
End Sub

yep another function in the same module, it returns an integer — Walter
– Walter, Commented May 24, 2016 at 20:53
What do you mean by "it crashes"? Does Excel simply quit stating it's not working any more? Or does your code just raise a run time error with an error message stating what the problem might be? In this case we would need to know the error message. — tyg
– tyg, Commented May 24, 2016 at 21:01
@ dave I got rid of the lastcol and last row and replaced it with integers, it still does not work, just to make it simpler — Walter
– Walter, Commented May 24, 2016 at 21:03

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

Based on the comment-conversation here, your code isn't "crashing".

It's working very hard and accessing cell values in a tight loop, which is the single slowest thing VBA code can do, and you're doing it for a large number of cells.

Wait it out. Excel "isn't responding" because it's busy running your loop and keeping up with all the updates going on.

Here's something that may help it complete faster:

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

' your code here

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

If that doesn't complete in a more reasonable amount of time, then you need to look into an array-based approach, where you dump the interesting range into an in-memory array, work against the array, modify the array values, and then dump the array onto the worksheet in a single write.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 24, 2016 at 21:35

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

3 Comments

findwindow Over a year ago

This would make sense but OP is using 50 for i and is still "crashing".

Mathieu Guindon Over a year ago

@findwindow Excel will always say "(not responding)" whenever there's VBA code running a loop and a user that starts clicking everywhere. Everything is running on a single thread.

Claudius Over a year ago

OP may have really slow pc

Collectives™ on Stack Overflow

VBA nested for loop crashes

1 Answer 1

Based on the comment-conversation here, your code isn't "crashing".

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Based on the comment-conversation here, your code isn't "crashing".

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related