Программирование в Microsoft Excel

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ

Государственное образовательное учреждение
высшего профессионального образования

НИЖЕГОРОДСКИЙ ГОСУДАРСТВЕННЫЙ

ЛИНГВИСТИЧЕСКИЙ УНИВЕРСИТЕТ

ИМ. Н. А.ДОБРОЛЮБОВА

ПРОГРАММИРОВАНИЕ В MICROSOFT EXCEL

Учебно - методическое пособие

Издание второе, дополненное

Нижний Новгород 2010

Печатается по решению редакционно-издательского совета ГОУ ВПО НГЛУ

УДК 519

ББК 32.97

П 784

П 784 Программирование в Microsoft Excel: Учебно-методическое пособие. Издание второе, дополненное. - Нижний Новгород: Нижегородский государственный лингвистический университет им. , 2010 – 68 с.

В данном учебном пособии изложены сведения по технологии создания пользовательских приложений в Microsoft Excel с использованием языка VBA (Visual Basic for Applications).

УДК 519

ББК 32.97

Составители: , канд. техн. наук, доц.,

, старший преподаватель,

, канд. техн. наук, доцент.

Рецензент , канд. техн. наук., профессор. каф. математики и информатики НГЛУ.

© ГОУ ВПО НГЛУ, 2010.

© , , 2010.

ОГЛАВЛЕНИЕ

ВВЕДЕНИЕ. 4

ГЛАВА 1. СОСТАВЛЕНИЕ И РЕШЕНИЕ АЛГОРИТМОВ.. 5

ГЛАВА 2. СРЕДА ДЛЯ НАПИСАНИЯ ПРОГРАММ НА VBA.. 13

2.1. ЭЛЕМЕНТЫ УПРАВЛЕНИЯ. КОМАНДНАЯ КНОПКА И ТЕКСТОВОЕ ОКНО 16

2.2. СПИСОК И ПОЛЕ СО СПИСКОМ.. 22

2.3. ЛИНЕЙКА ПРОКРУТКИ.. 25

2.4. РИСУНОК.. 27

ГЛАВА 3. ПРИМЕРЫ ПРОГРАММИРОВАНИЯ НА VBA.. 28

ГЛАВА 4. СВОЙСТВА И МЕТОДЫ ОБЪЕКТОВ EXCEL. 36

Задание 4.1. ИЗМЕНЕНИЕ СВОЙСТВ ЯЧЕЕК.. 37

Задание 4.2. РАЗРАБОТКА БЛАНКА ЗАКАЗА.. 38

Задание 4.3. РАЗРАБОТКА ЗАЯВКИ НА ПОСТУПЛЕНИЕ ТОВАРОВ.. 47

Задание 4.4. Разработка автоматизированного бланка.. 54

Задание 4.5. ИЗМЕНЕНИЕ ЦВЕТА ОБЪЕКТА.. 62

ГЛАВА 5. ВЫПОЛНЕНИЕ САМОСТОЯТЕЛЬНОЙ РАБОТЫ.. 64

Список литературы, рекомендуемой для чтения. 67

ВВЕДЕНИЕ

В первой главе учебно-методического пособия рассматриваются основные способы организации действий в алгоритмах, разбираются примеры составления и решения алгоритмов. Одним из первых алгоритмических языков программирования был известный всем Бейсик (Basic), созданный в 1964 г. В настоящее время существует достаточно много языков программирования алгоритмического типа: Pascal, С и др. С помощью алгоритмических языков программирования (их еще называют структурными языками программирования) любой алгоритм можно представить в виде последовательности основных алгоритмических структур: линейной, ветвления, цикла.

Во второй главе изучается популярный язык современного офисного программирования Visual Basic for Applications (VBA), созданный на основе языка Visual Basic. С помощью VBA можно создавать пользовательские приложения для различных офисных программ - Word, Excel, Access и др. В этом издании использование языка VBA рассматривается для офисной программы Microsoft Excel. Основным достоинством рассматриваемого языка программирования является возможность быстро конструировать достаточно профессиональные приложения.

VBA является языком программирования высокого уровня, то есть универсальным для всех типов процессоров. Следует отметить, что VBA относится к объектно-ориентированным языкам. В середине 80-х годов в программировании возникло новое направление, основанное на понятии объекта. И в целом объектно-ориентированное программирование – это технология написания приложений (другими словами программ) с помощью объектов.

ГЛАВА 1. СОСТАВЛЕНИЕ И РЕШЕНИЕ АЛГОРИТМОВ

Одним из фундаментальных понятий в информатике является понятие алгоритма.

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

Типы алгоритмов

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

Чтобы сделать алгоритм более наглядным, часто используют блок-схемы. Различные элементы алгоритма изображаются с помощью различных геометрических фигур: начало и конец алгоритма обозначаются прямоугольниками с закругленными углами или овалами, а последовательности команд — прямоугольниками. На рис. 1.1 приведена блок-схема линейного алгоритма.

 

Рис.1.1. Блок схема линейного алгоритма

Ветвление. В отличие от линейных алгоритмов, где команды выполняются последовательно, в алгоритмические структуры ветвление входит условие, в зависимости от выполнения или невыполнения которого реализуется та или иная последовательность команд (рис. 1.2). Такая структура еще называется развилкой.

Рис.1.2. Алгоритмическая структура «ветвление»

Ветвления могут быть многократными (рис. 1.3).

Рис. 1.3.

Цикл представляет собой алгоритмическую конструкцию, в которой многократно выполняется одна и та же последовательность шагов, называемая телом цикла. Каждое однократное исполнение цикла называется итерацией. Если тело цикла будет выполнено N раз, говорят, что произведено N итераций.

Различают два вида циклов: циклы с заранее известным числом повторений и циклы с заранее неизвестным числом повторений. Цикл с заранее известным числом повторений называют циклом с параметром. Структура блок-схемы цикла с параметром показана на рис. 1.4.а.

 

Рис. 1.4.a. Блок-схема цикла с параметром.

В цикле с параметром тело цикла продолжается до тех пор, пока некоторая переменная, которую называют параметром или счетчиком не «пробежит» ряд значений, начиная от первого заданного до последнего. Счетчик обычно обозначают буквой «i». В цикле с параметром указывается шаг цикла (Step). Если шаг равен 1, то его не указывают. Пример блок-схемы цикла с параметром показан на рис. 1.4.b.

Рис. 1.4.b. Пример блок-схемы цикла с параметром.

Цикл с условием. В циклах с заранее неизвестным числом повторений для того, чтобы определить момент прекращения выполнения тела цикла, используется условие цикла. Если при истинности условия цикл продолжается, то такое условие называется условием продолжения цикла. Если при истинности условия цикл завершается, то такое условие называется условием завершения цикла. В этом случае цикл продолжается до тех пор, пока условие не станет истинным.

Условие выхода из цикла можно поставить в начале, перед телом цикла (цикл с предусловием) (рис. 1.5. а), или в конце, после тела цикла (цикл с постусловием) (рис. 1.5.b).

- в цикле с предусловием условие проверяется до тела цикла, в цикле с постусловием - после тела цикла;

- в цикле с постусловием тело цикла выполняется хотя бы один раз, в цикле с предусловием тело цикла может не выполниться ни разу;

a) b)

Рис. 1.5. Блок-схемы циклов с условием

Если условие поставлено так, что оно всегда выполняется, например 2<3, то происходит явление, которое называется зацикливание.

