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

Отобразить результаты (значение V)

Конец алгоритма.

В этом алгоритме действия выполняются последовательно друг за другом, такие алгоритмы называются линейными.

Этот алгоритм можно записать в виде макроса Excel на VBA, а значения H и R поместить, например, в ячейки A1 и B1 или просто задать в теле макроса.

Для создания макроса выбирают на вкладке Вид группу Макросы, задают имя, например, Vk, и выбирают Создать. Появляется окно редактора VBA (V isual B asic for A pplication – визуального Бейсика для приложений). В окне обозревателя проектов виден включенный в проект модуль Module1 – в нём создаётся макрос. В окне кода уже записаны строки начала Sub Vk() и конца End Sub процедуры макроса (алгоритма), которые надо дополнить остальными строками алгоритма так, чтобы получился текст макроса:

Sub Vk ()

H = 4.5: R = 3.0 ‘Назначить конкретные значения H и R. Строка 2.

V = H/3*3.14 * R^2 ‘Вычислить объём конуса. Строка 3.

Debug.Print V ‘Отобразить результаты (значение V). Строка 4.

End Sub

В строке 2 текста макроса переменным H и R назначаются в двух операторах конкретные числовые значения. Её можно записать, указав явно имена операторов присваивания Let (пусть):

Let H = 4.5: Let R = 3.0 ‘Назначить конкретные значения H и R. Строка 2,

которые обычно в Бейсике по аналогии с другими языками программирования не указываются. По правилам Бейсика операторы, записанные в одной строке (как здесь во 2-ой строке), отделяются друг от друга двоеточием. Справа после апострофа «ʹ» можно дать примечания, которые выводятся на экран зелёным цветом, а в нашем тексте они будут выделяться курсивом. Ключевые слова (названия операторов) изображаются на экране синим цветом, а в наших текстах они выделяются полужирным шрифтом.

В строке 3 в операторе присваивания сначала вычисляется значение, стоящее справа от равенства, а затем это значение приписывается (присваивается) переменной V, стоящей слева от равенства.

В строке 4 осуществляется вывод вычисленного значения V объёма методом Print (печать) объекта Debug (отладчика) в окно Immediate Windows (окно отладки или ближе к оригиналу - окно «срочно»). Оно вызывается из пункта меню View (или по Ctrl+G).

Непосредственные вычисления можно выполнять в окне отладки, например, вычислить предполагаемый результат работы макроса. Для чего в этом окне следует набить:? (4.5/3) *3.14 *(3.0^2) и дать «Enter». Знак «?» вопроса перед выражением означает печать (Print), и по этому указанию на следующей строке будет выведен результат 84,78. Такой же результат можно получить, набив построчно операторы 2- ой и 3-ей строк макроса и выполнив затем печать вычисленного объёма по? V «Enter». Полезно отметить, что тот же результат получается и по записи? 4.5/3*3.14 *3.0^2 без простановки скобок, т. е. в Бейсике возведение в степень «^» имеет приоритет. После возведения в степень выполняются слева направо деление и умножения как операции одного ранга при отсутствии скобок.

Выполнение макроса в автоматическом режиме можно инициировать через пункт меню Run, нажатием кнопки запуска или клавиши F5. При первом знакомстве больше подходит выполнение в режиме отладки операторов макроса: через пункт меню Debug и/или нажатием клавиши F8. Причём курсор должен находиться внутри макроса. Тогда подсвечивается (обычно жёлтым цветом) оператор, который предстоит выполнить. Наведя указатель мышки на переменную H в первом операторе присваивания, можно увидеть её значение и отметить, что до выполнения оператора переменная имела значение «Empty» (пусто), а после выполнения (по очередному нажатию F8) значение изменилось на 4,5. После выполнения оператора 4-ой строки в окно отладки выводится результат 84,78.

Исходные значения H и R удобно задать в ячейках листа Excel, например, соответственно в ячейках A1 и B1. Для ссылки в тексте макроса на ячейки используется нотация (r, c) – строка (row), столбец (column). Например, ячейку B1 можно в такой нотации задать как (1, 2). В макросе на эту ячейку рабочего листа ссылаются, записывая Cells (1, 2). В таком варианте текст макроса получает следующий вид:

Sub Vk () H = Cells(1,1): R = Cells(1,2) ‘Выбрать значения H и R из ячеек A1, B1

V = H/3*3.14 * R^2 ‘Вычислить объём конуса

Debug.Print V: Cells(1,3) = V ‘Отобразить результаты (значения V)

End Sub

Результат выводится и в окно отладки и в ячейку C1 рабочего листа.

