Массив представляет собой набор значений, логически связанных друг с другом. Например слушатели в группе или квартиры в доме.
В VBA есть две основные причины почему для некоторых задач нужно использовать массивы
- Удобство
- Производительность
По 1 пункту. Задумайтесь как с помощью обычных переменных можно решить задачу по исчислению средней зарплаты в компании. Или посчитать суммарную площадь в доме. Есть, конечно, не самый плохой вариант — выгрузить данные на лист Excel и посчитать через обращения к ячейкам.
Но тут мы плавно подходим к п.2. Для теста возьму классическую задачу с 1 млн. строк с ФИО и разделю их на Фамилию, Имя, Отчество. Да, в реальности обычно меньшее количество строк, но также возможна и более сложная обработка, большее количество столбцов.
Я выдвину две гипотезы, которые буду проверять:
- На массивах задача будет решена быстрее в несколько раз
- Бонус: Application.ScreenUpdate не даст серьезного прироста производительности в этой задач
Код макроса
[code language=»vb»]
Sub test_score_range_vs_massive()
Dim cell As Range, arr()
Dim tMacro1, tMacro2
Dim txt As String, pos1 As Integer, pos2 As Integer
Dim i As Long
Range("b2:d1000000").Clear
Application.ScreenUpdating = False
‘———————————
‘ Range solution
‘———————————
tMacro1 = Timer
For Each cell In Range("a2:a1000000")
txt = cell.Value
pos1 = InStr(1, txt, " ")
pos2 = InStr(pos1 + 1, txt, " ")
With cell
.Offset(, 1).Value = Left(txt, pos1 — 1)
.Offset(, 2).Value = Mid(txt, pos1 + 1, pos2 — pos1)
.Offset(, 3).Value = Right(txt, Len(txt) — pos2)
End With
Next cell
tMacro1 = Timer — tMacro1
Debug.Print "Способ перебором ячеек без отрисовки экрана", tMacro1
Range("b2:d1000000").Clear
‘———————————
‘ Array solution
‘———————————
tMacro2 = Timer
arr = Range("a2:a1000000").Value
ReDim Preserve arr(UBound(arr, 1), 1 To 4)
For i = LBound(arr, 1) To UBound(arr, 1)
txt = arr(i, 1)
pos1 = InStr(1, txt, " ")
pos2 = InStr(pos1 + 1, txt, " ")
arr(i, 2) = Left(txt, pos1 — 1)
arr(i, 3) = Mid(txt, pos1 + 1, pos2 — pos1)
arr(i, 4) = Right(txt, Len(txt) — pos2)
Next i
Range("a2:d1000000").Value = arr
tMacro2 = Timer — tMacro2
Debug.Print "Способ массивами", tMacro2
Application.ScreenUpdating = True
End Sub
[/code]
Ну и сам результат
[code]
Способ перебором ячеек 222,4766
Способ массивами 6,382813
Способ перебором ячеек без отрисовки экрана 219,1328
[/code]
Как говорится — комментарии излишни.
PS Эту задачу можно было бы решить и третьим способом — записать формулу в ячейки на листе, а потом получить из них значения. В этом случае решение будет шустрее 1го варианта решения, но медленнее массивов.
PPS В способе массивов также можно использовать функцию для массивов под названием Split, которая бы упростила разбиения текста по разделителю