Файл: Лабораторная работа 3 Работа с диапазонами ячеек (объекты Range, Calls).pdf

Оглавление
Лабораторная работа №1 Макросы ....................................................................... 2 1. Понятие макросов .......................................................................................... 2 2. Автоматическая запись макросов .................................................................. 3 3. Просмотр макросов ......................................................................................... 5 4. Задание для самостоятельного выполнения ................................................ 7
Лабораторная работа №2 Объекты и коллекции .................................................. 8 1. Основные понятия ............................................................................................ 8 2. Использование свойств объекта ................................................................... 10 3. Использование методов объекта .................................................................. 11 4. Работа с коллекциями объектов и контейнерами объектов ..................... 13 5. Задание для самостоятельного выполнения ............................................... 14 6. Обработчики событий .................................................................................... 15 7. Вопросы для самоконтроля ........................................................................... 17
Теоретическая работа Операторы, переменные и типы данных ....................... 18 1. Понятие оператора ......................................................................................... 18 2. Переменные и типы данных ........................................................................... 19
Лабораторная работа №3 Работа с диапазонами ячеек (объекты Range, Calls)
................................................................................................................................... 20 1. Свойство Range .............................................................................................. 20 2. Свойство Cells .................................................................................................. 21 3. Свойства объектов Range .............................................................................. 21 4. Задание для самостоятельного выполнения ............................................... 22
Лабораторная работа №4 Функции ....................................................................... 23 1. Понятие функции ............................................................................................ 23 2. Математические функции ............................................................................. 23 3. Функции преобразования данных ................................................................ 25 4. Функции преобразования даты и времени ................................................... 27 5. Строковые функции ........................................................................................ 28 6. Практика .......................................................................................................... 30 7. Вопросы для контроля .................................................................................... 31
Лабораторная работа №5. Операторы ветвления и выбора.............................. 32 1. Что такое ветвление и как оно организуется в языке VBA? ...................... 32 2. Условный оператор IF .................................................................................... 32 3. Оператор выбора CASE .................................................................................. 33
Лабораторная работа №6 Разные конструкции цикла ...................................... 35 1. Теория .............................................................................................................. 35 2. Практика .......................................................................................................... 36 5. Задание для самостоятельного выполнения .............................................. 37 6. Вопросы для контроля ................................................................................... 37 1

Лабораторная работа №1
Макросы
1.
Понятие макросов
Макрос — это программа, состоящая из списка команд, которые должны быть выполнены приложением. Макрос служит для объединения нескольких различных действий в одну процедуру, которую вы можете легко вызвать. Этот список команд состоит в основном из макрокоманд, которые тесно связаны с командами приложения, в котором вы создаете макрос — т. е. с командами
Word, Excel или других приложений Microsoft Office. Запись макросов позволяет не просто запомнить последовательность вызовов команд меню, нажатий на кнопки мыши и ввод данных с клавиатуры, но и перевести эти действия на объектно-ориентированный язык программирования Visual Basic для приложений (Visual Basic for Applications, в дальнейшем просто VBA) и сохранить их в виде готовой к выполнению программы.
VBA является полноценным языком программирования, позволяющим записать не только последовательно выполняемые пользователем действия, но и содержащим все необходимые конструкции языка программирования высокого уровня, включая разнообразные средства организации ветвлений, циклов и ведения диалога с пользователем. Весьма удобный редактор VBA позволяет не только писать и редактировать программы, но и вести их отладку.
Можно выделить следующие разновидности макросов:
Командные макросы — это наиболее распространенные макросы, обычно состоящие из операторов, эквивалентных тем или иным командам меню или параметрам диалоговых окон. Основные предназначением такого макроса является выполнение действий, аналогичных командам меню т.е. изменение окружения и основных объектов приложения.
Пользовательские функции — работают аналогично встроенным функциям Excel. Отличие этих функций от командных макросов состоит в том, что они используют значения передаваемых им аргументов, производят некоторые вычисления и возвращают результат в точку вызова, но не изменяют среды приложения
Макрофункции — представляет собой сочетание командных макросов и пользовательских функций. Наряду с тем, что они могут использовать аргументы и возвращать результат, подобно пользовательским функциям, они могут также изменять среду приложения, как и командные макросы.
Чаще всего эти макросы вызываются из другого макроса, и активно используются дня модульного программирования. Если необходимо выполнить ряд одинаковых действий в различных макросах, то обычно эти действия выделяются в отдельную макрофункцию (подпрограмму), которая вызывается всякий раз, когда необходимо выполнить эти повторяющиеся действия.
2

