vba - loop inside a loop freezes excel

Question

i am trying to make a loop to go through an array(47193, 4) and an array 2 named attack(41892,1). The idea here is that the attack array has the values in order from the sheet i want to later on add the values to the next column, this is why i add the values to a third array. So the loop is going to go one by one the value from attack array while looping through arr array to find the common data. i tried copying the values directly to the sheet but excel freezes a lot. Now with this way, excel still freezes at this point. Is there anything wrong with it?

Dim arr3() As Variant
Dim dee As Long

ReDim arr3(UBound(attacks, 1), 1)

For k = 0 To UBound(attacks, 1)
   j = 0

   For j = 0 To UBound(arr, 1)

       If attacks(k, 0) = arr(j, 0) And attacks(k, 1) = arr(j, 2) Then
           arr3(dee, 0) = attacks(k, 0)
           arr3(dee, 1) = attacks(k, 1)
           de = dee + 1
       End If

    Next j

Next k

Load one of the arrays into a dictionary, using a concatenated key, then loop over the other array and check the dictionary.Exists method to find the matches. Also you have a typo here: de = dee + 1 — Tim Williams
– Tim Williams, Commented Jun 27, 2018 at 2:43
I think you were on the right track just putting the data directly into a worksheet, be it the final one or an intermediary from which you copy and paste to the final. Check out my answer here: stackoverflow.com/a/51031753/9981075 , specifically the parts at the top and bottom in which I disable then re-enable the screen updating, and calculations. That should help a lot with the freezing problem. I start and end every big sub with those commands for exactly that reason. — SeanW333
– SeanW333, Commented Jun 27, 2018 at 5:53

Tim Williams · Accepted Answer · 2018-06-28 06:30:02Z

Here's some code showing how to use a Dictionary:

Sub Tester()

    Const SZ As Long = 10000 'size of test arrays

    Dim arr1(1 To SZ, 1 To 2)
    Dim arr2(1 To SZ, 1 To 2)
    Dim arr3(1 To SZ, 1 To 2) '<<matches go here
    Dim n As Long, m As Long, i As Long, t, dict, k

    t = Timer
    'fill test arrays with random data
    For n = 1 To SZ
        arr1(n, 1) = CLng(Rnd * 200)
        arr1(n, 2) = CLng(Rnd * 200)
        arr2(n, 1) = CLng(Rnd * 200)
        arr2(n, 2) = CLng(Rnd * 200)
    Next n

    Debug.Print "Filled test arrays", Timer - t
    t = Timer
    'test the nested loop approach
    For n = 1 To SZ
    For m = 1 To SZ
        If arr1(n, 1) = arr2(m, 1) And arr1(n, 2) = arr2(m, 2) Then
            i = i + 1
            arr3(i, 1) = arr1(n, 1)
            arr3(i, 2) = arr1(n, 2)
        End If
    Next m
    Next n

    Debug.Print "Finished nested loop", Timer - t, i & " matches"
    t = Timer

    'create a lookup using a dictionary
    Set dict = CreateObject("scripting.dictionary")
    For n = 1 To SZ
        k = arr1(n, 1) & "|" & arr1(n, 2)
        dict(k) = dict(k) + 1
    Next n
    Debug.Print "Filled dictionary", Timer - t
    t = Timer

    i = 0
    Erase arr3

    'Perform the match against arr2 using the dictionary
    For m = 1 To SZ
        k = arr2(m, 1) & "|" & arr2(m, 2)
        If dict.exists(k) Then
            i = i + 1
            arr3(i, 1) = arr2(m, 1)
            arr3(i, 2) = arr2(m, 2)
        End If
    Next m

    Debug.Print "Finished dictionary loop", Timer - t, i & " matches"

End Sub

Output:

Filled test arrays           0 
Finished nested loop         9.101563     2452 matches
Filled dictionary            0.03125 
Finished dictionary loop     0.0078125    2177 matches

Note the # of matches is slightly different - the nested loop catches duplicate matches but the Dictionary only counts unique matches. You might need to make adjustments depending on your use case.

Collectives™ on Stack Overflow

vba - loop inside a loop freezes excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related