С именем макроса можно связать сочетание клавиш или световую кнопку, по которым он будет запускаться. Выбрав при входе в макрос Параметры и задав, например, сочетание клавиш Ctrl+Shift+V, получаем возможность удобно запускать макрос по этому сочетанию.

Ветвящиеся вычислительные процессы

Пример 2. По заданным значениям коэффициентов p, q квадратного уравнения вычислить значения вещественных корней или вывести сообщение «нет корней» об их отсутствии.

Алгоритм решения задачи можно представить наглядно, в виде блок-схемы примера 2 с записью этапов алгоритма в геометрических фигурах, соответствующих типу этапа.

После ввода коэффициентов и вычисления дискриминанта D следует проверка условия (заключено в ромб), в зависимости от выполнения или не выполнения, которого выбирается направление дальнейших действий. Блок-схема примера 2 в этом месте разветвляется – отсюда и название «ветвящиеся вычислительные процессы». При не отрицательном D вычисляются и выводятся значения корней, а при отрицательном выводится сообщение «нет корней».

Алгоритм реализован в виде макроса Korni на VBA. Значения коэффициентов p, q берутся соответственно из ячеек A1, B1, значения корней - выводятся в ячейки C1, D1, а символьная константа "Нет корней" помещается в ячейку C1.

Sub Korni () ‘Текст макроса

p = Cells (1, 1): q = Cells (1, 2)

D = (p / 2) ^ 2 – q

If D < 0 Then Cells (1, 3) = "Нет корней" ‘Ветвь «да»

Else

X1 = –p / 2 –Sqr (D) ‘Ветвь «нет»

X2 = –p / 2 + Sqr (D) Cells (1, 3) = x1: Cells (1, 4) = x2

End If

End Sub

В приведённом макросе условие D>0 записано в блочном условном операторе If (если). Ветвь «да» блок-схемы размещается между ключевыми словами Then (тогда) и Else (иначе) этого оператора, а ветвь «нет» - между Else и завершающим блок End If (конец если). После выполнения каждого из блоков «да» и «нет» происходит выход за End If, здесь на завершающий макрос оператор End Sub.

Для вычисления арифметического корня из D использована встроенная функция с именем Sqr. После имени функции всегда открывается скобка, затем записывается аргумент, и скобка закрывается.

Первый прогон макроса полезно выполнить в режиме отладки. Конкретные коэффициенты p, q при этом лучше назначить непосредственно в тексте макроса, записав, например, во второй строке:

p = –5: q = 6 'p = Cells (1, 1): q = Cells (1, 2)

Выполняя макрос по шагам (по F8), полезно проследить за изменением значений переменных и последовательностью выполнения операторов при разных исходных данных p, q. Уместно также дополнить ветви «да» и «нет» соответственно операторами: Debug.Print p, q, "Нет корней" и Debug.Print p, q, x1, x2

вывода в окно отладки.

При выполнении отлаженного макроса в ячейки A1, B1 помещаются значения коэффициентов, запускается макрос, и результаты просматриваются в ячейках C1, D1.

Замечания. 1. В блочном условном операторе блок ветви «нет» вместе с ключевым словом Else может быть опущен. Например, для определения количества Kol вычисляемых вещественных корней уравнения из примера 2 подойдёт следующий фрагмент программы

Kol = 0

If D>= 0 Then

Kol = 2

End If

2. Когда блок состоит как здесь из одного оператора, уместно использовать не блочный условный оператор, а «строчный » If:

Kol = 0: If D> = 0 Then Kol = 2

В случае справедливости условия выполнится присваивание Kol = 2, при невыполнении условия - следующий по порядку оператор.

Циклические вычислительные процессы

Пример 3. Вывести таблицу квадратов и кубов натуральных чисел от 2 до 20. Как результат в ячейках листа (и в окне отладки) должны появиться строки с числами:

2 4 8

.........

20 400 8000

Алгоритм решения примера 3 представлен в виде макроса:

Sub NKvKub ()

K = 2: N = 20

Start: L = K ^ 2

M = K ^ 3

Cells (K, 1) = K: Cells (K, 2) = L: Cells (K, 3) = M

Debug.Print K, L, M ‘вывод K, L, M

K = K + 1

If K <= N Then GoTo Start

End Sub

