Программирование макросов для электронной таблицы

Лекция 5.1

Программирование макросов для электронной таблицы

Макрос (от англ. macros, мн.ч. от macro) — программный объект, при обработке «развёртывающийся» в последовательность действий и (или) команд.

Во многих офисных продуктах, например, Microsoft Office, OpenOffice.org и др., в текстовых редакторах при обработке макроса автоматически выполняется заданная для каждого макроса последовательность действий — нажатия на клавиши, выбор пунктов меню и т. д. Предоставляется интерфейс для записи новых и перезаписи существующих макросов.

Макросы используются для автоматизации действий в OpenCalc.org. Макрос может автоматизировать такие действия, который иначе потребовали бы длительных ручных манипуляций с возможными ошибками.

В OpenOffice.org имеется возможность разрабатывать макросы на разных языках программирования, таких как Python, JavaScript. Но мы воспользуемся OpenOffice.org Basic.  Это связано с тем, что этот язык более похож на макроязык Microsoft Visual Basic for Application (VBA) и после его освоения вы сможете без труда разобраться с подобными задачами в Microsoft Office. Поэтому рассмотрим основные моменты использования OpenOffice.org Basic при создании макросов.

Задание переменных

В OpenOffice.org Basic объявление переменных может быть неявным. Объявить переменную можно с помощью инструкции Dim. Можно объявлять несколько переменных одновременно, если разделить их имена запятыми. Для определения типа переменной служит либо знак объявления типа после имени, либо соответствующее ключевое слово (см. таблицу 5.1 ).

Таблица 5.1

Примеры объявлений переменных

Типы переменных

В OpenOffice.org Basic предусмотрена поддержка четырех классов переменных:

• числовые переменные могут содержать числовые значения. Некоторые переменные служат для хранения больших или малых чисел, остальные используются для чисел с плавающей точкой или дробей;
• строковые переменные содержат строки символов;
• логические переменные содержат одно из значений: TRUE или FALSE;
• объектные переменные могут содержать объекты различных типов, например таблицы и документы внутри документа.

В OpenOffice.org Basic есть одно- и многомерные массивы, определяемые с помощью заданного типа переменной. Массивы применяются для изменения списков и таблиц в программах. К отдельным элементам массива можно обращаться с помощью числового индекса.

Массивы необходимо объявлять с помощью инструкции Dim. Для определения диапазона индексов массива используется несколько способов (см. таблицу 5.2).

Таблица 5.2

Диапазон индексов может включать положительные и отрицательные числа.

Константы имеют фиксированное значение. Они определяются в программе только раз и не могут быть переопределены позднее.

CONST ConstName=Expression

Библиотеки

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

Библиотека может включать до 16000 модулей.

Модули

Наряду с объявлениями переменных модуль содержит процедуры и функции. Длина программы, которую можно сохранить в модуле, ограничена 64 КБ. Если требуется память большего объема, можно разделить проект OpenOffice.org Basic между несколькими модулями и затем сохранить их в одной библиотеке.

Модули диалоговых окон

Модули диалоговых окон могут содержать определения диалоговых окон, в том числе свойства диалогового окна, свойства каждого элемента диалогового окна и назначенные события. Так как модули диалоговых окон могут содержать только по одному диалоговому окну, они часто упоминаются как диалоговые окна.

Подпрограмма (Sub) используется для управления конкретной задачей в программе. Подпрограммы служат для разбиения задачи на отдельные процедуры. Разбиение программы на процедуры и подпроцедуры улучшает читаемость и уменьшает возможность возникновения ошибок. Подпрограмма по возможности использует некоторые параметры как аргументы, но не возвращает значения вызывающей подпрограмме или функции, например:

DoSomethingWithTheValues(MyFirstValue,MySecondValue)

Функция, по существу, является подпрограммой, возвращающей значение. Функцию можно использовать в правой части описания переменной или в других местах, где обычно используются значения, например:

MySecondValue = myFunction(MyFirstValue)

Операция ветвления

