Взаимодействие макроса с объектами Excel

Макрос обычно взаимодействует с объектами приложения, в кото- ром он определѐн - host-приложения (host – хозяин). Объетами Excel являются Книга (Workbook), Рабочий лист (WorkSheet) с Диапазонами ячеек (Range). Объекты обладают свойствами, имеют методы, позво- ляющие изменять свойства. Объекты могут реагировать на происхо- дящие события. Такую возможность целесообразно использовать для эффективного решения примера 6, в котором по очередной указан- ной (выделенной) ячейке с фамилией должны вычисляться суммар- ные показатели для всех строк с этой фамилией.

Для получения адреса указанной (выделенной) ячейки важна реакция листа на событие «Изменение выделения».

Объекты Excel представлены в окне обозревателя проекта (Project) среды VBA. Клик по значку Лист1 в окне Project настраивает окно кода: Лист1 (code). А выбор на этом окне в (General) WorkSheet (по- сле щелчка по треугольнику справа) приводит к генерированию опе- раторов заголовка и конца процедуры обработки события «Измене- ние выделения»:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

В ответ на изменение выделения диапазона (ячейки) Excel передаѐт как значение (By Val ue), т.е. – копирует, в объект Target (мишень) этой внутренней (private – личной) процедуры объект типа Range (диапа-

зон) c характеристиками выделенной ячейки (например, адресом: Target.Address). Вообще говоря, все действия, связанные с обработ- кой этой ячейки, можно поместить внутрь данной процедуры. Но обычно общие действия для ряда объектов (в частности для различ- ных листов) выполняют в модулях. Следуя этому принципу можно всю обработку ячейки из макроса примера 6 оформить как процеду- ру, в которую из процедуры обработки события передаѐтся адрес Addr только что выделенной ячейки. В таком случае процедуру в мо- дуле (названную, например, ModuleSub) следует начать оператором

Sub ModuleSub(ByVal Addr As String) ' Addr – текстовая строка под адрес

Для еѐ вызова и передачи в неѐ фактического адреса только что выде- ленной ячейки в процедуре обработки события помещают оператор вызова процедуры Call:

Call ModuleSub(Target.Address) ' Вызов процедуры с передачей в неѐ адреса

Теперь можно сформулировать

сценарий универсального решения примера 6:

1. По сочетанию клавиш Ctrl+Shift+R макросом запускается процесс анализа общих сумм и количества звонков абонентов.

2. Выделяется ячейка. Если она пуста, то следует переход к пункту 4, иначе (ячейка содержит фамилию) выполняется пункт 3.

3. Процедура обработки события «Изменение выделения» передаѐт адрес только что выделенной ячейки в процедуру ModuleSub, ко- торая вычисляет и выводит требуемые величины. Затем следует переход к пункту 2, где ожидается выделение очередной ячейки.

4. После выделения пустой ячейки процесс анализа прекращается. В макросе RunMac для запуска (по Ctrl+Shift+R) анализа можно ис-

пользовать общую (public) для всех процедур переменную, например,

переменную с именем OnOff. Сначала она имеет значение Empty, а потом OnOff=1, когда процесс анализа запускается («включается») макросом:

Public OnOff ¢ объявление общей переменной

Sub RunMac()

OnOff = 1: Cells(2, 1) ="Вкл" ' Символы Вкл - в ячейку $A$2

End Sub

В процедуре (рабочего листа) обработки события «Изменение выде- ления» вызов ModuleSub выполняется лишь тогда, когда OnOff =1:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If OnOff = 1 Then Call ModuleSub(Target.Address) ' Вызов с передачей адреса

End Sub

При завершении анализа, когда при OnOff =1 адрес указывает на пустую ячейку, необходимо в процедуре ModuleSub назначить OnOff = Empty, «выключить» анализ и выйти из процедуры по оператору выхода Exit Sub. С этим добавлением размещаемая в том же модуле, что и макрос процедура ModuleSub имеет следующий вид:

Sub ModuleSub(ByVal Addr As String) ¢ Addr – текстовая строка под адрес

If Range(Addr) = Empty Then

OnOff = Empty: Cells(2, 1) = "Выкл": Exit Sub ' Выкл - в ячейку $A$2

End If

Fam$ = Range(Addr) ' Копировать фамилию в Fam$

i = 3: S = 0: Kol = 0 ' Текст из примера 6:

Do While Cells(i, 2) <> Empty

If Cells(i, 2) = Fam$ Then

S = S + Cells(i, 3) Kol = Kol + 1

End If

i = i + 1

Loop

Cells(1, 2) = S: Cells(1, 4) = Kol ¢ Запись результатов в B1, D1 и F1: If (Kol <> 0) Then Cells(1, 6) = S / Kol ' Kol=0 при некорректном указании Cells(1, 8) = Fam$ ' Фамилию - в ячейку $H$1

End Sub