Пример решения алгоритма с ветвлением

Рассмотрим решения алгоритма, блок–схема которого приведена на рис. 1.6.

Условие задачи: с клавиатуры вводится число "а = 15". Необходимо вычислить значения переменных а и S, полученных в результате исполнения алгоритма.

Рис.1.6.

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

1. Проверяем условие: 15<=10. Условие не выполняется. Значит, выполнение алгоритма пойдет по правой ветви.

2. Вычисляем значение переменной S: S = 5.

В результате действия алгоритма переменные примут значения: a =15; S = 5.

Самостоятельное задание 1.1.

На рисунке 1.7. дана блок схема алгоритма с условием. Необходимо вычислить значения переменных «х» и «y», полученных в ходе выполнения алгоритма.

Рис. 1.7.

Самостоятельное задание 1.2. Измените блок с условием: (x<y) и найдите значения переменных «x» и «y».

Разберем пример решения циклического алгоритма с условием. На рис. 1.8. дана блок-схема циклического алгоритма. В результате действия алгоритма переменные X и Y примут значения…

Рис. 1.8

1). Проверяем условие: 30>40 нет. x = 30+2=32; y = 40-4=36

2). Проверяем условие: 32>36 нет. 32+2=34; y = 36-4=32.

3). Проверяем условие: 34>32 да. x=34; y=32.

Самостоятельное задание 1.3. Разберем более сложный пример. На рис. 1.9. показана алгоритмическая структура цикл, внутри которого находится ветвление.

Рис. 1.9

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

Таблица 1

N итерации	X	Y	Проверка условия	Действия
0	55	75	55<>40 (не равно)-да. Выполняем тело цикла	55>75 – нет, y=y-x=75-55=20
1	55	20	55<>20 – да Выполняем тело цикла	55>20 – да x:=x-y=55-20=35
2	35	20	35<>20 – да Выполняем тело цикла.	35>20 – да x:=x-y=35-20=15

Закончите расчеты самостоятельно и сверьте с ответом в конце главы.

Контрольное задание 1.4.

На рис. 1.9. дана блок-схема циклического алгоритма. Необходимо вычислить значение переменной S, полученное после исполнения алгоритма.

Рис. 1.9.

Ответы к самостоятельным заданиям

Задание 1.1 x=1; y=2.

Задание 1.3. x=5; y=5.

ГЛАВА 2. СРЕДА ДЛЯ НАПИСАНИЯ ПРОГРАММ НА VBA

Реальные объекты окружающего мира обладают тремя базовыми характеристиками: они имеют набор свойств, способны разными методами изменять эти свойства и реагировать на события, возникающие как в окружающем мире, так и внутри самого объекта. Именно в таком виде в языках программирования и реализовано понятие объекта как совокупности свойств (структур данных, характерных для этого объекта), методов их обработки (подпрограмм изменения свойств) и событий, на которые данный объект может реагировать, и которые приводят к изменению свойств объекта.

Объект представляет программный компонент, который позволяет инкапсулировать (скрыть или расположить внутри себя) данные вместе с кодом (фрагментом текста программы), предназначенным для обработки объектов. Метод как раз и представляет собой действие, выполняемое над объектом. Синтаксис применения того или иного метода выглядит следующим образом:

Объект1.Метод. Здесь под названием Объект1 подразумевается имя объекта (все объекты имеют имена и по этим именам к ним можно обращаться), а слово Метод как раз определяет конкретный метод для данного объекта. Свойство представляет атрибут объекта, который определяет его характеристики, такие как цвет, размер, положение на экране, состояние объекта (например, видимость). Чтобы изменить характеристики объекта, надо просто изменить значения тех или иных его свойств. Синтаксис установки значения свойства аналогичен обращению к методу:

Объект1.Свойство = ЗначениеСвойства

Например, чтобы установить красный цвет у объекта с именем МоеЯблоко, необходимо написать конструкцию следующего вида МоеЯблоко . Цвет = Красный.

Следующим понятием, о котором стоит поговорить после объектов, методов и свойств, являются события. На языке VBA событие называется Events. Событие представляет собой действие, распознаваемое объектом (например, щелчок мышью или нажатие клавиши), для которого можно определить отклик (метод фактически определяет реакцию на событие). События возникают в результате действий пользователя или программы. Также они могут быть вызваны операционной системой.

Суть программирования на языке Visual Basic как раз и заключается в этих двух понятиях: событие и отклик на него. Если пользователь производит какое-либо воздействие (например, нажимает кнопку мыши), то в качестве отклика автоматически выполняется программный код созданной пользователем процедуры. Если такой отклик не создан, т. е. не написана соответствующая процедура, то и операционная система никак не реагирует на данное событие, и оно остается безответным.

В этом разделе мы начнем знакомство с возможностями VBA в среде Excel. Поэтому запустите Excel и выведите на экран панель инструментов – Элементы управления (для этого в меню Вид надо выбрать раздел Панели инструментов и поставить отметку напротив строки - Элементы управления). В результате на экране появится новая панель инструментов – Элементы управления (рис.2.1).

Рис. 2.1.

Для того чтобы на рабочем листе разместить какой-нибудь элемент управления (из указанной выше панели инструментов), необходимо перейти в режим конструктора (с помощью кнопки, показанной на рисунках 2.2 и 2.3). Теперь для того, чтобы перенести тот или иной элемент на рабочий лист, необходимо щелкнуть на нем левой кнопкой мыши, и далее разместить его на рабочем листе (при нажатой левой кнопке мыши).

В Office 2007 действия с элементами управления проводятся на вкладке Разработчик. Для того чтобы эта вкладка появилась на экране, необходимо активизировать кнопку Office, выбрать команду Параметры Excel и установить флажок «Показывать вкладку Разработчик на ленте».

Далее на вкладке Разработчик с помощью раскрывающегося списка команды «Вставить» находим Элементы управления – Рис.3. Используем элементы группы ActiveX. Не путайте их с элементами управления формы.

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

В Office 2003 выполните команду: Сервис – Макрос – Безопасность. Установите уровень безопасности не выше среднего. Сохраните изменения, закройте книгу и снова ее откройте. Макросы не отключайте.

В Office 2007 работа макросов настраивается на вкладке Разработчик.

Рис.2.2.

 

Рис.2.3.

2.1. ЭЛЕМЕНТЫ УПРАВЛЕНИЯ. КОМАНДНАЯ КНОПКА И ТЕКСТОВОЕ ОКНО

Задание 2.1. Разместим командную кнопку, как это показано на рис.2.4 (в дальнейшем тексте командная кнопка будет называться просто кнопкой).

Важно отметить, что после размещения элемента управления на рабочем листе Excel этот элемент становится объектом. При этом первоначальное имя объекта назначается автоматически (например, CommandButton1), но в дальнейшем это имя можно изменить. Таким образом мы создали элемент управления в режиме конструктора, и если Вы хотите воспользоваться этим элементом управления, необходимо выйти из режима конструктора (нажатием на кнопку «Режим конструктора»). В результате выполненных действий на рабочем листе появилась кнопка, которая пока никакой роли не играет.

Теперь вернемся в режим конструктора и рассмотрим некоторые свойства созданной кнопки.

Рис. 2.4.

