1

Я с трудом пытаюсь оптимизировать цикл VBA For Each который должен анализировать 10-15 тыс. Строк в сводной таблице, основанной на таблице Excel, и все в одной книге. В настоящее время это занимает около 2 минут, что я хотел бы улучшить, поскольку я использую это на собраниях.

Я искал и нашел несколько интересных предложений, таких как отключение обновления экрана, ручные вычисления и « dim » моих переменных как других типов данных, но я не получил разницы в скорости. Я предполагаю, что цикл For Each просто не предназначен для этой цели.

Читая этот пост Супер пользователя, похоже, что я мог бы поместить соответствующие значения в "Словарь" и это было бы очень быстро. Тот факт, что у меня есть два элемента для фильтрации, делает его немного сложнее для меня.

Я «учусь на собственном опыте» и не претендую на звание программиста VBA, так что любая помощь очень ценится! 

Dim pvtTable As PivotTable
Dim pvtField1, pvtField2 As PivotField
Dim pvtItem1, pvtItem2 As PivotItem

Set ws = ActiveSheet
Set pvtTable = ws.PivotTables("PTReport")
Set pvtField1 = pvtTable.PivotFields("callNummer")
Set pvtField2 = pvtTable.PivotFields("Destination")

Application.ScreenUpdating = False

For Each pvtItem1 In pvtField1.PivotItems
  If InStr(UCase(pvtItem1), "STORE") > 0 Then
    pvtItem1.Visible = True
  Else
    pvtItem1.Visible = False
  End If
Next

For Each pvtItem2 In pvtField2.PivotItems
  If InStr(UCase(pvtItem2), "221") > 0 Then
    pvtItem2.Visible = True
  Else
    pvtItem2.Visible = False
  End If
Next

Application.ScreenUpdating = True

Если я могу что-то уточнить или предоставить, более подробную информацию, пожалуйста, дайте мне знать.

|источник

2 ответа2

1

Я подозреваю, что проблема в 20-30K вызовов InStr(UCase()) .  Я предлагаю вам определить пару вспомогательных столбцов.  Например, если ваши данные «callNummer» и «Destination» находятся в столбцах A и B , установите

  • Y1 → =IFERROR(SEARCH("store", A1), 0)
  • Z1 → =IFERROR(SEARCH("221", B1), 0)

и затем измените свой код VBA, чтобы проверить, являются ли Yn и Zn > 0 .  Таким образом, проверка строки в строке выполняется всякий раз, когда изменяются данные «callNummer» и «Destination», и для подпрограммы VBA требуется гораздо меньше работы.  И, конечно же, вы можете скрыть вспомогательные столбцы, когда все заработает.

Я не уверен точно, как перевести ваши pvtItemN.Visible = … в эту структуру.

Но даже если вы не можете заставить это работать, вы можете изменить InStr(UCase(pvtItem2), "221") на InStr(pvtItem2, "221") .  Если все, что вам нужно, это число, то нет смысла преобразовывать алфавитное содержимое ячейки в верхний регистр.

|источник
1

Перед первым циклом for ... next добавьте следующий код:

Dim saveCalc as xlCalculation

With Application
   .ScreenUpdating = False
   saveCalc = .Calculation
   .Calculation = xlCalculationManual
End With

Затем, после второго цикла for ... next, добавьте следующий код:

With Application
    .ScreenUpdating = True
    .Calculation = saveCalc
End With
|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .