Очень часто, таблицы Excel, являющиеся отчетами баз данных требуют доработок. Так как при создании баз данных требования к отчету были одни, а с течением времени требования меняются, то внешний вид полученных отчетов тоже требует изменения.
Но не всегда имеется возможность переделать код в базе данных, чтобы изменить форму отчета. Иногда нужно просто изменить оформление ячеек (заливка, размер и цвет шрифта и т.п.), иногда чуть сложнее - изменить порядок столбцов, удалить столбцы, добавить строки и т.д. и т.п.
Вот в этом случае и помогает язык программирования VBA.
До того как вы начнете писать код, необходимо составить алгоритм действий, которые необходимо выполнять коду, например:
1. Определить диапазон ячеек, который необходимо отформатировать. Диапазон может быть как статичный (на один раз), так и динамический (если вы постоянно будете выгружать сведения из базы данных и объем этих данных будет постоянно меняться, средство для выделения этого диапазона данных должно быть универсальным).
2. Определить нужные и ненужные столбцы в таблице. Принять решение скрыть их или удалить. Определить ширины оставшихся столбцов.
3. Тоже самое сделать для строк.
4. Разобраться с порядком сортировки строк и их группировкой. При многоуровневой группировке, для удобства, можно задать разные размеры и цвета шрифта, заливки ячеек и т.п. для каждого уровня группировки.
Это основные пункты, которые необходимо выполнить, при форматировании таблицы, полученной из базы данных.
А, теперь, рассмотрим по порядку все указанные пункты с примерами кода.
1. Определение диапазона ячеек.
Определение диапазона ячеек на листе Excel с помощью кода VBA можно задать несколькими способами:
а) на текущем листе нужный диапазон задается границами последней строки и последнего столбца, в которых есть заполненные ячейки.
Команда - UsedRange
dim r as Range
Set r = Activesheet.UsedRange
Преимущество данного способа заключается в том, что пользователю не нужно знать границы диапазона, программа сама меняет диапазон, в зависимости от объема данных, выводимых на лист
Недостаток в том, что иногда на листе могут оказаться данные, которые каким-либо образом оказались слишком далеко за границами нужной таблицы (справа или снизу) и в диапазон входит слишком много ячеек, что может замедлить выполнение кода
б) метод похож на указанный в первом пункте, за исключением того, что размер диапазона определяется в зависимости от первой ячейки, адрес которой пользователь может задать с помощью мыши или прописав адрес (например Range("A1"), или Cells(1,1)). В диапазон включаются те строки и столбцы, которые следуют друг за другом без разрыва.
Команда - CurrentRegion
dim r as Range
Set r = Activesheet.Cells(1,1).CurrentRegion
Преимущества: диапазон может меняется динамически самим кодом
Недостатки: необходимо осуществлять строгий контроль для недопущения разрывов строк и столбцов в диапазоне, который необходимо редактировать, в противном случае расчеты могут оказаться недостоверными.
в) с помощью этого метода, рамки диапазона задаются жестко и не процедура сама не может определить рамки диапазона, заданного пользователем, создавшим код VBA
Команда - Range("F4:H10")
dim r as Range
Set r = Activesheet.Range("F4:H10")
или
Команда - Cells(3,6).Resize(7,3)
dim r as Range
Set r = Activesheet.Cells(3,6).Resize(7,3)
г) выделение всей строки
Выделение нужной строки с помощью кода VBA выполняется следующей команды EntireRow
dim r as Range
Set r = Activesheet.Range("A10").EntireRow
или
dim r as Range
Set r = Activesheet.Cells(10,1).EntireRow
Причем, необязательно исходной задавать ячейку в первом столбце. Вы можете изначально задать ячейку в любом столбце, выбранным диапазоном все равно будет вся строка.
