Ввод заголовка и “шапки” таблицы
1. Ввести заголовок и шапку таблицы (рис. 8), отображение которых на экране приведено на рис. 13
Рис. 13. Заголовок и шапка таблицы
2. Щёлкнуть по кнопке Остановить запись (Excel 207 / 2010 /2013) или выполнить команду Сервис ® Макрос ® Остановить запись (Excel 2003).
Теперь можно проверить, что получится при выполнении макроса. Для этого нужно удалить заголовок и шапку с листа Excel и запустить макрос ЗаголовокШапка на выполнение, для чего нужно выполнить команду
для MS Excel 2003 Сервис ® Макрос ® Макросы
для MS Excel 2007 / 2010 /2013 нажать кнопку Макросы на вкладке Разработчик (рис. 14) или на вкладке Вид (рис. 15)
Рис. 14. Лента MS Excel 2010 с открытой вкладкой Разработчик
Рис. 15. Лента MS Excel 2010 с открытой вкладкой Вид
В появившемся окне Макрос (рис16) дважды щёлкнуть по имени макроса ЗаголовокШапка в окне Макрос или нажать кнопку Выполнить. На активном листе появится таблица, приведённая на рис. 13.
Переход к просмотру или изменению макроса можно выполнить несколькими способами:
1. В окне Макрос (рис. 16) щёлкнуть по имени макроса (ЗаголовокШапка), а затем щёлкнуть по кнопке Изменить.
Рис.16. Окно макросов
2. Выполнить команду Сервис ® Макрос ® Редактор Visual Basic и выбрать нужный макрос (Excel 2003) или нажать кнопку Visual Basic, расположенную на вкладке Разработчик слева (Excel 2007 / 2010 / 2013).
3. Щёлкнуть по кнопке Редактор Visual Basic.
Рис.17 Фрагмент пиктографического меню в Excel2003
В Excel2007 / 2010 / 2013:
Рис.18 Лента MS Excel2010 с открытой вкладкой Разработчик
Используем первый способ. Ниже приведён текст макроса ЗаголовокШапка, сгенерированный Excel при выполнении действий пользователя по вводу заголовка и шапки таблицы.
Option Explicit
 |
Sub ЗаголовокШапка()
'
' ЗаголовокШапка Макрос
' Макрос записан 09.10.2015 (Neklyudova)
ActiveCell.FormulaR1C1 = "Поставка товаров"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Код товара"
Range("B2").Select
ActiveCell.FormulaR1C1 = "Наименование товара"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Единица измерения"
Range("D2").Select
ActiveCell.FormulaR1C1 = "Цена"
Range("E2").Select
ActiveCell.FormulaR1C1 = "Количество"
Range("F2").Select
ActiveCell.FormulaR1C1 = "Сумма"
Range("F3").Select
End Sub
При записи макроса VBA кодирует все действия пользователя. Для ввода данных в каждую ячейку текста VBA сгенерировал две инструкции, первая из которых соответствует выделению ячейки – используется свойство объекта Range (Диапазон ячеек) и метод Select, а вторая использует свойство FormulaR1C1 ячейки, которое возвращает или устанавливает формулу для объекта, используя нотацию (адресацию) стиля R1C1 на языке макро. Кроме того, редактор VBA вставил инструкцию Option Explicit, которая требует явное объявление переменных.
Для удобства проверки правильности функционирования макросов переименуем листы Excel следующим образом:
Лист1 
Шапка
Лист2 Формулы
Лист3 Форматирование
Лист4 ВводДанных
Выполнив макрос ЗаголовокШапка, убедимся, что он работает корректно только тогда, когда активной ячейкой на момент выполнения макроса является ячейка A1. Именно эта ячейка была активной в момент начала записи макроса. Если мы выполним макрос не сделав ячейку A1 активной, то "Поставка товаров" окажется в активной ячейке. Небольшое изменение в коде макроса ЗаголовокШапка позволит начинать выполнение макроса с ячейки A1 вне зависимости от того какая ячейка является активной на момент выполнения макроса. Вставим в начало макроса инструкцию Range("A1").Select, которая сделает ячейку A1 активной.
Sub ЗаголовокШапка()
'
' ЗаголовокШапка Макрос
' Макрос записан 09.10.2015 (Neklyudova)
Range("A1").Select
ActiveCell.FormulaR1C1 = "Поставка товаров"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Код товара"
Range("B2").Select
ActiveCell.FormulaR1C1 = "Наименование товара"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Единица измерения"
Range("D2").Select
ActiveCell.FormulaR1C1 = "Цена"
Range("E2").Select
ActiveCell.FormulaR1C1 = "Количество"
Range("F2").Select
ActiveCell.FormulaR1C1 = "Сумма"
Range("F3").Select
End Sub
 |
