6.1.12. Построение шаблона таблицы
Предположим, что каждый месяц составляется таблица учета расходов (рис. 6.9).
Рис. 6.9. Шаблон таблицы расходов
Можно облегчить подготовительную работу, автоматизировав процесс создания шаблона таблицы, в которую останется только ввести данные. Как всегда, при написании кода поможет макрорекордер.
Для построения шаблона таблицы нужно выполнить следующие действия:
1. Запустить макрорекордер на выполнение выбором команды Сервис Макрос Начать запись.
2. Выбрать ячейку В1 и ввести в нее Расходы.
3. Выбрать ячейку А2 и ввести в нее Телефон.
4. Выбрать ячейку A3 и ввести в нее Аренда.
5. Выбрать ячейку А4 и ввести в нее Амортизация.
6. Выбрать ячейку А5 и ввести в нее Страховка.
7. Выбрать ячейку А6 и ввести в нее Заработная плата.
8. Выбрать ячейку А7 и ввести в нее Итого.
9. Выбрать ячейку В7 и ввести в нее любым способом формулу =СУММ(В2:В6).
10. Выбрать столбец А и команду Формат Столбец Автоподбор ширины.
11. Завершить работу макрорекордера выбором команды Сервис Макрос Остановить запись.
В результате на листе стандартного модуля будет записан следующий макрос:
Sub Макрос1()
' Макрос1 Макрос
' Макрос записан 18.04.2006
Range("B1").Select
ActiveCell.FormulaR1C1 = "Расходы"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Телефон"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Аренда"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Амортизация"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Страховка"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Заработная плата"
Range("A7").Select
ActiveCell.FormulaR1C1 = "Итого"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)
Columns("A:A").Select
Selection.Columns.AutoFit
End Sub
Рассмотрим, как из этого непропорционально длинного (по сравнению с проделанной работой) и неказистого макроса составить разумный код для создания шаблона отчета. Первые четырнадцать строк кода макроса осуществляют попарно ввод текстовой информации в выбранные ячейки рабочего листа. Имеет смысл каждую из этих пар инструкций заменить одной.
Например, вместо
Range("B1").Select
ActiveCell.FormulaR1C1 = "Расходы"
можно ограничиться только инструкцией
Range("B1").Value = "Расходы"
Следующие две инструкции реализуют ввод формулы =СУММ(В2:В6) в ячейку B7. При этом в макросе эта формула записана в относительном формате R1C1, что выглядит совсем не к месту:
Range("B7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)
Разумно эти две инструкции заменить только одной, используя формат А1 и формулу локальной версии:
Range("B7").FormulaLocal = "=СУММ(В2:В6)"
Последние две инструкции макроса реализуют автоматический подбор ширины столбца А так, чтобы в нем помещались все введенные в него строки текста:
Columns("A:A").Select
Selection.Columns.AutoFit
Целесообразно сократить код, заменив эти две инструкции одной:
Columns("A:A").AutoFit
Кроме того, для пользователя было бы удобно, чтобы имя рабочего листа совпадало с названием месяца, за который составляется отчет. Поэтому будет целесообразно в процедуре добавить инструкции, которые привели бы к отображению на экране диалогового окна, предлагающего изменить название листа. Если пользователь соглашается с этим предложением, то он вводит в поле ввода появившегося диалогового окна новое имя рабочего листа и нажимает на кнопку ОK, а процедура сама уж переименует лист.
Итак, код окончательного варианта процедуры составления шаблона прозрачен, он состоит всего из нескольких инструкций и, что важно, значительно более функционален, чем макрос, на основе которого он был составлен:
Sub ChartBuilder()
Dim sheetName As String
sheetName = InputBox("Введите имя листа")
If sheetName <> Empty Then
ActiveSheet.Name = sheetName
End If
Range("B1").Value = "Расходы"
Range("A2").Value = "Телефон"
Range("A3").Value = "Аренда"
Range("A4").Value = "Амортизация"
Range("A5").Value = "Страховка"
Range("A6").Value = "Заработная плата"
Range("A7").Value = "Итого"
Range("B7").FormulaLocal = "=СУММ(В2:В6)"
Columns("A:A").AutoFit
End Sub