What is the best way to lookup specific data in a set of lines or columns in VBA

I did this article because I wanted to find the definitive answer to a problem every VBA programmer has come across at least once: how to find a specific line in a worksheet dataset, and how to do it fast?

There are usually a vast variety of ways to do it. And we often go with the solution we are used to, the one we find most reliable. In practice, loops interacting with the worksheets are often huge sources of inefficiency and under-optimization. The rule number one when dealing with a macro that will interact a lot with the worksheet is of course to put the data into arrays that will be much faster to browse. But even this can be under-optimized in the way we usually do it.

I will time different pieces of codes that do exactly the same thing, to see which approach wins the race against the clock! This being said, keep in mind that the efficiency of a specific approach can hugely vary depending on what you are trying to achieve. Speed may not be you priority if you are building small applications, but as soon as you will scale them up slowness could become a major problem!

Finding data in the worksheet in VBA: you probably do it wrong!

Here we will try four very commonly used methods to find a specific cell within a worksheet:

  • For loop
  • For Each loop
  • Find method
  • vlookup worksheet function
  • All these sub work the same: they loop through a list of words in Sheet3 that they try to find in Sheet1 column A, and get the corresponding value in column B to put it back in Sheet3. The dataset has roughly 500 lines, which is small for us to time precisely the execution, so this process will be repeated 21 times.

    Find a value in a range using For loops

    Sub test1()
    t = Time()
    For i = 0 To 20
        For Each c In Sheets("Sheet3").Range("A1:A500")
            For j = 1 To 500
                If Sheets("Sheet2").Cells(j, 1).Value = c.Value Then
                    c.Offset(0, 1).Value = Sheets("Sheet2").Cells(j, 1).Offset(0, 1).Value
                    Exit For
                End If
            Next
        Next
    Next
    Debug.Print (Time() - t) * 3600 * 24
    End Sub

    Run time: 22 seconds

    The first subroutine that we test is probably the most common and versatile possible solution: use a classical “for” loop with a counter of iteration “i”. This kind of loop isn’t bad in itself, but it forces us to access the worksheet object in multiple occasions, for example with the brutal “Sheets("Sheet2").Cells(j, 1).Offset(0, 1).Value“.

     

    Sub test2()
    t = Time()
    For i = 0 To 20
        For Each c In Sheets("Sheet3").Range("A1:A500")
            For Each c2 In Sheets("Sheet2").Range("A1:A500")
                If c2.Value = c.Value Then
                    c.Offset(0, 1).Value = c2.Offset(0, 1).Value
                    Exit For
                End If
            Next
        Next
    Next
    Debug.Print (Time() - t) * 3600 * 24
    End Sub

    Run time: 13 seconds

    The second subroutine that we are trying is using the “for each” loop, that allows us to use directly the object items we are looping through. It avoids the slow access to the worksheet object. Just compare with the previous subroutine: here c2 is equivalent to Sheets("Sheet2").Cells(j, 1)

    Find a value in a range using the Range.Find() method

    Sub test3()
    On Error Resume Next
    t = Time()
    For i = 0 To 20
        For Each c In Sheets("Sheet3").Range("A1:A500")
           c.Offset(0, 1).Value = Sheets("Sheet2").Range("A1:A500").Find(c.Value)
       
        Next
    Next
    Debug.Print (Time() - t) * 3600 * 24
    End Sub

    Run time: 10 seconds

    Here we try to use the find method of the range object. This method is very powerful and there could be ways to speed it up in certain cases, for example by specifying a search order, a starting point, and it has many other options that can prove very useful. The other advantage is that the find method will retrieve a range object. Notice that a small error handler has been added at the beginning of the macro: this method will be so sad if it can’t find the value you are asking for that it will raise an error!

    Find a value in a range using the vlookup worksheet function

    Sub test4()
    On Error Resume Next
    t = Time()
    For i = 0 To 20
        For Each c In Sheets("Sheet3").Range("A1:A500")
            c.Offset(0, 1).Value = WorksheetFunction.VLookup(c.Value, Sheets("Sheet2").Range("A1:B500"), 2, 0)
        Next
    Next
    Debug.Print (Time() - t) * 3600 * 24
    End Sub

    Run time: 3 seconds

    The last solution that is also obviously the winner of this race against the clock is using the worksheet function vlookup. Notice that this solution is more than 3 times faster than the previous solution and more than 6 times faster than the first solution. This is not necessarily a surprise, as you can imagine the effort the Excel development team has put in the optimization of the vlookup function, that is so pivotal in most business uses of Excel. On the other hand we considerably lost in flexibility: we only retrieve a value, we don’t know if there are several occurrences of the value we are looking for… In our very specific case, it is still an impressive time saver.

    Finding data in an array in VBA: you probably do it wrong too!

    Using VBA arrays or collections to speed-up searches in ranges and lists of values is always a good approach. But just as well as in the worksheet examples, even experienced VBA programmers don’t necessarily optimize their applications when it comes to array search.

    Once again let us try several approaches.

    First we will store the lookup area into a two dimensional flat array:

    Sub MAKE_ARRAY()
    j = 1
    For Each c In Sheets("Sheet2").Range("A1:A500")
        DataArray(j, 1) = c.Value
        DataArray(j, 2) = c.Offset(0, 1).Value
        j = j + 1
    Next
    End Sub

    Find a value in a VBA array using For loops

    Sub test1_2()
    t = Time()
    For i = 0 To 20
        For Each c In Sheets("Sheet3").Range("A1:A500")
            For j = 1 To 500
                If DataArray(j, 1) = c.Value Then
                    c.Offset(0, 1).Value = DataArray(j, 2)
                    Exit For
                End If
            Next
        Next
    Next
    Debug.Print (Time() - t) * 3600 * 24
    End Sub

    Run time: 9 seconds

    In the first trial, we go back to the textbook solution of the “for” loop.

    Notice that the simple use of an array has divided by more than two the execution time.

    Sub test2_2()
    t = Time()
    slidedArray = Application.WorksheetFunction.Index(DataArray, 0, 1)
    For i = 0 To 20
        For Each c In Sheets("Sheet3").Range("A1:A500")
            j = 1
            For Each Item In slidedArray
                If Item = c.Value Then
                    c.Offset(0, 1).Value = DataArray(j, 2)
                    Exit For
                End If
                j = j + 1
            Next
        Next
    Next
    Debug.Print (Time() - t) * 3600 * 24
    End Sub

    Run time: 9 seconds

    The second subroutine uses the “for each” approach. This time, it is taking roughly the same time to run as the previous one. The advantage it gave us in the previous situation was to reduce the time to access the items it was looping through (the ranges). It was looping literally on the cells, and not looping on an iteration variable “i” then used to access the cells. This advantage is nearly gone here as accessing the items of an array is way quicker than accessing the worksheets contents.

    On a side note, I also tried to use a nested array (instead of a flat array) to see if there was any substantial difference. In this case the nested array was a bit slower:

    Sub MAKE_NESTED_ARRAY()
    j = 1
    Dim PushArrayN(1 To 2) As String
    For Each c In Sheets("Sheet2").Range("A1:A500")
        PushArrayN(1) = c.Value
        PushArrayN(2) = c.Offset(0, 1).Value
        DataArrayN(j) = PushArrayN
        j = j + 1
    Next
    End Sub
    Sub test2_3()
    t = Time()
    'slidedArray = Application.WorksheetFunction.Index(DataArray, 0, 1)
    For i = 0 To 20
        For Each c In Sheets("Sheet3").Range("A1:A500")
            j = 1
            For Each Item In DataArrayN
                If Item(1) = c.Value Then
                    c.Offset(0, 1).Value = Item(2)
                    Exit For
                End If
                j = j + 1
            Next
        Next
    Next
    Debug.Print (Time() - t) * 3600 * 24
    End Sub

    Run time: 11 seconds

    Find a value in a VBA array using the vlookup worksheet function

    Sub test4_2()
    On Error Resume Next
    t = Time()
    For i = 0 To 20
        For Each c In Sheets("Sheet3").Range("A1:A500")
           c.Offset(0, 1).Value = WorksheetFunction.VLookup(c.Value, DataArray, 2, 0)
        Next
    Next
    Debug.Print (Time() - t) * 3600 * 24
    End Sub

    Run time: 6 seconds

    In the last subroutine, I wanted to try the array version of the vlookup function. I wasn’t quite sure as to what to expect. In fact I was surprised enough to find that is was slower than using the very same approach directly on the range object as in the previous vlookup code example. The total operation took 6 seconds versus 3 seconds for the range version. I don’t know the reason for this puzzling result. However it is still substantially faster than the “for” loop approach, which is worth keeping in mind next time you need to find a value in an array!

    I hope this article was useful in shuffling a bit our habits as VBA programmers. I was surprised by some results and would be happy to know your thoughts: was I the only one to be surprised?

    On the other hand the cases I studied here are a bit restrictive and not all approaches fit all situations obviously.

    Matthieu Liatard
    Remember this!
    Se souvenir de cela
    Play!
    Tester mes connaissances
    Close

    Bonjour :)
    Avec Question-Player, apprenez de nouvelles choses et n'oubliez plus rien. Jamais. Et en plus c'est gratuit.

    Pour nous aider, vous pouvez créer un compte:

    Login / Créer un compte
    Notez cet article :
    1
    2
    3
    4
    5

    Up next

    Articles similaires