Это происходит потому, что ваша процедура очень занята работой. Например, ваш Sub TheLoop()
обращается к ячейке размером 20995 x 16 раз, чтобы написать на них строку. Взаимодействие VBA с Excel происходит медленно.
Есть несколько вещей, которые вы можете сделать, чтобы ускорить процедуру.
1. Отключите обработчики событий, обновление экрана и расчеты перед запуском процедуры. По окончании процедуры снова восстановите настройки.
'Disable'
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'...... Code'
'Enable'
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
2.Вы можете оптимизировать Sub TheLoop
. Вместо того, чтобы писать сразу в ячейках, записывайте значения внутри массива. После заполнения массива значениями присвойте значения массива нужному диапазону. Например:
Dim ResultValues() As String
Dim j As Long
ReDim ResultValues(2 To 20997, 1 To 3)
For j = 2 To 20997
ResultValues(j, 1) = "New Defect"
ResultValues(j, 2) = "3"
ResultValues(j, 3) = "2"
Next j
With ThisWorkbook.Worksheets("myWorksheet")
.Range(.Cells(2, 3), .Cells(20997, 5)) = ResultValues
End With
ИЗМЕНИТЬ:
Учитывая, что столбцы между теми, которые вы изменяете, являются только текстовыми или пустыми ячейками, вы можете:
- прочитать весь диапазон в массив.
- Затем измените массив так же, как вы сейчас изменяете ячейки.
- После внесения изменений снова выгрузите всю матрицу в диапазон».
Например:
Sub TheLoop()
Dim arrRangeValues() as Variant
Dim j as Long
arrRangeValues= Range("A2:V20997").Value2
For j = 2 To 20997
arrRangeValues(j, 1) = "Defect" 'Cells(row_index , column_index)'
arrRangeValues(j, 3) = "New Defect"
arrRangeValues(j, 4) = "3" ' this one also might be empty'
arrRangeValues(j, 5) = "2" ' this one also might be empty'
arrRangeValues(j, 7) = "Name Surname"
arrRangeValues(j, 8) = arrRangeValues(j, 7)
arrRangeValues(j, 16) = arrRangeValues(j, 7)
...
arrRangeValues(j, 10) = " http://SERVER_NAME:8888/PROJECT_NAME/ "
Next j
Range("A2:V20997").Value2 = arrRangeValues
End Sub
person
CaBieberach
schedule
28.11.2012