Работа с ячейками, диапазонами и листами
Подготовка к записи и запись макроса
Перед запуском макрорекордера следует подготовить рабочий лист, произведя все действия, которые не нужно включать в процедуру. Это такие действия, как создание нового рабочего листа или перемещение в конкретную часть листа. Когда вы начнёте запись, все, что вы сделаете в приложении Microsoft Excel, будет записано в процедуре.
1. Запустите приложение Microsoft Excel и выберите команду Сохранить из панели быстрого доступа или из меню кнопки Office.
2. Для того чтобы новую книгу можно было сохранить вместе макросами, необходимо изменить расширение файла на xlsm – книга Excel с поддержкой макросов. В диалоговом окне Сохранение документа в поле Тип файла выберите нужный тип и сохраните файл Книга1 в своей рабочей папке под именем Макрос.xlsm.
3. Переименуйте Лист 1 в Макрос.
4. Нажмите кнопку Запись макроса, которая находится в группе Код на вкладке Разработчик. Появится диалоговое окно Запись макроса (см. рис. 1), в котором можно установить параметры записываемой процедуры. Если вкладка Разработчик отсутствует на ленте, необходимо открыть диалог задания параметров приложения Microsoft Excel и в разделе Основные установить флажок Показывать вкладку «Разработчик» на ленте.
5. В поле Имя макроса введите Расчет_стоимости. Обратите внимание на то, что имя макроса может содержать только буквы, цифры и знаки подчёркивания.
6. В поле Описание введите текст, поясняющий назначение макроса: Вычисление стоимости с учётом скидки.
7. Поле Сочетание клавиш предназначено для записи клавиши, нажатие на которую вмести с клавишей Ctrl, приведёт к запуску макроса. Не заполняйте пока это поле.
8. Нажмите кнопку ОК чтобы начать запись макроса. Кнопка Запись макроса в группе Код на вкладке Разработчик заменится на кнопку Остановить запись, с помощью которой можно будет остановить запись после выполнения всех необходимых действий. Все действия, которые вы произведёте до нажатия этой кнопки, в том числе повторяющиеся и отмены действий, будут записаны в макрос!
9. Чтобы выключить отображение сетки на листе Microsoft Excel, снимите флажок Сетка, который находится с группе Показать или скрыть на вкладке Вид.
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. Появится окно редактора Visual Basic с кодом процедуры (см. рис. 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
End With
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.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
|
| Рис. 4. Окно редактора Visual Basic с кодом записанного макроса |
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 (Активная ячейка). Свойство FormulaR1 C1 ячейки предназначено для записи в ячейке формулы.
· Следующая далее строка программного кода процедуры выполняет выделение столбца 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
End With
Нас интересовало только выравнивание по правому краю, но макрорекордер записывает установку всех свойств. В принципе, лишние строки можно удалить.
27. Сравните инструкции дальнейшей части кода процедуры с выполненными при записи макроса действиями. После сделанных пояснений вам должно быть понятно назначение инструкций заключительной части кода. Обратите внимание на то, как записана введённая формула: в ней указаны не конкретные ячейки, а их положение относительно ячейки с формулой.
Выполнение макроса
Записанный макрос можно выполнить различными способами: вызвать его через диалог Макросы, использовать сочетание клавиш, добавить кнопку на панель быстрого доступа, создать специальный объект на рабочем листе (например, кнопку).
Для начала вызовем записанный макрос через диалог Макросы.
28. Создайте новый лист и назовите его Диалог.
29. Нажмите кнопку Макросы, которая находится в группе Код на вкладке Разработчик. Появится диалоговое окно Макрос, содержащее имена всех доступных в данный момент макросов. С его помощью можно выполнять, изменять и удалять макросы.
30. Выделите макрос Расчет_стоимости и нажмите кнопку Выполнить. Процедура будет выполнена. После этого текущий рабочий лист будет выглядеть так же, как и предыдущий.
31. Теперь назначим нашему макросу сочетание клавиш. Снова нажмите кнопку Макросы, которая находится в группе Код на вкладке Разработчик. В появившемся диалоговом окне Макрос выделите макрос Расчет_стоимости и нажмите кнопку Параметры…. Вы увидите ещё одно диалоговое окно, которое позволяет задать макросу сочетание клавиш и изменить описание макроса. В поле рядом с надписью Ctrl+ введите любой символ – соответствующая клавиша будет использоваться для вызова макроса при нажатой клавише Ctrl. Выбранное сочетание клавиш заменит все совпадающие стандартные сочетания клавиш приложения Microsoft Excel на то время, пока книга, содержащая данный макрос, открыта. Поэтому желательно использовать символы, отличные от стандартных. Кроме того, можно использовать сочетание клавиш Ctrl + Shift + символ.
32. Создайте новый лист, назовите его Сочетание клавиш и нажмите выбранное сочетание клавиш. Макрос снова должен быть выполнен.
33. Если вы предполагаете часто использовать созданный макрос, можно добавить кнопку для его вызова на панель быстрого доступа. Нажмите кнопку Настройка панели быстрого доступа 
, которая находится справа от панели быстрого доступа. В появившемся меню выберите пункт Другие команды… В диалоговом окне Параметры Excel в разделе Настройка в выпадающем списке Выбрать команды из: выберите Макросы, в нижележащем списке выберите имя созданного макроса и нажмите кнопку Добавить. Нажмите кнопку ОК для завершения настройки панели быстрого доступа.
34. Создайте новый лист, назовите его Панель быстрого доступа и попробуйте использовать новую кнопку.
Редактирование макроса
Полученный в результате записи текст процедуры можно изменить для достижения той или иной цели, применяя инструкции Visual Basic.
35. Если вы попытаетесь снова запустить созданный макрос на рабочем листе, на котором макрос уже выполнялся, возникнет ошибка из-за установленной защиты листа. Чтобы избежать этого добавьте в начало процедуры команду ActiveSheet.Unprotect. Теперь макрос будет самостоятельно снимать защиту листа.
36. Предположим, что вы хотите не использовать фиксированный размер скидки, а задавать его при выполнении макроса. В коде процедуры Расчет_стоимости замените инструкцию ActiveCell.FormulaR1C1 = "5%" на инструкцию ActiveCell.FormulaR1C1 = InputBox("Введите процент скидки"). Сохраните процедуру.
37. Создайте новый лист и назовите его Ввод скидки. Выполните макрос для проверки внесённых изменений. В появившемся диалоговом окне введите значение скидки в виде десятичной дроби 0,3 (соответствует 30%) и нажмите кнопку ОК. Если вы не сделали ошибки, то новый лист будет заполнен, а в ячейке C7 появится значение скидки 30,00%.
Предположим, что размер скидки ограничен, например, он не должен превышать 50%. Внесём в процедуру такие изменения, чтобы выполнялась проверка размера скидки. Если введённая скидка больше 50%, то ячейка должна быть очищена, формирование рабочего листа приостановлено, подан сигнал, а также выведено соответствующее предупреждение. Если же размер скидки в норме, то должно быть завершено формирование рабочего листа и выведено подтверждающее сообщение.
38. В коде процедуры Расчет_стоимости замените инструкцию ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True на следующий набор инструкций:
Range("C7").Select
If Val(ActiveCell.FormulaR1C1) > 0.5 Then
MsgBox "Величина скидки не должна превышать 50%"
ActiveCell.Clear
Beep
Else
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
MsgBox "Формирование листа закончено"
End If
39. Обратите внимание на применение стандартной функции Val(Аргумент строчного типа), которая выполняет преобразование аргумента типа строки в число типа Double. VBA, в отличие от Visual Basic, сам не выполняет преобразование в выражениях операнда строчного типа в число, а операнда числового типа в строку. За соответствием типов операндов выражения обязан следить программист. При необходимости преобразовать число в строку используется стандартная функция Str(Аргумент любого числового типа).
40. Сохраните процедуру.
41. Создайте новый лист и назовите его Проверка скидки. Выполните макрос для проверки внесённых изменений.
42. Задайте значение скидки равным 0,6 (соответствует 60%) и нажмите кнопку ОК. Если всё было сделано правильно, должно появиться сообщение об ошибке и ячейка со скидкой должна быть очищена.
43. Попробуйте задать процент скидки равным 40%.
Работа с ячейками, диапазонами и листами
До сих пор рассматривались изменения данных в активной ячейке и на текущем рабочем листе. Иногда требуется вводить данные на другой лист. Предположим, например, что после выполнения расчёта стоимости необходимо заполнить накладную. Создайте процедуру, копирующую значения ячеек одного листа в ячейки другого листа.
44. Создайте новый лист.
45. Выполните макрос Расчет_стоимости, введя допустимую величину скидки 30%. Введите значение розничной цены.
46. Чтобы создать новую процедуру, нажмите кнопку Макросы в группе Код на вкладке Разработчик. В появившемся диалоговом окне в поле Имя макроса введите название создаваемой процедуры Создать_Накладную и нажмите кнопку Создать.
47. В открывшемся окне редактора введите следующий текст процедуры:
Sub Создать_Накладную ()
'Дадим активному листу имя Расчет
ActiveSheet.Name = "Расчет"
'Добавим рабочий лист после текущего
Worksheets.Add After:=ActiveSheet
'Дадим только что добавленному листу имя Накладная
ActiveSheet.Name = "Накладная"
'Введем в ячейку C1 значение "Накладная"
Range("C1").Value = "Накладная"
'Установим в ячейке C1 шрифт полужирный, 18, курсив
Range("C1").Font.Bold = True
Range("C1").Font.Size = 18
Range("C1").Font.Italic = True
'Введем данные в ячейки A3, A4 и A5
Range("A3").Value = " Склад № 5"
Range("A4").Value = " ул. Энергетическая, 1"
Range("A5").Value = " Москва, Российская Федерация"
'Введем данные в ячейки B7, B8 и B9
Range("B7").Value = " Розничная цена:"
Range("B8").Value = " Оптовая скидка:"
Range("B9").Value = " Оптовая цена:"
'Установим ширину столбцов B и C
Columns("B:C").ColumnWidth = 20
'Установим в ячейках B7:B9 курсив и выравнивание по правому краю
Range("B7:B9").Font.Italic = True
Range("B7:B9").HorizontalAlignment = xlRight
'Установим в ячейках B7:C9 размер шрифта 14
Range("B7:C9").Font.Size = 14
' Скопируем данные из ячеек листа Расчет в ячейки текущего листа
Range("C7").Value = Worksheets("Расчет").Range("C5").Value
Range("C8").Value = Worksheets("Расчет").Range("C7").Value
Range("C9").Value = Worksheets("Расчет").Range("C9").Value
'Установим в ячейках C7:C9 нужный формат
Range("C7").NumberFormat = _
"_-* #,##0.00[$р.-419]_-;-* #,##0.00[$р.-419]_-;_-* ""-""??[$ р.-419]_-;_-@_-"
Range("C8").Style = "Percent"
Range("C8").NumberFormat = "0.00%"
Range("C9").NumberFormat = _
"_-* #,##0.00[$р.-419]_-;-* #,##0.00[$р.-419]_-;_-* ""-""??[$ р.-419]_-;_-@_-"
Range("B11").Value = "*** Спасибо за покупку! ***"
Range("B11").Font.Italic = True
End Sub
48. Макрос Создать_Накладную создаёт новый рабочий лист (см. рис. 5) с именем Накладная и выполняет копирование на него данных с рабочего листа с именем Расчёт. Назначение отдельных инструкций процедуры поясняет комментарий.
|
| Рис. 5. Рабочий лист, созданный макросом Создать _Накладную |
49. Покажите результаты работы преподавателю.
50. Закройте приложение Microsoft Excel.
Вопросы для контроля
1. Есть ли что-либо общее между макросом Microsoft Excel и процедурой Visual Basic?
2. Как записать процедуру с помощью макрорекордера?
3. Как можно увидеть текст записанного макроса?
4. Как выполнить записанный макрос, используя команды ленты?
5. На что влияет значение свойства DisplayGridlines объекта ActiveWindow?
6. Зачем нужен объект Range?
7. Что делает метод Select при применении к объекту Range?
8. Что означает ссылка ActiveCell?
9. Каково назначение свойства FormulaR1 C1 объекта Range?
10. Каково назначение метода Columns?
11. На что влияет значение свойства ColumnWidth?
12. Каково назначение стандартных функций Val и Str? Какие аргументы у этих функций?
13. Чем является объект ActiveSheet?
14. Чем является объект Worksheets?
15. Что произойдет в результате применения метода Add к объекту Worksheets?
Дата добавления: 2022-06-11; просмотров: 17; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!