Для просмотра свойств кнопки ее сначала необходимо выделить (щелкнуть мышкой), а затем нажать кнопку для открытия окна свойств объекта (рис.2.4). Можно открыть окно свойств и правой кнопкой мыши, выбрав в контекстном меню пункт «Свойства».

Левая колонка окна свойств содержит название свойства, а правая - значение этого свойства. Так, первая строка окна свойств на рис.2.4 (Name CommandButton1) говорит о том, что свойству Name по умолчанию присваивается значение CommandButton1. Если мы хотим дать другое имя этой кнопке, то вместо CommandButton1 мы должны набрать другое имя (без пробелов). В качестве примера введем имя Кнопка1. Теперь выберем свойство Caption, которое означает надпись на кнопке. Измените значение этого свойства (например, на название Ввод текста). Значение свойства Caption можно писать с пробелами. Далее поменяем цвет кнопки. Для этого изменим значение свойства BackColor.

Можно также поменять шрифт букв – для этого существует свойство Font. За цвет букв отвечает свойство ForeColor. В результате кнопка на рабочем листе может принять примерно следующий вид (рис.2.5).

Свойство Visible отвечает за видимость элемента в режиме программы. Если свойство Visible принимает значение True, элемент видим. Если False – невидим.

Рис. 2.5.

Теперь разместим на листе другой элемент управления – Текстовое окно (рис. 2.6). В списке элементов оно носит название «поле». Для размещения этого элемента необходимо щелкнуть на пиктограмме элемента поле, и далее при нажатой левой кнопке мыши разместить этот элемент на рабочем листе.

Рис. 2.6.

Установите значение свойства Namе этого нового элемента – ТекстовоеОкно. Выберем цвет (свойство BackColor ) текстового окна серым. Далее установим свойство MultiLine в значение True. В этом случае длинный текст в этом окне будет переноситься со строки на строку.

Теперь перейдем к обработке событий. Наша задача сделать так, чтобы при щелчке по кнопке в текстовом окне выводился текст (например, "Пример вывода текста"). Разберем, как это сделать.

Перейдите в режим конструктора. Далее откройте редактор для написания исходного кода. Для этого можно дважды щелкнуть по созданной кнопке (Кнопка1). Откроется окно редактора исходного кода (Рис. 2.7). Другой способ перехода в среду для написания кода – меню Сервис, далее пункты Макросы и Редактор Visual Basic).

