Подготовка к записи и запись макроса

Перед запуском макрорекордера следует подготовить рабочий лист, произведя все действия, которые не нужно включать в процедуру. Это такие действия, как создание нового рабочего листа или перемещение в конкретную часть листа. Когда вы начнёте запись, все, что вы сделаете в приложенииMicrosoftExcel, будет записано в процедуре.

1. ЗапуститеприложениеMicrosoftExcel и выберите командуСохранитьиз панели быстрого доступа или из меню кнопки Office.

2. Для того чтобы новую книгу можно было сохранить вместе макросами, необходимо изменить расширение файла на xlsm – книга Excelс поддержкой макросов. В диалоговом окне Сохранение документав поле Тип файла выберите нужный тип и сохраните файл Книга1в своей рабочей папке под именем Макрос.xlsm.

3. Переименуйте Лист 1 в Макрос.

4. Нажмите кнопку Запись макроса, которая находится в группе Код на вкладке Разработчик. Появится диалоговое окно Запись макроса (см. рис. 1), в котором можно установить параметры записываемой процедуры. Если вкладка Разработчик отсутствует на ленте, необходимо открыть диалог задания параметров приложенияMicrosoftExcel и в разделе Основныеустановить флажокПоказывать вкладку «Разработчик» на ленте.

5. В поле Имя макроса введите Расчет_стоимости. Обратите внимание на то, что имя макроса может содержать только буквы, цифры и знаки подчёркивания.

6. В поле Описание введите текст, поясняющий назначение макроса: Вычисление стоимости с учётом скидки.

7. Поле Сочетание клавиш предназначено для записи клавиши, нажатие на которую вмести с клавишей Ctrl, приведёт к запуску макроса. Не заполняйте пока это поле.

8. Нажмите кнопку ОК чтобы начать запись макроса. Кнопка Запись макроса в группе Код на вкладке Разработчик заменится на кнопкуОстановить запись, с помощью которой можно будет остановить запись после выполнения всех необходимых действий. Все действия, которые вы произведёте до нажатия этой кнопки, в том числе повторяющиеся и отмены действий, будут записаны в макрос!

9. Чтобы выключить отображение сетки на листе MicrosoftExcel, снимите флажок Сетка, который находится с группеПоказать или скрыть на вкладке Вид.

10. В ячейку B5 введите текст «Розничная цена:» и нажмите клавишу Enter.

11. В ячейку B7 введите текст «Размер скидки:» и нажмите клавишу Enter.

12. В ячейку B9 введите текст «Цена с учётом скидки:» и нажмите клавишу Enter.

Рис. 1. Диалоговое окно «Запись макроса»

13. Расширьте столбец Bтак, чтобы текст в ячейке B9 полностью отображался внутри столбца B. Для этого поместите указатель мыши на вертикальную черту между заголовками столбцов B и C и дважды щёлкните по ней или перетащите её.

14. Выделите диапазон B5:C9 и установите в нем выравнивание текста по правому краю.

15. В ячейкеC5установите денежный форматв рублях с двумя разрядами дробной части.

16. Перейдите на вкладку Главная и нажмите кнопку, которая находится в правом нижнем углу группы Число, для вызова полного диалога форматирования ячеек. В появившемся диалоге перейдите на вкладку Защита и снимите флажок Защищаемая ячейка.Это позволит изменять значение в ячейке C5 после установки защиты всего листа. Нажмите кнопку ОК.

17. В ячейке C7 установите процентный формат с двумя разрядами дробной части.

18. Задайте размер скидки, введя значение 5 в ячейку C7 и нажав клавишу Enter.

19. В ячейкеC9установите денежный форматв рублях с двумя разрядами дробной части.

20. Введите в эту ячейку формулу =(1-C7)*C5.

21. Для защиты листа от изменений нажмите кнопкуЗащитить лист, которая находится в группе Изменения на вкладке Рецензирование. Появится диалоговое окно, которое позволяет установить пароль для снятия защиты и разрешить или запретить некоторые действия с листом (см. рис. 2). Нажмите кнопку ОК.

22. Ваш рабочий лист теперь должен выглядеть так, как показано на рис. 3.Для остановки записи щёлкните на кнопкеОстановить запись в группе Код на вкладке Разработчик.

23. Проверьте правильность работы созданной вами таблицы. Введите в ячейку C5 значение 100 и нажмите клавишу Enter. В ячейке C5 должно отобразиться значение 100,00р., а в ячейке C7 – появиться результат 95,00р.

24. Теперь можно посмотреть процедуру, которая получилась в результате записи. Нажмите кнопку Макросы, которая находитсяв группе Код на вкладке Разработчик. Выделите имя вашего макроса в предлагаемом списке и нажмите кнопкуИзменить.

Рис. 2. Диалог установки защиты листа

Рис. 3. Вид рабочего листа перед окончанием записи макроса

