Как я могу ускорить мой макрос в Excel 2003?

Question

У меня есть макрос, который копирует данные из одной ячейки в другую и, среди прочего, использует формулу VLOOKUP . Моя таблица содержит около 2000 строк.

Когда я запускаю его в Excel 2003, Excel начинает замедляться, поскольку макрос обрабатывает строки 500 и выше. Становится еще хуже, когда он достигает 1000-го ряда. Это займет более 5 часов.

В Excel 2007, однако, макрос выполняется только полчаса.

Может ли кто-нибудь помочь мне найти хорошее решение?

Answer 1

Я не могу установить точную причину, пока не посмотрю ваш код. Но пока смотрите следующие ссылки: Офис.Com - Excel VBA Performance Coding лучшие практики
Ozgrid.com VBA ускоряет код VBA

Они описывают, как оптимизировать производительность любого макроса Excel.

1. Ускорить код и остановить мерцание экрана

   Application.ScreenUpdating=False
   Application.ScreenUpdating=True
   

2. Предотвращение расчета при выполнении кода

   Application.Calculation = xlCalculationManual
   Application.Calculation = xlCalculationAutomatic
   

3. Ускорение кода, если у вас есть рабочий лист или события рабочей книги.

   Также останавливает бесконечные циклы в событиях

   Application.EnableEvents = False
   Application.EnableEvents = True
   

4. Используйте оператор With при работе с объектами

   With Range("A1")
       .Font.Bold = True
       .Interior.ColorIndex = 6
   End With
   

5. Используйте VbNullString вместо = "" Если необходимо по умолчанию для переменной String вернуться к ее значению по умолчанию ""

   strWords = "Cats"
   strWords = vbNullString
   

6. Вставка относительной формулы в диапазон ячеек: быстрее, чем автозаполнение или копирование

   Range("A1:A200").FormulaR1C1 = "=SUM(RC[1]:RC[5])"
   

7. Избегайте использования копирования и вставки всякий раз, когда это возможно (обход буфера обмена)

   Sheet1.Range("A1:A200").Copy Destination:=Sheet2.Range("B1")
   

8. Всегда объявляйте свои переменные правильно!

   Dim wSheet as Worksheet
   Set wSheet = Sheet1
   Set wSheet = Nothing
   

Answer 2

Блог, предложенный tumchaaditya, предлагает несколько отличных предложений, которые стоит реализовать, но я сомневаюсь, что они помогут здесь.

Для меня ключевой вопрос заключается в том, что макрос замедляется. У вас есть такие команды, как:

StrA = StrA & NewData

ReDim Preserve MyArray(1 To UBound(MyArray)+1)

Эти команды делают StrA и MyArray немного больше. Для каждого цикла интерпретатор должен найти пространство для более крупного объекта, скопировать данные из старого объекта и затем освободить старый объект для сбора мусора. Каждый раз, когда вы увеличиваете StrA или MyArray, этот процесс занимает больше времени. Я не знаю, почему проблема хуже с Excel 2003; возможно, Excel 2007 имеет лучший сборщик мусора.

Если вы накапливаете данные из каждой строки, что-то вроде этого намного лучше:

Option Explicit
Type SRowDtl      ' The definition of a User Type must preceed any routines
  Info1 As String
  Info2 As Long
  Info3() As Double
End Type

Sub ProcessRows()

  Dim RowDtl() as SRowDtl
  Dim InxRowDtlCrntMax as Long 

  ReDim RowDtl(NumberOfRows)
  InxRowDtlCrntMax = -1 

  For Each Row ....

     ' Store data from new row
     InxRowDtlCrntMax = InxRowDtlCrntMax+1

     RowDtl(InxRowDtlCrntMax).Info1 = xxx
     RowDtl(InxRowDtlCrntMax).Info2 = yyy
     RowDtl(InxRowDtlCrntMax).Info3(5) = zzz

  Next

Синтаксис может выглядеть странно, если вы не знакомы с тем, что большинство языков называют Structures, а VBA называет типы пользователей. Но как только вы освоитесь с синтаксисом, структуры сделают ваш код намного понятнее и, зачастую, намного быстрее.