1
\$\begingroup\$

I have already resolved several others using Application.Index with Application.WorksheetFunction.Match and reduced time to perform from about 7-8 seconds to milliseconds. But I feel there is still room for improvement.

Should I use an array with Index and Match?

I was also told to use Scripting.Dictionary, but I am looking for someone who can demonstrate how to do it the right away in this scenario. Because in my head I have to populate the dictionary with a loop before I can even use that, so wont it be similar in terms of speed?

'Production Quantity for Dashboard
For i = 2 To Total_rows_Prod
    For j = 2 To Total_rows_Dash
        If ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 5) = ThisWorkbook.Worksheets("Dashboard").Cells(j, 1) Then
           ThisWorkbook.Worksheets("Dashboard").Cells(j, 4) = ThisWorkbook.Worksheets("Dashboard").Cells(j, 4) + ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 31) / ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 4)
        End If
    Next j
Next i

After doing some bottleneck testing as shown below (run time of code is shown at row 10): enter image description here

However, when using Index and Match while only using 1 for-next loop as shown in the code below:

'Production Quantity for Dashboard
For i = 2 To Total_rows_Prod
    m = Application.Match(ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 5), ThisWorkbook.Worksheets("Dashboard").Range("A:A"), 0)
    If Not IsError(m) Then
        ThisWorkbook.Worksheets("Dashboard").Cells(Application.WorksheetFunction.Match(ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 5), ThisWorkbook.Worksheets("Dashboard").Range("A:A"), 0), 4) = ThisWorkbook.Worksheets("Dashboard").Cells(Application.WorksheetFunction.Match(ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 5), ThisWorkbook.Worksheets("Dashboard").Range("A:A"), 0), 4) + ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 31) / ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 4)
    End If
Next i

The run time would be negligible as shown below (still at row 10):

enter image description here

Any improvements that would make the time to perform optimally minimized would be great.

The final time I was able to make everything run with Index and Match replacements was 2 seconds:

enter image description here

But on a slower netbook running a Pentium Atom processor, it takes 26 seconds to perform the same code. So I am wondering if there is way to bring down that 26 seconds.

\$\endgroup\$
  • \$\begingroup\$ You should read this article: Excel VLOOKUP vs INDEX MATCH vs SQL vs VBA - The Analyst Cave \$\endgroup\$ – TinMan Jan 23 at 20:06
  • \$\begingroup\$ Here is some demo code: Dictionary Demo.txt / 600,000 lookups is exactly why you should use a Dictionary. Dictionaries are optimized for lightning fast lookups. You have loop over the data once just to load the Dictionary but you have to pass the complete dataset into Application.Match 600,000 times. \$\endgroup\$ – TinMan Jan 23 at 20:27
3
\$\begingroup\$

You've not provided a fully functional set of code with data, so I can only make untested suggestions as an example for you to incorporate into your application.

Without seeing the data that you are working with, it's impossible to know if a Dictionary is the best approach. Using a Dictionary is a good idea, but only if all of the data in the table is unique. In your code fragment, for example, you are relying on the comparison of two specific cells and are not searching for a value within the entire range of data (which is better suited to a Dictionary).

Using the Index or Match is also reasonable, but remember that these functions directly interact with the worksheet cells. With larger data sets in the Range, this results in slower execution because the Range object must be traversed to access the desired Cells.

Memory-based arrays would be my recommendation for the execution speed improvements you're looking for, with a straight up array-to-array comparison. You can learn more information about using arrays here and here.

Again, you didn't include a complete set of your code for review here, but there are a few observations I can make for improvement.

  1. Always use Option Explicit. (You may be doing this, but we can't see it)
  2. Create intermediate objects/variables to make your code more readable. As an example, you are repeating the full reference for a worksheet as ThisWorkbook.Worksheets("Prod. Qty.") everywhere.

While it might seem more tedious at first, trust me when I say it will make your code far more readable and easier to maintain if you set it up like this:

Dim prodQtyWS As Worksheet
Dim dashboardWS As Worksheet
Set prodQtyWS = ThisWorkbook.Worksheets("Prod. Qty.")
Set dashboardWS = ThisWorkbook.Worksheets("Dashboard")
  1. Establish your ranges explicitly so the extent of your data is clear.

Again, it's a few extra lines of code, but it will not slow down the execution time. (My example here may be an incorrect reading of your OP, you'll have to make adjustments.)

Dim prodQtyRange As Range
Set prodQtyRange = prodQtyWS.Range("A2").Resize(Total_rows_Prod, 31)

From here on out it's implementing your business logic to update the dashboard data. Here is my example Sub, which again, you have to adapt to your specific application. You will see a significant speed improvement with this method.

Option Explicit

Sub Example(ByVal Total_rows_Prod As Long, ByVal Total_rows_Dash As Long)
    Dim prodQtyWS As Worksheet
    Dim dashboardWS As Worksheet
    Set prodQtyWS = ThisWorkbook.Worksheets("Prod. Qty.")
    Set dashboardWS = ThisWorkbook.Worksheets("Dashboard")

    '--- establish the ranges and copy the data into a memory array
    Dim prodQtyRange As Range
    Dim prodQtyData As Variant
    Set prodQtyRange = prodQtyWS.Range("A2").Resize(Total_rows_Prod, 31)
    prodQtyData = prodQtyRange.Value

    Dim dashboardRange As Range
    Dim dashboardData As Variant
    Set dashboardRange = dashboardWS.Range("A2").Resize(Total_rows_Dash, 31)
    dashboardData = dashboardRange.Value

    Dim i As Long
    Dim j As Long
    For i = 2 To Total_rows_Prod
        '--- calc this factor once, since we're adding the same value
        '    to all the dashboard rows
        Dim prodQtyFactor As Double
        prodQtyFactor = prodQtyData(i, 31) / prodQtyData(i, 4)
        For j = 2 To Total_rows_Dash
            If prodQtyData(i, 5) = dashboardData(i, 1) Then
                dashboardData(j, 4) = dashboardData(j, 4) + prodQtyFactor
            End If
        Next j
    Next i

    '--- copy the updated data back to the dashboard
    dashboardRange.Value = dashboardData

End Sub
\$\endgroup\$
  • \$\begingroup\$ About point 2: this isn't just about readability: repeatedly dereferencing the same object over and over at every iteration is extraneous work that doesn't need to be done every time. Pulling work out of the loop can positively affect performance, depending on how many iterations we're looking at (hard to tell with that little to chew on though). \$\endgroup\$ – Mathieu Guindon Jan 20 at 14:47
  • \$\begingroup\$ Thanks for this post. Right i've seen someone post about the same point regarding setting a range that is used over and over to a variable, so you don't keep calling the range on every loop. With regards to how many iterations, since I have about 3 months worth of data, Total_rows_prod has about 1,500 rows, and Dashboard has about 100 rows. So it'll be 150,000 loops. In 1 year it'll be about 600,000 loops only for this part of my code. \$\endgroup\$ – Pherdindy Jan 21 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.