1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using VBA to search table data is slow

Discussion in 'VBA Macros' started by Bob G., Jul 14, 2017.

  1. Bob G.

    Bob G. New Member

    Messages:
    28
    I am using Excel 2016 and have two sheets "Base" and "Update" data. I have the data in tables for dashboard purposes.

    However, i have several VBA compares that I run on the data and I noticed the other day that when the data is in a table the compares take about a 1 to 2 min's to run, BUT, if my data is not in a table the compare takes 10 to 15 sec's. When running 15+ report it added up.

    Has anyone seen this? If so how did you address?

    Thanks in advance for any and all help...
  2. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,577
    Depends on multiple factors. I'd suggest uploading sample workbook that mirrors your actual workbook (with sanitized data) along with the code.
  3. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,619
    Bob

    VBA can be blindingly fast or soul destroyingly slow
    The only difference is how the task is implemented in VBA

    Can you either post the file or a sample file along with the code so we can see what your doing?
  4. Bob G.

    Bob G. New Member

    Messages:
    28
    Hui and Chihiro,

    Attached is my VBA code and 2 sets of data. The first set of data is not structured in a table and the second set is in a table structure. On the "Run Sheet" cell B2 you can switch between the two different formats. Also, the macro can be run from the "Run Sheet" as well, and after it is ran cell C2 will show the elapsed time from the start to the finish of the code.

    Thanks in advance for any and all help...

    Attached Files:

  5. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,619
    Bob

    Can you please briefly

    1. Tell us what you are trying to achieve with this code
    2. What is the problem ?
    3. How to replicate the problem
    4. What you want fixed
  6. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,577
    Hmm, looks like it's due to this line below.
    Code (vb):
    Set rFind = wsUpdt.Range("A:A").Find(R.Value2, [A1], xlValues, xlByRows)
    When I ran test.
    For regular range, it takes about 0.015625 sec, every 5~6 set operation. Where as for table range, it takes about 0.015625 sec, every set operation.

    Not sure about what the reason is for the difference.

    Personally I'd use different workbook to store procedure and use ADO query to compare two tables. Or use PowerQuery.

    Or I'd use dictionary object or array to do compare operation.
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,403
    Hi ,

    See your file now.

    Narayan

    Attached Files:

    paulcherianc and Chihiro like this.
  8. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,577
    Nice way to trim down the range.
  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,403
    Hi Chihiro ,

    Thanks.

    Narayan
    paulcherianc likes this.
  10. Bob G.

    Bob G. New Member

    Messages:
    28
    Hui,

    My issue is when the code is run with data in a table structure, the code takes x10 longer then non-table structured data.

    Replicate the problem by going to "Run Sheet" cell B2, the pull down will show U68EL1 and U68EL1(t). Start by running U68EL1 note the duration in C2, then run U68EL1(t) and note the revised duration. When I run the code it go from 7sec to over a min.

    What I am looking for is there any thing i can do to my code to make it run fast when the data structure is in table.
  11. Bob G.

    Bob G. New Member

    Messages:
    28
    Narayan, Thank you...
  12. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,577
    Here's my preferred method using Array & Dictionary. Should run in under 1 sec.

    Only change to your code logic in Des_Diff sub.
    Code (vb):
    Private Sub Des_Diff()

    Dim wsBase As Worksheet, wsUpdt As Worksheet, wsChng As Worksheet
    Dim bsArr, upArr, Key, dic As Object
    Dim bsCol As Long, upCol As Long, i As Long

    Set wsBase = Sheets(Sheets("Run Sheet").Range("PBase").Value2) 'Base Data
    Set wsUpdt = Sheets(Sheets("Run Sheet").Range("PUpdate").Value2) 'Update Data
    Set wsChng = Sheets("Name Changes") 'Copy to Sheet

    bsArr = wsBase.UsedRange.Value
    upArr = wsUpdt.UsedRange.Value

    For i = 1 To UBound(bsArr, 2)
        If bsArr(1, i) = "Name" Then bsCol = i
    Next
    For i = 1 To UBound(upArr, 2)
        If upArr(1, i) = "Name" Then upCol = i
    Next

    Set dic = CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(bsArr)
        dic(bsArr(i, 1)) = bsArr(i, bsCol)
    Next

    For i = 2 To UBound(upArr)
        If dic.Exists(upArr(i, 1)) Then
            If dic(upArr(i, 1)) <> upArr(i, upCol) Then
                dic(upArr(i, 1)) = dic(upArr(i, 1)) & "," & upArr(i, upCol)
            Else
                dic.Remove upArr(i, 1)
            End If
        End If
    Next

    i = 2
    For Each Key In dic.Keys
        With wsChng
            .Cells(i, 1) = i - 1
            .Cells(i, 2) = Key
            .Cells(i, 3) = Split(dic(Key), ",")(0)
            .Cells(i, 4) = Split(dic(Key), ",")(1)
        End With
        i = i + 1
    Next
    End Sub
    Edit: Woops, forgot to change one of column index with upCol variable. Code updated.

    Attached Files:

    Last edited: Jul 27, 2017
    NARAYANK991 likes this.
  13. Bob G.

    Bob G. New Member

    Messages:
    28
    Chihiro,

    Thank you for your code, I had looked into the dictionary option, but it was a little over my head. Over the last few days I have had some time to work through your code. I have one question. How do I have the code disregard new item(s) that have been added to Update file? For this report, new item(s) are not “Changes” they are “Adds”, and the “Added” items are captured in a separate report output.

    Thank you again for your help, this has been very help full.
  14. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,577
    Sorry, I'm extremely busy at work. Won't be able to spend time on forum until I'm done with the current project. Likely week or two.
  15. Bob G.

    Bob G. New Member

    Messages:
    28
    Chihiro,

    I totally understand. Thanks again for your help. Hope your project is successful.

Share This Page