2.
Автоматическая запись макросов
Для автоматической записи макроса надо выполнить следующие действия:
1.
В обычном режиме выполнить команды, которые надо сохранить в макросе. Это надо чтобы вы полнить последовательность команд.
2.
Перейти в режим записи макроса. Задать имя макроса.
3.
Выполнить необходимую последовательность команд.
4.
Выйти из режима записи макроса.
Рассмотрим подробнее пункты со 2 по 5.
Для перехода в режим макроса надо на вкладке Разработчик выполнить команду
Макрос Запись макроса. В появившемся окне задать имя макроса.
Имя макроса может содержать буквы и цифры и не может содержать пробелы. Можно связать выполнение макроса с комбинацией клавиш. Эта комбинация так же задается в окне записи макроса. В этом же окне можно описать, что делает данными макрос.
После щелчка по кнопке ОК. Выполняется последовательность
команд, которую надо запомнить в макросе.
В конце выполняется команда Макрос Остановить запись.
Вызвать макрос можно двумя способами.
1 способ.
Выполнить команду Макрос Макросы. В окне Макрос (Рис.1) выбрать нужный макрос и щелкнуть по кнопке Выполнить. В этом же окне можно удалить макрос, который работает не правильно.
Рисунок 1 - Окно запуска макроса
2 способ
Нажать комбинацию клавиш, связанную с данным макросом.
Задание 1
Создать Макрос, который выделяет диапазон таблицы A1:D5, и выполняет его обрамление. Внешние границы обрамления - двойная линия.
Внутренние линии - одинарные.
Для этого надо сделать:
3

1.
Активировать ячейку A1.
2.
Начать запись макроса.
3.
Задать имя макроса Обрамление 1, комбинация клавиш Ctrl+й.
!
Обратите внимание, что кнопка «Относительные ссылки» должна быть в неактивном
состоянии.
4.
Выполнить необходимые действия.
5.
Остановить запись макроса.
6.
Проверьте действие макроса, выделив любую другую ячейку на текущем листе, новом листе.
Результат всегда должен быть таким:
Задание 2
Создать Макрос, который выполняет обрамление 4 столбцов и 5 строк в произвольном месте листа. Внешние границы обрамления - двойная линия.
Внутренние линии - одинарные.
Порядок выполнения следующий:
1.
Установить курсор в ячейку A1.
2.
Начать запись макроса.
3.
Задайте имя макроса Обрамление2. комбинация клавиш Ctrl+t.
!
Обратите внимание, что кнопка «Относительные ссылки» должна быть в активном
состоянии.
4.
Выделите диапазон А1:D5.
5.
Выполните заданное обрамление диапазона.
6.
Остановить запись макроса.
После записи макроса поставьте курсор в ячейку D8 и выполните макрос
Обрамление2.
В обоих заданиях выделение диапазона выполнялось в макросе. Это приводило к тому, что обрамление выполнялось для диапазона определенных размеров — 4 столбца и 5 строк. Если выделение диапазона выполнять до записи макроса, то размер диапазона обрамления будет произволен.
Задание 3
Выполните обрамление диапазона Аl:D5. Но сначала надо выделить данный диапазон, а потом выполнить команду. Задайте имя макроса
ОбрамлениеЗ
,
комбинация клавиш — Ctrl+у.
После записи макроса выделите произвольные ячейки и вызовите макрос
ОбрамлениеЗ. В результате должно выполниться обрамление выделенного
диапазона.
4

Задание 4
Ответьте на поставленные вопросы:
1)
Как влияет на выполнение макроса активизация кнопки
«Относительные ссылки»?
2)
Как влияет выделение диапазона ячеек до начала записи макроса и после?
Сохраните книгу под именем Макросы.

Пригласите преподавателя и продемонстрируйте выполнение заданий.
3.
Просмотр макросов
При записи макросов Макрорекодер записывает все выполненные команды в макрос. Эти макросы находятся в модулях и можно увидеть текст этих макросов и, при необходимости, изменить. Это широко используется в программировании VBA. Нет необходимости точно напоминать команды форматирования, копирования и т.д. Достаточно записать их в командный макрос, а потом изменить, так как надо.
Задание 5
Запишите макрос, который будет записывать в выделенную ячейку некоторое значение, установит цвет заливки ячейки и цвет текста. Назовите макрос
Заливка1
Задание 6
Просмотрите содержание макроса
Заливка1
в редакторе VBA. Для этого:
1.
Выполнить команду Макрос Макрос.
2.
Выбрать нужный макрос (Заливка1).
3.
Щелкнуть по кнопке Изменить.
В результате выполнения откроется окно редактора VBA c текстом выбранного макроса как на рисунке 2.
5

