Как увеличить скорость кода, используя структуры данных, а не циклы for-next?

Question

Я уже решил несколько других, используя Application.Index с Application.WorksheetFunction.Match и сокращенное время выполнения от 7-8 секунд до миллисекунд. Но я чувствую, что есть еще возможности для улучшения.

Должен ли я использовать массив с Index и Match?

Мне также сказали использовать Scripting.Dictionary , но я ищу кого-то, кто может продемонстрировать, как сделать это прямо сейчас в этом сценарии. Потому что в моей голове я должен заполнить словарь циклом, прежде чем я смогу его использовать, поэтому не будет ли он похож на скорость?

'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

После тестирования узких мест, как показано ниже (время выполнения кода показано в строке 10):

Тем не менее, при использовании Index и Match при использовании только 1 цикла for-next как показано в приведенном ниже коде:

'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

Время выполнения будет незначительным, как показано ниже (все еще в строке 10):

Последний раз, когда я смог запустить все с заменами Index и Match было 2 секунды:

Но на более медленном нетбуке с процессором Pentium Atom выполнение того же кода занимает 26 секунд. Поэтому мне интересно, есть ли способ сбить эти 26 секунд.

Любые улучшения, которые позволили бы минимизировать время выполнения, были бы хороши Что касается словарей, но я не знаю, как их применять, поскольку для них есть параметр Key and Value .Add и в моей голове требуется 2 цикла for-next чтобы сделать то же самое?

Answer 1

В целом, наиболее трудоемкой частью кода VBA является чтение и запись значений из / в таблицы. Вы должны уменьшить это столько, сколько сможете.

Самый простой способ сократить количество таких операций - это читать входные данные в массивы, манипулировать ими по мере необходимости, а затем записывать выходные данные.
Вы можете прочитать больше об этом, например, здесь: http://www.cpearson.com/Excel/ArraysAndRanges.aspx

Ваш обновленный код будет выглядеть примерно так:

Dim arr_prodQty5 As Variant
Dim arr_DashBoard1 As Variant
Dim arr_DashBoard4 As Variant
Dim arr_prodQty31 As Variant
Dim arr_prodQty4 As Variant

arr_prodQty5 = ThisWorkbook.Worksheets("Prod. Qty.").Range(Cells(2, 5), Cells(Total_rows_Prod, 5))
arr_prodQty4 = ThisWorkbook.Worksheets("Prod. Qty.").Range(Cells(2, 4), Cells(Total_rows_Prod, 4))
arr_prodQty31 = ThisWorkbook.Worksheets("Prod. Qty.").Range(Cells(2, 5), Cells(Total_rows_Prod, 5))
arr_DashBoard1 = ThisWorkbook.Worksheets("Dashboard").Range(Cells(2, 1), Cells(total_rows_dash, 1))
arr_DashBoard4 = ThisWorkbook.Worksheets("Dashboard").Range(Cells(2, 4), Cells(total_rows_dash, 4))

For i = 2 To Total_rows_Prod
    For j = 2 To total_rows_dash
        If arr_prodQty5(i, 1) = arr_DashBoard1(j, 1) Then
           arr_DashBoard4(j, 1) = arr_DashBoard4(j, 1) + arr_prodQty31(i, 1) / arr_prodQty4(i, 1)
        End If
    Next j
Next i

ThisWorkbook.Worksheets("Dashboard").Range(Cells(2, 4), Cells(total_rows_dash, 4)) = arr_DashBoard4

(У меня нет ваших данных, поэтому я не смог проверить, работает ли это на самом деле, скорее всего, вам нужно исправить ошибку перед запуском)

Использование словаря вместо массивов - это просто более сложный способ достижения того же результата.