Каков наиболее эффективный/быстрый способ циклического преобразования строк в VBA (excel)?

Ответ 1

Если вы просто зацикливаете строки 10k в столбце A, затем выгрузите строку в вариантный массив, а затем выполните цикл.

Затем вы можете добавить элементы в новый массив (при необходимости добавляя строки) и использовать Transpose(), чтобы поместить массив в ваш диапазон за один ход или вы можете использовать переменную iterator для отслеживания той строки, в которой вы находитесь и добавьте строки таким образом.

Dim i As Long
Dim varray As Variant

varray = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value

For i = 1 To UBound(varray, 1)
    ' do stuff to varray(i, 1)
Next

Вот пример того, как вы можете добавлять строки после оценки каждой ячейки. Этот пример просто вставляет строку после каждой строки, которая содержит слово "foo" в столбце A. Не то, что "+2" добавляется к переменной я во время вставки, так как мы начинаем с A2. Было бы +1, если бы мы начали наш массив с A1.

Sub test()

Dim varray As Variant
Dim i As Long

varray = Range("A2:A10").Value

'must step back or it'll be infinite loop
For i = UBound(varray, 1) To LBound(varray, 1) Step -1
    'do your logic and evaluation here
    If varray(i, 1) = "foo" Then
       'not how to offset the i variable 
       Range("A" & i + 2).EntireRow.Insert
    End If
Next

End Sub

Ответ 2

РЕДАКТИРОВАТЬ Сводка и рекомендации

Использование конструкции for each cell in range само по себе не является медленным. Медленным является повторный доступ к Excel в цикле (будь то чтение или запись значений ячеек, формат и т.д., Вставка/удаление строк и т.д.).

То, что слишком медленно, зависит от ваших потребностей. Sub, который занимает минуты для запуска, может быть в порядке, используется редко, но другой, который занимает 10 секунд, может быть слишком медленным, если он выполняется часто.

Итак, некоторые общие рекомендации:

• сначала попробуйте. Если результат слишком медленный для ваших нужд, оптимизируйте
• сосредоточиться на оптимизации содержимого цикла
• не просто предположить, что цикл необходим. Есть некоторые альтернативы
• если вам нужно использовать значения ячейки (много) внутри цикла, загрузите их в массив вариантов вне цикла.
• хороший способ избежать сложностей со вставками - это цикл диапазона снизу вверх (for index = max to min step -1)
• Если вы не можете этого сделать, а "вставить строку здесь и там" не так уж много, подумайте о перезагрузке массива после каждой вставки
• Если вам нужно получить доступ к свойствам ячейки, отличным от value, вы застряли с ссылками на ячейки
• Чтобы удалить ряд строк, необходимо создать ссылку на диапазон в области диапазона в цикле, а затем удалить этот диапазон за один проход после цикла

например (не проверено!)

Dim rngToDelete as range
for each rw in rng.rows
    if need to delete rw then

        if rngToDelete is nothing then
            set rngToDelete = rw
        else
            set rngToDelete = Union(rngToDelete, rw)
        end if

    endif
next
rngToDelete.EntireRow.Delete

Оригинальное сообщение

Традиционная мудрость гласит, что петля через клетки плоха, и цикл через вариантный массив хорош. Я тоже был сторонником этого в течение некоторого времени. Ваш вопрос заставил меня задуматься, поэтому я сделал несколько коротких тестов с сюрпризом (для меня в любом случае):

набор тестовых данных: простой список в ячейках A1.. A1000000 (это 1 000 000 строк)

Тестовый пример 1: цикл массива

Dim v As Variant
Dim n As Long

T1 = GetTickCount
Set r = Range("$A$1", Cells(Rows.Count, "A").End(xlUp)).Cells
v = r
For n = LBound(v, 1) To UBound(v, 1)
    'i = i + 1
    'i = r.Cells(n, 1).Value 'i + 1
Next
Debug.Print "Array Time = " & (GetTickCount - T1) / 1000#
Debug.Print "Array Count = " & Format(n, "#,###")

Результат:

Array Time = 0.249 sec
Array Count = 1,000,001

Тестовый пример 2: скопируйте диапазон

T1 = GetTickCount
Set r = Range("$A$1", Cells(Rows.Count, "A").End(xlUp)).Cells
For Each c In r
Next c
Debug.Print "Range Time = " & (GetTickCount - T1) / 1000#
Debug.Print "Range Count = " & Format(r.Cells.Count, "#,###")

Результат:

Range Time = 0.296 sec
Range Count = 1,000,000

Итак, цикл массива выполняется быстрее, но только на 19% - намного меньше, чем я ожидал.

Тестирование 3: объединение массива со ссылкой на ячейку

T1 = GetTickCount
Set r = Range("$A$1", Cells(Rows.Count, "A").End(xlUp)).Cells
v = r
For n = LBound(v, 1) To UBound(v, 1)
    i = r.Cells(n, 1).Value
Next
Debug.Print "Array Time = " & (GetTickCount - T1) / 1000# & " sec"
Debug.Print "Array Count = " & Format(i, "#,###")

Результат:

Array Time = 5.897 sec
Array Count = 1,000,000

Тестовый случай 4: диапазон петель с ссылкой на ячейку

T1 = GetTickCount
Set r = Range("$A$1", Cells(Rows.Count, "A").End(xlUp)).Cells
For Each c In r
    i = c.Value
Next c
Debug.Print "Range Time = " & (GetTickCount - T1) / 1000# & " sec"
Debug.Print "Range Count = " & Format(r.Cells.Count, "#,###")

Результат:

Range Time = 2.356 sec
Range Count = 1,000,000

Итак, событие с одной простой ссылкой на ячейку, цикл на порядок медленнее, и, более того, цикл цикла в два раза быстрее!

Итак, вывод , что самое главное, это то, что вы делаете внутри цикла, и если скорость действительно важна, проверьте все параметры

FWIW, протестированный в Excel 2010 32 бит, Win7 64 бит Все тесты с помощью

• ScreenUpdating off,
• Calulation руководство,
• Events отключен.

Ответ 3

Для каждого из них почему-то намного быстрее, чем для я = 1 - X. Просто попробуйте пройти через тот же словарь,

один раз для каждого Dkey в dDict,

и один раз для Dkey = lbound (dDict.keys) для ubound (dDict.keys)

= > Вы заметите огромную разницу, даже если вы проходите через ту же конструкцию.