Щелчок по созданной кнопке должен обеспечить вывод в текстовое окно соответствующего текстового сообщения. Для этого в левом списке (риснеобходимо выбрать объект Кнопка1, а затем в правом списке событие Click (щелчок по кнопке). На рис.2.8 показан результат – на экране появляется окно процедуры, которая будет выполняться при щелчке по кнопке.

Рис. 2.7.

Первое слово Private говорит о том, что это внутренняя процедура только для данного листа. Для других листов эта процедура недоступна. Альтернативным вариантом является Public (общая процедура – в этом случае доступна для других листов). Слово Sub говорит о том, что это процедура. Дальнейшая запись Кнопка1_Click( ) – имя процедуры. Это имя формируется автоматически из двух составляющих - Кнопка1 (имя кнопки) и Click (действие – щелчок по кнопке). Это так называемая предопределенная процедура – она существует и выполняется всегда - когда мы щелкаем по кнопке.

Рис. 2.8.

Запись End Sub говорит о завершении рассматриваемой процедуры. Теперь между этими двумя строками напишем программный фрагмент (рис. 2.9).

Рис. 2.9.

Прокомментируем новые моменты, присутствующие на рис. 2.9. Словосочетание ТекстовоеОкно. Text говорит о том, что выбирается объект с именем ТекстовоеОкно и изменяется его свойство Text (фактически это – текст, который располагается в этом текстовом окне). Знак равенства и последующая фраза говорят о том, что рассматриваемому свойству присваивается значение Пример вывода текста. Теперь перейдем из окна редактора Visual Basic в Excel (рис. 2.10). В результате щелчка по кнопке в текстовом окне появится заданный текст.

Рис. 2.10.

Создадим еще одну кнопку, щелчок по которой будет приводить к удалению текста в текстовом окне. Зададим имя (Name) «Кнопка2». Надпись на кнопке Caption – Удаление текста (рис. 2.10).

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

Рис. 2.11

Две кавычки означают пустую строку.

2.2. СПИСОК И ПОЛЕ СО СПИСКОМ

Рассмотрим теперь следующий элемент управления – поле со списком (рис. 2.12). Этот элемент применяется для хранения списка значений. В этом элементе всегда отображается только одно значение списка. После активизации поля со списком список раскрывается и из него можно выбрать необходимый элемент. Кроме свойства Name у данного элемента есть несколько важных свойств. Рассмотрим их для дальнейшего использования.

Рис. 2.12

Создайте на листе элемент Поле со списком и откройте окно его свойств. Свойства Name, Visible, BackColor, ForeColor аналогичны свойствам кнопки. Обратите внимание, что свойство Caption отсутствует, как и у текстового окна.

Свойства, которые будут рассматриваться ниже: ListIndex, ListCount, в окне свойств не отображаются. Они будут фигурировать только в тексте программы.

Свойство ListIndex содержит номер текущего элемента списка (того, который подсвечен – на нем щелкнули мышкой при выборе). Нумерация элементов списка начинается с нуля. Фактически по значению этого свойства можно определить, какой элемент списка ему соответствует. Свойство ListCount содержит число элементов списка, а свойство Text выбранный в списке элемент.

Теперь рассмотрим наиболее используемые методы работы со списком. Метод Clear удаляет все элементы из поля со списком. Синтаксис использования:

ИмяОбъекта.Clear

Метод AddItem добавляет элемент в список. Синтаксис использования:
ИмяОбъекта.AddItem элемент. Метод RemoveItem – удаление элемента из списка. Синтаксис использования:

RemoveItem номер удаляемого элемента

Задание 2.2. Разработаем на рабочем листе следующий программный фрагмент (рис. 2.13).

Рис. 2.13.

Необходимо обеспечить выполнение следующих действий:

·  по щелчку по кнопке Заполнение список заполняется строками – яблоко, груша, слива, дыня, арбуз;

·  по щелчку по полю со списком выбранный элемент помещается в текстовое окно;

·  по щелчку по кнопке Очистка текстовое окно и поле со списком очищаются – становятся пустыми.

Для заполнения поля со списком по щелчку по кнопке Заполнение необходимо процедуру Заполнение_Click( ) оформить так как показано на рис. 2.14.

Рис. 2.14.

Далее для заполнения текстового окна по щелчку по полю со списком (при выборе того или иного элемента списка) необходимо процедуру Список_Click ( ) оформить так как показано на рис. 2.15.

Рис. 2.15.

Теперь для очистки списка и текстового окна необходимо написать обработку события Щелчок по кнопке Очистка (рис. 2.16).

Рис. 2.16.

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

2.3. ЛИНЕЙКА ПРОКРУТКИ

Рассмотрим работу с линейкой прокрутки (элемент управления ScrollBar). Откройте новый лист, на котором расположите линейку прокрутки и текстовое окно (рис.2.17).

Рис. 2.17.

Рассмотрим свойства:

·  Value – значение, соответствующее положению ползунка на линейке прокрутки ;

·  Max - значение, соответствующее правому крайнему положению ползунка на линейке прокрутки;

·  Min - значение, соответствующее левому крайнему положению ползунка на линейке прокрутки;

·  SmallChange – значение, соответствующее изменению Value при щелчках по стрелкам линейки прокрутки;

·  LargeChange – значение, соответствующее изменению Value при щелчках по полосе линейки прокрутки.

Установим Min = 0, Max =100, SmallChange = 1, LargeChange = 5. Далее введите функцию, которая выполняется при передвижении движка линейки прокрутки:

Private Sub Линейка_Change()

ТекстовоеОкно. Text = Линейка. Value

End Sub

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

2.4. РИСУНОК

Элемент управления Рисунок (Image) служит контейнером для размещения рисунка. Свойство Picture определяет, какой рисунок вставлен в контейнер – рис. 2.18. Свойство PictureSizeMode позволяет устанавливать нужный размер рисунка. Значение этого свойства 1- fmPictureSizeModeStretch позволяет сохранить пропорции рисунка. Для помещения рисунка в контейнер используется метод LoadPicture. Пример записи:

Image1.Picture = LoadPicture("c:\Windows\Паркет. bmp")

В скобках, в кавычках, указывается полный путь к файлу и название файла с указанием расширения рисунка.

Будьте внимательны с указанием расширения рисунка - у разных типов рисунков оно разное. Например, в папке Windows хранятся рисунки для оформления Рабочего стола. Эти рисунки имеют расширения. bmp. Примеры расширений графических файлов: .TIFF; .PNG; .GIF; .BMP.

Для удаления рисунка используется программный код:

Image1.Picture = LoadPicture("")

Две кавычки означают, что рисунка в контейнере нет.

Рис. 2.18

Выполните упражнение. Создайте программный фрагмент, показанный на рис. 2.18. Запишите программные коды для командных кнопок для размещения и удаления картинки в контейнере Image.

ГЛАВА 3. ПРИМЕРЫ ПРОГРАММИРОВАНИЯ НА VBA

В этом разделе рассмотрим примеры, поясняющие возможности VBA. Первая задача – разобраться с работой операторов цикла. Цикл – это повторяющиеся действия. Циклы бывают разных типов: циклы с условием, циклы с предусловием, циклы с известным числом повторений. Цикл с условием выполняется, пока не наступит некоторое событие – например, некоторая переменная примет заданное значение. Цикл с известным числом повторений будет повторяться, пока некоторая переменная, называемая счетчиком, не «пробежит» заданный диапазон значений – от начального до конечного. Рассмотрим работу двух типов цикла.

Задание 3.1. Разработаем программный фрагмент следующего содержания. На рабочем листе располагается кнопка, при нажатии на которую происходит подсчет суммы всех чисел, начиная от 1 до 10 . На рис. 3.1 показан результат действия этой программы - после щелчка по кнопке появляется окно с сообщением о подсчитанной сумме.

Рис.3.1

Применим оператор цикла с известным числом повторений. Синтаксис этого цикла следующий:

For <i = начальное значение> To <конечное значение> Step

<группа операторов>

Next

Где i - переменная типа счетчик;

Step – шаг изменения переменной типа счетчик. Если шаг равен единице, то он не записывается в тексте программы;

<группа операторов> - это повторяющиеся действия;

Next – ключевое слово, после которого цикл снова идет к своему началу или завершается.

Для вывода на экран значения суммы будем использовать функцию MsgBox(), которая вызывает диалоговое окно с сообщением.

Создадим на листе кнопку и назовем ее Счет. Откроем редактор исходного кода и введем переменную для обозначения суммы (Sum) типа Integer. Ввод и описание переменных производится в особой области исходного кода General_Declarations (Рис.3.2). Dim – определить (ключевое слово Visual Basic), далее Sum – имя переменной, As Integer - определение типа данной переменной как целого числа.

Рис.3.2.

Для щелчка по кнопке Счет1 запишем программный код, показанный на рис. 3.3.

Рис.3.3.

Поясним эту запись:

Sum = 0’ переменной Sum присваиваем значение 0.

For i =1 To 10’ счетчик пробегает значения от 0 до 10;

Sum = Sum + i’ при каждом изменении счетчика сумма будет увеличиваться на значение равное i.

MsgBox(Sum) - для вывода значения на экран используется функция вывода окна с сообщением.

Задание 3.2. Использование оператора цикла с условием

Создадим следующий программный фрагмент. На рабочем листе располагаются две кнопки (Старт и Стоп). Также на листе располагается текстовое окно, в котором при щелчке по кнопке Старт начинает постоянно отображаться текущее время. После щелчка по кнопке Стоп постоянное обновление времени останавливается. На рис. 3.4. показан вид рабочего листа с необходимыми элементами управления.

Рис. 3.4.

Первый шаг – введем переменную (индикатор остановки обновления времени). Эта переменная должна быть известна как процедуре обработки щелчка по кнопке Старт, так и процедуре обработки щелчка по кнопке Стоп. Введем ее в области General, как и в предыдущем примере.

Dim s As Integer

Далее на рис. 3.5. приведена процедура обработки события – щелчка по кнопке Старт (CommandButton1).

Рис. 3.5.

Идея заключается в том, что переменной s присваивается начальное значение 0. Далее в цикле мы будем постоянно выводить текущее время в текстовое окно. При этом, если значение переменной s изменится (с 0 на 1), то вывод текущего времени прекращается. Это изменение происходит при щелчке по кнопке Стоп.

Но сначала о процедуре на рис. 3.5. Здесь выполняется цикл от строки While 2 > 1 до Wend. А именно, пока выполняется условие (While 2> 1), выполняются все строки программы до строки Wend (это ключевое слово помечает нижнюю границу цикла). Затем программа возвращается опять к строке While 2 > 1 .

Далее опять выполняются все строки программы до строки - Wend. И, так как условие всегда выполняется, то цикл может выполняться бесконечное число раз. В строке ТекстовоеОкно. Text = Now() происходит обращение к внутренней функции Visual Basic - Now(). Эта функция возвращает текущую дату и текущее время, которые затем присваиваются свойству Text объекта ТекстовоеОкно. Далее по рис.3.5 следует – DoEvents. Это функция Visual Basic, которая позволяет переключаться на обработку других событий (Events - события). В данном случае программа перейдет к обработке щелчка по кнопке Стоп. Далее в программном фрагменте располагается условие:

If s = 1 Then

Exit Sub

End If

Эта конструкция означает, что если s = 1, то выполняются все строки до End If. В нашей ситуации здесь одна строка Exit Sub, которая означает выход из процедуры. Значение s = 1 может установить только щелчок по кнопке Стоп (рис. 3.6).

Рис. 3.6.

После запуска (выхода из режима конструктора и щелчка по кнопке Старт) рабочий лист должен иметь вид, показанный на рис.3.4.

Задание 3.3. Рассмотрим программный пример с использованием элемента управления список (ListBox). На рис.3.7 показан вид рабочего листа программного фрагмента, который нам предстоит разработать.

Рис. 3.7.

Основные свойства и методы элемента список аналогичны свойствам и методам элемента поле со списком. Разместим на листе два списка. Дальнейшие шаги – заполнить список несколькими словами примерно поровну. При двойном щелчке мышкой на том или ином элементе этот элемент должен перемещаться в соседний список, а из текущего удаляться. Заполнение списков можно выполнить в предопределенной процедуре Worksheet_Activate(). Эта процедура всегда выполняется, когда происходит переключение на текущий лист с другого листа (щелчок по корешкам листов в нижней части Рабочего листа Excel).

На рис. 3.8. показан программный код для данной процедуры.

Рис. 3.8.

Здесь мы сначала очищаем списки, а затем заполняем их данными. Далее нам необходимо написать процедуры, которые будут автоматически выполняться при двойном щелчке по левому и правому списку. Тексты этих процедур приведены на рис.3.9 и рис.3.10.

Рис. 3.9.

Теперь программный фрагмент готов и Вы можете его протестировать. Отметить только важный момент. Заполнение списков происходит при событии – активизация листа. Это событие происходит тогда, когда производится переход на этот лист с другого листа. Поэтому для работы программы необходимо сначала активизировать какой-нибудь другой лист, а затем вернуться на рассматриваемый.

Рис. 3.10.

Задание 3.4. Рассмотрим теперь свойство, которое позволяет разрешать и запрещать доступ к элементам управления. У всех элементов управления есть свойство Enabled . Когда это свойство принимает значение True, то доступ к данному элементу управления разрешен. В противном случае (свойство принимает значение False) доступ к данному элементу запрещен. Наша задача – разместить на листе две кнопки. Далее щелчок по одной из кнопок приводит к тому, что эта кнопка становится недоступной, а к другой кнопке, наоборот, доступ разрешается. Поэтому введите следующие функции обработки событий – щелчков по кнопкам:

Private Sub Кнопка1_Click()

Кнопка1.Enabled = False

Кнопка2.Enabled = True

End Sub

Private Sub Кнопка2_Click()

Кнопка2.Enabled = False

Кнопка1.Enabled = True

End Sub

Выйдите из режима конструктора, запустите программу на выполнение и пощелкайте по кнопкам.

Задание 3. 5. Рассмотрим еще один пример. Задача заключается в том, чтобы разместить на листе кнопку, и эта кнопка при попытке навести на нее курсор должна автоматически перемещаться на другое место на рабочем листе. Для этого перейдем в режим конструктора и далее в процедуру Кнопка1_MouseMove(). Эта процедура (рис. 3.11) автоматически выполняется при наведении курсора мыши на кнопку. В приведенном тексте используется внутренняя функция Visual Basic – rnd. Эта функция при обращении к ней выдает случайное число в интервале от 0 до 1. В результате при попадании курсора мыши на кнопку происходит вызов процедуры Кнопка1_MouseMove(). Итог выполнения данной процедуры - положение кнопки меняется (Left – левая граница кнопки, Top - смещение кнопки по вертикали).

Рис. 3.11.

ГЛАВА 4. СВОЙСТВА И МЕТОДЫ ОБЪЕКТОВ EXCEL

До настоящего времени мы рассматривали возможности Visual Basic без особой связи с информацией на рабочем листе. В этом разделе мы познакомимся с объектами VBA, которые позволяют работать с информацией в книгах Excel. Для работы с ячейками Excel в VBA существует объект Range – диапазон. Если установить в качестве параметра объекта Range значение А1, то тем самым мы выбираем объект – ячейка A1. Для изменения содержимого этой ячейки у объекта Range есть свойство Value. Буквально оно означает значение ячейки ( или группы ячеек). Запись Range("A1").Value = "Фамилия" позволяет программно внести в эту ячейку слово – Фамилия). Также, если в качестве параметра Range использовать обозначение другой ячейки, то можно обратиться и к ней.