Инструкция If...Then используется для выполнения участков программы в зависимости от заданных условий. Когда OpenOffice.org Basic встречает инструкцию If, то проверяется логическое условие. Если оно равно True (истине), то выполняются все дальнейшие инструкции вплоть до следующей инструкции Else (лож) или ElseIf. Если это условие равно False и встречается инструкция ElseIf, то OpenOffice.org Basic проверяет следующее условие и выполняет дальнейшие инструкции, если это условие равно True. Если оно равно False, программа переходит к следующей инструкции ElseIf или Else. Инструкции после Else выполняются только в том случае, если ни одно из предыдущий условий не было равно True. После того как все условия проверены и соответствующие инструкции выполнены, программа переходит к инструкции, следующей за EndIf.

Инструкции If...Then можно вкладывать одну в другую.

Else и ElseIf — необязательные инструкции.

Пример:

If a>0 Then

MsgBox “a>0”

End If

Инструкция Do...Loop [время выполнения]

Повторяет инструкции между инструкцией Do и Loop, пока условие равно True или пока условие не станет равно True.

Синтаксис

Do [{While | Until} Условие = True]

Участок_программы

[Exit Do]

Участок_программы

Loop

или

Do

Участок_программы

[Exit Do]

Участок_программы

Loop [{While | Until} Условие = True]

Лекция 5.2

Программирование макросов для электронной таблицы. Продолжение

Параметры/Элементы

Условие. Сравнение, числовое или строковое выражение, которое вычисляется либо как True, либо как False.

Участок_программы. Инструкции, которые нужно повторять, пока условие равно True или пока условие не станет равно True.

Инструкция Do...Loop выполняет цикл, пока определенное условие равно True или пока оно не станет равно True. Условие для выхода из цикла должно быть введено после инструкции Do или Loop. В следующих примерах представлены допустимые комбинации:

Синтаксис

Do While Условие = True

участок_программы

Loop

Участок программы между инструкциями Do While и Loop повторяется, пока условие истинно.

Do Until Условие = True

участок_программы

Loop

Участок программы между инструкциями Do Until и Loop повторяется, пока условие ложно.

Do

участок_программы

Loop While Условие = True

Участок программы между инструкциями Do и Loop повторяется, пока условие истинно.

Do

участок_программы

Loop Until Условие = True

Участок программы между инструкциями Do и Loop повторяется, пока условие не станет истинно.

Инструкция Exit Do используется для безусловного завершения цикла. Эту инструкцию можно добавлять в любое место инструкции Do...Loop. Также можно определить условие выхода, используя структуру If...Then следующим образом:

Do...

инструкции

If Условие = True Then Exit Do

инструкции

Loop...

Инструкция For...Next [время выполнения]

Повторяет инструкции между For и Next указанное число раз.

Синтаксис:

For Счетчик=Начало To Конец [Step Шаг]

Участок_программы

[Exit For]

Участок_программы

Next [Счетчик]

Переменные:

Счетчик. Счетчику циклов первоначально присваивается значение справа от знака равенства (Начало). Допустимы только числовые переменные. Счетчик циклов увеличивается или уменьшается в зависимости от значения переменой Шаг до достижения значения Конец.

Начало. Числовая переменная, которая определяет исходное значение счетчика в начале цикла.

Конец. Числовая переменная, которая определяет последнее значение счетчика в конце цикла.

Шаг. Задает значение, на которое увеличивается или уменьшается счетчик цикла. Если параметр Шаг не указан, счетчик цикла увеличивается на 1. В этом случае параметр Конец должен быть больше, чем параметр Начало. Если нужно уменьшать параметр Счетчик, параметр Конец должен быть меньше параметра Начало, а параметру Шаг необходимо присвоить отрицательное значение.

Цикл For...Next повторяет все инструкции в цикле число раз, определяемое параметрами.

По мере изменения переменной счетчика OpenOffice.org Basic проверяет, достигнуто ли значение Конец. После прохождения счетчиком конечного значения цикл автоматически завершается.

Инструкции For...Next могут быть вложенными. Если переменная, следующая за инструкцией Next, не указана, инструкция Next автоматически ссылается на самую последнюю инструкцию For.

Если задается приращение 0, инструкции между For и Next повторяются непрерывно.

