МЕТОДИЧЕСКОЕ ПОСОБИЕ ПО ПРОГРАММИРОВАНИЮ ДЛЯ MICROSOFT EXCEL НА VISUAL BASIC for APPLICATIONS (VBA)

 

Содержание

 

1. Введение

1.1 Запись макросов

1.2 Выполнение макросов

1.3 Просмотр кода макроса

1.4 Редактирование кода макроса

1.5 Что нельзя сделать с помощью макросов?

1.6 Удаление макросов

1.7 Назначение макросов командным кнопкам

1.8 Назначение макроса графическим изображениям

1.9 Назначение макросов кнопкам панелей инструментов

2.     Введение в процесс разработки приложений

2.1  Типы элементов управления

2.2  Вставка элементов управления в рабочий лист

2.3  Как осуществляется связь элемента управления с рабочим листом?

3.       Программирование на VBA

3.1  Объявление типа переменной

3.2  Общие правила написания имени переменной

3.3  Описание констант

3.4  Область видимости переменных и констант

3.5  Ввод данных

3.6  Функция MsgBox

3.7  Функция InputBox

3.8  Метод InputBox

3.9  Объединение текстовых строк

3.10                   Применение условных операторов

4.       Простой условный оператор

4.1  Сокращённый условный оператор

4.2  Составной условный оператор

4.3  Многозначные ветвления

4.4  Оператор выбора Select Case

4.5  Циклический алгоритм. Оператор цикла For…Next

4.6  Сложный циклический процесс с использованием оператора

4.7  For…Next. Вложенные циклы

4.8  Оператор Exit For

4.9  Программирование циклов Do

4.10                   Цикл Do…While

4.11                   Цикл Do…Until

4.12                   Массивы

5.       Объекты

5.1  Ссылка на объекты в коде VBA

5.2  Работа с объектами

5.3  Коллекции

5.4  Объект Application

5.5  Использование встроенных функций Excel

5.6  Объект Workbook и семейство Workbooks

5.7  Объект Worksheet

5.8  Объект Range

5.9  Оператор With

5.10                   Оператор For Each

6.       Экранные формы

6.1  Обзор встроенных элементов управления

6.2  Кнопка (CommandButton)

6.3  Поле (TextBox)

6.4  Надпись (Label)

6.5  Переключатель (OptionButton)

6.6  Рамка (Frame)

6.7  Флажок (CheckBox) и Выключатель (ToggleButton)

6.8  Полоса прокрутки (ScrollBar) и Счётчик (SpinButton)

6.9  Список (ListBox)Поле со списком (ComboBox)

6.10 Рисунок (Image)

6.11  Набор страниц (MultiPage)

6.12  Набор вкладок (TabStrip)

6.13  Дополнительный элемент управления Календарь (Calendar)

7.       Пример создания приложения

7.1  Автоматизация экранной формы

7.2  Вызов экранной формы

7.3  Проверка вводимых данных

7.4  Сохранение данных, введённых в форму

8.       Диаграммы

8.1  Изменение кода макроса

8.2  Список литературы

 

 

Введение

В условиях развития глобальных информационных процессов, решения социально-экономических проблем актуализации реформы среднего профессионально образования. Соответствующие положения нашли отражение в принятой в конце 2001 года Правительством Российской Федерации Концепции модернизации российского образования на период до 2010 года. Концепция завершает формирование общенациональной образовательной политики Российского государства, развивая положение законов в области образования и Национальной Доктрины образования, а также во впервые принятой Министерством образования РФ за всю историю СПО «Программы развития среднего профессионального образования России на 2000-2005 годы».

Главные направления данных документов положены в основу Программы развития Красногорского оптико-электронного колледжа на 2002-2003 учебный год, Программы информатизации колледжа на 2002-2005 годы. В этих документах особое внимание удалено созданию новых учебников, методических пособий. Методическое пособие по программированию для Microsoft Excel на Visual Basic for Applications (VBA) и является учебным пособием нового поколения, призвано помочь студентам овладеть первоначальными навыками и умениями работы с таким непростым языком программирования как Visual Basic for Applications. Большинство из существующей литературы по VBA носит либо описательный характер, либо справочный. Научиться программировать на VBA можно, только зная теоретические положения, внимательно изучая основы языка на лекционных занятиях. Работая с данным пособием, студент, не имеющий навыков программирования в Excel ,сможет приобрести все необходимые навыки и умения, начиная с создания макросов и заканчивая созданием собственных приложений, с применением автоматизации. Пособие содержит теоретические сведения, которые помогут начинающему пользователю освоить новый для него язык программирования.

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

VBA – отличное средство автоматизации не только простых рутинных операций, которые пользователю приходится периодически выполнять, например, при подготовке текстов в Word или расчётов в Excel, но и сложнейших вычислений над большими объёмами данных.

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

Дело в том, что Microsoft лицензирует ядро VBA и предоставляет технологию его использования другим компаниям, разрабатывающим программное обеспечение для самых разных предметных областей – от картографии до бизнеса. Очевидно, что наборы объектов этих приложений коренным образом отличаются наборов объектов приложений Microsoft Office. Однако язык программирования и технология работы с объектами, их свойствами и методами одни и те же.

Квалифицированному пользователю, профессионально работающему с каким-либо приложением, необходимо как можно лучше знать все объекты приложения, с которыми ему придётся работать. Когда же этому пользователю придётся что-либо автоматизировать для выполнения часто встречающихся операций, то, если в это приложение встроен VBA, пользователю будет предоставлены язык программирования, среда разработки и технология работы с объектами, хорошо знакомые ему, например, по Excel.

