Программирование в MS ( москс 7 ...library.miit.ru/methodics/2696.pdf · vba...
Transcript of Программирование в MS ( москс 7 ...library.miit.ru/methodics/2696.pdf · vba...
JVI. X .№2606
I 'iCUUBd 1 П уч£Программирование в MS (
7 >СИТЕТмоскс
Кафедра «Путевые, строительные машины и робототехнические комплексы»
Т.Н. Глебова, Н.А Зайцева
Программирование в MS Office Excel на Visual Basic
Рекомендовано редакционно-издательским советом университета в качестве методических указаний к курсовой и индивидуальной работам
Для студентов специальностей 190205 «Подъемно - транспортные, строительные, дорожные
машины и оборудование» и 220402 «Роботы и робототехнические системы»
Москва - 2007
УДК 004 Г -5 3
Глебова Т.Н., Зайцева Н.А. Программирование в MS Office Excel на Visual Basic: Методические указания. - М.: МИИТ, 2007.- 48 с.
В методических указаниях рассмотрены основы работы с программой MS Office Excel, создание макросов в Excel, среда и основные команды языка программирования Visual Basic for Application. Приведены примеры, иллюстрирующие применение рассмотренных команд- Изложение материала в методических указаниях ориентировано на студентов, выполняющих курсовые работы по дисциплинам «Информатика» и «Решение на ПЭВМ уравнений динамики механических систем».
© Московский государственный университет путей сообщения (МИИТ), 2007
Подписано в печать 2Q, /2 . - Формат 60x84/16Тираж - 100 экз. Усл.-печ.л. - St 8 Заказ 68^-, Изд.№ 178-07
Введение.Первое поколение языков программирования высокого
уровня было языками процедур, с помощью которых программисты реализовывали алгоритм, используя при этом переменные (зарезервированные места в памяти). Каждая процедура, назначением которой было выполнение определенной функции в рамках алгоритмической реализации решения задачи, работала с некоторым подмножеством множества всех данных программы. Эти подмножества пересекались между собой произвольным образом. Когда задачи и программы усложнились (вслед за стремительным ростом отношения производительность/стоимость микропроцессоров), начался резкий рост проблем поддержки программного продукта: изменения в составе исходных данных программы вело к изменению заранее непредсказуемого количества зависимых от этого состава процедур.
В объектно-ориентированном программировании (ООП) данные вместе с процедурами хранятся в классе. Класс определяет общие для всех его объектов методы и свойства. Свойства представляют собой характеристики объекта (цвет, размер шрифта, название, положение на экране и т.п.). Методы - это программные процедуры, реализующие некоторый алгоритм, который определяет взаимодействие объектов класса с внешней средой. Объект, с одной стороны обладает определенными свойствами, а, с другой стороны, над ним возможны операции (методы), которые приводят к изменению этих свойств. Это свойство объединения в объекте его свойств и методов называется инкапсуляцией.
Понятие ООП включает также возможность наследования. Наследование - это возможность сопоставления с создаваемым классом одного или даже нескольких уже создан-
ных классов в качестве родительских. Все члены родительских классов будут при этом являться и членами создаваемого класса, в котором они, обычно, переопределяются в соответствии с его характеристиками.
Наследование обеспечивает один из способов реализации третьего принципа ООП - полиморфизма, т.е. возможности использовать одноименные методы для выполнения однотипных действий у объектов разных классов.
Visual Basic for Applications (VBA) относится к языкам ООП. Программирование на VBA можно рассматривать как управление объектами Excel (книгами, листами, диапазонами ячеек).
Изложение материала в методических указаниях предполагает знакомство читателей с основами информатики и принципами программирования, наличие навыков работы в текстовом редакторе Word. Изложение материала по VBA ориентировано на студентов, выполняющих индивидуальные и курсовые работы по дисциплине «Решение на ПЭВМ уравнений динамики механических систем». При выполнении этих работ перед студентами стоит задача научиться решать технические задачи в области исследования динамики механических систем. В работе для ввода исходных данных и построения графиков используется среда Excel, а решение дифференциальных уравнений методом Рунге- Кутта оформляется в виде макроса на VBA. Поэтому в методических указаниях в основном рассматриваются способы создания и редактирования макросов и команды языка программирования VBA. Т.к. при выполнении работы не требуется создание форм, то они рассматриваются очень кратко. Материал по MS Excel предназначен также для студентов первого курса, выполняющих курсовую работу по дисциплине «Информатика».
Как в любой программе фирмы MS в Excel и VBA можно добиться результата разными способами (например, ис
пользуя кнопки главного меню, контекстное меню, сочетание клавиш). В методических указаниях из-за ограничений по объему рассмотрен какой-либо один из вариантов.
При описании команд VBA в методических указаниях приняты следующие обозначения:1) ключевые слова при задании общего вида (формата) оператора, команды или функции выделены жирным шрифтом;2) курсив используется для обозначения операндов и параметров;3) необязательные элементы команды, заключены в квадратные скобки;4) многоточие означает возможность повторения элемента конструкции команды;5) обязательные пробелы обозначены знаком подчеркива-
1. Excel1.1. Основные понятия Excel.
1.1.1. Рабочая книга и лист, ячейка.Основой структуры Excel является рабочая книга - ос
новной документ, который используется для хранения и обработки данных. По умолчанию приложение запускается с открытым в нем документом Книга 1, который имеет три рабочих листа.
Интерфейс Excel состоит из шести областей:- окно рабочей книги (рабочий лист);- строка меню;- панелей инструментов (например, панель форматирования, стандартная, рисование и т.п.);- строка формул;- строка состояния.
Главное меню и состав кнопок на панелях инстру-
ментов во многом аналогичнысоответствующим элементам текстового редактора Word, поэтому в методических указаниях рассматриваются только их некоторые элементы.
Каждый рабочий лист может содержать определённые данные. Количество рабочих листов в книге можно изменять (добавлять, удалять). Все рабочие листы, входящие в одну книгу, хранятся вместе в едином файле.
Рабочий лист (или рабочее поле) Excel поделен вертикальными и горизонтальными линиями на прямоугольные ячейки. Ячейки, объединённые по вертикали, образуют столбцы, а по горизонтали - строки. Ячейка может содержать какое-либо значение (число, дату, текст) или формулу. Каждый рабочий лист состоит из 256 столбцов и 65 536 строк.
Каждая ячейка имеет свой адрес (ссылку на ячейку), то есть указание на столбец и строку, в которых находится ячейка.
В Excel по умолчанию используется стиль А1 адресации ячеек. Это когда по столбцам используется алфавит, а по строкам цифры. Столбцы обозначены буквами от А до Z, далее - от АА до AZ, затем - ВА до BZ и т.д. до столбца JV. Эти обозначения отображаются вверху окна документа. Это заголовок столбца. Строки пронумерованы сверху вниз от 1 до 65 536 с левой стороны окна. Это номер строки. Например, D10 - это десятая строка в колонке D. Есть стиль, называемый R1C1, который удобен при вычислении позиции строки и столбца в макросах, а также при отображении относительных ссылок. В стиле R1C1, после буквы "R" указывается номер строки ячейки, после буквы "С" номер столбца, относительно активной ячейки. Например, R9C2 - это ячейка в 9-ой строке 2-го столбца. При работе стили адресации переключаются в меню Сервис => Параметры => Общие => Стиль ссылок, но при программировании на
VBA удобно пользоваться не этими стилями, а именами ячеек. Тогда работа с ячейкой будет похожа на работу с обычной переменной. Для того, чтобы дать имя ячейке, надо навести на неё курсор, выбрать команды меню Вставка = > Имя => Присвоить. Появится диалоговое окно, куда надо ввести имя, присваиваемое ячейке, и нажать кнопку ОК.
Ячейка, обрамлённая чёрной рамкой, является активной. Если начать ввод данных, то они появятся в этой ячейке. Чтобы ввести данные в ячейку, нужно сделать её активной, указав на нее мышкой или с помощью клавиатуры.
Строка формул - это длинная белая полоса над заголовками колонок в листе, в ней выводится формула из активной ячейки. Строка формул состоит из трёх частей:
1) правая часть строки представляет собой поле, которое служит для отображения и редактирования содержимого текущей ячейки. Для перехода в режим редактирования нужно щёлкнуть мышью в нужном месте формулы в правой части строки формул или нажать клавишу F2. Чтобы установить режим редактирования в ячейке, нужно выполнить на ней двойной щелчок, вследствие чего в ячейке появится курсор ввода. При редактировании формул в ячейке или в строке формул адреса ячеек, участвующих в вычислении, выделяются различными цветами, а сами ячейки окружаются рамочками того же цвета, что и соответствующие им ссылки в формуле.
2) средняя часть строки формул состоит из трёх кнопокдля обработки содержимого ячейки- левая кнопка служит для отмены последнего действия (Esc),- средняя кнопка служит для подтверждения ввода данных или изменения содержимого ячейки (Enter),- с помощью правой кнопки можно активизировать «Мае-
тер функций» для использования в формулах встроенных функций;
3) в левой части строки формул расположено поле “Имя”, в котором отображается адрес или имя активной ячейки, размер выделенного диапазона.
1.1.2. Копирование и перемещение данных.Выделение ячеек производится с целью обозначения
диапазона или ячейки, к которым должна относиться следующая команда или функция. Выделенный на экране диапазон всегда имеет прямоугольную форму. Наименьшая структурная единица выделенного диапазона - ячейка. Активная ячейка считается выделенной. Все выполняемые команды относятся только к активной ячейке или к выделенному диапазону.
Если щёлкнуть по заголовку столбца, то этот столбец будет выделен. Если щёлкнуть по заголовку строки, то будет выделена эта строка.
Для выделения нескольких столбцов или строк необходимо указать столбец (строку) начала выделяемого диапазона и, удерживая левую клавишу мыши, указать на конец диапазона.
Выделение несмежных диапазонов осуществляется указанием диапазонов при нажатой клавише Ctrl. Следует обратить внимание на то, что на выделенные несмежные диапазоны распространяется действие не всех команд, например, эти диапазоны нельзя перемещать и копировать.
Чтобы скопировать данные с помощью мыши, нужно:1) выделить диапазон ячеек, который требуется скопировать;2) поместить указатель мыши на рамку выделенного диапазона;3) удерживая клавишу Ctrl, нажать левую клавишу мыши и перетащить выделенный диапазон в новую область рабочего листа. Во время перемещения указателя мыши Excel
отображает контур, указывающий размер ипозицию копируемых данных;4) отпустить кнопку мыши, когда закончится копирование.
Перемещение данных происходит аналогично, но указатель мыши должен иметь вид пересекающихся разнонаправленных стрелок без нажатия каких-либо клавиш. Также можно производить копирование с помощью пункта Правка главного меню или Контекстного меню, появляющегося при нажатии правой кнопки мыши. Строки и столбцы можно добавлять, копировать, удалять из них данные (Правка - Очистить, Правка - Удалить) или полностью строку или столбец (Правка - Вырезать).
При использовании режима автозаполнения диапазона ячеек рядом чисел с заданным шагом необходимо:1) ввести начальное значение в первую ячейку диапазона, во вторую ячейку ввести следующее значение ряда.2) выделить диапазон, содержащий введённые числа;3) протащить маркер автозаполнения (крестик в правом нижнем углу на рамке выделенного диапазона) через диапазон смежных ячеек, который нужно заполнить;4) отпустить кнопку мыши. Excel заполнит выделенный диапазон ячеек числовыми значениями с шагом равным разности второго и первого значения.
Для форматирования нужно выделить ячейку с некоторыми данными и обратиться к команде Формат - Ячейки. В окне формат ячеек можно изменить тип и цвет шрифта, цвет заливки, представление чисел, дат, времени и другие параметры ячейки и данных в ней. Можно также изменить размеры столбца (ширину) и строки (высоту).
1.2. Использование формул.Формула - это суть Excel, ведь назначение Excel, в ос
новном, это выполнение всевозможных операций с различными данными. Формулы могут быть построены с помо-
щью символов арифметических операций(сложение +, вычитание, умножение *, деление /, возведение в степень Л), встроенных функций, которых в Excel несколько сотен, и данных, расположенных в ячейках рабочего листа.
1.2.1. Запись формул в Excel.Ввод любой формулы начинается со знака а затем
следует запись формулы. Требования к записи формул аналогичны требованиям к записи выражений в языках программирования высокого уровня.
Арифметические операторы имеют следующий приоритет:1) в первую очередь выполняются выражения внутри скобок;2) возведение в степень, умножение и деление имеют более высокий приоритет, чем сложение и вычитание;3) операторы с одинаковым приоритетом выполняются слева направо.
Каждой открывающейся скобке в формуле должна соответствовать закрывающаяся. Если в формуле количество закрывающих и открывающих скобок не совпадает, Excel выдает сообщение об ошибке и предлагает вариант ее исправления. Сразу после ввода закрывающей скобки Excel отображает жирным шрифтом (или другим цветом) последнюю пару скобок, что очень удобно при наличии в формуле большого количества скобок. Если формула очень громоздкая, то можно включить в текст формулы пробелы или разрывы строк. Это никак не влияет на результаты вычислений. Для разрыва строки надо нажать комбинацию клавиш Alt+Enter.
Если знак равенства в формуле будет отсутствовать, то вводимая в ячейку информация будет восприниматься как текст, числа или их комбинация. В процессе ввода формул необходимо указывать адреса ячеек (делать ссылки на
ячейки), содержимое которых используется ввычислениях. Для этого можно указывать эти ячейки и диапазоны, выделяя их мышью (в этом случае вокруг выделенной ячейки появится бегущая рамочка), а можно вводить адреса и диапазоны ячеек в формулу с клавиатуры. Когда значения в ячейках, на которые есть ссылка в формуле, изменяются, Excel автоматически заново вычисляет формулу и обновляет значения, используя новые данные в этих ячейках.Пример. Выделим ячейку А1. В строке формул введем =2+3 и нажмем Enter. В ячейке появится результат (5). А в строке формул останется сама формула.| ' ~ д Г ж £ =2+3 ‘
в ! с г—* А |~
2.. !Используемые в формулах ссылки на ячейки могут
быть абсолютными или относительными. Относительная ссылка указывает на ячейку, согласно ее положения относительно ячейки, содержащей формулу. Обозначение относительной ячейки в формуле - А1. Абсолютная ссылка указывает на ячейку, местоположение которой неизменно. Обозначение абсолютной ячейки - $А$1. Смешанная ссылка содержит комбинацию относительной и абсолютной ссылок - $А1, А$1. Для быстрого изменения типа ссылки используется клавиша F4. При указании курсором на имя ячейки в строке формул после каждого нажатия клавиши F4 тип ссылки будет меняться.Пример. Введите в ячейки следующие значения: А1 - 20, В1 -200 , А 2 -3 0 , В 2-300, С1 - 5.В ячейку АЗ введите следующую формулу =А1+А2+$С$1. Если скопировать ячейку АЗ в ВЗ, то в строке формул увидим, что относительные ссылки "подстроились" под новые значения, а вот абсолютная (ссылка на ячейку С1) - оста-
лась неизменной.„ ~ ” * £ =B1+B2+’jc$1_ J . J# .... D. Л
1 20 200 5!Г“....S „30 ~ 'зю-э ■___ ffil 505| „ ___
Можно ссылаться на другие листы той же книги или на листы других книг (внешняя ссылка) точно так, как и на ячейки текущего листа.Пример. Чтобы записать в ячейку А1 (Лист1) ссылку на ячейку А5 (Лист2), надо: выделить ячейку А1 и ввести знак равенства; щелкнуть на ярлыке "Лист 2"; щелкнуть на ячейке А5 и нажать клавишу ввода; после этого будет активизирован опять Лист1 и в ячейке А1 появится следующая формула =Лист2! А5.
Ссылка на ячейку из другой книги записывается аналогично и будет выглядеть так — =[Книга2]Лист2! А5.
1.2.2.Встроенные функции Excel.Процесс составления формул максимально облегчает
«Мастер функций», одно из окон которого показано на ри- c. 1. Встроенные функции Excel объединены по категориям согласно типу производимых расчётов. В окне Мастер функций в списке Категория отображается набор всех категорий, а в списке Функция - набор функций выбранной категории.
Любая функция Excel представляет собой встроенную формулу для выполнения операций над указанными данными. Кроме того, функции могут применяться совместно, то есть они могут быть вложены друг в друга таким образом, что результат одной функции будет являться аргументом для другой.
Обращение к функции состоит из имени функции и одного или нескольких аргументов, записываемых в скобках сразу после имени функции и разделенных точкой с запятой. Имя функции, например ЕХР, описывает операцию,
которую эта функция выполняет, аргументы, например В56, задают ячейки или значения, используемые функцией.
Аргументами функций могут быть данные числового, текстового или логического типа. Аргументом могут быть адреса смежных или несмежных ячеек и даже диапазонов ячеек. Адреса смежного диапазона состоит из адресов первой и последней ячеек, разделённых двоеточием. Адрес несмежного диапазона включает адреса несмежных ячеек (или диапазонов), разделённые точкой с запятой. Оба символа разделения вставляются в формулу при указании диапазона ячеек с помощью мыши автоматически.
Мастер функций - шаг 1 из 2
Поиск функции;
Введите краткое описание действия, которое к выполнить, и нажмите кнопку '‘Найткк_____.
категория: ] 10 недавно использовавшихся "3
Найти
Выберите функцию'
СУММСТЕПЕЙ) “ТЕНДЕНЦИЯЕСЛИПРОИЗВЕЛ “ *СРЗНАЧГИПЕРССЫЛКА
СУММ(число 1 ;чис ло2р_)Суммирует аргументы.
Справка по этой Ф унк ции QK Отмена
Рис. 1. Окно «Мастера функций».При использовании некоторых функций, например
ПИ(), аргумент не указывается, но скобки нужно вводить обязательно. Имена функций можно вводить с клавиатуры или с помощью второго окна «Мастера функций». Если при вводе имени функции с клавиатуры использовались только строчные буквы, то при нажатии Enter строчные бу
квы будут заменены на прописные, если в имени не будет опечаток. Когда Excel 2003 распознает набираемую функцию, программа отображает расширенные параметры для формулы как всплывающую подсказку при указании на имя функции в формуле. Значения аргументов функций или адреса ячеек, где они находятся, можно ввести в строку формул непосредственно с клавиатуры или выделив с помощью мыши нужный диапазон ячеек на рабочем листе. В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символов. Пример. Формула =ПРОИЗВЕД(А1;АЗ;А6) указывает, что необходимо перемножить числа в ячейках А1, АЗ, А6. Формула =СУММ(А2:А5;В4:В8) указывает, что необходимо сложить содержимое ячеек двух диапазонов А2:А5 и В4:В8.
В Excel существуют функции для работы с матрицами. Если аргументами функции являются матрицы и результат работы функции тоже матрица, необходимо:1) установить курсор на ячейку, где будет находиться первый элемент первой строки искомой матрицы, выбрать из вставки функций соответствующую;2) выделить матрицу или матрицы, которые являются аргументами функции;3) после ввода формулы, выделить диапазон ячеек, в котором будет помещаться искомая матрица; установить курсор на начало формулы в строке редактирования (сразу после знака равно) и нажать CTRL+SHIFT+ENTER. В выделенных ячейках при этом должен появиться искомая матрица.
1.3. Построение диаграмм и графиков.Графики и диаграммы — наиболее эффективный способ
представления данных. Они облегчают восприятие и анализ предлагаемых материалов, выявляют соотношение значе-
нкй данных и динамику изменения ряда данных. В Excel диаграмма может быть расположена на том рабочем листе, который содержит данные, по которым она построена. или построена на отдельном листе. Диаграмму можно перемещать и вносить в неё некоторые изменения. Причём, вне зависимости от положения диаграммы и данных, по которым она построена, при изменении данных автоматически изменяется и диаграмма. Диаграммы в Excel могут быть различных типов, что позволяет выбрать наиболее удобный способ представления результатов.
ЦШМДШД1И1ЛИ«МЫДШ11ИЦИИМ»-.- д х}|
rwror'pa('t*"3 отсаоажэет различны кзтссрич
Просмотр результата
Отмена_______________________
. Ь чзд ji Далее ■? | ГиРис. 2. Одно из окон «Мастера диаграмм».
Графики и диаграммы в Excel можно создать с помощью «Мастер диаграмм» или команды главного меню Вставка => Диаграмма. «Мастер диаграмм» имеет насколько окон, каждое из них имеет вкладки (рис. 2). Переходя от окна к окну и раскрывая необходимые вкладки (сетка, оси и т.д.) можно добиться требуемого вида диа
граммы. На каждом из шагов можно отменять операцию или возвращаться назад.В первом окне «Мастера диаграмм» «Тип диаграммы» выбираем тип диаграммы и ее вид. При построении графиков, по мнению авторов, удобнее выбирать тип диаграммы точечная, что обеспечивает автоматическое расположение параметра, от которого строится график, по оси абсцисс и нет необходимости дополнительно указывать данные для обозначения по оси абсцисс.
Во втором окне «Источник данных диаграммы» производится ввод числовых данных, на основании которых строиться диаграмма (если они не указаны до обращения к мастеру диаграмм), присваивание имен рядам данных, указания данных для подписи по оси абсцисс.
Открыв третье окно «Параметры диаграммы», можно разместить на диаграмме заголовки, обозначить оси координат, создать на графике сетку с требуемым шагом.
В четвертом окне «Размещение диаграммы» выбирается размещение диаграммы: на отдельном или уже имеющихся листах рабочей книги.
После размещения диаграммы на рабочем листе ее можно редактировать. Для этого необходимо активизировать диаграмму или ее элемент (оси, легенду и т.п.) щелчком левой клавиши мыши, затем нажать на правую клавишу мыши и в появившемся окне или меню выбрать нужный пункт.
2. Visual Basic for Application (VBA).2.1. Основные понятия объектно-
ориентированного программирования.Пользователь ПК часто выполняет одни и те же после
довательности команд для многих рутинных задач. Вместо этого можно создать макрос, который будет выполнять эту последовательность всякий раз, когда необходимо решить
данную задачу. Макрос — это последовательность команд и функций, хранящаяся в модуле Visual Basic.
При использовании макрорекордера (MacroRecorder) Excel для организации записи действий в макрос, рекордер автоматически сохраняет последовательность текстовых инструкций, которые описывают на языке программирования VBA различные действия, выполняемые пользователем. Это текстовое описание команд называется исходным кодом для этого макроса. Транслируя действия пользователя, макрорекордер создает макрос, который можно выполнить в любой нужный момент. Если запустить такой макрос на выполнение, VBA считает записанные в исходном коде инструкции и выполнит каждую последовательно, дублируя действия, которые выполнялись пользователем при записи макроса.
Поскольку можно использовать макрорекордер в Excel для записи действий в макрос и затем воспроизводить их, может показаться, что изучать VBA не обязательно. Однако записанный макрорекордером макрос может только воспроизводить без отклонений каждое действие в той же последовательности, в которой первоначально они выполнялись. VBA можно использовать для улучшения макросов, записанных макрорекордером, значительно повышая их мощь и возможности, с помощью VBA можно создавать пользовательские меню, диалоговые окна и панели инструментов.
Excel приложение является объектом для VBA- программирования с именем- Application. В этот объект могут входить несколько книг -Workbook, объединённые в коллекцию Workbooks. В книге находятся листы Sheet (рабочие листы - Worksheet), объединённые в коллекцию Sheets (Worksheets). Листы разбиты на ячейки (Cell). При работе активными могут быть только одна книга и один лист. При работе (записи оператора программы) в VBA ак
тивными могут быть только одна книга и один лист. Программирование на VBA можно рассматривать как управление объектами приложений
Разработка проекта (т.е. программы) в VBA включает в себя следующие этапы:1. создание интерфейса, т.е. создания окна диалога (на
пример, формы) и размещение в нем элементов управления;
2. установку параметров (свойств) элементов управления;3. ввод текста программы и ее отладку;4. сохранение проекта.
При программировании в Excel на VBA каждый макрос размещается в отдельном модуле, присоединенном к книге Excel. При программировании на Visual Basic, проект строится из форм (файлы с расширение .frm). модулей (файлы с расширением .bas), которые содержат объявление переменных, определение констант, функций и подпрограмм пользователя, и элементов управления Active (файлы с расширением .осх). В VB проект можно сохранить с созданием .ехе файла или без.
Строение команд. Иерархия зависимости объектов, свойств и методов в VBA записывается через точку слева направо от глобального к подчиненному.
Коллекция — это группа объектов определенного типа, содержащая в себе различные объекты. Например, книга содержит коллекцию, состоящую из одного или более листов. Обычно коллекции имеют 4 метода: Add, Remove, Count, Item. Методом Add мы добавляем данные в коллекцию, Count возвращает количество элементов в коллекции, Item возвращает элемент коллекции, a Remove удаляет объекты по индексу.
Объект - есть представитель некоторого класса объектов. Класс определяет общие для всех его объектов методы и свойства. Объекты описываются фиксированным набором
характеристик. Характеристикаописывается наименованием и типом значения.
Чтобы указать на объект, нужно задать на него ссылку. Полная ссылка состоит из ряда имен объектов, вложенных в друг друга, расположенных от высшего к низшему и разделенных точками. При записи ссылки можно не указывать те объекты, которые активны в данный момент.
Например, полная ссылка на ячейку имеет вид: Application. Workbooks("Bookl ").Sheets(,!Sheeti ").Range(“D5").
Свойства представляют собой характеристики, описывающие какие-либо стороны объекта, которому они принадлежат. Общим свойством для объектов Excel является Name (имя), содержащее идентифицирующее значение, установленной пользователем (или по умолчанию Excel) для книги, листа, диапазона ячеек и т.п. В VBA свойства, управляемые программой, отмечаются точкой, которая разделяет имя объекта в первой части и имя свойства во второй. Обращение к свойству объекта имеет формат: имя-объекта, имя-свойства
Если мы хотим изменить свойство объекта, используется команда:имя-объекта.имя-свойства=новое-значение-свойства.
Методы позволяют объекту узнать, что необходимо делать. Методы - программные процедуры, определяющие взаимодействие объектов с внешней средой. Доступ к методам осуществляется командой: имя-объекта. имя-метода[список параметров]
Если свойства объекта определяют его внешний вид и поведение, то методы определяют задачи, которые может выполнять объект. Свойства и методы могут быть связанными, например, использование метода Move применительно к форме приводит к изменению свойств Left и Тор, которые определяют положение формы на экране монитора.
Для объекта Application существует более 170 методов, наиболее часто используются: ActiveCell (активная ячейка), ActiveChart (выбранная диаграмма), ActiveSheet (выбранный рабочий лист), RangeSelection (выбранные ячейки), Selection (выбранный объект).
События. Так как свойство есть определенный атрибут объекта, а метод, который действует на объект, знает, что следует сделать, то событие - это действие на объект, которое воспринимается как случившиеся. Excel отслеживает все события и выполняет определенный код при наступлении того или иного события.
Для события (нажатие клавиши, перемещение мыши и т.п.) можно запрограммировать реакцию объекта на произошедшее событие (процедура обработки событий).
2.2. Работа с макросами.Наибольшая выгода от создания макросов заключается
в возможности автоматически обрабатывать в Excel повторяющиеся действия.
Для того чтобы начать запись макроса макрорекордером, выберите команды Сервис => Макрос => Запись макроса. В результате на экране отобразится окно диалога (рис. 3 а), в котором необходимо задать имя макроса, заполнение остальных ячеек окна необязательно. После нажатия на клавишу ОК окно будет свернуто и появится панель инструментов (рис. 3 б) с именем Остановить запись, после этого любые действия, выполненные в книге Excel, будут записаны в виде макроса. Окончить запись макроса можно командой Остановка записи. После этого записанный таким образом макрос можно использовать - редактировать, запускать на выполнение. Этот метод хорошо использовать, когда вы знаете, как решить задачу, но не знаете, как ее запрограммировать в VBA.
Для редактирования макроса (как записанного с по-
мощью макрорекордера, так и написанногосамостоятельно) необходимо выбрать команды Сервис => Макрос => Макросы. В окне Макрос указать имя макроса и нажать клавишу Изменить (рис.4). После этого VBA-код макроса будет показан на экране в редакторе VB (см. рис.5).
Для запуска существующего макроса выбираем команды Сервис => Макрос => Макросы. В окне указываем имя макроса и нажимаем кнопку запуск (рис. 4).
Е£>;*1• ..............
Имя макроса:
I Макрос 1
Сочетание клавиш. Сохранить в
ctri+J ----------------------"Эта книга
Личная книга макросов Новая книга
“ГГ
О с т X 1ОК } Отмена J
i l i i щ
а) б)Рис. 3. Запись макроса макрорекордером.
Можно также запустить макрос и с помощью нажатия группы клавиш (задание сочетание клавиш в окне запись макроса), подсоединив макрос к кнопке панели инструментов, пункту меню или объекту (например, рисунку) на рабочем листе.
Если вы хотите самостоятельно написать VBA-код (программу), то выберите команды Сервис => Макрос => Макросы. После этого на экране появится окно Макрос, в котором указываете имя макроса, в какой книге он будет находиться, затем нажимаете кнопку Создать. После этого на экране появляется окно VBA. Далее можно писать про-
грамму, после написания ее необходимо сохранить отладить.
Имя макроса
Макрос!
Макрос!Макрос2МакросЗ
~ш г gjtinomriTb̂ J i ■— I ;Отмена
Войти “ |
Изменить j
Создать |
У да ли ть . j
Параметры.,.
И
Рис.4. Окно «Макрос».Отладка макроса - это процесс, обнаруживающий, по
чему VBA-код не делает того, что должен. Отладка происходит аналогично отладке программ в других языкая программирования высокого уровня. Для отладки в главном меню VBA существуют команды Run и Debug (см. рис. 5). При отладке написанной программы можно устанавливать точки остановы (слева от оператора, вне поля программы). Пример. Включаем запись макроса и проделываем в Excel следующие действия: в ячейку С4 записываем число 3, в ячейку С5 - число 4, в ячейку С6 - формулу С4+С5. Затем выделяем ячейку С4 и нажимает Delete. После этого нажимает кнопку Остановить запись. Если после этих действий выполнить команды Сервис - Макрос - Макросы, указать Макрос 1 и нажать кнопку Изменить, то на экране появится окно VBA с кодом Макроса! (рис. 5).
■ i
В |j$ VBAProjert (KHwal)$ ® Microsoft Excel Objects
Лист1 (Лист!)“ Лист2(Лист2)
i - ® ЛистЗ (ЛистЗ) I j ЭтаКнм-а
В ® Modules '-««1
Moduli'! Module Alphabetic I categorirod j
Modulel
Sub Макрос1()
1 М акрос! Макрос 1 Макрос зап и сан 2 2 .1 0 .2 0 0 7
A c tlv e C e U .F o rra u la R lC l - "3"R a n g e (" C S " ) .S e le c t A c tiv e C e ll .F o rn iu la R lC l * "4 ''R ange("C 6” ) .S e l e c tA c tiv e C e ll .F o rm u la R lC l « "=R [-2] C*R[-1] CM R a n g e (" C 4 " ) .S e le c t S e l e c t i o n . C le a rC o n te n ts
End Sub
ЮU)
Рис.5. Окно VBA.
2.3. Интерфейс VBA.Первая строка в окне VBA - строка заголовка (рис. 5).
В ее правом конце (на рис. 5 не показано) располагается значок системного меню, позволяющего изменять геометрию окна и его местоположение, а также производить над ним те же действия, на которые способны и три кнопки изменения статуса окна (свертывания, максимиза- ции/восстановления, закрытия). В центральной части строки располагается имя окна. Оно состоит из названия программной среды (Microsoft Visual Basic), после которого через тире указано имя проекта (Projectl). Далее в квадратных скобках [] указан текущий режим приложения (run, design и т.д.).
Под строкой заголовка располагается строка главного меню. При максимизации активного окна документа с какой-либо формой в правый конец этой же строки переходят три кнопки изменения состояния указанного окна документа.
Под строкой главного меню располагается стандартная (Standart) панель инструментов. Стандартная панель инструментов содержит пять групп пиктограмм, дублирующих наиболее употребительные команды, доступные через те или иные пункты главного меню.
Окно проекта (Project). В окне проекта представлена иерархическая структура файлов форм и модулей текущего проекта. В проекте автоматически создается модуль для каждого рабочего листа и для всей книги, также модули создаются для каждой пользовательской формы, макросов и классов.
Следующим важнейшим для работы окном является окно свойств (Properties). Оно предназначено для установки свойств объектов на стадии проектирования. Для его по
каза на экране можно использовать пиктограмму стандартной панели инструментов, соответствующую команду из пункта View главного меню, либо клавишу F4. Список свойств разделен на две колонки. В левой находятся имена свойств указанного в верхнем окошке объекта, а в правой - значения, установленные для этих свойств по умолчанию, либо программистом. Для некоторых свойств предусмотрена возможность выбора из списка, раскрывающегося после щелчка по значку стрелки, который появляется в поле значения свойства после выделения имени свойства.
Список свойств присутствует на двух вкладках: Alphabetic (Алфавитный) и Categorized (По категориям), обозначающих принцип упорядочения списка свойств. Переключаться на вкладку Categorized может быть удобно тогда, когда имя свойства забыто, но известны его функциональные признаки. Отметим, что под списком свойств в окне Properties выводится краткое описание текущего выделенного свойства.
Окно редактирования кода VBA служит для ввода и изменения кода процедур приложения. Два раскрывающихся списка (General и Declarations) в верхней части окна облегчают ориентацию в созданных процедурах. Левый список позволяет выбирать управляющий элемент или форму, а правый - содержит список допустимых для них событий.
Также можно открыть окно редактирования форм (User- Form) и окно просмотра объектов (Object Browser).
2.4. Язык программирования VBA.2.4.1. Области видимости, время жизни и именова
ние переменных. Переменные имеют установленное время жизни и область видимости в модулях и процедурах. Области видимости переменных характеризуют возможность обращения к размещенной в памяти переменной по ее име
ни из той или иной процедуры. Время жизни переменных определяется тем периодом, в течение которого по имени переменной можно обращаться к ее значению в сопоставленной ей при размещении области памяти. Время жизни переменой начинается с момента описания ее в модуле или в процедуре и длится до тех пор, пока не завершится их выполнение. Области видимости и время жизни определяются категориями программных единиц, в которых объявлены переменные ("уровнями" их объявления). Переменные можно объявлять в процедуре или в верхней части модуля, называемой областью общих объявлений (Declarations). Переменные уровня процедуры (подпрограммы или функции), называемые локальными, имеют областью видимости только саму процедуру и время жизни, определяемое временем от входа в процедуру до выхода из нее, если только эта процедура или переменная не объявлены с ключевым словом Static. Если переменная описана как статическая, то она имеет неограниченное виртуальное время жизни в рамках работы программы. Чтобы использовать переменную в нескольких процедурах ее необходимо описать на уровне модуля. Переменные уровня формы или модуля (объявленные в разделе General Declarations) доступны внутри всех процедур той же формы или модуля и не доступны в каких-либо других процедурах. В случае объявления переменной как Public, она доступна из всех процедур данного проекта и даже (при отсутствии инструкции Option Private Module) - везде вне данного проекта. При этом обращение к ней извне формы осуществляется с помощью конструкции Имя_формы.Имя_переменной.
Комментарий в VBA начинается с апострофа (‘) или зарезервированного слова Rem.
2.4.2. Константы и переменные.Имена переменных (и именованных констант) должны
состоять из последовательностей заглавных и строчных
букв латинского алфавита, цифр и знаков подчеркивания, начинаться с буквы, иметь длину не более 255 символов и не совпадать с ключевыми словами. Строчные и прописные буквы не различаются программой.
Присваивание значений переменным осуществляется оператором присваивания, знак равно =.
Переменные и константы объявляются в процедуре с помощью зарезервированных слов Dim и Const. Несколько переменных или констант могут быть объявлены в одной строке оператора Dim через запятую.
Формат команды объявления переменной:Dim varl as тип-переменной! , var2 as тип-переменной2, ...
Тип-переменной определяет возможные значения переменной и объем памяти, необходимый для ее размещения.
Основные типы данных VBA и диапазон их значений указаны в таблице 1.
Таблица 1.Тип данных Диапазон значений ОписаниеByte 0 - 2 5 5 Двоичные данныеBoolean True inraFalse ЛогическийInteger -32768 - 32767 Целые числаLong -2147483648
2147483647Целые числа (длинные)
String(variabie-length)
От 0 до 2000000000 символов
Символьный (переменной длины)
String(fixed-length)
От 0 до 2000000000 символов
Символьный (фиксированной длины)
Currency -22337203685477,58 - 922337203685477,58
Число с фиксированной десятичной точкой
Date От January 1, 100 до December 31,9999
Дата
Object ОбъектSingle От -1,445 до +З,43* Вещественные чис
ла
Существует тип данных Variant, который охватывает все типы данных. Если переменная объявлена, но не приведена к типу, то она будет расцениваться как тип Variant.
Строковые типы данных также описываются с помощью команды Dim. При описании строк фиксированной длины добавляется код, определяющий длину строки.
Для описания констант применяется команда Const, которая работает также, как команда Dim. Значение константы задается оператором формата:[Public или Private] Const Имя [As Тип] = Выражение Public - ключевое слово, используемое на уровне модуля для описания констант, доступных всем процедурам во всех модулях. Не допускается его использование в процедурах. Private - ключевое слово, используемое на уровне модуля для описания констант, доступных только внутри модуля, в котором выполняется описание. Не допускается использование в процедурах, действует по умолчанию.Имя - имя константы, удовлетворяющее стандартным правилам именования переменных.Тип - один из поддерживаемых типов данных (см. Таблицу1). Для каждой описываемой константы следует использовать отдельное предложение As Тип.Выражение - константа или любое сочетание, которое может включать все арифметические или логические операторы за исключением Is. Не допускается использование переменных, определяемых пользователем, а также любых функций в выражениях, присваиваемых константам.
В операторе присваивания присваемое символьной переменной значение необходимо заключить в двойные кавычки. Константы даты и времени (тип Date) помещают внутри пары символов "#".
Используя Dim можно описать тип объекта, который вы еще только собираетесь создать. Запись Dim argl, arg2 As Range означает, что вы сообщили редактору VBA, что в пе
ременных argl и arg2 будут храниться объекты типа Range. Для присвоения переменным ссылок на объекты применяется команда Set: Set argl = Range(“B14”).Пример. Описания констант и переменных.Dim stroka as string*25 - переменная stroka символьного типа длиной 25 символов.Dim alf, bet As Integer Const g As Single = 9.81
2.4.3. Работа с массивами.Массив - группа значений, принадлежащих к одному
типу данных. Массивы имеют верхнюю и нижнюю границы, по умолчанию нижняя граница индексов массивов в VBA равна 0. Нижнюю границу массива можно изменить оператором Option Base 1 или при описании массива. Массив задается именем, размерностью и границами изменения индексов. Если при объявлении одномерного массива на месте индекса (в круглых скобках после имени массива) стоит константа (возможно константное выражение), то ее значение определяет верхнюю границу массива; нижняя граница при этом по умолчанию равна 0. Можно, однако, задать обе границы измерения явно. При объявлении многомерного массива границы каждой размерности массива указываются через запятую.Пример. Dim Arr(20) As String - оператор задает одномерный массив с именем Агг размером в 21 элемент символьного типа;Dim Koef(3 То 43, 1 то 3) As Double - оператор задает двумерный массив с именем Koef размером в 123 элемента типа Double (в массиве 41 строка и 3 столбца).
VBA позволяет создавать динамические массивы, границы которых при объявлении не указываются. Реальное размещение элементов массива производится, затем, инструкцией ReDim, причем неоднократно (но число измерений при переразмещениях меняться не должно).
2.4.4. Обращение к ячейкам Excel.Как говорилось в гл. 1 документ Excel является рабочей
книгой, каждый рабочий лист которой представляет конечную прямоугольную таблицу, элементы которой называются ячейками. Листы книги, строки, столбцы таблицы и сами ячейки именованы (имеют адрес). В любой части рабочего листа можно выделить некоторую подобласть (обычно, прямоугольный интервал ячеек) и с ней работать. Ячейки, строки, столбцы, сама таблица целиком, любые ее подобласти, в том числе и не смежные, полученные, как пересечение или объединение интервалов - все это представимо объектами Range, которые затем можно использовать в программе как переменные. Благодаря тому, что объект Range имеет свойство Name, можно давать собственные имена используемым объектам, в том числе и ячейкам. Свойство Value позволяет задать значение объекта, любого допустимого типа. Здесь можно использовать возможности универсального типа данных Variant, с которым совместим тип Range. Свойство Formula позволяет связать с объектом формулу, вычисляющую какое-либо значение. К объекту Range можно применить методы Select, Activate, Clear.
Обращение к ячейкам можно осуществлять с помощью свойства Range и метода Cells:Range(duana3ou)СеШ(номер-строки, номер-столбца)
Диапазон указывается в стиле А1 (см. п.1.1.1). Если аргументы в диапазоне указаны через запятую ("D3:D10, АЗ:А10"), то это объединение диапазонов ячеек, а если через пробел ("А1:А20 A8:D8") - это пересечение. Следует обратить внимание, что для корректной работы Range и Cells рабочая книга и лист должны быть активными. Т.е. при наличии нескольких книг или листов необходимо перед каждым использованием этих свойств указывать книгу и лист, с которым работаем, чтобы не внести изменения в
другую книгу или лист.Если метод Cells используется без указания аргументов,
то действия выполняются над всеми ячейками активного рабочего листа.Пример. 1) Помещение формулы в ячейку:Range("A4") = ”=А2+А3”Range(“N3”).Formula=”=RAND0”ActiveSheet.Cells(2,1 ).Formula = "=Sum(Bl:B5)"2) Заполнение диапазона ячеек:Range("C:C")=2 (Значение всех ячеек в столбце С станет равным 2)Range("A2:A5") = 2Range("C5:D9,G9:H16,B14:D18").ClearContents (очистка содержимого указанного диапазона ячеек)3) В следующем фрагменте программы задается объект типа Range и изменяется его свойство Formula:Dim massiv As Range
Set massiv = Worksheets("Sheetl").Range("Al:D5") massiv.Formula = "=RAND()"
4) Добавление числа 5 к содержимому ячейки А5, присвоение полученного значения переменной sum, помещение значения sum в ячейку В5:With Application. Workbooks(“Book 1 ”)
Worksheets(3).Activate Range("A5")=10 sum=Range("A5")+l 5 Range("B5 ")=sum
End With2.4.5. Виды операции VBA.При перечислении операции расположены по их при
оритету.Категория арифметических содержит 8 операций:
1) Возведение в степень (А);2) Изменение знака операнда (-);
3) Умножение и деление (*, /);4) Целочисленное деление (\);5) Остаток от целочисленного деления (Mod);6) Сложение и вычитание (+, -).
VBA имеет три синтаксические разновидности операций сравнения, имеющие равный приоритет: операнд1 оператор-сравнения операнд2 операнд! Is операнд2 операнд! Like операнд2
В первой разновидности сравниваются операнды, представляющие собой числовые, либо строковые выражения. Оператор Is используется для сравнения двух переменных, содержащих ссылки на объекты. Если переменные содержат ссылки на один объект, результат имеет значение True; в противном случае результат имеет значение False. Оператор Like используется для сравнения на совпадение двух строковых выражений. Если значение левого операнда соответствует значению правого (имеющего смысл "образец"), результат имеет значение True, при их несоответствии результат имеет значение False.
Операция конкатенации используется для работы с символьными данными и имеет следующий формат: операнд! & операнд2
Несмотря на то, что допускается использование оператора "+" для слияния строковых значений, рекомендуется во избежание возможной путаницы и необходимости дополнительных описаний в программе использовать для слияния строк оператор конкатенации
В VBA имеется 6 логических операций (одна унарная - Not, остальные бинарные):1) Not (оператор логического отрицания, формат: Not операнд);2) And (конъюнкция или логическое "И");3) Or (дизъюнкция или логическое "ИЛИ");
4) Хог (исключающее "ИЛИ" или сумма по модулю 2);5) Eqv (логическая эквиваленция);6) Imp (логическая импликация от первого операнда ко второму).Бинарные операции имеют формат: операнд 1 логический-оператор операнд2.Таблица истинности для логических операций. ________
А В A And В A O rB AXorB A Eqv В A Imp ВTrue True True True False True TrueTrue False False True True False FalseFalse True False True True False TrueFalse False False False False True True
2.4.6. Стандартные функции.Как любой язык программирования VBA имеет стан
дартные (встроенные) функции. Вызов таких функций осуществляется указанием имени функции и списка ее аргументов после имени в скобках через запятую. В VBA есть такие стандартные функции как:Rnd - генерирования случайных чисел от 0 до 1;Str - преобразование числа в строку;Val - преобразование строки в число;Тал - тангенс (угол в радианах)Log(X) - десятичный логарифм (LogN(x)= Log(X) / Log(N)) Sgn - знак числа.
2.4.7. Основные операторы.Оператор Function описывает имя процедуры-функции,
возвращаемое ею значение, аргументы, а также локализованную часть программного кода, называемую ее телом. [Public или Private] [Static] Function ИмяЦСписок- аргументов)] [As Тип]Инструкции End FunctionPublic - указывает, что процедура доступна для всех других
процедур во всех модулях. При использовании в личном модуле (содержащем инструкцию Option Private) такая процедура недоступна вне проекта.Private - указывает, что процедура доступна для других процедур только того модуля, в котором она описана.Static - указывает, что локальные переменные процедуры сохраняются в промежутках времени между вызовами этой процедуры. Не действует на переменные, описанные вне процедуры.Имя - имя процедуры, удовлетворяющее стандартным правилам именования переменных.Список-аргументов - список переменных с указанием типа, разделенных запятой, представляющий аргументы, которые передаются в процедуру при ее вызове. Допускаются следующие типы данных, передаваемых в функцию Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String (только строки переменной длины), Object, Variant, определяемый пользователем тип.Тип - тип значения, возвращаемого процедурой-функцией. Поддерживаются типы Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String (за исключением строк фиксированной длины), Object, Variant или любой определяемый пользователем тип. Невозможен возврат массивов любого типа, однако допускается возврат значения типа Variant, содержащего массив.Инструкции - любая группа операторов, выполняемых внутри процедуры.Пример. Программа вычисления факториала числа, находящегося в ячейке А1 с помощью функции с выводом результата в окне сообщений.Sub Test()MsgBox Str(Fact(Ceils(l, 1)))End SubFunction Fact(n As Integer)
If n < 1 Then Fact = 1 Else Fact = Fact(n - 1) * n End Function
Оператор Sub описывает имя процедуры-функции, возвращаемое ею значение, аргументы, а также локализованную часть программного кода, называемую ее телом:[Public или Private] [Static] Sub Имя[(Список-аргументов)]ИнструкцииEnd SubСмысл и назначение обозначений в формате записи этого оператора аналогичны оператору Function.В отличие от процедуры Sub вызов функции может (но не обязан!) применяться в качестве операнда выражения (как и вызов любой встроенной функции).Пример. Ниже приведен текст макроса, который выделяет заданное число ячеек через одну строку и очищает их содержимое.Sub Макрос 10 Dim i, j As IntegerRem i - номер строки, j- номер столбца For i = 1 To 100 Step 2 For j = 1 To 50 Step 1 Cells(i, j).Select
Selection.ClearContents Next j: Next i
End SubОператор Do ... Loop повторяет выполнение набора
операторов, пока условие имеет значение True, либо пока оно не примет значение True. Оператор имеет два варианта синтаксиса:1) Do (While j Until} Условие
ОператорыLoop2) Do
Операторы
Loop {While или Until} Условие Условие - числовое или строковое выражение, которое (будучи преобразовано к типу Boolean) имеет значение True или False. Если условие имеет значение Null (пустое значение, ноль), то аргумент условие рассматривается как значение False.Операторы - один или несколько операторов, составляющих тело цикла, выполнение которых повторяется, пока (при использовании ключевого слова While) условие имеет значение True, или до тех пор, пока (при использовании ключевого слова Until) оно не приобретет значение True. Пример. Цикл в фрагменте программы выполняется до тех пор пока t не станет больше 3.Doа = а + 1 t = t + 0.005 Loop Until t > 3
Оператор Exit принудительно завершает выполнение инструкций Do...Loop, For...Next, а также процедур и функций. Имеет формат:Exit Do Exit For Exit Function Exit Sub
В любом месте тела цикла, функции или процедуры может быть размещено любое число операторов Exit Do, обеспечивающих альтернативные возможности выхода из цикла. Часто используемый вместе с определением некоторого условия (например, If...Then), оператор Exit Do передает управление оператору, непосредственно следующему за вышеупомянутыми операторами.
Оператор While...Wend. Циклическая инструкция, выполняющая последовательность операторов, составляющих тело цикла, пока (While) заданное условие имеет значение
True. Формат оператора:While Условиеоп ерат орыWend
Условие - логическое, а также числовое или строковое выражения, которые преобразуются к типу Boolean (если это возможно) и имеют значение True или False. Если условие имеет значение Null, условие рассматривается как имеющее значение False.О перат оры - составляющие тело цикла одна или несколько инструкций, выполняемых, пока Условие имеет значение True. Если Условие имеет значение True, выполняются все О перат оры до предложения Wend. Затем управление возвращается предложению While и вновь проверяется Условие. Если Условие по-прежнему имеет значение True, процесс повторяется. Если оно не имеет значение True, выполнение возобновляется с оператора, следующего за Wend. Пример.Dim Counter As Integer Counter = 0 While Counter < 20
Counter = Counter + 1 Wend
Оператор For Each...In...Next повторяет выполнение группы операторов (тела цикла) для каждого элемента массива или коллекции. Формат этого оператора:For Each И м я-п арам ет ра In И мя-группы
О перат оры Next [И м я-п арам ет ра]И м я-парам ет ра - переменная, используемая для выполнения итераций по элементам семейства или массива (последовательном принятии значений этих элементов), которая должна быть типа Variant, Object.И м я_группы - имя коллекции объектов или массива (за ис-
ключением массивов с определяемымипользователем типами).О перат оры - один или несколько операторов, составляющих тело цикла, которые выполняются на каждой итерации.
Инструкция For Each...In...Next выполняется только в том случае, если группа (коллекция объектов, либо массив) содержит хотя бы один элемент.
Допускается организация вложенных циклов For Each...In...Next (один цикл For Each...In...Next располагается внутри другого), однако имена параметров каждого цикла не должны совпадать.Пример. Приведенная ниже программа заполняет диапазон ячеек B1:D5 случайными числами, а затем заменяет те из этих чисел, которые больше 0,5 на 20.Sub mas()Dim myRange As Range
Set myRange = Range("Bl:D5") myRange.Formula = "=RAND()"
Const Limit As Single = 0.5 Dim c As Variant
For Each c In myRange If c.Value > Limit Then c.Value = 20 Next c
End SubОператор For...Next повторяет выполнение группы
операторов (тела цикла):For П арам ет р=Н ач.знач. То Конеч.знач. [Step Ш аг]
О перат оры Next [П арам ет р]П арам ет р - числовая переменная-счетчик, принимающая значения от Н ач.знач. до Конеч.знач. с шагом Ш аг. Не может принадлежать к типу Boolean или быть элементом массива.Н ач.знач. и Конеч.знач. - выражения, значения которых
значение па-представляют начальное и конечное раметра.Шаг - выражение, значение которого добавляется к Параметру при каждом достижении предложения Next после очередного выполнения тела цикла (по умолчанию шаг равен единице).Операторы - один или несколько операторов, составляющих тело цикла, которые выполняются на каждом шаге.
Изменять значение переменной Параметр внутри цикла возможно, но крайне нежелательно, т.к. такой цикл уже не будет пошаговым.
Пример. 1) Фрагмент программы заполнения диапазона ячеек A1:J4 случайными числами, в этом фрагменте также присутствует оператор With.For I = 1 to 4
For J = 1 to 10With Worksheets(" Sheet 1 ").Cells(I, J)
.Value = RND()End With
Next J Next I2) поиск минимального элемента в массиве ti(5,8). timin = ti(l, 1)For j = 1 To 5 For i = 1 To 8
If ti(i, j) < timin Then tim in7 ti(i, j)Next i: Next j
Оператор GoTo передает управление на указанную строку внутри процедуры и имеет формат:GoTo СтрокаСтрока - уникальный номер строки, помещаемый в ее начало, либо метка (алфавитно-цифровая последовательность с двоеточием на конце).
Оператор If...Then...Else задает выполнение опреде
ленных групп инструкций в зависимости от значений выражений и имеет две формы синтаксиса:1) однострочную (оператор записывается на одной строке): If Условие Then И нст рукции! [Else И н ст р ук ц и й ]2) блоковую:If Условие Then
И нст рукции![Elself УсловиеЫ Then
И нст рукции-для- "УсловияЫ"...[Else
И нст рукции-для-лож ност и-всех- ’’Условий "]]End IfУсловие - имеет 2 варианта:1) логическое выражение, а также числовое или строковое выражения, приводимые к логическому значению. Если Условие имеет значение Null, то оно рассматривается как False;2) TypeOf И м я-О бъект а Is Тип-Объекта, где И м я-О бъект а - ссылка на объект, а Тип-О бъект а - любой допустимый тип объекта. Выражение имеет значение True, если объект Имя- О бъект а имеет тип объекта, указанный в аргументе Тип- О бъект а; в противном случае выражение имеет значение False.И нст рукции! - в строчной форме оператора If - один или несколько операторов, разделяемых двоеточиями. И нст рукц и и ! выполняются, если Условие имеет значение True. Инст рукции-для-"УсловияЫ ” - один или несколько операторов, выполняемых, если УсловияЫ ближайшего из предшествующих предложений Elself имеет значение True. И нст рукции-для-лож ност и-всех-"Условий" - один или несколько операторов, которые выполняются, если ни одно из предыдущих выражений Условие или УсловиеЫ не имеет значение True.
Оператор If...Then...Else может иметь множество управ-
ляющих предложений Elself и не более одного Else. Без предложений Elself инструкция If...Then...Else реализует обычную структуру ветвления. Каждое предложение Elself со своим "УсловиемЫ" эквивалентно помещению в Else- ветвь обычной инструкции If...Then...Else другой обычной инструкции If...Then...Else.Пример. Программа заполнения ячеек случайными числами в диапазоне от -5 до 5, а затем замены содержимого ячейки на 1, 0 или -1 в зависимости от его знака (естественно можно было использовать функцию Sgn). Фрагмент блок- схемы программы (оператор If) представлен на рис.6.Sub test2()Range("d5:plO").Formula = "=int(RAND()*10)-5"Dim f As Variant
For Each fin Range("d5:pl0")If f. Value > 0 Then
f. Value = 1Elself f. Value < 0 Then f. Value = -1 Else f. Value = 0 End If
N extfEnd Sub
Рис. 6. Блок-схема оператора If блочной структуры.Оператор Select Case выполняет одну из нескольких
групп инструкций (в Case- ветвях) в зависимости от значения выражения. Имеет формат:Select Case В ы раж ен ие Case С п и сок-ди ап азон ов!О п ерат оры !
[Case Else О перат оры -иначе\End SelectВ ы раж ение - числовое или строковое выражение. С п и сок-ди ап азон ов! - список, состоящий из одного или нескольких элементов, задающих числовые или строковые диапазоны элементов (разделенные запятыми) в следующих формах:1) В ы раж ение - диапазон из отдельного значения;2) В ы раж ен и е! То В ы раж ен ие2 - непрерывный диапазон значений от значения В ы раж ен ие! (меньшего) до значения В ы раж ен ие2 (большего).3) Is О перат ор-С равнения В ы раж ение - диапазон значений задается как множество таких значений недостающего левого операнда оператора сравнения (в качестве правого операнда выступает В ы раж ен ие), которые обеспечивают истинность результата операции сравнения. В качестве операторов сравнения не допустимы Is и Like. Если ключевое слово Is не указано, оно вставляется по умолчанию. О п ерат оры ! - один или несколько операторов, выполняемых в том случае, если значения В ы раж ения совпадает со значением любого элемента С п и ска-ди ап азон ов!. О перат оры -иначе - один или несколько операторов, выполняемых в том случае, если значение В ы раж ения не попадает ни в один из диапазонов, заданных в предложениях оператора Case.
Как только значение В ы раж ения попадает в диапазон из списка в предложении Case, выполняются все операторы,
следующие за данным предложением Case до следующего предложения Case, либо, для последнего предложения Case, - до предложения End Select. Затем управление передается инструкции, следующей за End Select, вне зависимости от наличия подходящих диапазонов в других предложениях Case. Если ни в одном предложении Case не содержится включающего значения Выражения диапазона, и отсутствует операторы Case Else, выполнение продолжается с оператора, следующего за End Select.
Пример. При значениях переменной i равных 1 и 3 будет вычислена переменная t.Select Case i Case 1, 3: t = t + d t / 2 End Select
Оператор Stop приостанавливает выполнение, но, в отличие от оператора End, не закрывает все файлы и не очищает все переменные. Использование оператора Stop в любом месте программы эквивалентно установке в программе точки останова.
W ith...End W ith - оператор, который применяется для указания объекта, к свойствам которого необходимо получить доступ. Имеет формат:With имя-объекта
Операторы End W ith
Если объект определен со строкой кода With, то можно использовать разделительную точку перед именем свойства, которое хотите установить.
Пример. Изменение шрифта на Anal размером 8 точек. With Worksheets("Sheetl ").Cells.Font
.Name = "Arial"
.Size = 8 End With
Операторы для работы с файлами. Формат команд
для работы с текстовыми файлами в VBA такой же как и в Turbo Basic. Открываются файлы командой Open (для чтения Input и для записи Output).Пример. Запись в файл rez.dat на диске С: переменной summa.Open "c:\rez.txt" For Output As #1 Print# 1, summa Close #1
Цифра после служебного слова as - идентификатор файла в программе. Для записи в данных используется команда Print, а для чтения Input. Функция EOF {идентификатор- файла) позволяет определить конец файла.
2.5. Основы создания форм.Система программирования VBA, как и другие Visual-
системы, позволяет формировать графический интерфейс (экранные формы Userform). Элементы управления встраиваются сначала в форму без написания кода, а просто средствами элементарного графического редактирования (компоновки). Чтобы сформированная визуальная составляющая определенным образом реагировала на те или иные события, происходящие вовне (сигнал от мыши или клавиатуры, получение сообщения от другого приложения и т.д.), в соответствие объектам формы пишется код VBA.
Форма в проект добавляется с помощью команды Insert —» UserForm. После выполнения этой команды на экране появляется незаполненная форма с панелью инструментов Toolbox, содержащей кнопки элементов управления. Для размещения нового элемента управления на форме:1) нажмите на значок элемента на панели инструментов Toolbox;2) поместите указатель мыши на то место формы, где будет располагаться управляющий элемент;3) растяните появившийся прямоугольник до требуемых
размеров.Далее в окне свойств (Properties) можно изменить свой
ства кнопок и формы (размер, название, шрифт сообщений и т.п.). Двойным щелчком по элементу управления попадаем в код, который будет выполняться при нажатии кнопки.
Для показа формы на экране используется метод Show, для закрытия - Unload.
Пример. Создаем форму с двумя кнопками типа Label. В окне свойств формы и кнопок изменяем свойство Caption (изменение заголовка окна формы и надписи на кнопках), SpesialEfFect (эффекты расположения кнопок), Font (размер шрифта кнопок) (рис. 7).ШВШШШШЯШШШШКШ;:':-. -■ £ !
гросолжить оасчет |
выход
Рис. 7. Пример пользовательской формы.Для кнопки «продолжить расчет» код:
Private Sub расчет_СИск()MsgBox ("Расчет Button Press")
End SubДля к н о п к и «выход» набираем следующий код::
Private Sub выход_СИск()Unload Me End Sub
Далее создаем программу для загрузки формы с кодом: Sub forma() pacneT.Show End Sub
Если теперь запустить макрос, то появится диалоговое окно. Если нажать кнопку «продолжить расчет», появится окно с сообщением «Расчет Button Press», при нажатии на кнопку Выход, окно формы закроется.
2.6. Организация диалоговых окон.Функция MsgBox служит для вывода на экран окна со
общения. Окно сообщения нельзя проигнорировать, т.к. дальнейшие действия в программе возможны только после реакции пользователя на сообщение. Окно сообщений может иметь не более трех кнопок, которое имеют определенное назначение. Формат функции:
MsgBox {сообщ ение [, кнопки][, заголовок][, справочны й файл, конт екст ])
С ообщ ение — аргумент символьного типа, содержащий текст, отображающийся в окне; кнопки - числовое значение, используемое для определения числа кнопок в окне сообщений (0 - кнопка OK, 1 - кнопки ОК и Cancel и т.п.); заголовок - символьный аргумент, используемый для отображения текста в заголовке окна сообщений; справочны й файл и конт екст указывают, если возможно предоставление помощи.
InputBox (окно ввода) - это окно сообщений, содержащее текст запроса и имеющее текстовой поле, в которое пользователь вводит ответ на запрос. На нем всегда присутствуют кнопки ОК и Cancel. Функция имеет формат:
InputBox (<сообщ ение[, за го л о во к ][, по ум олчанию ][, к о о р ди н а т а х][, координат аУ ][, справочны й файл, конт екст ])
П о ум олчан ию - символьный аргумент, значение которого показано в текстовом поле как значение по умолчанию; к о о р д и н а т а х и к о о р д и н а т а х числовой аргумент, задающий положение окна сообщений на экране монитора, остальные аргументы аналогичны аргументам функции MsgBox.
Следует иметь в виду, что функция возвращает строковое значение (в программе его скорее всего необходимо будет преобразовать в числовое), если текстовое поле пусто или нажата кнопка Cancel, то функция возвращает пустую строку.Пример. После выполнения следующей программы появятся окна сообщения о количестве рабочих листов в проекте и их именах.Sub Test3()Dim х As IntegerMsgBox ("число листов" & Str(Application.Sheets.Count)) With Application
For x = 1 To .Sheets.Count MsgBox (Sheets.Item(x).Name)
Nextx End With
End Sub
Литература.1. Фрай К., Фриз В., Бакингем Ф. Эффективная работа:
программирование в Office Excel 2003. - СПб.: Питер, 2005. - 544с.
2. Biblioteka.Net.Ru - электронная библиотека компьютерных электронных учебников 2004-2006 Программирование на Visual Basic 5. Опубликовано без приложений в "Информатике" №№ 23,28,31 за 1999 год
3. Назаров С.В., Мельников П.П. Программирование на MS Visual Basic: Учеб.пособие/Под ред. С.В.Назарова. - М.: Финансы и статистика, 2002. - 320с.
Содержание.Введение................................................................................ 31. Excel................................................................................... 51.1. Основные понятия Excel.............................................51.1.1. Рабочая книга и лист, ячейка..................................... 51.1.2. Копирование и перемещение данных....................... 81.2. Использование формул..............................................91.2.1. Запись формул в Excel............................................... 101.2.2. Встроенные функции Excel..................................... 121.3. Построение диаграмм и графиков ..............................142. Visual Basic for Application (VBA)...................................162.1. Основные понятия объектно-ориентированногопрограммирования...................................................... 162.2. Работа с макросами...................... 202.3. Интерфейс VBA...................... 242.4. Язык программирования VBA......................................252.4.1. Области видимости, время жизни иименование переменных......................................................252.4.2. Константы и переменные........................................... 262.4.3. Работа с массивами.................. 292.4.4. Обращение к ячейкам Excel.......................................302.4.5. Виды операции VBA.......................................... 312.4.6. Стандартные функции................................................332.4.7. Основные операторы.................................................. 332.5. Создание форм...................................... 442.6. Организация диалоговых окон..................... 46Литература.............................................................................47