Рисунок 2 - Окно редактора VBA
Любой макрос имеет следующую структуру:
Sub
Название макроса
Команда VBA
Команда VBA
…
End Sub
Текст зеленого цвета
– это комментарии. Комментарии начинаются со знака «апостроф» и не выполняются компьютером.
Текст синего цвета
– ключевые слова, зарезервированные редактором.
Рисунок 3 - Код макроса
Командами VBA здесь являются:
Название макроса
Комментарии
Команды VBA
Конец макроса
6

Selection
– выделенная ячейка или диапазон ячеек.
Interior
– оформление диапазона ячеек.
Запись
Selection.Interior означает, обращение к свойству
Interior объекта (в данном случае это диапазон выделенных ячеек)
Selection.
Фрагмент кода означает, что изменяются свойства выделенного диапазона ячеек, отвечающие за цвет и стиль заливки.
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Следующий фрагмент кода означает изменения параметров шрифта выделенного диапазона ячеек.
With Selection.Font
.Color = -16777024
.TintAndShade = 0
End With
!
Если какое-то из операторов вызывает затруднение, то пояснения и примеры
использования можно посмотрев, вызвав контекстное меню по сочетанию клавиш
Ctrl+F1
.
Использование оператора
With … End With эквивалентно записи
Selection.Font.Color = -16777024
Задание 7
Запишите макрос изменения параметров шрифта произвольно выделенного диапазона ячеек, просмотрите код макроса, запишите комментарии к каждой строке макроса и ответьте на следующие вопросы:
1)
Как называется макрос?
2)
Где конец макроса?
3)
Сколько макросов создано в вашем модуле?

Пригласите преподавателя и продемонстрируйте выполнение заданий.
4.
Задание для самостоятельного выполнения
1.
Создать макрос, который объединяет выделенные ячейки и форматирует текст в этой объединенной ячейке по центру по горизонтали и вертикали.
!
Объединение целесообразно выполнять через окно «Формат ячеек».
2.
Создать макрос, который защищает лист паролем.
3.
Создать макрос, который снимает защиту с листа с паролем.
4.
Добавить комментарии к фрагментам кода всех макросов.
7

Лабораторная работа №2
Объекты и коллекции
1.
Основные понятия
VBA является идеальным инструментом для изучения основ объектно- ориентированного программирования, так как имеет встроенные объекты
(рабочие книги и листы, ячейки, документы и выделенные фрагменты текста) их свойства и методы.
Объектно-ориентированное приложение организует данные и выполняемые операторы программного кода в связанные объекты, что облегчает разработку, организацию и работу со сложными структурами данных и действиями, выполняемыми на этими данными (или с ними). Каждый объект в программном приложении содержит данные и операторы, связанные вместе и образующие один элемент. Большинство приложений содержит много различных типов объектов.
В Excel объектами являются рабочие книги (
Workbooks
), листы
(
WorkSheets
)
, диапазоны даных (
Range
), таблицы, графические объекты, диалоговые окна и само приложение Excel (
Application
).
Как и объекты реального мира, объекты VBA имеют различные присущие им качества или свойства (
Properties
).
Объекты в Excel имеют свойства, определяющие их вид и поведение: рабочие листы могут быть видимыми или нет, строки рабочего листа имеют свойство высоты, столбцы имеют свойства ширины и так далее.
Свойства регулируют вид и поведение объекта. Чтобы изменить вид и поведение объекта, необходимо изменять его свойства. Чтобы узнать о текущем виде и поведении объекта, необходимо узнать о его свойствах. Вы можете определить диск, папку и имя рабочей книги Excel рассмотрев свойство
FullName этой рабочей книги.
Некоторые свойства можно изменять, некоторые – нет. Например в рабочей книге Excel можно изменить имя автора книги, изменяя свойство
Author
, но нельзя изменить свойство
Name рабочей книги. (Свойство
Name рабочей книги содержит имя файла на диске и не может быть изменено без создания нового дискового файла или переименования файла рабочей книги).
Некоторые объекты имеют свойства с одними и теми же именами: объекты
Application
,
Workbook и
WorkSheet
– все имеют свойство
Name
Каждый объект рабочего листа сохраняет данные для своих свойств внутри самого рабочего листа вместе с пользовательскими данными.
Объекты реального мира почти всегда имеют тот тип присущего им поведения или действия, которые они могут выполнять. Объекты VBA также имеют поведение и возможности, называемые методами (methods). Объект рабочей книги Excel, например, имеет встроенною способность добавлять к себе новый рабочий лист - метод добавления рабочих листов (называемый
Add
).
Методы изменяют значения свойств объектов; методы выполняют также действия с данными (или над данными), сохраняемые объектом. Для вызова метода объекта указывается не только наименование метода, но и объект (имя),
8