При отсчете переменной счетчика OpenOffice.org Basic проверяет переполнение или потерю значимости. Цикл завершается, если значение Счетчик превышает значение Конец (положительное значение Шаг) или становится меньше значения Конец (отрицательное значение Шаг).

Инструкция Exit For используется для безусловного завершения цикла. Эта инструкция должна находиться внутри цикла For...Next. Используйте инструкцию If...Then для проверки условия выхода следующим образом:

For...

инструкции

If Условие = True Then Exit For

инструкции

Next

Примечание. Во вложенных инструкциях For...Next, если производится безусловный выход из цикла с помощью инструкции Exit For, происходит выход только из одного цикла.

Создание макроса

Чтобы создать свой макрос в OpenOffice.org не обязательно быть программистом. В OpenOffice.org имеется встроенная программа регистратор которая может запоминать все действия пользователя с документом и записывать их последовательно в специальный текстовый модуль называемый.

Для автоматического создания макроса необходимо выполнить следующие действия: выбрать команду Сервис → Макросы→ Записать макрос. Откроется маленькое диалоговое окно Запись макроса с одной только кнопкой «Завершить запись» (см. рис. 10).

Рис. 1

Затем выполнить действия, которые хотите записать в документе. Нажать клавишу ESC, чтобы снять выделение объекта, поскольку в настоящее время макрорегистратор не записывает это действие, произведенное щелчком мыши. Нажать кнопку «Завершить запись».

После этого откроется диалоговое окно Макрос, в котором можно сохранить и выполнить макрос (см. рис. 11).

Рис. 2

При необходимости можно создать новую библиотеку или новый модуль.

Ограничения макрорегистратора

Следующие действия не регистрируются:

• открытие окон не регистрируется;
• действия, выполненные в других окнах (где регистратор не запущен), не регистрируются;
• переключение окон не регистрируется;
• действия, не связанные с содержимым документа, не регистрируются. Например, изменения, внесенные в диалоговое окно "Параметры", организатор макросов, настройки;
• выделения регистрируются только в том случае, если они выполняются с помощью клавиатуры (перемещением курсора), но не регистрируются при использовании мыши;
• макрорегистратор работает только в OpenCalc.org и OpenWriter.org.

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

Практическая работа 5.1

Автоматизация работы в OpenOffice Calc

Задание 5.1. Автоматическое создание макроса.

Порядок работы:

• запустим программу OpenCalc.org;
• переименуем лист Sheet 1 в Макрос;
• выберем команду Сервис  Макросы  Записать макрос;
• откроется маленькое диалоговое окно Запись макроса с одной только кнопкой «Завершить запись» (рис. 5.1).

Рис. 5.1

• выполним действия, которые хотим записать в документе. Нажмем клавишу ESC, чтобы снять выделение объекта, поскольку в настоящее время макрорегистратор не записывает это действие, произведенное щелчком мыши;
• нажмем кнопку «Завершить запись»;
• откроется диалоговое окно Макрос, в котором можно сохранить и выполнить макрос: рис. 5.2;

Рис. 5.2

• если нужно прервать запись, не сохраняя макрос, необходимо нажать кнопку Закрыть в диалоговом окне Макрос;
• сохраним макрос в файле, сначала в списке Сохранить макрос, выберем объект Zadanie_5, в котором хотим сохранить макрос;
• если нужно сохранить макрос в новой библиотеке или модуле, необходимо нажать кнопку Новая библиотека или Новый модуль и ввести имя библиотеки или модуля;
• в текстовом поле Название макроса введем имя нового макроса;
• нажмем кнопку «Записать»;
• сохраним выполненное задание, задав имя файлу Practica5.

Задание 5.2. Автоматическое создание макроса для автозаполнения данных.

Порядок работы:

• запустим программу OpenCalc.org;
• откроем файл Practica5;
• переименуем лист Sheet 2 в Макрос1;
• заполним таблицу
• как на рис. 5.3;

Рис. 5.3

• выполним команду Сервис → Макросы → Записать макрос;
• с помощью автозаполнения заполним первую колонку до 10;
• нажмем кнопку «Записать»;
• сохраним макрос с именем «Zadanie1», см. рис. 5.4;
• очистим в первой колонке ячейки до исходного состояния, т.е. оставим заполненными только первую и вторую ячейки колонки;