В данном алгоритме переменной K, предназначенной для хранения натуральных чисел от 2 до 20, сначала присваивается начальное значение 2. После вычисления и вывода квадрата и куба числа 2 в операторе присваивания K=K+1 к исходному значению K прибавляется 1 и сумма (3) присваивается этой же переменной, то есть записывается в ту же ячейку памяти, затирая предыдущее значение (2). После этого можно снова выполнять операторы вычисления и вывода - ведь они оперируют с содержимым всё той же ячейки, которое теперь уже увеличилось на единицу. Поэтому в «строчном» операторе If после Then записан оператор безусловного перехода GoTo «метка», передающий управление на оператор L=K^2, отмеченный меткой Start. Но повторять вычисления и вывод нужно пока значение K не превысит предельной величины N=20, поэтому и использован условный переход по ветви «да».

Приведённый выше алгоритм реализует циклический вычислительный процесс (цикл). Для цикла характерны повторения, обведённых на блок-схеме пунктиром операторов, составляющих тело цикла. Для этого цикла характерно изменение как в арифметической прогрессии значения переменной K - счетчика цикла, определяющей число выполненных повторений, от начального значения (здесь от 2) до своего конечного значения (здесь до 20) с шагом (здесь 1).

Поскольку циклы широко используются при программировании, в Бейсике есть специальные операторы для организации циклов.

Для организации цикла примера 3 подойдут операторы For и Next, и с ними макрос NKvKub примет следующий вид:

Sub NKvKub ()

N=20

For K = 2 To N Step 1 ‘Для K от 2 до N с шагом 1:

L = K ^ 2 ‘Тело цикла (начало)

M = K ^ 3

Cells (K, 1) = K: Cells (K, 2) = L: Cells (K, 3) = M

Debug.Print K, L, M ‘Тело цикла (конец)

Next K ‘Следующее K

End Sub

В приведённом макросе оператор For начала цикла организует повторное выполнение тела цикла при значениях счётчика цикла K равного 2, 3, …, N, т, е. изменяющегося от 2 до N с шагом 1. Оператор Next отмечает конец тела цикла. В этом операторе не обязательно указывать имя переменной-счётчика цикла. Если шаг равен (как здесь) единице, то в операторе For его вместе со словом Step можно опустить. После выхода из цикла счётчик цикла равен очередному значению, при котором тело перестанет выполняться. При не нулевом шаге, не позволяющем счётчику цикла достичь предельного значения, тело цикла обходится – не выполняется ни разу.

Использование циклов при накапливании сумм

Пример 4. Для заданного значения N вычислить сумму квадратов натуральных чисел от 1 до N и проверить справедливость формулы: .

Для указанной проверки вычислить сумму, стоящих слева от знака равенства слагаемых, а также значение (обозначенного как C) выражения, стоящего справа от знака равенства, и сравнить их.

Вычисление суммы чисел, указанных слева от знака равенства, можно организовать в цикле со счётчиком цикла K, принимающим значения от 1 до N с шагом 1. При каждом значении K можно вычислять одно слагаемое A= K2 и добавлять его к сумме S, которая сначала должна равняться нулю. Алгоритм вычисления суммы S представлен в виде блок-схемы и текста макроса.

Sub Sum ()

N = 3 ‘Назначить какое-либо значение.

S = 0 ’Подготовка к накапливанию суммы.

For K = 1 to N Step 1

A = K ^ 2

S = S + A ‘Пополнение суммы.

Next K

C = N * (N + 1) * (2 * N+ 1) / 6

Debug.Print N, S, C

End Sub

При выполнении макроса в режиме отладки полезно проследить (наводя курсор на переменную S) то, как пополняется сумма при каждом выполнении оператора S = S + A. Для принятого выше значения N=3 верно вычисленная сумма должна составить 14. Такое же значение C даёт вычисление по формуле. Совпадут ли результаты при иных значениях N?

Пример 5. Значения высоты H и радиуса R из примера 1 заданы начиная с ячеек A2 и B2 в нескольких последовательных строках соответственно столбцов A и B. Требуется: 1) заполнить столбец C, начиная с ячейки C2, значениями объёмов конусов, 2) записать в ячейки D2, E2 и F2 соответственно общий объём всех конусов, количество конусов и средний объём конуса.

Для перебора строк (начиная со второй) и записи вычисляемых значений объёмов уместно применить цикл, однако в общем случае не известно в какой строке оканчиваются данные. Поэтому обработка данных должна вестись до тех пор, пока есть данные в строках. Циклы, которые организуют повторное выполнение тела цикла пока справедливо (или наоборот не справедливо) некоторое условие, называют итерационными циклами. В VBA такие циклы начинает оператор Do (выполнять), за которым могут следовать опции: служебные слова While (пока справедливо) или Until (пока не справедливо) и условие. Конец тела цикла отмечает оператор Loop (петля, цикл), который также может иметь названные опции (только если они не указаны вместе с оператором Do).

