Цветкова О.Н., Миронова И.В. Программирование на VBA

Подождите немного. Документ загружается.

• если продукции продано не меньше, чем на 10000 руб., то ко-

миссионные составляют 2% от стоимости продукции;

• если стаж работы не меньше 5 лет, то выплачивается доплата в

размере 0.5% от стоимости реализованной продукции.

Задание 7.6

Напишите функцию, которая дописывает к числовому значению

слова «рубль», «рубля» или «рублей» в зависимости от значения ар-

гумента. Алгоритм формирования окончаний сформулируем так:

• исключение: если число оканчивается на 11, 12, 13 или 14, до-

бавляем слово «рублей»;

• если целое число оканчивается на 1, добавляем слово «рубль»;

• если число оканчивается на 2, 3 или 4, добавляем слово «рубля»;

• если число оканчивается на цифры 5, 6, 7, 8, 9 или 0, добавляем

слово «рублей».

Задание 7.7

Напишите функцию для расчета срока окупаемости инвестиций

(PP). Формула имеет вид: PP = n, при котором

∑

P > IC,

где Pk – годовые доходы, IC – исходные инвестиции.

Например, в приведенном ниже случае период окупаемости бу-

дет равен 2 годам:

Год Денежные потоки

0-й -10

1-ый 0

2-ой 20

3-ий 5

Задание 7.8

Напишите функцию, которая определяет, есть ли в заданном од-

номерном массиве повторяющиеся элементы. Функция должна воз-

вращать значение True, если есть, и False – в противном случае.

8. Работа с макросами в MS Excel

Основные объекты MS Excel

Объектная модель Microsoft Excel содержит много объектов. На

первых порах достаточно использовать Application (Приложение),

Workbook(s) (Рабочая книга), Worksheet(s) (Рабочий лист), Range

(Интервал ячеек), Chart (Диаграмма). Эти объекты не являются неза-

висимыми. Между ними существует иерархия. Для доступа к подчи-

ненным объектам используется точка.

Объекты Microsoft Excel, как и все другие объекты, имеют свой-

ства, методы и события. Наиболее полное их описание можно найти в

справочной системе.

Объект Application – это главный объект в иерархии объектов

Excel. Он представляет само приложение Excel. Очень часто свойства

и методы этого объекта используют, не указывая слово Application.

Например, вместо Application.ActiveCell.Value можно написать

ActiveCell.Value.

Многие объекты объединены в семейства (их ещё называют

коллекциями). Это позволяет хранить созданные объекты вместе и

при необходимости обрабатывать их как единое целое. Названия объ-

екта и семейства отличаются окончанием. Например, Workbook – это

рабочая книга, а Workbooks – это семейство рабочих книг. Для указа-

ния конкретного объекта в семействе используется индекс. Элементы

в семействах нумеруются с 1. Можно также использовать имена эле-

ментов.

Объект Range предназначен для работы с ячейками, строками,

столбцами, а также их группами. Для доступа к объекту чаще всего

используются свойства Range и Cells объектов Application и

Worksheet, хотя есть и другие возможности.

Например, так можно присвоить значение ячейке "A1" первого

рабочего листа первой рабочей книги, используя свойство Range:

1. Workbooks(1).Worksheets(1).Range("A1").Value = 5

2. Workbooks("Книга1.xls").Worksheets("Лист1").Range("A1").Value = 5

3. Worksheets("Лист1").Range("A1").Value = 5

4. Range("A1").Value = 5

5. Лист1.Range("A1").Value = 5

6. Dim X As Worksheet 'Объявляем переменную

Set X = Workbooks("Книга1.xls").Worksheets("Лист1") 'Значение

X.Range("A1").Value = 5 'Используем переменную X

Если рабочая книга и рабочий лист не указаны, то инструкция

выполняется для активного листа активной рабочей книги. Обратите

внимание, что в варианте 3 "Лист1" – это имя, которое выводится на

ярлычке рабочего листа (значение свойства Name объекта Worksheet),

а в варианте 5 Лист1 – это значение свойства (Name) объекта

Worksheet (свойства Name и (Name) – это разные свойства, и их зна-

чения могут отличаться). Если объект используется многократно,

можно запомнить ссылку в переменной соответствующего типа (ва-

риант 6). Ссылка на диапазон ячеек выглядит аналогично:

Range("A1:B9,G1:H9").

Свойство Cells позволяет обращаться к ячейке рабочего листа,

указывая номер строки и столбца. Например, ссылки Range("B9") и

Cells(9,2) эквивалентны. Можно также использовать свойство Cells

для альтернативного указания диапазона: Range("A2:C3") и

Range(Cells(2,1), Cells(3,3)) определяют один и тот же диапазон. Что-

бы приведенные выше примеры записать с использованием свойства

Cells нужно Range("A1") заменить Cells(1,1).