25. Появится окно редактора VisualBasic с кодом процедуры (см. рис. 4). Ниже приведён текст записанной процедуры.

SubРасчет_стоимости()

' Расчет_стоимости Макрос

' Вычисление стоимости с учётом скидки

ActiveWindow.DisplayGridlines = False

Range("B5").Select

ActiveCell.FormulaR1C1 = "Розничнаяцена:"

Range("B7").Select

ActiveCell.FormulaR1C1 = "Размерскидки:"

Range("B9").Select

ActiveCell.FormulaR1C1 = "Ценасучётомскидки:"

Columns("B:B").EntireColumn.AutoFit

Range("B5:C9").Select

With Selection

   .HorizontalAlignment = xlRight

   .VerticalAlignment = xlBottom

   .WrapText = False

   .Orientation = 0

   .AddIndent = False

   .IndentLevel = 0

   .ShrinkToFit = False

   .ReadingOrder = xlContext

   .MergeCells = False

EndWith

Range("C5").Select

Selection.NumberFormat = _

"_-* #,##0.00[$р.-419]_-;-* #,##0.00[$р.-419]_-;_-* ""-""??[$р.-419]_-;_-@_-"

Selection.Locked = False

Selection.FormulaHidden = False

Range("C7").Select

Selection.Style = "Percent"

Selection.NumberFormat = "0.00%"

ActiveCell.FormulaR1C1 = "5%"

Range("C9").Select

Selection.NumberFormat = _

"_-* #,##0.00[$р.-419]_-;-* #,##0.00[$р.-419]_-;_-* ""-""??[$р.-419]_-;_-@_-"

ActiveCell.FormulaR1C1 = "=(1-R[-2]C)*R[-4]C"

Range("C10").Select

ActiveSheet.ProtectDrawingObjects:=True, Contents:=True, Scenarios:=True

EndSub

Рис. 4. Окно редактора VisualBasic с кодом записанного макроса

26. Сравните записанный код с выполненными при записи макроса действиями. Как можно заметить, каждому выполненному действию соответствует одна или более строк процедуры.

· Первое, что вы сделали, когда начали запись процедуры – отключили отображение сетки активного рабочего листа. Этому действию соответствует следующая строка процедуры.

ActiveWindow.DisplayGridlines = False

Эта инструкция VBA задает значение Falseсвойству DisplayGridlines(Отображать сетку) объекта ActiveWindow(Активное окно). Активное окно – это то окно, в котором находится фокус ввода. Необязательно помещать в код макроса имя рабочего листа, с которым он должен работать, если этот лист является активным. Благодаря этому макрос может применяться не только к одному рабочему листу, а к любому активному рабочему листу.

· Следующие строки программного кода выделяют ячейку B5 и помещают в неё текст «Розничная цена:». Затем выделяются ячейки B7 иB9 и в них помещается соответствующий текст.

Range("B5").Select

ActiveCell.FormulaR1C1 = "Розничнаяцена:"

Range("B7").Select

ActiveCell.FormulaR1C1 = "Размерскидки:"

Range("B9").Select

ActiveCell.FormulaR1C1 = "Цена с учётомскидки:"

В VBA отдельная ячейка рабочего листа не является объектом, а диапазон ячеек, например,Range("B2:D3"), является объектом даже тогда, когда он состоит из одной ячейки, например,Range("A1"). Метод Select(Выделить) активизирует ячейки, указанные как аргументы объекта Range. Ссылкой на активную ячейку является ActiveCell(Активная ячейка). Свойство FormulaR1C1ячейки предназначено для записи в ячейке формулы.

· Следующая далее строка программного кода процедуры выполняет выделение столбца B и изменяет его ширину.

Columns("B:B").EntireColumn.AutoFit

Один или более столбцов можно выделить с помощью метода Columns(Столбцы). Ссылка на столбцы является аргументом этого метода. Аналогичным образом можно выделить одну или несколько строк с помощью метода Rows(строки). EntireColumn– это свойство, которое используется для представления всего столбца.Метод AutoFit позволяет выполнить автоматическую подгонку ширины столбца. Можно также использовать свойство ColumnWidth(Ширина столбца), чтобы установить заданную ширину для выделенных столбцов.

· Затем мы установили выравнивание по правому краю для ячеек B5:C9. Это было сделано с помощью следующих строк программного кода.

Range("B5:C9").Select

With Selection

.HorizontalAlignment = xlRight

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

EndWith

Нас интересовало только выравнивание по правому краю, но макрорекордер записывает установку всех свойств. В принципе, лишние строки можно удалить.

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

Выполнение макроса

Записанный макрос можно выполнить различными способами: вызвать его через диалог Макросы, использовать сочетание клавиш, добавить кнопку на панель быстрого доступа, создать специальный объект на рабочем листе (например, кнопку).

Для начала вызовем записанный макросчерез диалог Макросы.

28. Создайте новый лист и назовите его Диалог.

