How to reduce number of loops in VBA

[th081]

Hi All

I have two arrays (TIimeTS and Data) and i am looping through them but i think i am doing unnecessary loops and i hope someone can point me in how to reduce the loops or use a different type of loop as its taking a long time to run through as i am looping a total of ~264 million times over my If code! and i am sure i dont need that iterations.

TimeTS has a list of dates and times sorted for a month so a records for each minute so 1440*31 = 44,640 records, sorted in order
Data also has date and times but not for the whole month ~6000 minutes, a minute is not duplicated and Data is sorted in order

I have

For i = 2 to Ubound(TimeTS)
For j = 2 to Uboound(Data)

If TimeTS(i, 2) = Data(j, 4) then
'match found so do something here
End If

Next J
Next i

i want to find when the date and minute in matches the date in minute in Data and then use the If statement.

can anyone suggest and alterative approach ?

what i want is for the j loop to only

 

[offthelip]

This type of problem can often be solved using the dictionary object , you still need two loops but it is much faster because the loops are NOT nested so you only do each one once.
If you are not familiar with dictionaries they are well worth getting to grips with because they fast and powerful . I like to think of them as being just like a one dimensional variant array but with a variable index instead of a linear numerical index.
something like this:

    Sub dictionary()
   Dim i As Long
   Dim Dic As Object
 
   Set Dic = CreateObject("Scripting.dictionary")
   For i = 2 To UBound(TimeTS)
      Dic(TimeTS(i, 2)) = i
   Next i
 
For j = 2 To Uboound(Data)
       tt = Dic(Data(j, 4)) ' this pick up the matching index from the first array
       ' incomplete because you haven't said what you are doing
Next j

End Sub

 

[th081]

thank you, i will try now and report back.

 

[Eric W]

You can also do it gracefully without a Dictionary. This is Merge/Match approach. You only read through each array once. It depends on the arrays being sorted, which you said is the case:

Sub Test2()
Dim TimeTS As Variant, Data As Variant
Dim Index1 As Long, Index2 As Long

    TimeTS = Range("L1:L10").Value
    Data = Range("M1:M9").Value
    
    Index1 = 2
    Index2 = 2
    
    While Index1 <= UBound(TimeTS) And Index2 <= UBound(Data)
        If TimeTS(Index1, 1) = Data(Index2, 1) Then
            Debug.Print TimeTS(Index1, 1), Data(Index2, 1)
            ' do something
            Index1 = Index1 + 1
            Index2 = Index2 + 1
        ElseIf TimeTS(Index1, 1) < Data(Index2, 1) Then
            Index1 = Index1 + 1
        Else
            Index2 = Index2 + 1
        End If
    Wend
        
End Sub

The idea is that the indexes start at the same spot. If they match, you do something and increment both of them. If they don't match, you increment the one that is behind until it catches up.

I read my test data from the worksheet, you'll probably not need the ,1 in your arrays.

 

[th081]

thank you very much offthelip,

yesterday when i set the macro up it was taking ~1hour 20 min to run through, today i spent a few hours tidying up the earlier part of the macro and changed the loop to the one i showed you to improve performance and it was even slower!

Your dictionary method did it in about 10 seconds.

Thanks again and i will do some reading around dictionaries

Regards

 

[th081]

Thank you Eric W,

I will have a good look at your suggestion as a learning point.

I did have a vague inkling that your idea of "the indexes start at the same spot. If they match, you do something and increment both of them" might improve performance but had no idea how to implement such a thing.

 

[offthelip]

1hour 20 minutes to 10 seconds is one of the best "improvements" I have ever managed, so glad to be able to help.