Задание 4.1. ИЗМЕНЕНИЕ СВОЙСТВ ЯЧЕЕК

Наша задача – разработать на рабочем листе следующий программный фрагмент. Создадим командную кнопку (Имя - Ввод). При щелчке по кнопке на рабочем листе должны заполняться информацией следующие ячейки: A1 - Фамилия, A2 - Имя, A3 - Адрес. Для этого в окно кода программы обработки щелчка по кнопке необходимо написать следующие строки:

Range("A1").Value = "Фамилия"

Range("A2").Value = "Имя"

Range("A3").Value = "Отчество"

Альтернативным способом работы с ячейками является объект Cells. Синтаксис использования этого объекта - Cells (номер строки, номер столбца). Так, ячейку A2 можно выбрать двумя эквивалентными способами : Range(“A2”) или Cells(2,1) .

Как известно, рабочая книга – это совокупность рабочих листов. Для работы с листами можно использовать коллекцию объектов - листов текущей книги следующим образом: Worksheets ( номер листа в книге). В качестве примера напишем фрагмент, который устанавливает значения двух ячеек на разных листах( может выполняться по щелчку по кнопке):

Worksheets(3).Cells(5, 1).Value = 5

Worksheets(2).Range(“C5”).Value = “Строка”

Задание 4.2. РАЗРАБОТКА БЛАНКА ЗАКАЗА

Теперь, после того как мы познакомились с простыми примерами использования VBA, пора перейти к более сложной задаче. Мы должны разработать удобный бланк заказа для обслуживания покупателей в компьютерном салоне. Ваша задача подобрать необходимую конфигурацию компьютера из возможных вариантов по прайсу. Для упрощения ситуации будем считать, что комплектация компьютера состоит максимум из 3 компонентов: системного блока, монитора и принтера. При этом различных системных блоков, мониторов и принтеров много. Эта номенклатура отражается на одном из листов книги – лист с названием Прайс. Вариант этого листа (не в полном объеме) представлен на рис. 4.1. Заметим, что это второй рабочий лист в последовательности листов книги (для того, чтобы последующие строки программы работали без изменений, необходимо сделать так же).

Рис.4.1.

Cтолбцы А и В содержат соответственно описание системного блока и его стоимость. Следующие два столбца – название принтера со стоимостью, и далее монитор с ценой. Наша задача – обеспечить на другом рабочем листе (на первом) удобный электронный бланк заказа с использованием информации из прайса. Вид этого бланка представлен на рис. 4.2.

Рис. 4.2.

Разберем теперь технические действия на листе 1 (рис. 4.2). Расширим столбцы A и B, а затем в ячейку В2 введем текст - «Бланк заказа». Увеличим шрифт этого текста и далее разместим на рабочем листе элемент надпись (Label). Мы ранее не рассматривали элемент управления надпись (Label), но он достаточно прост и его роль заключается в создании поясняющего текста к какому-либо другому объекту. На рис. 4.3 показан элемент Надпись в Office 2007.

Поменяйте значения свойств элемента Надпись следующим образом: свойство Caption установим - Заказ №, а свойство BackColor сделайте серым.

Рис. 4.3.

Далее разместите на листе текстовое окно (TextBox) для отображения номера заказа. Имя (свойство Name) оставляем TextBox1. Установите серый фон текстового окна (с помощью свойства BackColor). Далее установите для свойства AutoSize значение True. В этом случае ширина текстового поля будет автоматически увеличиваться при вводе символов. Теперь заполните текстом (как показано на рис. 4.2) содержимое ячеек A5, B5, C5 , A7, A8 , A9 , A12. После этого уберите сетку с экрана (меню Сервис, раздел Параметры и далее следует убрать отметку с отображения сетки).

В Office 2007 сетка убирается на вкладке «Вид», подложка выбирается на вкладке «Разметка страницы». Для прямоугольного диапазона A5 – C14 установите внешние и внутренние границы. Установите любую светлую заливку для рассматриваемого диапазона и выберите подложку для листа. Подложка выбирается с помощью команды Формат – Лист – Подложка (Office 2003).