Рис. 5.4

• установим курсор в ячейку A1 и выполним команду Сервис  Макросы Выполнить макрос;
• в открывшемся окне «Выбор макроса» найдем наш макрос и нажмем кнопку «Выполнить», Библиотека  Мои Макросы Standart→Module1;
• в результате у нас должны автоматически заполнится ячейки, с 3 по 10. Далее мы усовершенствуем свой макрос, добавив ему возможность автоматически заполнять нужное количество ячеек, методом автозаполнения, в той колонке, в которой установлен курсор;
• запустим редактор макросов командой Сервис  Макросы  Управление макросами  OpenOffice.org Basic;
• в диалоге Макрос выберем макрос «Zadanie1» и нажмем кнопку редактировать (рис. 5.5);

Рис. 5.5

• в окне редактора найдем процедуру Sub Zadanie1, именно эта процедура запускается на выполнение, когда вы запускаете макрос;
• отредактируем процедуру примерно как в листинге 5.1 (комментарии писать не обязательно);

Листинг 5.1

sub Zadanie1

rem ----------------------------------------------------------------------

rem Блок объявления переменных

dim document as object 'Ссылка на обьект документ

dim dispatcher as object 'ссылка на объект сервис UNO

dim Selection As object 'Ссылка на выделенную область в документе

dim CurRowNumber as Long 'Номер строки на которой стоит курсор

dim EndRowNumber as Long 'Переменная в которую записывается введённое значение

dim StartCell As String 'Текстовая ссылка на текущую ячейку

dim EndCell As String 'Текстовая ссылка на последнюю ячейку

rem ----------------------------------------------------------------------

rem Здесь определяем переменные для доступа к документу

document = ThisComponent.CurrentController.Frame 'Текущий документ

dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

Selection = document.Controller.Selection 'Выделение

' Номер текущей строки (счёт с нуля)

CurRowNumber= Selection.CellAddress.Row ' Имя текущей колонки

CurColumnName= Selection.Columns.ElementNames(0)

' Методом конкатенации формируем адрес ячеек

StartCell= "$" & CurColumnName & "$" & CStr(CurRowNumber+1) 'Начальная ячейка

'Просим пользователя ввести число строк и прибавляем это число к номеру текущей строки

EndRowNumber= CurRowNumber+CLng(InputBox("Введите число :","Ввод количества строк (Max 65536)","10"))

'адрес последней ячейки

EndCell= "$" & CurColumnName & "$" & CStr(EndRowNumber)

rem ----------------------------------------------------------------------

dim args1(0) as new com.sun.star.beans.PropertyValue

args1(0).Name = "ToPoint"

'Закомментируем значение введённое автоматически

'и заменим нашей переменной для начальной ячейки

args1(0).Value = StartCell '"$A$1:$A$2"

'Задание стартовой позиции

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------

dim args2(0) as new com.sun.star.beans.PropertyValue

args2(0).Name = "EndCell"

'Заменяем значение на переменную конечной ячейки

args2(0).Value = EndCell '"$A$10"

'Запускаем функцию автозаполнения