Решение 1-ой части примера 5 даёт следующий макрос:

Sub Primer5_1()

i = 2

Do While Cells (i, 1) <> Empty ‘Цикл «Выполнять пока справедливо»

H = Cells (i, 1): R = Cells (i, 2) ‘Назначить конкретные значения H и R

V= H/3*3.14 * R^2: Cells (i, 3) =V ‘Вычислить объём конуса

i = i + 1

Loop

End Sub

В условии оператора цикла Do While проверяется: не равно ли содержимое очередной i-ой ячейки 1-го столбца (столбца A) значению Empty («пусто»)? И пока оно не «пусто» вычисляется объём и увеличивается каждый раз на единицу номер i, указывающий на очередную строку. Цикл Do Until представлен во 2-ой части задачи.

Для решения 2-ой части задачи надо дополнить приведённый макрос накапливанием суммы S и количества k обработанных строк:

Sub Primer5_2()

S=0: k = 0

Do Until Cells (k+2, 1) = Empty 'Цикл «Выполнять пока не справедливо»

H = Cells (k+2, 1): R = Cells (k+2, 2) 'Назначить конкретные значения H и R

V= H/3*3.14 * R^2: Cells (k+2, 3) =V 'Вычислить объём конуса

S = S +V: k = k + 1 'Накапливание суммы и количества

Loop

Cells (2, 4) = S: Cells (2, 5) = k: Cells (2, 6) = S / k ‘Результаты- в D2, E2, F2

End Sub

Можно отметить, что количество k накапливается как сумма S, но в качестве слагаемых к накапливаемому количеству добавляют единицы. В макросе номер обрабатываемой строки получается увеличением количества k на 2.

Пример 6 (задание к лабораторной работе). По списку повторяющихся фамилий абонентов и сумм за каждый телефонный разговор абонента (записанных соответственно в столбцах B и C, начиная с ячеек B3 и C3) вычислить для указанной фамилии абонента: общую сумму к оплате, количество его звонков, среднюю стоимость его звонка.

ʹМакрос - Решение примера 6

Sub Primer6 () '== Образец задания 1 ==

Fam$ ="Котов": i = 3: S=0: Kol = 0:

Do While Cells (i, 2) < > Empty

If Cells (i, 2) = Fam$ Then 'Вычислния для Fam$

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

End If

i = i + 1 'i – номер обрабатываемой строки

Loop

'Запись результатов в B1, D1 и F1:

Cells (1, 2) = S: Cells (1, 4) = Kol: Cells (1, 6) = S / Kol

End Sub

Следует отметить, что в условном блочном операторе, изменение счётчика Kol и суммирование производится лишь тогда, когда содержимое ячейки совпадает с содержимым текстовой переменной Fam$. Здесь текстовая (или строковая – типа String) переменная Fam$ по старым правилам Бейсика отмечена знаком $ на конце. Перед циклом ей присваивается значение текстовой константы с фамилией (здесь "Котов"). Можно сделать макрос более универсальным, если настроить его на считывание по оператору Fam$ = ActiveCell.Value фамилии, как значения активной (т.е. выделенной) ячейки. В этом случае макрос удобно запускать по сочетанию клавиш или клику по назначенной макросу фигуре, выделив предварительно ячейку с фамилией. В разделе «3.Дополнительные сведения» этот вопрос решён в общем виде – ячейки с фамилиями в процессе анализа просто указываются (выделяются) мышкой.

2. Дополнительные сведения

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

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 Value), т.е. – копирует, в объект 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. После чего это новое название появится в заголовке формы.

К объекту UserForm можно добавлять элементы управления с панели Toolbox. Для наших целей подойдёт 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 выводятся символы «Вкл» - включен анализ, и можно выделять ячейку с фамилией. После её выделения процедура модуля выполняет вычисление общей суммы, определяет количество вызовов и среднюю стоимость одного звонка указанного абонента, а также заполнение результатами ячеек B1, D1 и F1 (см. рис. 6.4). Фамилия выводится в H1. Кроме того будет выведена форма с фамилией и итоговой суммой. Закрыв форму, можно снова выделить ячейку с фамилией и получить суммарные показатели для этой фамилии. После выделения пустой ячейки процесс анализа завершается, а в ячейку $A$2 выводятся символы «Выкл» - анализ выключен.

Задания.

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

Вариант 13. Для указанной фамилии в списке спонсорских пожертвований (за год) определить: число пожертвований и среднюю сумму одного пожертвования.

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