Разместите три элемента управления – Поле со списком. Имена этим спискам дайте Spisok1, Spisok2 и Spisok3.

Теперь мы подошли к написанию программного кода. Для этого перейдите в редактор VBA . Наша задача заключается в том, чтобы при открытии книги списки автоматически заполнялись содержимым листа – Прайс.

Для заполнения списков будем использовать процедуру Workbook_Open(), которая автоматически выполняется при открытии книги. Эта процедура выполняется над объектом Workbook (текущая рабочая книга). На рис. 4.4 показано окно редактора кода с заголовком данной процедуры. В том случае, если Вы не видите в левой части редактора окна Проекта и объекта Workbook, нажмите на панели инструментов кнопку «Project Explorer» (выделено рамкой).

 

 

Рис. 4.4.

На рисунке 4.5 показан программный код для заполнения первого списка. Далее приводится объяснение каждой строки данной процедуры.

Рис. 4.5.

Строки после апострофа – это комментарии для Вас. Не вводите их в текст программы.

Private Sub Workbook_Open( )

‘Worksheets(1) – первый лист нашей книги, Spisok1 – первый список,

‘Clear – метод, заключающийся в очистке списка.

Worksheets(1).Spisok1.Clear

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

N = 0

While Worksheets(2).Cells(N + 2, 1).Value <> “”

N = N + 1

Wend

‘ N – переменная, в которой мы таким образом подсчитали количество

записей в первом столбце прайса. В качестве условия у нас используется следующая конструкция

‘ Worksheets(2).Cells(N + 2, 1).Value < > "".

‘ Здесь Worksheets(2) – второй лист книги и этот лист - Прайс.

‘ Cells (…) – функция выбора ячейки.

‘ В скобках указывается номер строки и номер столбца.

‘ Знак < > обозначает не равно.

‘ Далее "" – обозначает, что ячейка пустая. Таким образом, в совокупности эта запись обозначает выполнение цикла, пока значение в очередной ячейки в списке системных блоков не окажется пустым.

‘ В итоге мы подсчитали количество непустых ячеек в списке системных блоков (фактически сколько системных блоков в прайсе).

For i = 2 To N + 2

Worksheets(1).Spisok1.AddItem Worksheets(2).Cells(i, 1).Value

Next

‘ В записи Worksheets(1).Spisok1 используется список Spisok1 на первом рабочем листе. Метод AddItem добавляет в список строку. Далее через пробел записывается строка, которая добавляется, а именно значение

‘ (свойства Value) ячейки ( Cells( i, 1) ) из листа Worksheets(2).

‘ В следующем фрагменте производится аналогичное заполнение второго списка.

Worksheets(1).Spisok2.Clear

N = 0

While Worksheets(2).Cells(N + 2, 3).Value <> ""

N = N + 1

Wend

For i = 2 To N + 2

Worksheets(1).Spisok2.AddItem Worksheets(2).Cells(i, 3).Value

Next

‘В следующем фрагменте производится заполнение третьего списка.

Worksheets(1).Spisok3.Clear

N = 0

While Worksheets(2).Cells(N + 2, 5).Value <> ""

N = N + 1

Wend

For i = 2 To N + 2

Worksheets(1).Spisok3.AddItem Worksheets(2).Cells(i, 5).Value

Next

End Sub

Таким образом, процедура Workbook_Open() обеспечивает заполнение списков данными со второго листа (из прайса).

Далее на рис. 4.6 показана процедура, выполняемая при щелчке по первому полю со списком (Spisok1), когда мы выбираем из списка ту или иную строку.

Рис. 4.6.

Строка Range("c7").Value =Worksheets(2).Cells(Spisok1.ListIndex + 2, 2). Value позволяет заполнить ячейку С7 ценой системного блока. При этом Spisok1.ListIndex – индекс элемента, который выделен щелчком мыши. Щелчки по двум другим спискам приводят к аналогичным действиям ( они приводят к заполнению цен монитора и принтера). Эти процедуры приводятся далее.

Private Sub Spisok2_Click()

Range("c8").Value = Worksheets(2).Cells(Spisok2.ListIndex + 2, 4).Value

End Sub

Private Sub Spisok3_Click()

Range("c9").Value = Worksheets(2).Cells(Spisok3.ListIndex + 2, 6).Value

End Sub

Таким образом в результате мы получили автоматизированное заполнение бланка заказа (рис. 4.7).

Рис. 4.7.

В ячейку С12 вставим функцию для вычисления суммы.

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

Рис. 4.8.

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

Private Sub CommandButton1_Click()

Worksheets(3).Cells(10, 2).Value = Worksheets(1).Cells(7, 1).Value + " "

+ Spisok1.Text

‘ На третьем листе будет автоматически формироваться печатная форма бланка. Ячейка на пересечении 10-й строки и второго столбца будет содержать информацию о системном блоке. В эту ячейку записывается информация с первого листа – о названии системного блока.

Worksheets(3).Cells(10,3).Value = Worksheets(2).Cells(Spisok1.ListIndex + 2,2).Value

‘Ячейка на пересечении 10-й строки и третьего столбца будет содержать информацию о стоимости системного блока.

‘ В эту ячейку записывается информация из второго листа с учетом выбора из списка.

Worksheets(3).Cells(11, 2).Value = Worksheets(1).Cells(8, 1).Value + " " + Spisok2.Text

Worksheets(3).Cells(11, 3).Value = Worksheets(2).Cells(Spisok2.ListIndex + 2,4).Value

‘ Ячейка на пересечении 11-й строки и второго столбца будет содержать информацию о принтере. В эту ячейку записывается информация с первого листа – название принтера.

‘ Ячейка на пересечении 11-й строки и третьего столбца будет содержать информацию о стоимости принтера. В эту ячейку записывается информация из второго листа, соответствующая выбранному названию из списка.

Worksheets(3).Range("c3").Value = TextBox1.Text

Worksheets(3).Activate

End Sub

В приведенной процедуре не предусматривается заполнения параметров монитора. Добавьте эту запись самостоятельно.

Задание 4.3. РАЗРАБОТКА ЗАЯВКИ НА ПОСТУПЛЕНИЕ ТОВАРОВ

Наша задача - выполнить задание, близкое по содержанию предыдущему, а именно, Вам необходимо разработать удобное приложение для составления заявки на канцтовары. Создайте новую книгу Excel и на втором рабочем листе создайте список некоторых товаров с их ценами (рис. 4.9).

Рис. 4.9.

Далее на первом рабочем листе (рис. 4.10) должна быть расположена удобная форма для ввода информации о заказе.

Рис. 4.10.

Текст в ячейки А3:D5 вписывать не надо, они заполнятся при дальнейшем выполнении программы.

Оформим столбцы A, B ,C и D следующим образом: установим границы, введем названия в ячейки A2, B2, C2 и D2.

Далее расположим поле со списком ( Name – Spisok1), две метки – Label1 (Caption - Итог) и Label2, три кнопки Очистить, Пересчет и Печать (рис. 4.11). Далее уберем сетку с экрана.

Рис. 4.11.

Теперь наша задача – сделать так, чтобы Spisok1 автоматически заполнялся при открытии книги. Для этого процедуру, автоматически выполняемую при открытии книги, оформите следующим образом:

Private Sub Workbook_Open()

Worksheets(1).Spisok1.Clear

‘ Очистка списка. И далее подсчет количества записей на втором листе – в прайсе.