Основные свойства объекта Range:

Value Значение ячейки или диапазона. Например, заполним

диапазон I2:I5 тем же значением, что находится в B1:

Range("I2:I5").Value = Range("B1").Value

CurrentRegion Возвращает объект Range, в состав которого входит

заданный диапазон. Границами этого объекта явля-

ются пустые строки и столбцы. Например, так можно

выделить диапазон, содержащий ячейку A2,

Range("A2").CurrentRegion.Select

Font Возвращает объект Font (шрифт). Например,

With Worksheets("Л1").Range("B5").Font

.Size = 14

.Bold = True

.Italic = True

End With

Formula Формула в формате A1. Например, так можно ввести

формулу в ячейку B5:

Range("B5").Formula = "=$A$4+$A$10"

При считывании значения, возвращается текстовая

строка (как в строке формул).

FormulaLocal Формула в формате A1 с учетом языка пользо-

вателя (для неанглоязычных версий Excel). На-

пример,

Range("B5").FormulaLocal = "=ПИ()"

FormulaR1C1 Формула в формате R1C1. Например,

Range("B1").FormulaR1C1 = "=R1C1+1"

FormulaR1C1Local Формула в формате R1C1с учетом языка поль-

зователя (для неанглоязычных версий Excel).

HorizontalAlignment Горизонтальное выравнивание. Возможные значе-

ния: обычное, по центру, по центу выделения, по

ширине, по левому краю, по правому краю и другие.

VerticalAlignment Вертикальное выравнивание. Возможные значения:

по нижнему краю, по центру, по верхнему краю и

другие.

Для ссылок на ячейки в Excel используются 2 формата: формат

A1 и формат R1C1. В первом случае ссылка состоит из имени столбца

(обозначаются буквами от A до IV) и номера строки. Признаком аб-

солютной ссылки является знак доллара перед именем строки или

столбца.

В стиле R1C1, после буквы R указывается номер строки ячейки,

после буквы C – номер столбца. Например, абсолютная ссылка R1C1

эквивалентна абсолютной ссылке $A$1 для формата A1. Для задания

относительной ссылки указывается смещение по отношению к актив-

ной ячейке. Смещение указывается в квадратных скобках. Знак ука-

зывает направление смещения. Например, R[-3]C (относительная

ссылка на ячейку, расположенную на три строки выше в том же

столбце), R[2]C[2] (относительная ссылка на ячейку, расположенную

на две строки ниже и на два столбца правее), R2C2 (абсолютная

ссылка на ячейку, расположенную во второй строке и во втором

столбце), R[-1] (относительная ссылка на строку, расположенную

выше текущей ячейки), R (абсолютная ссылка на текущую строку).

Стиль R1C1 часто используется при записи макросов.

Методы объекта Range можно разделить на две большие груп-

пы: методы, относящиеся к самому объекту, и методы, реализующие

команды. Многие из них имеют параметры. Описание методов можно

найти в справочной системе Excel. Для изучения методов, реализую-

щих команды, рекомендуется записать макрос, выполняющий нуж-

ную команду, и проанализировать полученный код.

Понятие макроса. Создание, редактирование, выполнение

макросов

Если какое-то действие часто повторяется, его выполнение

можно автоматизировать с помощью макроса. Макрос – это набор

инструкций, хранящихся в модуле Visual Basic. Их можно выполнять

всякий раз, когда необходимо решить данную задачу. Редактор Visual

Basic позволяет редактировать макросы. Макрос можно создать дву-

мя способами: автоматически и вручную.

Для автоматической записи макроса выполните действия:

• Выберите команду меню Сервис\ Макрос\ Начать Запись.

• Введите имя макроса и нужную букву в поле Сочетание

клавиш. В поле Сохранить в книге выберите книгу, в которой дол-

жен быть сохранен макрос (в текущей книге). Нажмите кнопку OK.

• Выполните команды, которые нужно записать.

• Нажмите кнопку Остановить запись на соответствующей

панели инструментов.

Чтобы увидеть текст макроса, перейдите в редактор Visual Basic

и откройте соответствующий модуль.

Чтобы создать макрос вручную выполните следующие действия:

• Перейдите в редактор Visual Basic и откройте модуль с

макросами. Если нужно, добавьте новый модуль с помощью ко-

манды

Вставка\ Модуль.

• Поместите в данном модуле заголовок процедуры (можете

использовать команду

Вставка\ Процедура). Обратите внимание,

что перед Sub должно стоять ключевое слово Public. Имя макроса

должно удовлетворять правилам, предъявляемым к именам в

Visual Basic (начинаться с буквы, без пробелов).

• Добавьте в тело процедуры необходимые инструкции.

Для вызова макроса имеется много способов. Наиболее часто

