Блог, предложенный 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 называет типы пользователей. Но как только вы освоитесь с синтаксисом, структуры сделают ваш код намного понятнее и, зачастую, намного быстрее.