Чтобы лучше понять код макроса ЗаголовокШапка и других макросов, создаваемых в лабораторной работе, необходимо ознакомиться с объектной моделью Excel, свойствами и методами объекта Range. В иерархии объектов Excel объект Range идет сразу после объекта Worksheet (Рабочий лист). Объект Range является одним из ключевых объектов VBA.
Задание групп строк и столбцов с помощью объекта Range
Если в диапазоне указываются только имена столбцов или строк, то объект Range задает диапазон, состоящий из указанных столбцов или строк. Например, Range ("А:C") задает диапазон, состоящий из столбцов А, B и C, а Range ("2: 2") – из второй строки.
Другим способом работы со строками и столбцами являются методы Rows (строки) и Columns (столбцы), возвращающие коллекции строк и столбцов. Например, столбцом А является Columns(1), а второй строкой – Rows(2).
Далее приводятся наиболее часто используемые свойства и методы объекта Range.
Основные свойства объекта Range:
| Свойство | Описание |
| Value | Возвращает значение из ячейки или в ячейки диапазона. Примеры переменной х присваивается значение из ячейки C1: х = Range("C1").Value в диапазон AI : В2 введена 1: Range("A1:B2").Value = 1 |
| Name | Возвращает имя диапазона. В данном примере диапазону А1:В2 присваивается имя итоги: Range("Al:B2").Name ="Итоги" |
| Count | Возвращает число объектов в наборе. В данном примере переменной х присваивается значение, равное числу строк диапазона AI : В2: х = Range("Al:B2").Rows.Count |
| CurrentRegion | Возвращает число строк текущего диапазона. Текущим является диапазон, ограниченный пустыми строками и столбцами и содержащий данный элемент. В данном примере переменной у присваивается значение, равное числу строк в текущем диапазоне, содержащем ячейку A1: у = Range("Al").CurrentRegion.Rows.Count |
| WrapText | Позволяет переносить текст при вводе в диапазон. Допустимые значения True и False. В следующем примере в ячейку В2 вводится текст "Длинный текст" и в этой ячейке устанавливается режим ввода текста с переносом (действие, аналогичное установке флажка Переносить по словам на вкладке Выравнивание окна Формат ячейки): With Range ("B2") .Value = "Длинный текст" .WrapText = True End With |
| EntireColumn, EntireRow | Возвращает столбец и строку соответственно. В данном примере очищается содержимое строки и выделяется столбец с активной ячейкой: ActiveCell.EntireRow.Clear ActiveCell.EntireColumn.Select |
| ColumnWidth, RowHeight | Возвращает ширину столбцов и высоту строк диапазона соответственно. |
| Comment | Возвращает объект Comment (примечание), который связан с левым верхним углом диапазона при отображении на экране. Объект Comment является элементом семейства Comments. Метод AddComment, примененный к диапазону, создает новое примечание. Среди методов объекта comment отметим только метод Text, который задает текст, выводимый в примечании. Синтаксис: Text (Text, Start, Overwrite) Аргументы: • Text — строка, выводимая в качестве примечания; • Start — с какого символа вводится текст в уже существующее примечание. Если аргумент опущен, то из примечания удаляется весь ранее введенный текст; • Overwrite — допустимые значения: True (вводимый текст записывается поверх уже существующего) и False (вводимый текст вставляется в уже существующий) Среди свойств объекта Comment отметим только свойство visible, устанавливающее отображение примечания при активизации диапазона, имеющего определенное примечание. В качестве примера рассмотрим следующие инструкции, которые создают и отображают примечание ячейки B3, поясняющее запланированное событие (рис. 3.1): With Range("ВЗ") .AddComment .Text Text:= "Чрезвычайно важно!" & Chr(10)&"Про это никак нельзя забыть!" .Visible = True End With |
| Font | Возвращает объект Font (шрифт). Объект Font имеет следующие свойства: • Name — строка, указывающая имя шрифта, например "Arial Cyr" • FontStyle — СТИЛЬ, возможен Regular (обычный), Bold (ПОЛУЖИРНЫЙ), Italic(курсив), Bold italic (полужирный курсив) • Size — размер • Strikethrough — допустимы два значения: True (буквы имеют линию по центру, как будто они перечеркнуты) и False (не имеют линии по центру) • Superscript — допустимы два значения: True (текст используется как верхний индекс) и False (не используется как верхний индекс) • Subscript — допустимы два значения: True (текст используется как нижний индекс) и False (не используется как нижний индекс) • Underline — допустимыми являются значения: - xlNone (нет подчеркивания) - xlSingie (одинарное, по значению) - xlDoubie (двойное, по значению) - xlSingleAccounting (одинарное, по ячейке) - Accounting (двойное, по ячейке) В следующем примере устанавливается для диапазона A1 : B2 полужирный шрифт, красного цвета и с высотой символов 14: With Range ("A1:B2").Font .Size = 14 .FontStyle = Bold .Colorlndex = 3 End With |
| Formula | Возвращает формулу в формате Al. Например, следующая инструкция вводит в ячейку C2 формулу =$А$4+$А$10: Range("C2").Formula = "=$А$4+$А$10" |
| FormulaArray | Возвращает формулу диапазона в формате А1. В отличие от обыкновенной формулы рабочего листа, формула диапазона вводится на рабочем листе не посредством нажатия на клавишу <Enter>, а с помощью комбинации клавиш <Ctrl>+<Shift>+<Enter>. Далее инструкция вводит в диапазон Е1:ЕЗ формулу “=Sum(Al:A3*Bl:B3)”: Range( "El:E3").FormulaArray = "=Sum(Al:A3*Bl:B3)" |
| FormulaHidden | Допустимые значения: True (формула спрятана, если рабочий лист или книга защищены) и False (в противном случае). Например, следующая инструкция скрывает формулы в столбце А: Columns "A").FormulaHidden = True |
| FormulaLocal | Возвращает неанглоязычные (местные) формулы в формате А1. Например, следующая инструкция вводит в ячейку В2 формулу =СУММ(С1:С4): Range("B2").FormulaLocal = "=СУММ(С1:С4)" |
| FormulaRlCl | Возвращает формулу в формате R1C1. Например, Range("Bl"). FormulaRlCl = "=SQRT(R3C2 )" |
| FormulaRlClLocal | Возвращает неанглоязычные формулы в формате R1C1 |
| Text | Возвращает содержание диапазона в текстовом формате |
| HorizontalAlignment | Горизонтальное выравнивание. Допустимые значения: • xlceneral (обычное выравнивание, зависящее от типа вводимых значений) • xlcenter (выравнивание по центру) • xlRight (выравнивание по правому краю) • xlLeft (выравнивание по левому краю) • xUustify (выравнивание по ширине) • xlCenterAcrossSelection (выравнивание по центру в выделенном диапазоне) • xlFill (выравнивание по ширине |
| Vertical Alignment | Вертикальное выравнивание. Допустимые значения: • xlBottom (выравнивание по нижнему краю), • xlcenter (выравнивание по центру), • xUustify (выравнивание по высоте), • xlTop (выравнивание по верхнему краю) |
| Orientation | Ориентация. Допускается либо угол поворота текста в градусах от —90° до 90°, либо одно из допустимых значений: • xlDownward (выравнивание по левому краю сверху вниз, соответствует углу -90°) • xlHorizontal(выравнивание по горизонтали, соответствует нулевому углу ) • xlupward (выравнивание по правому краю снизу вверх, соответствует углу 90°) • xlvertical (выравнивание по вертикали, нет соответствия в градусах) |
| ShrinkToFit | Допустимые значения: True (автоматическое изменение шрифта так, чтобы текст помещался в ячейку) и False (в противном случае) |
Наиболее часто используемые методы объекта Range:
| Метод | Описание |
| Address | Возвращает адрес ячейки. Синтаксис: Address(rowAbsolute, coluimAbsolute, referenceStyle, external, relativeTo) Аргументы: • rowAbsoiute — допустимы два значения True и False, если используется значение True или аргумент опущен, то возвращается абсолютная ссылка на строку • columnAbsoiute — допустимы два значения True и False, если используется значение True или аргумент опущен, то возвращается абсолютная ссылка на столбец • referenceStyle — допустимы два значения xlAl и x1R1C1, если используется значение x1A1 или аргумент опущен, то возвращается ссылка в виде формата А1, при использовании x1R1C1 – в виде формата R1C1 • external — допустимы два значения True и False, если используется значение False или аргумент опущен, то возвращается относительная ссылка • relativeTo — В случае, если rowAbsoiute и columnAbsoiute равны False, a referenceStyle x1R1C1, то данный аргумент определяет начальную ячейку диапазона, относительно которой производится адресация. Следующие примеры показывает различные результаты адресации. В диалоговом окне отображается адрес $А$1: MsgBox Cells(1, 1).Address() В диалоговом окне отображается адрес $А1: MsgBox Cells(1,1).Address(rowAbsolute:=False) В диалоговом окне отображается адрес R1C1 MsgBox Cells(1,1).Address(ReferenceStyle:=x1R1C1) |
| Clear, ClearComments, ClearContents, ClearFormats, ClearNotes | Метод clear очищает диапазон. В следующем примере очищается диапазон Al : G37: Range("A1:G37").Clear Методы ClearComments, ClearContents, ClearFormats и ClearNotes очищают в указанном диапазоне комментарии, содержание, форматы и примечания соответственно. |
| AutoFit | Автоматически настраивает ширину столбца и высоту строки |
| Copy | Копирует диапазон в другой диапазон или в буфер обмена. Синтаксис: Copy(destination) Аргумент destination определяет диапазон, куда копируется данный диапазон. Если аргумент destination опушен, то копирование происходит в буфер обмена. В данном примере диапазон A1 : D4 рабочего листа Лист1 копируется в диапазон Е5 : Н8 листа лист2: Worksheets("Лист1").Range("А1:D4").Сору destination:=Worksheets "Лист2").Range "E5:H8") |
| Cut | Копирует диапазон с удалением в указанный диапазон или в буфер обмена, Синтаксис: Cut(destination) Аргумент destination определяет диапазон, в который копируется данный диапазон. Если аргумент destination опущен, то диапазол копируется в буфер обмена. В данном примере диапазон A1 : D4 рабочего листа Лист1 копируется с удалением в буфер обмена: Worksheets("Лист1").Range("А1:D4").Cut |
| Delete | Удаляет диапазон. В данном примере удаляется третья строка активной рабочей страницы: Rows(3).Delete |
| Columns, Rows | Возвращают соответственно семейства столбцов и строк, из которых состоит диапазон. В следующем примере переменным i и j присваиваются значения, равные количеству столбцов и строк в выделенном диапазоне соответственно: i = Selection.Columns.Count j = Selection.Rows.Count |
| Insert | Вставка ячейки или диапазона ячеек. В следующем примере вставляется новая строка перед четвертой строкой рабочего листа Лист1: Worksheets("Лист1").Rows(4).Insert |
| Offset | Возвращает диапазон, смещенный относительно данного на величины, специфицированные в аргументах. Синтаксис: Offset (rowOffset, columnOffset ) Аргументы: • rowOffset — целое число, указывающее сдвиг по строкам • columnOffset — целое число, указывающее сдвиг по столбцам. Например, в следующем примере активизируется ячейка, расположенная на три строки ниже и на два столбца левее относительно предыдущей активной ячейки: ActiveCell.Offset(rowOffset:=3, columnOffset:=-2).Activate |
| Select | Выделение диапазона |
| PasteSpecial | Специальная вставка из буфера обмена.
Синтаксис:
BasteSpecial (paste, operation, skipBlanks, transpose)
Аргументы:
• paste — определяет ту часть содержимого буфера обмена, которая должна быть вставлена в диапазон.
Допустимые значения:
- xlAll (все)
- xl Formulas (формулы)
- xlvaiues (значения)
- xlFormats (форматы)
- xlNotes (примечания)
- xlAllExceptBorders (без рамки)
• operation — определяет операции.
Допустимые значения:
- xlNone (нет)
- xlAdd (сложить)
- xlSubtract (вычисть)
- xlMultiply (умножить)
- xlDivide (разделить)
• wskipBlanks — допустимые значения: True (пустые ячейки при вставке не учитываются) и False (пустые ячейки учитываются)
• transpose — допустимые значения True (диапазон выводится транспонированным) и False (не транспонированным)
В приведенном ниже примере данные из диапазона C1:C5 рабочего листа Лист1 вставляются в диапазон D1 : D5 того же листа, причем они не заменяют уже существующие данные в диапазоне D1 : D5, а прибавляются к ним данные из диапазона C1 : С5:
Worksheets("Лист1").Range("С1:С5").Сору Worksheets("Лист1").Range("D1:D5").PasteSpecial operation : =xlAdd
Метод PasteSpecial программирует выполнение на рабочем листе команды Правка, Специальная вставка (Edit, Paste Special). Аргументы метода PasteSpecial соответствуют установкам диалогового окна Специальная вставка (Paste Special), отображаемого с помощью этой команды.
|
| AddComment | Добавляет примечание к диапазону. Синтаксис: AddComment (Text) Text — строковое выражение добавляемое в качестве примечания. В следующем примере создается примечание "Внимание!" ячейки A1 активного рабочего листа: Range("А!").AddComment "Внимание!" |
Часто программы используют объект Selection (выбор).
Объект Selection возникает в VBA двояко — либо как результат работы метода Select, либо при вызове свойства Selection. Тип получаемого объекта зависит от типа выделенного объекта. Чаще всего объект Selection принадлежит классу Range и при работе с ним можно использовать свойства и методы объекта Range. Интересной особенностью объектов Range и Selection является то, что они не являются элементами никакого семейства объектов.
Оператор With . . . End With
В VBA доступ к свойствам и методам объекта осуществляется через точку. Например, Range("A2").Select, ActiveCell.FormulaR1C1 = "Код товара", ActiveSheet.Range(“A1”). Если нужно обратиться к нескольким свойствам одного объекта, то придется каждый раз писать имя этого объекта. Но есть способ этого избежать. Оператор With . . . End With позволяет использовать упрощенный синтаксис доступа к членам объекта. С помощью With...End With можно выполнять последовательность операторов с указанным объектом без необходимости многократного указания имени объекта.
Синтаксис оператора:
With objectExpression
[ statements ]
End With
objectExpression — выражение, результатом которого является объект. Это объект или члены объекта, с которыми производится работа. Указывается обязательно.
statements — операции с объектом. Это дин или несколько операторов между With и End With, которые могут ссылаться на члены объекта, создаваемого при вычислении выражения objectExpression. Присутствуют не обязательно.
В блоке операторов statements члены объекта можно указывать начиная с точки, как если бы перед ней стоял объект objectExpression.
Примеры использования данного оператора показаны в таблице описания основных свойств объекта Range: Font, Comment, WrapText.
Адресация ячеек
При работе с объектом Range необходимо помнить, как в Excel ссылаются на ячейку рабочего листа. Имеются два способа ссылки на ячейки рабочего листа: относительная адресация (т. е. когда начало координат, задающее нумерацию строк и столбцов, связывается с объектом, вызвавшим Range) и абсолютная адресация. И относительная и абсолютная адресация может быть использована в двух форматах (стилях): А1(классический вид) и R1C1.
Относительная адресация:
• Формат А1
Имя ячейки состоит из имени столбца (их 256 — А, В, ..., Z, АВ, ..., HZ, IA, ..., IV) и номера строки (1, ...., 16384).
Например, A1, C2.
• Формат R1C1
Адресация задается индексом строки и индексом столбца.
Например, R1C1, R2C3 .
Абсолютная адресация:
• Формат А1
Признаком абсолютной адресации является знак "$", предшествующий имени строки (абсолютной адресации на строку) или столбца (абсолютной адресации на столбец). Например, $А10, А$10 и $А$10 задают абсолютную адресацию на столбец А, строку 10 и ячейку А10 соответственно.
• Формат R1C1
Указывается смещение по отношению к активной ячейке. Смещение приводится в квадратных скобках, причем знак указывает на направление смещения. В общем случае направления вниз и вправо считаются положительными, вверх и влево — отрицательными. Например, если активной ячейкой является R2C3, то R[1]C[-1] дает ссылку на ячейку R3C2.
Включить стиль ссылок R1C1 можно следующим образом.
В Office 2003 включив в настройках «Сервис» —> «Параметры» —> закладка «Общие» —> флажок «Стиль ссылок R1C1»
В Office 2007 надо нажать на кнопку «Office» 
, после чего выбрать «Параметры Excel» —> закладка «Формулы» —> «Стиль ссылок R1C1»
В Office 2010/2013
Если включен классический вид и в ячейке с адресом A1 находится формула «=B3» (B3 — относительная ссылка), то после переключения в вид R1C1 она примет вид «=R[2]C[1]». В режиме R1C1 в квадратных скобках указывается относительное смещение. В скобках после R указано смещение в строках (row) — две строки вниз (вниз, потому что число положительное, если бы стояло «R[-2]» — было бы вверх). В скобках после C указано смещение в столбцах — 1 столбец вправо (если бы было C[-1] — влево). В общем случае направления вниз и вправо считаются положительными, вверх и влево — отрицательными.
Если включен классический вид и в ячейке с адресом A1 находится формула «=$B$3» ($B$3 — абсолютная ссылка), то после переключения в вид R1C1 она примет вид «=R3C2». Квадратных скобок нет, то есть указано не относительное смещение по отношению к положению формулы, а абсолютное смещение по отношению к всему листу (вспомните абсолютную и относительную системы координат).
Адресация ячейки рабочего листа является лишь частью полного адреса ячейки, который в общем случае включает имя рабочего листа и адрес книги. При задании полного адреса за именем листа следует знак "!", а адрес книги заключается в скобки.
Например,
А1
Лист2!А1
[МояКнига.хls]Лист2!А1
В первой строке данного примера дана относительная ссылка на ячейку A1 активного рабочего листа, во второй – на ячейку A1 рабочего листа Лист2 активной книги, а в третьей на ячейку A1 рабочего листа Лист2 книги МояКнига.xls текущего рабочего каталога.
Ввод расчётных формул
Для большей наглядности перед записью макроса Формулы целесообразно выполнить уже созданный макрос ЗаголовокШапка и только тогда выполнить следующие действия:
- начать запись макроса и ввести в диалоговом окне рис. 9 имя макроса Формулы;
- Ввести в ячейку F3 формулу =D3*E3 и протянуть курсор автозаполнения с ячейки F3по ячейку F10.
- Ввести в ячейку E11 текст “Итого:”.
- Ввести в ячейку F11 формулу =СУММ(F3:F10).
Ниже приведён текст макроса Формулы, сгенерированного Excel, осуществляющего расчёты по приведённым выше формулам.
Sub Формулы()
' Формулы Макрос
' Макрос записан 09.10.2015 (Neklyudova)
'
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F10"), Type:=xlFillDefault
Range("F3:F10").Select
Range("E11").Select
ActiveCell.FormulaR1C1 = "Итого:"
Range("F11").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
Range("F12").Select
Range("F12").Select
End Sub
Форматирование таблицы
1. В блоке ячеек A2 : F2
- На вкладке выравнивание окна формат ячеек устанавливаем перенос текста по словам;
- значения данных выравниваем по центру по горизонтали и по верхнему краю по вертикали;
- устанавливаем шрифт “полужирный”,
2. Устанавливаем в столбцах A, B, C, D, E, F такую ширину столбцов, чтобы шапка таблицы была такой же, как показано на образце (рис. 8).
3. Выделяем блок ячеек A1:F1 и объединяем их, располагая информацию в центре. Устанавливаем в объединенной ячейке шрифт Arial Cyr, размер кегля 14, жирный, горизонтальное выравнивание – по центру, вертикальное – по нижнему краю.
4. Внутренние и внешние границы ячеек разделяем тонкими линиями, текст с данными таблицы сверху и снизу отделяем двойными тонкими линиями, объединяем ячейки A11:E11, информацию в объединенной ячейке выравниваем по правому краю.
5. Выделяем диапазоны ячеек A3:A10 и E3:E10 устанавливаем для них формат ячеек Числовой с количеством десятичных знаков, равным 0.
6. Выделяем диапазоны ячеек D3:D10 и F3:F11, и устанавливаем для них формат ячеек Денежный с количеством десятичных знаков, равным 2 и с обозначением денежных единиц в рублях.
7. Выделяем диапазон ячеек A3:C10 и устанавливаем для него выравнивание по горизонтали по левому краю.
8. В диапазоне ячеек D3:D10 устанавливаем горизонтальное выравнивание по центру.
9. В диапазоне ячеек E3:F10 станавливаем горизонтальное выравнивание по правому краю.
10. Слово «Итого:» объединенной ячейки и общую сумму поставок товаров выделяем жирным шрифтом и курсивом.
В результате получим макрос:
Sub Форматирование()
'
' Форматирование Макрос
' Макрос записан 09.10.2015 (Neklyudova)
'
Range("A2:F2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
Columns("A:A").ColumnWidth = 11
Columns("A:A").ColumnWidth = 8.71
Columns("B:B").ColumnWidth = 13
Columns("B:B").ColumnWidth = 14.29
Columns("B:B").ColumnWidth = 15.43
Columns("C:C").ColumnWidth = 12.14
Columns("D:D").ColumnWidth = 11.14
Columns("E:E").ColumnWidth = 11.71
Columns("E:E").ColumnWidth = 11.86
Columns("F:F").ColumnWidth = 15.57
Range("A1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Bold = True
Range("A2:F11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("A11:E11").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A3:F3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A10:F10").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("D3:D10,F3:F11").Select
Range("F3").Activate
Selection.NumberFormat = "#,##0.00$"
Range("A3:C10").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("D3:D10").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E3:F10").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A11:F11").Select
Selection.Font.Bold = True
Selection.Font.Italic = True
End Sub
Ввод данных
Ниже приведена таблица с исходными данными (Код товара, Наименование товара, Единица измерения, Цена, Количество) и результатными данными, полученными при выполнении макросов ЗаголовокШапка, Формулы, Форматирование и ВводДанных.
| Поставка товаров | |||||
| Код товара | Наименование товара | Единица измерения | Цена | Количество | Сумма |
| Товар1 | шт | 100,00р. | 50 000,00р. | ||
| Товар2 | шт | 200,00р. | 1 000 000,00р. | ||
| Товар3 | шт | 300,00р. | 15 000 000,00р. | ||
| 0,00р. | |||||
| 0,00р. | |||||
| 0,00р. | |||||
| 0,00р. | |||||
| 0,00р. | |||||
| Итого: | 16 050000,00р. |
Рис.19. Результат выполнения макросов
В результате записи макроса получим следующий программный код:
Sub ВводДанных()
'
' ВводДанных Макрос
' Макрос записан 09.10.2054 (Neklyudova)
'
Range("A3").Select
ActiveCell.FormulaR1C1 = "101"
Range("B3").Select
ActiveCell.FormulaR1C1 = "Товар1"
Range("C3").Select
ActiveCell.FormulaR1C1 = "шт."
Range("D3").Select
ActiveCell.FormulaR1C1 = "100"
Range("E3").Select
ActiveCell.FormulaR1C1 = "500"
Range("A4").Select
ActiveCell.FormulaR1C1 = "102"
Range("B4").Select
ActiveCell.FormulaR1C1 = "Товар2"
Range("C4").Select
ActiveCell.FormulaR1C1 = "шт."
Range("D4").Select
ActiveCell.FormulaR1C1 = "200"
Range("E4").Select
ActiveCell.FormulaR1C1 = "5000"
Range("A5").Select
ActiveCell.FormulaR1C1 = "103"
Range("B5").Select
ActiveCell.FormulaR1C1 = "Товар3"
Range("C5").Select
ActiveCell.FormulaR1C1 = "шт."
Range("D5").Select
ActiveCell.FormulaR1C1 = "300"
Range("E5").Select
ActiveCell.FormulaR1C1 = "50000"
Range("A6").Select
End Sub
Итак, в результате получен программный код, содержащий макросы, которые были получены при выполнении пунктов:
- Ввод заголовка и “шапки” таблицы
- Ввод расчётных формул
- Форматирование таблицы
- Ввод данных.
Option Explicit
| |
Sub ЗаголовокШапка()
'
' ЗаголовокШапка Макрос
' Макрос записан 09.01.2015 (Neklyudova)
'
ActiveCell.FormulaR1C1 = "Поставка товаров"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Код товара"
Range("B2").Select
ActiveCell.FormulaR1C1 = "Наименование товара"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Единица измерения"
Range("D2").Select
ActiveCell.FormulaR1C1 = "Цена"
Range("E2").Select
ActiveCell.FormulaR1C1 = "Количество"
Range("F2").Select
ActiveCell.FormulaR1C1 = "Сумма"
Range("F3").Select
End Sub
| |
Sub Формулы()
' Формулы Макрос
' Макрос записан 09.10.2015 (Neklyudova)
'
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F10"), Type:=xlFillDefault
Range("F3:F10").Select
Range("E11").Select
ActiveCell.FormulaR1C1 = "Итого:"
Range("F11").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
Range("F12").Select
Range("F12").Select
End Sub
 |
Sub Форматирование()
'
' Форматирование Макрос
' Макрос записан 09.10.2015 (Neklyudova)
'
Range("A2:F2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
Columns("A:A").ColumnWidth = 11
Columns("A:A").ColumnWidth = 8.71
Columns("B:B").ColumnWidth = 13
Columns("B:B").ColumnWidth = 14.29
Columns("B:B").ColumnWidth = 15.43
Columns("C:C").ColumnWidth = 12.14
Columns("D:D").ColumnWidth = 11.14
Columns("E:E").ColumnWidth = 11.71
Columns("E:E").ColumnWidth = 11.86
Columns("F:F").ColumnWidth = 15.57
Range("A1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Bold = True
Range("A2:F11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("A11:E11").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A3:F3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A10:F10").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("D3:D10,F3:F11").Select
Range("F3").Activate
Selection.NumberFormat = "#,##0.00$"
Range("A3:C10").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("D3:D10").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E3:F10").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A11:F11").Select
Selection.Font.Bold = True
Selection.Font.Italic = True
End Sub
| |
Sub ВводДанных()
'
' ВводДанных Макрос
' Макрос записан 09.10.2015 (Neklyudova)
'
Range("A3").Select
ActiveCell.FormulaR1C1 = "101"
Range("B3").Select
ActiveCell.FormulaR1C1 = "Товар1"
Range("C3").Select
ActiveCell.FormulaR1C1 = "шт."
Range("D3").Select
ActiveCell.FormulaR1C1 = "100"
Range("E3").Select
ActiveCell.FormulaR1C1 = "500"
Range("A4").Select
ActiveCell.FormulaR1C1 = "102"
Range("B4").Select
ActiveCell.FormulaR1C1 = "Товар2"
Range("C4").Select
ActiveCell.FormulaR1C1 = "шт."
Range("D4").Select
ActiveCell.FormulaR1C1 = "200"
Range("E4").Select
ActiveCell.FormulaR1C1 = "5000"
Range("A5").Select
ActiveCell.FormulaR1C1 = "103"
Range("B5").Select
ActiveCell.FormulaR1C1 = "Товар3"
Range("C5").Select
ActiveCell.FormulaR1C1 = "шт."
Range("D5").Select
ActiveCell.FormulaR1C1 = "300"
Range("E5").Select
ActiveCell.FormulaR1C1 = "50000"
Range("A6").Select
End Sub
Сохраните книгу, содержащую эти макросы под именем Запись_макроса_исходная.
Макросы можно корректировать в редакторе кода VBE. Например, макрос ЗаголовокШапка можно упростить следующим образом:
- удалить инструкции активизации ячеек;
- удалить строки кода, определяющие свойства шрифта надписей;
- использовать инструкцию присваивания в стиле Range.
В результате произведенных изменений получим следующий код:
Option Explicit
Sub ЗаголовокШапка()
'
' ЗаголовокШапка Макрос
' Макрос записан 09.10.2015 (Neklyudova)
'
Range("A1") = "Поставка товаров"
Range("A2") = "Код товара"
Range("B2") = "Наименование товара"
Range("C2") = "Единица измерения"
Range("D2") = "Цена"
Range("E2") = "Количество"
Range("F2") = "Сумма"
End Sub
Макрос Формулы можно откорректировать так:
Sub Формулы()
'
' Формулы Макрос
' Макрос записан 09.10.2015 (Neklyudova)
'
Range("F3").Select
ActiveCell = "=D3*E3"
Selection.AutoFill Destination:=Range("F3:F10"), Type:=xlFillDefault
Range("E11") = "Итого:"
Range("F11") = "=SUM(F3:F10)"
End Sub
Примечание
Если нет опасности перегрузить оперативную память компьютера или пользователь не обладает достаточной квалификацией в области программирования на VBA, то лучше оставить макрос таким, как его спроектировал Excel.
Пока у вас нет достаточных знаний для полного понимания кода откорректируем только один макрос ЗаголовокШапка.
Создайте копию книги Запись_макроса_исходная под именем Запись_макроса_отредактированная. При этом в новую книгу будут скопированы все макросы из исходной книги.Измените макрос ЗаголовокШапка как показано выше.
Чтобы удостовериться в том, что при выполнении изменённого макроса получился тот же самый результат, очистить заполненные ячейки, выполнить записанный макрос и сравнить результат с рис.11.
Процедуры не могут в своём составе содержать вложенные процедуры, но они могут вызывать друг друга. Одним из способов вызова процедуры является использование оператора Call. Добавив в конец макроса ЗаголовокШапка три оператора
Call Формулы
Call Форматирование
Call ВводДанных
макросы Формулы, Форматирование и ВводДанных будут выполняться при вызове на выполнение макроса ЗаголовокШапка, в результате чего на рабочий лист будут выведены название таблицы и заголовки столбцов и выполнены инструкции макросов Формулы, Форматирование и ВводДанных, т.е. получена таблица, представленная на рис. 8.
Создайте копию книги Запись_макроса_отредактированная под именем Запись_макроса_шаблон. Удалите листы: Формулы, Форматирование и ВводДанных. Переименуйте лист Шапка в Поставка товаров. Измените макрос ЗаголовокШапка, сохраните книгу с поддержкой макросов.
Итак, у вас имеются три рабочих книги:
Запись_макроса_исходная
Запись_макроса_отредактированная
Запись_макроса_шаблон
В рабочей книге Запись_макроса_исходная хранятся макросы, полученные в результате записи в кодах VBA действий пользователя: ЗаголовокШапка, Формулы, Форматирование и ВводДанных. На рабочем листе Шапка приведён результат выполнения макроса Шапка, на листе Формулы – последовательности макросов Шапка ® Формулы, на листе Форматирование – последовательности макросов Шапка ® Формулы ® Форматирование и на листе ВводДанных – макросов Шапка ®Формулы ® Форматирование ® ВводДанных.
Конечно, все действия пользователя, включая ввод исходных данных, можно записать в один макрос, но это неудобно при отладке макросов.
В рабочей книге Запись_макроса_отредактированная макрос ЗаголовокШапка отредактирован c целью его упрощения. В обеих этих книгах для наглядности рабочие листы: Лист1, Лист2, Лист3, Лист4 – переименованы соответственно в Шапка, Формулы, Форматирование и ВводДанных.
В рабочей книге Запись_макроса_Шаблон содержится один рабочий лист Поставка товаров, и в конец макроса ЗаголовокШапка добавлены инструкции вызова макросов Формулы, Форматирование и ВводДанных:
В данной лабораторной работе макрос ВводДанных приведён в качестве иллюстрации возможности записи в макрос исходных данных. На практике это может быть использовано при вводе данных в таблицы, в которых меняется небольшая часть их значений.