используются следующие:

 с помощью комбинации клавиш;

 с помощью кнопки панели инструментов;

 с помощью кнопки или другого объекта, помещенного на лист;

 с помощью графического объекта, помещенного на лист.

Чтобы макрос вызывался с помощью комбинации клавиш, вы-

полните следующие действия:

• В меню Сервис выберите команду Макрос\ Макросы.

• В поле Имя макроса введите имя того макроса, которому

нужно присвоить сочетание клавиш, с помощью которых он будет

выполняться.

• Нажмите кнопку Параметры.

• Введите соответствующую букву в поле Сочетание кла-

виш

. Чтобы создать краткое описание макроса, введите необходи-

мый текст в поле

Описание.

• Нажмите кнопку OK.

• Нажмите кнопку Отменить.

Макрос можно запустить с помощью кнопки на встроенной или

специальной панели инструментов. Для этого выполните следующие

действия:

• Выберите команду Настройка в меню Сервис.

• Для создания новой панели инструментов на вкладке Па-

нели инструментов

выберите кнопку Создать….

• Если панель инструментов, содержащая кнопку, не ото-

бражается на экране, выберите вкладку

Панели инструментов и

установите флажок рядом с именем этой панели.

• Чтобы добавить на панель инструментов кнопку, которая

должна запускать макрос, на вкладке

Команды в списке Категории

выберите параметр

Макросы. Из списка Команды перетащите с

помощью мыши нужную кнопку на панель инструментов.

• Щелкните мышью на необходимой кнопке панели инстру-

ментов. Чтобы изменить свойства выделенной кнопки, восполь-

зуйтесь контекстным меню или нажмите на кнопку

Изменить вы-

деленный объект

в окне Настройка. В появившемся меню выбе-

рите команду

Назначить макрос. Введите имя макроса в соответ-

ствующее поле.

• Чтобы удалить выделенную кнопку, перетащите её с по-

мощью мыши за пределы панели инструментов.

Цикл For Each

Эта специфическая форма цикла For предназначена для выпол-

нения некоторой операции с каждым объектом, входящим в состав

коллекции объектов (такой операцией, например, может быть вызов

метода или присваивание значения свойству). Это дает возможность

выполнять массовые манипуляции с объектами так же, как это дела-

ется с массивами переменных. Синтаксис этой разновидности цикла

For:

For Each Элемент In ИмяКоллекции

Инструкции

Next [Элемент]

Здесь Элемент – имя переменной цикла. Переменная может

иметь тип Variant, Object или тип конкретного объекта. Для досроч-

ного выхода из цикла используется инструкция Exit For.

Инструкция With

Инструкция позволяет выполнить последовательность действий

над указанным объектом, не повторяя имени объекта. Ссылка на

свойства и методы этого объекта внутри инструкции With начинается

с точки. Эта инструкция очень часто используется при записи макро-

сов. Синтаксис инструкции:

With Объект

[

Инструкции]

End With

Практические задания

Задание 8.1

Запишите макрос, который выполняет следующие действия:

1. Добавить новый лист.

2. Переименовать лист, дав ему имя «Таблица».

3. В ячейку A1 ввести заголовок таблицы «Ведомость».

4. Выделить диапазон ячеек A1:C1, отформатировать за-

головок (курсив, размер – 14, выравнивание – по центру выде-

ления).

5. Во второй строке ввести заголовки столбцов «Фами-

лия», «Оклад», «Премия».

6. В ячейку C3 ввести формулу «=B3*0,25».

Вы должны получить таблицу приведенную ниже:

Проанализируйте текст макроса. Задайте комбинацию клавиш

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

всегда добавлялся после последнего существующего листа, и имя но-

вого листа вводилось с клавиатуры. Ниже приведен отредактирован-

ный текст макроса, который решает эту задачу.

Sub СозданиеТаблицы()

Sheets.Add after:=Worksheets(Worksheets.Count)

Sheets(Worksheets.Count).Name = InputBox("Введите имя листа")

Sheets(Worksheets.Count).Activate

ActiveCell.Value = "Ведомость"

Range("A1:C1").Select

Selection.Font.Italic = True

Selection.Font.Size = 14

Selection.HorizontalAlignment = xlCenterAcrossSelection

Range("A2:C2").Value = Array("Фамилия", "Оклад", "Премия")

Range("C3").FormulaR1C1 = "=RC[-1]*0.25"

Range("A3").Select

End Sub

Семейство Sheets содержит все листы рабочей книги. Метод

Add используется для создания нового листа. Свойство Count позво-

ляет узнать количество объектов в семействе. Метод Activate делает

указанный объект активным. Метод Select выделяет указанный объ-

ект. Selection – это свойство объекта Application, которое содержит

выделенный в данный момент объект.

Задание 8.2

Создайте форму, приведенную ниже. При выводе окна в тексто-

вом поле показывается содержимое текущей ячейки рабочего листа.