Язык Visual Basic for Applications является одной из версий популярного языка разработки приложений Visual Basic. В чём же отличия двух языков?

Язык Visual Basic разрабатывался для создания законченных самостоятельных приложений, VBA используется для автоматизации существующих приложений

Visual Basic имеет собственную среду программирования, VBA использует среду приложения

Для выполнения разработанной программы созданной в Visual Basic, пользователю не обязательно иметь доступ к самой среде программирования, т.к. разработанные приложения можно скомпилировать в .EXE файлы. VBA - приложения могут выполняться только в том приложении, в котором они были созданы

Общими для VBA и VB являются:

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

Во многом похожие средства разработки и отладки программ

Аналогичные модели объектно-ориентированного и событийно-управляемого программирования

Общие библиотеки объектов, механизмы подключения и использования библиотек

Знакомство с одним из языков программирования создаёт основу для успешного изучения другого языка, то есть если вы знакомы с Visual Basic, то легко будете программировать на VBA, верно и обратное утверждение. Даже если Вы начинающий программист, то будете чувствовать себя уверенно в знакомой среде, поскольку Вам будут знакомы и система меню, и панели инструментов, и вообще рабочие книги Excel. Кроме того, пользователь имеет доступ к огромной библиотеке функций Excel.

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

Автоматизировать повторяющиеся действия

Настроить интерфейс Excel с помощью модификации существующих или создания новых панелей инструментов, меню и пользовательских форм

Вносить дополнительную функциональность в среду Excel

 

 

1. Запись макросов

 

Макрос - это записанная последовательность команд и действий пользователя, сохранённая под уникальным именем, которую может выполнить Excel.

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

Имя макроса может содержать до 255 символов и должно начинаться с буквы. В имя могут входить буквы, цифры и знаки подчёркивания. Пробелы в именах макросов не допускаются.

Рассмотрим процедуру записи простого макроса, который изменяет шрифт и цвет в выделенном диапазоне ячеек.

1. Откройте новую рабочую книгу

2. В ячейку А1 введите вашу фамилию, в ячейку В1 - имя, в ячейку С1 - номер группы

3. Перейдите в ячейку А1

4. Выполните команду Сервис/ Макрос/ Начать запись. Появится диалоговое окно Запись макроса, показанное на рис.1

Рис. 1 Диалоговое окно Запись макроса

 

5. Введите название макроса МойПервыйМакрос, и нажмите ОК. На экране появится панель инструментов Остановить запись.

6. Выполните команду Формат/ Ячейки. В диалоговом окне Формат ячеек перейдите на вкладку Шрифт

7. Установите размер шрифта 18 пунктов, цвет - красный. Щёлкните на кнопке ОК

8. Щёлкните на кнопке Остановить запись одноимённой панели инструментов.

 

2. Выполнение макросов

 

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

1. Выделите ячейку В1

2. Выполните команду Сервис/ Макрос/ Макросы. Появится диалоговое окно Макрос

Рис. 2 Диалоговое окно Макрос

 

3. Выделите макрос МойПервыйМакрос и щёлкните на кнопке Выполнить. Шрифт в ячейке В1 станет красным и размером 18 пунктов

4. Проделайте туже последовательность действий для ячейки С1

 

3. Просмотр кода макроса

 

По ходу записи макроса Excel запоминает действия пользователя и одновременно преобразует их в код VBA. Чтобы увидеть полученный код, выполните следующие действия:

 

1. Выберите команду Сервис/ Макрос/ Макросы. Отобразится диалоговое окно Макрос

2. Выделите макрос МойПервыйМакрос и щёлкните на кнопке Изменить. Откроется окно редактора Microsoft Visual Basic, показанное на рис. 3

Рис. 3 Окно редактора Microsoft Visual Basic

 

Первая строка макроса Sub МойПервыйМакрос () - представляет собой начало процедуры выполнения макроса и его имя. Следующие строчки, в начале которых стоит одиночная кавычка, являются комментариями, поясняют отдельные строки макроса.

 

1.4 Редактирование кода макроса

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

Редактировать код можно в редакторе Visual Basic. Попробуем изменить код макроса:

 

1. Выделите строку, которая начинается с .Name

2. Удалите эту строку, нажав клавишу Delete

3. Продолжайте удалять строки, пока не останется следующий фрагмент программы

 

Sub МойПервыйМакрос()

'

' МойПервыйМакрос Макрос

' Макрос записан 02.07.2001 (Serg)

'

 

'

With Selection.Font

.Size = 18

.ColorIndex = 3

End With

End Sub

 

 

 

4. Закройте окно редактора Visual Basic и вернитесь в свою рабочую книгу

5. В ячейке Е1 введите слово Тест

6. Не снимая выделения с ячейки Е1, выполните макрос МойПервыйМакрос, Вы видите, что макрос работает точно также, как и до удаления строк кода

7. Выполните команду Сервис/ Макрос/ Макросы

8. В диалоговом окне Макрос выделите макрос МойПервыйМакрос и щёлкните на кнопке Изменить

9. Измените размер шрифта с 18 пунктов на 24. Код макроса должен выглядеть следующим образом:

 

Sub МойПервыйМакрос()

'

' МойПервыйМакрос Макрос

' Макрос записан 02.07.2001 (Serg)

'

'

With Selection.Font

.Size = 24

.ColorIndex = 3

End With

End Sub

 

10. Закройте окно редактора Visual Basic и вернитесь в рабочую книгу

11. Выделите ячейку А1 и выполните макрос МойПервыйМакрос. Вы заметите, что шрифт в ячейке А1 увеличился