dispatcher.executeDispatch(document, ".uno:AutoFill", "", 0, args2()

rem Закомментируем лишние инструкции ----------------------------------

'rem dim args3(0) as new com.sun.star.beans.PropertyValue

'args3(0).Name = "ToPoint"

'args3(0).Value = "$A$1:$A$10"

'dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

end sub

• cохраним полученный макрос;
• для назначения макросу комбинации клавиш выполним команду Сервис Настройка и откройте вкладку Клавиатура;
• в открывшемся диалоге в поле Категория выделим модуль, в котором расположен макрос «Zadanie1», в поле Функция выделим макрос, после чего в поле Комбинация клавиш выберем свободную комбинацию и нажмите кнопку «Заменить» (рис.5.6);

Рис. 5.6

• нажмем кнопку ОК;
• используя комбинацию клавиш назначенную на макрос заполним список дней недели и месяцев года (рис. 5.7);

Рис 5.7

• сохраним выполненное задание.

Задание 5.3. Ручное задание макроса.

Порядок работы:

• запустим программу OpenCalc.org;
• откроем файл Practica5;
• переименуем лист Sheet 3 в Макрос2;
• откроем редактор OOo Basic, используя меню Сервис  Макросы Управление макросами  OpenOffice.org Basic, чтобы начать диалог макросов Macro. С левой стороны окна диалога найдем документ, который Вы только что открыли. Новый документ, вероятно, назван “untitled1” или Безымянный1. Кликним (нажмите левую клавишу мыши) справа ниже от “untitled1” на слове “standard”. Нажмем кнопку «Создать», справа, для создания нового модуля;
• использование имени “Module1”, вероятно, не лучшее решение. Когда у нас открыто несколько документов, и все они имеют модуль с именем “Module1”, то становится трудно работать с ними. Лучше назовем наш первый модуль “MyFirstModule”. Откроется среда редактирования и отладки макросов OOo Basic IDE . Введем текст, приведенный в Листинг 5.2;

Листинг 5.2

• Sub Main
• Print "OpenCalc"

• End Sub
• кликнем на кнопке с зеленым треугольничком ("Выполнить Basic") в верхней панели для выполнения нашего первого макроса OOo Basic;
• сохраним выполненное задание.

Практическая работа 5.2

Автоматизация работы в OpenOffice Calc.Продолжение

Задание 5.4. Написать программу на OpenOffice.org Basic для функции пользователя, которая вычисляет получаемую потребителем электроэнергию QH по количеству электроэнергии Q, которое вырабатывается производителем, и величине потерь P (%) при ее передаче: QH=Q(100-P)/100.

Порядок работы:

• запусти программу OpenCalc.org;
• откроем файл Practica5;
• создадим новый лист Макрос3;
• заполним таблицу исходя из данных, приведенных на рис. 5.8;
• заполним ячейку С2 следующей таблицы с использованием построенной функции пользователя;

Рис. 5.8

• вызовем редактор OOo Basic: Сервис®Макросы®Управление макросами®OpenOffice.org Basic. Выберем в диалоге выбора макроса пункт соответствующий названию документа, и нажмём кнопку «Создать». В появившемся окне напишем следующий текст программы: Листинг 5.3

Листинг 5.3

Function QH(Q, P)

QH=Q*(100-P)/100

End Function

• вернемся в окно Максрос3, и при помощи мыши выберем ячейку С2, в которую необходимо внести число, вычисляемое по только что созданной пользовательской функции. Для этого щёлкнем левой кнопкой мыши в поле ввода формул (которое находится выше ячеек таблицы, после кнопок обозначенных f(x) ? = ) и напишем следующую строку :=QH(A2;B2), где QH название только что написанной функции, A2 адрес ячейки для аргумента Q, B2 адрес ячейки для аргумента P. После этого нажмем клавишу Enter на клавиатуре. В результате в ячейку С2 запишется значение функции QH: рис. 5.9

Рис. 5.9

• сохраним выполненное задание.

Задание 5.5 Написать программу на OpenOffice.org Basic для следующей функции пользователя, используя ее дополнить таблицу вычисленными значениями и построить график зависимости y=y(x):

Таблица

Порядок работы:

• запустим программу OpenCalc.org;
• откроем файл Practica5;
• создадим новый лист Макрос4;
• вызовем редактор OOo Basic: Сервис®Макросы®Управление макросами®OpenOffice.org Basic. Выберем в диалоге выбора макроса пункт соответствующий названию документа, и нажмём кнопку «Создать». В появившемся окне напишем следующий текст программы: Листинг 5.4;

Листинг 5.4

Function FY(x)

a=1+Abs(0.2-x)

b=1+x+x*x

FY=a/b+Sin(x)

FY=FY+Log(x+2)

FY=FY-Atn(x^3+1)

FY=FY+Exp(-x)-Tan(x^3.13)

FY=FY+Sqr(x)+Cos(x+1)

End Function

• теперь подготовим таблицу. В первый столбец введём подписи к данным. Далее в первой строке заполним диапазон изменения независимой переменной X. Для этого введём вручную два первых значения переменной X, далее воспользуемся методом автоматического заполнения ячеек числовым рядом, соседние члены которого отличаются на постоянную величину;
• далее введём формулу в ячейку В2: =FY(В1), скопируем формулу в остальные ячейки таблицы:

Рис. 5.10

• для построения графика зависимости y=y(x) выберим Вставка®Диаграмма. Выберим тип диаграммы «Линии», «Только точки». Нажмем кнопку далее, и в поле вводу «Диапазон данных» введите диапазон $A$1:$J$2, отметьте пункт «Ряды данных в строках» и включите пункты «Первая строка как надпись», «Первый столбец как надпись». Нажмем кнопку «Далее» два раза. В заголовке ось X введем символ X, а ось Y введем символ Y. В результате получите график функции: рис. 5.11.

Рис. 5.11.

• сохраним выполненное задание.

Задание 5.6 Оператору, принимающему заказы на доставку пирожков по телефону, необходимо быстро сформировать бланк заказа, рассчитать стоимость заказа с учетом цен, скидок и других данных, распечатать бланк.

В поля для ввода текста вносится информация о заказчике. В определенных ячейках введено текущее время и дата заказа. Данные об ассортименте продукции внесены в обычный список, содержащий три колонки (номер по порядку, наименование, цена). Различные варианты скидок или надбавок можно внести в определенные ячейки, а можно использовать прямо в формулах. Список выбора (форма) формируется по диапазону ячеек с наименованием пиццы. Различные опции, скидки, надбавки указываются с помощью переключателей или флажков. Очистка бланка делается нажатием кнопки.

Порядок работы:

• запустим программу OpenCalc.org;
• откроем файл Practica5;
• создадим новый лист Макрос4;
• для вставки элементов управления можно использовать панель инструментов Tools или Элементы управления, если ее нет, воспользуетесь командой Вид→Панель инструментов→Элементы управления (рис. 5.12-5.13);

Рис. 5.12

Рис. 5.13

• при компоновке документа удобно визуальное проектирование формы. Для этого на панели инструментов нажмем на кнопку ;
• затем, выбрав необходимый элемент управления расположим его в нужном месте таблицы. Например, мы хотим использовать флажок для указания срочности доставки. Если флажок включен – доставка срочная, за срочность установлена надбавка от стоимости заказа;
• выберем . Определим его в нужное место документа. Затем в контекстном меню выберем Элемент управления;
• во вкладке Общие определяем текст подписи, цвет фона: рис. 5.14;
• во вкладке Данные устанавливаем привязку к какой-нибудь ячейке электронной таблицы;
• удобно все возвращаемые из элементов управления значения связывать с ячейками одного столбца, который потом можно скрыть;

Рис. 5.14

• адрес связанной ячейки нужно вводить вручную;
• если во вкладке данные не указывать значение индекса, например 1, если флажок установлен и 0, если флажок снят, то в связанной ячейке будет значение ИСТИНА, если флажок поставлен, или ЛОЖЬ, если флажок снят: рис.5.16.

Рис. 5.15

• для установки переключателей сначала нажимаем на кнопку More Controls на панели Элементов управления и выбираем Окно группы: рис.5.17;

Рис. 5.16

• можно воспользоваться Мастером форм для определения нужных данных: рис.5.18;

Рис. 5.17

• далее указываем, какие значения будут при выборе одного из переключателей, и как будет называться окно группы;
• затем обязательно в контекстном меню выбираем Элемент управления и во вкладке Данные указываем связанную ячейку. Для обоих переключателей связанная ячейка одна, поэтому она принимает значения 1 или 2 в зависимости от выбора переключателя: рис.5.19. Особенность состоит в том, что возвращаемые значения индекса при установке флажка и номер установленного переключателя задаются в текстовом формате;

Рис. 5.18

• для того, чтобы раскрывался список имеющихся видов продукта, выбираем на панели инструментов элемент Список;
• необходимо в свойствах списка (вкладка Данные) указать связанную ячейку и диапазон ячеек, по которому список формируется. Адреса ячеек вводятся вручную. Исходный диапазон ячеек - это таблица с наименованием пиццы и ценой. Во вкладке Общие можно указать цвет фона, обрамление. Удобно таблицу, по которой формируется диапазон для списка расположить на другом листе документа;
• сохраним выполненное задание.