N = 0

While Worksheets(2).Cells(N + 1, 1).Value <> ""

N = N + 1

Wend

‘Заполнение списка

For i = 1 To N

a1 = Worksheets(2).Cells(i, 1).Value

a2 = Worksheets(2).Cells(i, 2).Value

a = a1 & " " & a2 & " " & " руб."

Worksheets(1).Spisok1.AddItem a

Next

End Sub

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

Private Sub Spisok1_Click()

‘ Подсчет в переменной N заполненных строк бланка заказа

N = 0

While Worksheets(1).Cells(N + 3, 1).Value <> ""

N = N + 1

Wend

Worksheets(1).Cells(N + 3, 1) = Worksheets(2).Cells(Spisok1.ListIndex + 1, 1).Value

Worksheets(1).Cells(N + 3, 2) = Worksheets(2).Cells(Spisok1.ListIndex + 1, 2).Value

‘ Далее используется функция InputBox() для ввода значения количества товара.

‘ А именно то, что мы вводим, заносится в переменную qw.

qw = InputBox("Введите количество", "Ввод числа", 1)

Worksheets(1).Cells(N + 3, 3).Value = qw

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

Worksheets(1).Cells(N + 3, 4).Value =

qw * Worksheets(1).Cells(N + 3, 2).Value

Label2.Caption = Str(Val(Label2.Caption) + Worksheets(1).Cells(N + 3, 4))

End Sub

Здесь используются стандартные функции Visual Basic Val (для перевода из тестового вида в числовой) и Str (для перевода из числового вида в текстовый).

Мы пока не создали обработку событий по кнопкам Пересчет и Очистка. Это мы сделаем чуть позже, а пока оформим третий лист так, как показано на рис. 4.12.

Наша задача сделать так, чтобы по кнопке Печать заполнялся бланк.

Рис. 4.12.

Private Sub Печать_Click()

‘ Подсчет строк в бланке заказа

N = 0

While Worksheets(1).Cells(N + 3, 1).Value < > ""

N = N + 1

Wend

For i = 0 To N-1

‘Заполнение номера позиции

Worksheets(3).Cells(14 + i, 1) = i + 1

‘Заполнение названия позиции заказа

Worksheets(3).Cells(14 + i, 2) = Worksheets(1).Cells(3 + i, 1)

‘Заполнение цены единицы товара

Worksheets(3).Cells(14 + i, 3) = Worksheets(1).Cells(3 + i, 2)

‘Заполнение количества товара

Worksheets(3).Cells(14 + i, 4) = Worksheets(1).Cells(3 + i, 3)

‘Заполнение цены с учетом количества

Worksheets(3).Cells(14 + i, 5) = Worksheets(1).Cells(3 + i, 4)

Next

‘Активизация третьего листа

Worksheets(3).Activate

End Sub

Теперь вернемся к первому листу и рассмотрим процедуру, вызываемую по кнопке Очистить:

Private Sub Очистить_Click()

N = 0

While Worksheets(1).Cells(N + 3, 1).Value <> ""

N = N + 1

Wend

For i = 3 To N + 2

Worksheets(1). Cells(i, 1).Value = ""

Worksheets(1). Cells(i, 2).Value = ""

Worksheets(1). Cells(i, 3).Value = ""

Worksheets(1). Cells(i, 4).Value = ""

Next

Label2.Caption = ""

End Sub

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

Private Sub Пересчет_Click()

N = 0

While Worksheets(1).Cells(N + 3, 1).Value <> ""

N = N + 1

Wend

symma = 0

For i = 3 To N + 2

a = Worksheets(1).Cells(i, 3).Value

Worksheets(1).Cells(i,4).Value =Worksheets(1).Cells(i, 2).Value * a

symma = symma + Worksheets(1).Cells(i, 4).Value

Next

Label2.Caption = Str(symma)

End Sub

При открытии книги мы рассматривали ранее процедуру Workbook_Open(). В процессе работы с книгой мы можем на втором листе вносить изменения в номенклатуру товаров. Чтобы эти изменения сразу же отражались в уже открытой книге, аналогичные действия выполним в процедуре Worksheet_Activate():

Private Sub Worksheet_Activate()

Worksheets(1).Spisok1.Clear

N = 0

While Worksheets(2).Cells(N + 1, 1).Value <> ""

N = N + 1

Wend

For i = 1 To N

a1 = Worksheets(2).Cells(i, 1).Value

a2 = Worksheets(2).Cells(i, 2).Value

a = a1 & " " & a2 & " " & " руб."

Worksheets(1).Spisok1.AddItem a

Next

End Sub

Задание 4.4. Разработка автоматизированного бланка

Начнем разработку следующего рабочего листа (рис. 4.13). Следует заметить, что на представленном фрагменте используются не элементы управления, а просто ячейки, отформатированные определенным образом. При этом сами фамилии набирать не требуется (необходимо лишь подобрать заливку и размер шрифта для вводимого в последующем текста). В качестве первого действия следует убрать сетку с рабочего листа и далее отформатировать ячейки (рис. 4.14).

Рис.4.13

Теперь расположим на листе элементы управления (рис.4.14).

Рис. 4.14

На рис. 4.14 присутствует новый элемент – переключатель (OptionButton). Этот элемент позволяет обеспечить два состояния - включено и выключено.

Идея следующая – человек, заполняющий бланк, указывает (щелчком по одному из переключателей), где он проживает в Нижнем Новгороде или в другом городе. При этом в случае выбора другого города в текстовом окне указывается город. На листе мы реализуем оформление следующим образом. В начальном виде установлен вариант - Н. Новгород, а текстовое окно city для выбора города невидимо (Visible = False). При щелчке на переключателе Другой город текстовое окно city становится видимым (Visible = True).

Напишем процедуры обработки щелчков по переключателям:

Private Sub OptionButton1_Click()

OptionButton2.Value = False

City. Visible = False

End Sub

Private Sub OptionButton2_Click()

OptionButton1.Value = False

City. Visible = True

End Sub

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

Создадим элементы, показанные на рис. 4.15, 4.16 и 4.17.

Рис. 4.15

Рис. 4.16.

Рис.4.17

После всех указанных действий лист примет вид, показанный на рис. 4.18.

Рис.4.18.

Теперь приведем процедуры (выполняемые по щелчкам на объектах) для рабочего листа:

Private Sub St_Click()

TextBox1. Visible = True

TextBox2. Visible = True

TextBox3. Visible = False

TextBox4. Visible = False

End Sub

Private Sub Sp_Click()

TextBox1. Visible = False

TextBox2. Visible = False

TextBox3. Visible = True

TextBox4. Visible = True

End Sub

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

Откройте в режиме конструктора окно свойств переключателя Н. Новгород (OptionButton1) и измените свойство GroupName (вместо Лист1 введите Лист1_2). То же самое сделайте в окне свойств переключателя Другой город (OptionButton2). Тем самым мы выделили эти переключатели в новую группу. Теперь на созданной форме можно будет включить два переключателя.

Следующая задача – запись на второй лист введенных данных. На рис. 4.19 показана таблица данных, которая будет заполняться автоматически по щелчку по кнопке.

Рис.4.19.

При этом для отображения полной картины выполненных действий на рис.4.20 показан первый лист в рабочем состоянии с введенными данными.

Рис.4.20.

Напишем обработку события щелчка по кнопке с названием - Записать на лист 2. Текст процедуры, обрабатывающей данное событие, приводится далее.