29. Нажмите кнопку Макросы, которая находится в группе Код на вкладке Разработчик. Появится диалоговое окно Макрос, содержащее имена всех доступных в данный момент макросов. С его помощью можно выполнять, изменять и удалять макросы.

30. Выделите макросРасчет_стоимости и нажмите кнопкуВыполнить. Процедура будет выполнена. После этого текущий рабочий лист будет выглядеть так же, как и предыдущий.

31. Теперь назначим нашему макросу сочетание клавиш. Снова нажмите кнопку Макросы, которая находится в группе Код на вкладке Разработчик. В появившемся диалоговом окне Макрос выделите макрос Расчет_стоимости и нажмите кнопку Параметры…. Вы увидите ещё одно диалоговое окно, которое позволяет задать макросу сочетание клавиш и изменить описание макроса. В поле рядом с надписью Ctrl+ введите любой символ – соответствующая клавиша будет использоваться для вызова макроса при нажатой клавише Ctrl. Выбранное сочетание клавиш заменит все совпадающие стандартные сочетания клавиш приложения MicrosoftExcelна то время, пока книга, содержащая данный макрос, открыта. Поэтому желательно использовать символы, отличные от стандартных. Кроме того, можно использовать сочетание клавиш Ctrl + Shift + символ.

32. Создайте новый лист, назовите его Сочетание клавиш и нажмите выбранное сочетание клавиш. Макрос снова должен быть выполнен.

33. Если вы предполагаете часто использовать созданный макрос, можно добавить кнопку для его вызова на панель быстрого доступа. Нажмите кнопку Настройка панели быстрого доступа , которая находится справа от панели быстрого доступа. В появившемся меню выберите пункт Другие команды… В диалоговом окне Параметры Excel в разделе Настройкав выпадающем списке Выбрать команды из: выберите Макросы, в нижележащем списке выберите имя созданного макроса и нажмите кнопку Добавить. Нажмите кнопку ОК для завершения настройки панели быстрого доступа.

34. Создайте новый лист, назовите его Панель быстрого доступа и попробуйте использовать новую кнопку.

Редактирование макроса

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

35. Если вы попытаетесь снова запустить созданный макрос на рабочем листе, на котором макрос уже выполнялся, возникнет ошибка из-за установленной защиты листа. Чтобы избежать этого добавьте в начало процедуры команду ActiveSheet.Unprotect. Теперь макрос будет самостоятельно снимать защиту листа.

36. Предположим, что вы хотите не использовать фиксированный размер скидки, а задавать его при выполнении макроса. В коде процедуры Расчет_стоимости замените инструкцию ActiveCell.FormulaR1C1 = "5%" на инструкцию ActiveCell.FormulaR1C1 = InputBox("Введите процент скидки"). Сохраните процедуру.

37. Создайте новый лист и назовите его Ввод скидки. Выполните макрос для проверки внесённых изменений. В появившемся диалоговом окне введите значение скидки в виде десятичной дроби 0,3 (соответствует 30%) и нажмите кнопкуОК. Если вы не сделали ошибки, то новый лист будет заполнен, а в ячейке C7 появится значение скидки 30,00%.

Предположим, что размер скидки ограничен, например, он не должен превышать 50%. Внесём в процедуру такие изменения, чтобы выполнялась проверка размера скидки. Если введённая скидка больше 50%, то ячейка должна быть очищена, формирование рабочего листа приостановлено, подан сигнал, а также выведено соответствующее предупреждение. Если же размер скидки в норме, то должно быть завершено формирование рабочего листа и выведено подтверждающее сообщение.

38. В коде процедуры Расчет_стоимостизамените инструкцию ActiveSheet.ProtectDrawingObjects:=True, Contents:=True, Scenarios:=Trueнаследующийнаборинструкций:

Range("C7").Select

IfVal(ActiveCell.FormulaR1C1) > 0.5 Then

MsgBox "Величина скидки не должна превышать 50%"

ActiveCell.Clear

Beep

Else

ActiveSheet.ProtectDrawingObjects:=True, Contents:=True, Scenarios:=True

MsgBox "Формирование листа закончено"

EndIf

39. Обратите внимание на применение стандартной функции Val(Аргумент строчного типа), которая выполняет преобразование аргумента типа строки в число типа Double. VBA, в отличие от VisualBasic, сам не выполняет преобразование в выражениях операнда строчного типа в число, а операнда числового типа в строку. За соответствием типов операндов выражения обязан следить программист. При необходимости преобразовать число в строку используется стандартная функция Str(Аргумент любого числового типа).

40. Сохраните процедуру.

41. Создайте новый лист и назовите его Проверка скидки. Выполните макрос для проверки внесённых изменений.

42. Задайте значение скидки равным 0,6 (соответствует 60%) и нажмите кнопку ОК. Если всё было сделано правильно, должно появиться сообщение об ошибке и ячейка со скидкой должна быть очищена.

43. Попробуйте задать процент скидки равным 40%.