Вывод результатов в форму можно обеспечить, если использовать объект UserForm (экранная форма или просто форма), который со- ставляет часть пользовательского интерфейса приложений Windows. Объекту UserForm (как экземпляру класса UserForm) присущи его свойства и методы. Каждый объект UserForm включает в себя также модуль класса, в который можно добавлять собственные методы и

свойства или код для обработки событий формы. В приложениях MS Office, и в частности в Excel, форма подключает- ся после выбора UserForm в пункте Insert меню редактора VBA. В результате появляется пустое диалоговое окно формы, имя формы UserForm1 включается в

Project, в окне Properties представлены свойства формы, доступные для ре- дактирования. Можно изме- нить, например, имя (Name) формы на новое frmITOG, а также свойство Caption (на- звание), набив, например, слово ИТОГ в поле справа от Caption. После чего это новое название появится в заголов- ке формы (см. рис. 6.3).

К объекту UserForm можно добавлять элементы

управления с панели Toolbox.

Рис. 6. 3.

Для наших целей подойдѐт Label (Надпись) – пиктограмма с буквой

«A», которую следует отбуксировать в центр формы. После этого надпись Label1 становится подобъектом формы frmITOG; она имеет своѐ свойство Caption (записывается как frmITOG.Label1.Caption), ко- торое можно использовать для вывода результатов в управляющий элемент Надпись. Для примера 6 в конец процедуры ModuleSub (перед оператором End Sub) добавлены следующие операторы:

frmITOG.Caption = Fam$ & "Сум.СЧЕТ:" ' вывод на место названия формы frmITOG.Label1.Caption = S & " руб." ' вывод в Надпись Call frmITOG.Show ' вызов метода Show делает форму видимой

По сочетанию клавиш Ctrl+Shift+R можно снова запускать теперь уже итоговый макрос для примера 6.

После запуска в ячейку

$A$2 выводятся симво- лы «Вкл» - включен анализ, и можно выде- лять ячейку с фамилией. После еѐ выделения процедура модуля вы- полняет вычисление общей суммы, определя-

 

Рис. 6. 4.

ет количество вызовов и среднюю стоимость одного звонка указанно- го абонента, а также заполнение результатами ячеек B1, D1 и F1 (см. рис. 6.4). Фамилия выводится в H1. Кроме того будет выведена форма с фамилией и итоговой суммой. Закрыв форму, можно снова выде- лить ячейку с фамилией и получить суммарные показатели для этой фамилии. После выделения пустой ячейки процесс анализа заверша- ется, а в ячейку $A$2 выводятся символы «Выкл» - анализ выключен.

4.Задание 2

Дополнить и изменить макрос 1-ой части так, чтобы по выделенной ячейке таблицы с наименованием (фамилией) этот макрос вычислял и записывал значения требуемых параметров.

Предъявить работающий макрос преподавателю. Текст макроса и схематичный вид результатов его выполнения записать в отчѐт.

Рекомендуемая литература

1. Информатика базовый курс. Учебник под редакцией С.В. Симоновича – С.Пб.: «Питер», 2005.

2. Информатика и информационные технологии, под ред. Романовой Ю.Д. - М: Издательство «ЭКСМО», 2010.

3. Алехина Г.В., Основы информатики – М.: Маркет ДС, 2009.

4. Дж. Кокс, С. Ламберт, К. Мюррей, К. Фрай, Дж. Преппернау, Microsoft Office System 2007. Русская версия - С.Пб.: ЭКОМ, 2007.

5. Л. Д. Слепцова, Программирование на VBA в Microsoft Office 2007 – М.: Диалектика, 2007.

СОДЕРЖАНИЕ

Введение........................................................................................................... 3

№1. Операционная система Windows........................................................... 4

№2. Работа с текстовым процессором

MS Word.......................................................................................................... 15

№3. Работа с табличным процессором

Microsoft Excel 2007...................................................................................... 30

№4. Система управления базой данных (СУБД) Access. Создание таблиц и схемы данных............................................................................................................. 42

№5. Анализ информации таблиц и БД Access. Создание запросов.. 54

№6. Начала алгоритмизации. Обработка в Excel табличных данных с использованием макросов на VBA............................................................. 66

Рекомендуемая литература....................................................................... 83

 

 

Учебное издание

Калядин Вячеслав Иванович, Макаров Алексей Иванович

 

 

Основы работы на персональном компьютере.

Использование программных средств ПК при проектировании авто- мобильных конструкций. Учебное пособие по дисциплине «Инфор- матика» для студентов, обучающихся по специальности Автомобиле- и тракторостроение. М.: МАМИ, 2010. 85 с.: ил.

Под редакцией авторов

Оригинал-макет подготовлен редакционно-издательским отделом МГТУ «МАМИ»

По тематическому плану внутривузовских изданий учебной литературы на 2010 г.

 

 

Подписано в печать 08.11.2010 Формат 90x60 1/16 Бумага 80 г/м2 Гарнитура «Таймс». Ризография. Уч. печ. л. 5,3.

Тираж 200 экз. Заказ № 129-10

 

МГТУ «МАМИ»

107023, г. Москва, Б.Семѐновская ул. 38.