Private Sub CommandButton2_Click()

‘ Подсчет количества записей на втором листе.

N = 0

While Worksheets(2).Cells(N + 2, 1).Value <> ""

N = N + 1

Wend

‘Заполнение очередной строки. Номер записывается в первую колонку.

Worksheets(2).Cells(N + 2, 1).Value = N + 1

‘Фамилия записывается во вторую колонку.

Worksheets(2).Cells(N + 2, 2).Value = Range("c2")

‘Имя записывается в третью колонку.

Worksheets(2).Cells(N + 2, 3).Value = Range("c4")

‘Отчество попадает в четвертую колонку.

Worksheets(2).Cells(N + 2, 4).Value = Range("c6")

‘Название города располагается в пятой колонке.

If OptionButton1.Value = True Then

Worksheets(2).Cells(N + 2, 5).Value = "Н. Новгород"

Else

Worksheets(2).Cells(N + 2, 5).Value = city. Text

End If

‘Вид деятельности расположим в шестой колонке, место работы или

‘ учебы в седьмой, а примечание в восьмой колонке.

If st. Value = True Then

Worksheets(2).Cells(N + 2, 6).Value = "студент"

Worksheets(2).Cells(N + 2, 7).Value = TextBox1.Text + " курс " +

TextBox2.Text

Else

Worksheets(2).Cells(N + 2, 6).Value = "спец. с в/о"

Worksheets(2).Cells(N + 2, 7).Value = TextBox3.Text

Worksheets(2).Cells(N + 2, 8).Value = TextBox4.Text

End If

‘Характеристики, указанные во флагах.

If flag1.Value = True Then

Worksheets(2).Cells(N + 2, 9).Value = "Да"

Else

Worksheets(2).Cells(N + 2, 9).Value = "Нет"

End If

If flag2.Value = True Then

Worksheets(2).Cells(N + 2, 10).Value = "Да"

Else

Worksheets(2).Cells(N + 2, 10).Value = "Нет"

End If

If flag3.Value = True Then

Worksheets(2).Cells(N + 2, 11).Value = "Да"

Else

Worksheets(2).Cells(N + 2, 11).Value = "Нет"

End If

End Sub

Задание 4.5. ИЗМЕНЕНИЕ ЦВЕТА ОБЪЕКТА

Решим следующую задачу. На рабочем листе находится командная кнопка (Name = Цвет). При наведении мыши на левую половину кнопки кнопка должна окрашиваться в красный цвет. При наведении на правую половину – в зеленый. Здесь используется метод MouseMove (наведение мыши).

Цвет объекта устанавливается с помощью свойства BackColor. Для выбора цвета используется внутренняя функция RGB(), которая позволяет получать множество оттенков путем сложения трех основных цветов спектра: R – red, G – green. B – Blue. Согласно цветовой модели RGB каждая из составляющих может меняться от 0 до 255 градаций. Таким образом, цвет кнопки будет определяться следующим выражением:

Цвет1.BackColor = RGB(a, b,c)

a, b и c – числовые значения красного, зеленого и синего оттенков, лежащие в пределах от 0 до 255.

Например:

Цвет. BackColor(255,0,0) – красный цвет.

Цвет. BackColor(255,255,255) – белый цвет.

Цвет. BackColor(255,255,0) – желтый цвет.

Представим кнопку, разделенную осями координат «x» и «y». Используем свойства кнопки Width и Height (ширина и высота) –
рис. 4.21.

0

Рис. 4.21.

Выражение :

x >1/2*Цвет. Width

‘будет означать, что координата лежит «х» лежит правее начала координат (координата больше половины ширины кнопки).

Выражение:

x<1/2*Цвет. Width

‘координата «х» лежит левее начала координат.

Программный код для изменения цвета кнопки показан на рис. 4.22.

Для изменения цвета применен оператор ветвления (If-Then-Else).

Рис. 4.22.

Если в задаче встречается несколько условий (например, необходимо раскрасить кнопку в четыре цвета), используем координату по высоте «у» и свойство кнопки «Height».

В этом случае используем оператор ветвления с ключевым словом ElseIf.

Текст программы показан на рис. 4.23.

Com1- имя раскрашиваемой кнопки.

Рис.4.23.

ГЛАВА 5. ВЫПОЛНЕНИЕ САМОСТОЯТЕЛЬНОЙ РАБОТЫ

Задание 5.1. На рабочем листе расположите элемент управления - кнопку. Напишите программный код, чтобы при наведении курсора мыши на кнопку она могла принимать один из двух цветов. Например, красный, при наведении на верхнюю половину кнопки и синий, при наведении курсора мыши на нижнюю половину кнопки.

Задание 5.2. Разместите на листе окно для рисунка (элемент управления Рисунок). Разработайте программный код так, чтобы при наведении курсора мыши на левую половину окна рисунка, в этом окне отображалась одна картинка, а при попадании курсора мыши на правую половину окна, другая.

Задание 5.3

На втором рабочем листе Рабочей книги создайте список сотрудников, как показано в таблице 5.1.

Таблица 5.1

	A	B	C
1	Петров	Петр	Петрович
2	Иванов	Иван	Иванович
3	Сидоров	Николай	Николаевич

Далее на первом рабочем листе разместите элементы управления, показанные на рис. 5.1.

Рис. 5.1.

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

Далее при выборе фамилии из списка в текстовые поля должны автоматически вводиться соответствующие имя и отчество. При щелчке по командной кнопке «Печать» информация о выбранном лице должна переписываться на третий лист в очередную свободную строку.

Задание 5.4. Заполните второй лист Рабочей книги данными о сотрудниках, в соответствии с таблицей 5.2.

Таблица 5.2

	A	B	C
1	Петров	Петр	Петрович
2	Иванов	Иван	Иванович
3	Сидоров	Николай	Николаевич
4

Третий лист заполните данными о подразделениях (таблица 5.3).

Таблица 5.3.

	A	B	C
1	Администрация
2	Основное производство
3	Транспортный отдел

На первом рабочем введите необходимый текст и создайте элементы управления (рис. 5.2).

Рис.5.2

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

Список литературы, рекомендуемой для чтения

1.  Программирование в Microsoft Excel на примерах +СD-ROM. C. Кашаев/ БХВ-Петербург, 2007 – 320 с.

2. Профессиональное программирование на VBA в Excel 2002. Уокенбах Джон. /М.: Издательский дом "Вильямс", 2003 – 784 с.

3. Microsoft Office Excel 2007. Библия пользователя + CD-ROM. Уокенбах Джон./Диалектика., 2008 – 816 с.

Интернет-ресурсы

1.  http://psbatishev. *****/vb/v014.htm - Основы программирования на Visual Basic. Электронный учебник.

2.  http://www. *****/department/se/vbamsoffice2007/1/ VBA в MS Office 2007. Автор

ПРОГРАММИРОВАНИЕ В MICROSOFT EXCEL

Учебно-методическое пособие

Издание второе, дополненное

Сергей Михайлович Кашаев

Галина Викторовна Курицына

Юрий Васильевич Воронков

Редакторы :

Лицензия ПД от 01.01.2001

Подписано к печати Формат 60х90 1/16

Печ. л. 4,3 Тираж 300 экз. Заказ

Цена договорная

Типография НГЛУ им. Н.А. Добролюбова

Н. Новгород, а.