VBA. Чтение и запись в Excel. Оптимизация.

1. Оптимизация с помощью Range.Resize()
Впервые столкнувшись с задачей программно прочитать содержимое ячеек Excel, многие используют самый очевидный способ: цикл чтений Cells(row,column).value.

Пример:

For i = 1 To 10
  destination.Cells(i,1).value = source.Cells(i,1).value 
Next

Не трудно заметить, что в данном примере было произведено 10 чтений.

Для ускорения чтения и записи данных из Excel, через VBA, всегда используйте метод класса Range: Resize(). Метод Resize() позволяет прочитать указанный диапазон ячеек в массив, для последующей обработки.

Пример:

arSales = Array()
arSales = source.Cells.Resize(10,1)

В данном примере, в массив arSales помещаются 10 строк и один столбец с листа source. По сравнению с чтением в цикле, данный метод работает в разы быстрее, поскольку чтение происходит за один этап. Таким-же образом данные помещаются в место назначения.

Пример:

destination.Cells.Resize(10,1) = arSales

Если имеется заданная переменная класса Range, то для получения массива можно просто использовать свойство Range.Value
Пример:

Set rng = Range("DATA")
arSales = rng.Value

2. Отключение вычислений на время записи данных.
При каждой записи ячейки Excel из кода VBA, программа автоматически пересчитывает все формулы в книге. Это вызывает серьезное замедление.
Для ускорения записи имеет смысл отключить автоматические вычисления и обновление экрана.

Пример:

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

После произведения необходимых действий в VBA, можно вернуть настройки.

Пример:

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

3. Использование условного форматирования.

Иногда необходимо заполнить и форматировать(раскрасить) таблицу Excel, используя VBA. Для решения этой задачи есть два способа.
Первый, "в лоб": форматировать каждую ячейку или диапазон ячеек в цикле непосредственно из программы. У этого способа два недостатка: скорость и неудобство форматирования.

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

Для реализации этого метода можно создать xls-файл, с заголовком и одной строкой, с заданным условным форматированием. Формат этой строки необходимо скопировать в строки, которые будут заполнятся. Поскольку задать формат одновременно для большого кол-ва ячеек нельзя, то делать это придется блоками.

Пример:

rowscount = 20000 ' Кол-во форматируемых строк
colscount= 5 ' Кол-во форматируемых столбцов
onepass = 1024 ' Кол-во строк форматируемых за один проход
src_row = 1 ' Номер строки с заданным условным форматированием
For i = src_row+1 To rowscount Step onepass
  Rows(src_row).Resize(1, colscount).Copy
  If i + onepass > rowscount Then: onepass = rowscount - i
  Rows(i).Resize(onepass, colscount).PasteSpecial _ 
    Paste:=xlPasteFormats, _
    Operation:=xlNone, _
    SkipBlanks:=False, _ 
    Transpose:=False
Next

Теперь можно вставлять значения. Настройки цвета и шрифта будут применяться автоматически.