Раздел "Таблица заказа"

Основная часть этого бланка - таблица со сведениями о заказываемых товарах. Сетка, которая обычно очерчивает границы ячеек рабочего листа, была удалена, на электронном бланке заказа она неуместна. Теперь необходимо восстановить некоторые из этих границ, чтобы нарисовать таблицу в привычной для глаз форме. На этом этапе я буду работать с вкладкой Borders ("Границы"), открываемой в окне Format Cells ("Формат ячеек") из меню Format ("Формат"). С объектной точки зрения границы объекта класса Range составляют коллекцию Borders. Меняя свойства элементов этой коллекции (объектов Border), можно добиться нужного эффекта. Я построю таблицу в три этапа:

столбцы таблицы;
шапку таблицы с заголовками полей;
последнюю, итоговую строку.

Такое разделение сделает обозримыми макросы, транслирующие мои действия в тексты на VBA.

Построение столбцов

Столбцы таблицы это ее поля. Размер поля, его ширина зависит от содержания поля. В Excel требуемого размера можно достичь двумя путями объединением (слиянием) ячеек, составляющих одно поле, или изменением размера соответствующего столбца Excel, отведенного для поля. Поскольку второй способ действует на всю таблицу и может привести к изменению внешнего вида уже сформатированного листа, то применять его следует с определенной осторожностью. При применении такого способа рекомендуется начинать форматирование документа с создания таблицы и соответствующего изменения размеров ее столбцов. В данном случае применяются оба способа, изменяются размеры нескольких столбцов и сливаются ячейки для поля, задающего название товара.

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

Листинг 6.24.

(html, txt)

Шапка таблицы

Шапка таблицы будет состоять из двух строк, в первой содержатся названия полей, во второй их индексы. Используя соответствующие атрибуты на вкладке Alignment (Выравнивание), я задал центрирование текста по вертикали и горизонтали, а также автоматическое изменение высоты строки, чтобы текст названия полей был полностью видимым. Кроме того, я выделил графически границы шапки, задав их двойными линиями. Макрос получается, конечно, большим, поскольку оперирует с большим числом объектов. Вот его текст:

Листинг 6.25.

(html, txt)

Последняя строка

Последняя строка, как и строки шапки, отличается от остальных строк таблицы. Она используется для подведения итогов. Также как и шапку, я выделил ее границы графически. Итак, макрос "ПоследняяСтрока":

Листинг 6.26.

(html, txt)

Задание расчетных формул и форматирование полей

Одно из преимуществ построения таблиц в Excel состоит в том, что можно задать формулы для автоматического подсчета значений некоторых полей таблицы. В нашем случае все поля, задающие суммы, будут вычисляться по формулам. Формулы для расчета сумм достаточно очевидны и я не буду их выписывать. В тексте макроса они приведены. При работе вручную я в соответствующих столбцах для сумм задал эти формулы в первой рабочей строке таблицы, а затем скопировал их на оставшиеся рабочие строки. В строке итогов я задал суммирование по столбцам таблицы. Для столбцов таблицы я задал также подходящее форматирование данных. Вот текст макроса "Расчеты":

Листинг 6.27.

(html, txt)

Я собрал макросы, строящие отдельные части таблицы под одной обложкой в макросе "ТаблицаРасчеты":

Sub ТаблицаРасчеты() 'Построение таблицы СтолбцыТаблицы ШапкаТаблицы ПоследняяСтрока Расчеты End Sub

Листинг 6.28.

(html, txt)

Запустив этот макрос на листе Excel, я получил таблицу, готовую для заполнения. Вот как она выглядит с заполненными двумя строчками. Заметьте, что все расчеты в ней ведутся автоматически.

увеличить изображение
Рис. 6.8. Таблица продажи товаров бланка Счет-Фактура

Заключительный макрос "УтверждающиеПодписи"

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

Листинг 6.29.

(html, txt)

Запустив этот макрос на чистом листе Excel, я получил полностью сформированный бланк, который в дальнейшем можно использовать в качестве соответствующего шаблона.

Sub Расчеты() ' ' Расчеты Macro ' Macro recorded 29.11.1999 by Vladimir Billig '

'Форматирование полей и задание расчетных формул 'Форматирование поля "Название товара" Range("A34:D45").Select Selection.ShrinkToFit = True

'Форматирование поля "Единица Измерения" Range("E34:E45").Select Selection.HorizontalAlignment = xlCenter 'Форматирование поля "Цена" Range("G34:G45").Select Selection.NumberFormat = "0.00" 'Форматирование поля "Сумма" Range("H34:H46").Select Selection.NumberFormat = "0.00" 'Формула: Сумма = Цена * Количество Range("H34").Select ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" 'Копирование формулы Range("H34").Select Selection.AutoFill Destination:=Range("H34:H45"), Type:=xlFillDefault Range("H34:H45").Select 'Итоговая сумма Range("H46").Select ActiveCell.FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"

'Форматирование поля "НДС" Range("I34:I45").Select Selection.NumberFormat = "0%"

'Форматирование поля "Сумма НДС" Range("J34:J46").Select Selection.NumberFormat = "0.00" 'Формула: Сумма НДС = Сумма * НДС Range("J34").Select ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" 'Копирование формулы Range("J34").Select Selection.AutoFill Destination:=Range("J34:J45"), Type:=xlFillDefault Range("J34:J45").Select 'Итоговая сумма Range("J46").Select ActiveCell.FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"

'Форматирование поля "Всего с НДС" Range("K34:K46").Select Selection.NumberFormat = "0.00" 'Формула: Всего с НДС = Сумма + Сумма НДС Range("K34").Select ActiveCell.FormulaR1C1 = "=RC[-3]+RC[-1]" 'Копирование формулы Range("K34").Select Selection.AutoFill Destination:=Range("K34:K45"), Type:=xlFillDefault Range("K34:K45").Select 'Итоговая сумма Range("K46").Select ActiveCell.FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)" Range("K47").Select

'Нулевые значения в таблице не отображаются ActiveWindow.DisplayZeros = False End Sub

Листинг 6.27.

Я собрал макросы, строящие отдельные части таблицы под одной обложкой в макросе "ТаблицаРасчеты":

Sub ТаблицаРасчеты() 'Построение таблицы СтолбцыТаблицы ШапкаТаблицы ПоследняяСтрока Расчеты End Sub

Листинг 6.28.

Запустив этот макрос на листе Excel, я получил таблицу, готовую для заполнения. Вот как она выглядит с заполненными двумя строчками. Заметьте, что все расчеты в ней ведутся автоматически.