• 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...

  • 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

Bob G.

Member
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...
 
Depends on multiple factors. I'd suggest uploading sample workbook that mirrors your actual workbook (with sanitized data) along with the code.
 
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?
 
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...
 

Attachments

  • Compare_File_2017_07_17.xlsm
    196.3 KB · Views: 4
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
 
Hmm, looks like it's due to this line below.
Code:
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.
 
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.
 
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:
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.
 

Attachments

  • Compare_File_2017_07_17.xlsm
    200.4 KB · Views: 6
Last edited:
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.
 
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.
 
Back
Top