ПРЕДЛАГАЮ КОЛЛЕГАМ

О.А. Житкова, Т.И. Панфилова
Москва

VBA в приложении к Excel, Word и Power Point

В № 1-10/2006 вниманию читателей будет предложен цикл статей (разработок занятий), посвященных использованию VBA (Visual Basic for Application) пакета Microsoft Office. Все материалы прошли многолетнюю апробацию в НОУ “Ломоносовская школа” и могут быть использованы как на уроках, так и во внеурочной деятельности - на факультативах, кружках, при реализации учебных проектов.

Занятие № 1. Основные понятия языка VBA Excel

На занятии рассматриваются следующие вопросы:

общее понятие о языке VBA;
объекты VBA;
свойства, методы и события;
элементы языка VBA;
структура редактора VBA.

VBA относится к языкам объектно-ориентированного программирования, поэтому знакомство с ним естественно начать с понятия объекта.

Объекты

Объект - основной элемент VBA Excel. В VBA объектами являются рабочая книга, рабочий лист и его составляющие.

Примеры объектов:

Sheet - лист Excel;
Cell - ячейка;
Range - диапазон ячеек;
Application - приложение;
UserForm - пользовательская форма.

Доступ к объекту возможен через его методы и свойства.

Методы

Над объектами можно совершать различные действия. Действия, совершаемые над объектами, называются методами. Например, ячейку можно очистить (Clear), приложение закрыть (Quit), пользовательскую форму показать (Show) или скрыть (Hide).

Название метода отделяется от названия объекта точкой: объект.метод.

Примеры использования методов:

Range("B2:E2").Select - выбрать диапазон ячеек B2:E2;

Range("C1:C5").Clear - очистить диапазон ячеек C1:C5;

UserForm2.Hide - скрыть форму № 2;

UserForm5.Show - показать форму № 5;

Application.Quit - выйти из приложения.

Свойства

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

Синтаксис установки значения свойства:

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

- где Объект обозначает имя объекта, Свойство - имя свойства, которому присваивается значение.

Имя объекта отделяется от имени свойства точкой.

Примеры свойств:

Range("D1").Value = 2005 - поместить в ячейку D1 значение 2005.

Range("C1:C10").Text = "Информатика" - поместить в диапазон ячеек C1:C10 текст Информатика.

Range("B2").Font.Size = 14 - в ячейке B2 установить размер шрифта 14.

Методы и свойства некоторых объектов VBA

Объект: Application (приложение).

Метод: Quit (закрыть).

Свойство: Caption (имя главного окна).

Примеры. Application.Quit - закрыть приложение; Application.Caption = "Протокол" - установить в качестве заголовка окна приложения “Протокол”.

Объект: Sheet (лист), ActiveSheet (активный/выбранный лист).

Семейство: Sheets (листы).

Методы: Select (выбрать); ShowDataForm (показать встроенную форму).

Примеры: Sheets("Меню").Select - выбрать лист “Меню”; ActiveSheet.ShowDataForm - на активном в настоящий момент листе показать встроенную форму.

Объект: Range (диапазон).

Методы: Select (выделить); Clear (очистить).

Свойство: Name (имя).

Примеры. Sheets("Протокол").Range("В4:В10").Name = "Класс" - диапазону В4:В10, расположенному на листе “Протокол”, присвоить имя “Класс”; Sheets("Протокол").Range("В4:В10").Select - выделить диапазон В4:В10 на листе “Протокол”.

Объект, Семейство: UserForm (пользовательская форма).

Методы: Show (показать); Hide (скрыть).

Свойство: Caption (текст, отображаемый в строке заголовка).

Примеры. UserForm1.Show - показать пользовательскую форму номер один;

UserForm1.Hide - скрыть пользовательскую форму номер один;

UserForm1.Caption - "Информатика"- вывести заданный в кавычках текст в строке заголовка.

Объект, Семейство: TextBox (Поле ввода).

Свойство: Text (содержимое).

Примеры. UserForm1.TextBox1.Text = Date - в поле ввода номер один в пользовательской форме номер один записать текущую дату; UserForm1.TextBox2.Text = " " - очистить поле ввода номер два в пользовательской форме номер один.

Объект, Семейство: ComboBox (поле со списком).

Метод: AddItem (добавить элемент в список).

Свойства: Text (содержимое); Rowsource (источник строк для списка).

Примеры. UserForm1.ComboBox2.Text = " " - очистить значение поля для поля ввода со списком номер два в пользовательской форме номер один; UserForm2.ComboBox1.Rowsource="В2:В10" - источником строк для поля один со списком в пользовательской форме два установить данные из диапазона В2:В10;

UserForm1.ComboBox1.AddItem ("Факс") - добавить к списку элемент, заключенный в кавычки.

Объект, Семейство: OptionButton.

Свойства: Value (значение); Сaption (надпись).

Примеры. UserForm3.OptionButton1.Value = True - выбрать переключатель номер один в пользовательской форме номер три; UserForm3.OptionButton1.Capture = "Успеваемость" - установить надпись “Успеваемость” рядом с переключателем в пользовательской форме номер три.

Объект, Семейство: CheckBox.

Свойства: Value (значение); Сaption (надпись).

Примеры. UserForm2.CheckBox1.Value = True - установить флажок номер один в пользовательской форме номер два; UserForm3.CheckBox1.Value = False - сбросить флажок номер один в пользовательской форме номер три; UserForm4.CheckBox1.Capture="Класс" - установить надпись “Класс” рядом с флажком в пользовательской форме номер четыре.

События

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

В языке VBA для каждого объекта определен набор стандартных событий.

Стандартное событие для объекта “кнопка” (CommandButton) - щелчок мышью (Click).

Если пользователь нажимает на кнопку, то это событие. На это событие должен быть отклик, т.е. выполнение какой-либо программы. Такая программа называется процедурой обработки событий и имеет стандартное имя. Если такой отклик не создан (не написана соответствующая программа), то система никак не будет реагировать на это событие.

Элементы языка VBA

Объекты - основные элементы языка VBA, но не единственные. К другим элементам относятся: константы, переменные, массивы, выражения, встроенные функции, встроенные диалоговые окна, операторы.

Константы - данные, не изменяющиеся в процессе решения задачи. Константы бывают двух видов: числовые и символьные.

Числовые константы - это целые либо вещественные числа.

Символьные константы - текст, заключенный в кавычки. Пример числовой константы - 5,8 (использование запятой или точки зависит от настроек операционной системы). Пример символьной константы - ООО “Темп”.

Переменные - данные, значения которых меняются в ходе выполнения программы. Для переменной задается имя и тип.

Имя переменной должно начинаться с буквы и может содержать любую комбинацию букв, цифр и символов за исключением точек, пробелов и следующих символов: “!”, “%”, “&”, “$”, “#”, “@”. Длина имени не должна превышать 255 символов. Не следует использовать имена, совпадающие с ключевыми словами VBA и именами встроенных функций и процедур.

Основные типы переменных, их размеры и диапазоны принимаемых значений приведены в табл. 1.

табл.1.

Переменные типа Variant могут хранить все, что в них поместят. Их тип изменяется в зависимости от последнего присвоения. В программах переменные описываются с помощью специального оператора Dim.

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

Из констант, переменных и встроенных функций (они рассмотрены далее) с помощью скобок и знаков арифметических операций (“+”, “-”, “*”, “/”, “^”) можно составлять выражения. Частным случаем выражения может быть просто одиночный элемент, т.е. константа, переменная или обращение к встроенной функции.

Примеры выражений:

Z
(a+b)^2
45
sin(y)

В VBA имеется большой набор встроенных функций, которые разделяют на категории. Примеры категорий:

математические функции;
функции преобразования форматов;
логические функции;
функции времени и даты.
функции проверки типов;

Некоторые из функций приведены в табл. 2:

Таблица 2

Кроме перечисленных функций, объект Applications позволяет вызвать более 400 встроенных функций рабочего листа при помощи конструкции вида:

Application.Функция Рабочего Листа(Аргументы).

Примеры:

Application.Sum(Sheets("Проверка").Range("A1:B20")) - суммируются значения из ячеек диапазона A1:B20, расположенного на листе “Проверка”;
Application.CountA(Sheets("Ученики").Range("A:A")) - подсчитывается количество непустых ячеек в столбце А на листе “Ученики”.

Структура редактора VBA

Редактор VBA активизируется командой Сервис ® Макрос ® Редактор Visual Basic. После выполнения команд мы попадаем в редактор VBA. Возвратиться из редактора VBA в рабочую книгу можно нажатием кнопки Вид(View) ® Microsoft Excel . Рассмотрим основные компоненты окна редактора. Внешний вид окна редактора представлен на рисунке. Окно редактора состоит из следующих компонентов:

панели инструментов;
окно проекта;
окно свойств;
окно редактирования кода;
меню.

Панели инструментов

Стандартная панель инструментов редактора Visual Basic содержит кнопки, предназначенные для выполнения наиболее часто используемых команд.

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

Кнопки первого сегмента:

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

Кнопки второго сегмента:

Кнопки для вырезания, копирования, вставки и поиска фрагментов кода.

Кнопки третьего сегмента:

Кнопки отмены действий и повторения отмененных действий.

Кнопки четвертого сегмента:

Кнопки для выполнения, остановки и прекращения выполнения процедуры, а также смены режима отображения экранной формы.

Кнопки пятого сегмента:

Кнопки, управляющие отображением окон проектов, свойств и просмотра объектов, а также панели инструментов.

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

Окно проекта

Окно проекта активизируется выбором команды Вид ® Окно проекта (View, Project window) или нажатием кнопки Окно проекта . В окне проекта представлена иерархическая структура файлов, форм и модулей текущего проекта.

Окно свойств

В окне свойств перечисляются основные значения свойств выбранного объекта. Используя это окно, можно просматривать свойства и изменить их значения. Для просмотра свойств выбранного объекта надо выполнить команду Вид ® Окно свойств (Properties Windows) или нажатием кнопки .

Окно свойств состоит из двух составных частей: верхней и рабочей. В верхней части окна располагается раскрывающийся список, из которого можно выбрать любой элемент управления текущей формы или саму форму. Рабочая часть состоит из двух вкладок, в которых свойства располагаются По алфавиту (Alphabetic) и По категориям (Categorized).

Окно для просмотра объектов (Object Browser)

Окно Просмотр объектов (Object Browser) вызывается командой Вид ® Просмотр объектов (View, Object Browser) или нажатием кнопки . В этом окне можно просматривать все объекты проекта. Здесь вы найдете все свойства, методы и события, связанные с любым объектом.

Окно Просмотр объектов состоит из трех основных частей:

1. Раскрывающегося списка Проект/Библиотека в верхнем левом углу экрана. Например, библиотеки объектов Excel, VBA, Office и VBAProject (объекты пользовательского проекта).

2. Списка Классы. Выводятся все классы выбранной библиотеки.

3. Списка Компоненты (Members). Выводятся все компоненты выбранного класса.

Это окно предоставляет доступ ко всем объектам, свойствам, методам и событиям.

Проверочная работа № 1-1

Выберите правильный ответ (возможно несколько правильных ответов):

1. Объектом VBA являются:

рабочая книга;

рабочий лист;

диапазон ячеек;

массивы.

2. Щелчок мышью или нажатие клавиши, перемещение мыши или выход из программы, это действие называется:

откликом;

просмотром;

свойством;

событием.

3. Для того чтобы подсчитать дискриминант в квадратном уравнении, надо его записать следующим образом:

d = b2 - 4 ac

d = b^2 - 4ac

d = b^2 - 4*a*c

d = b^2 - 4*ac

4. Продолжите определение:

Метод - это…

основной элемент языка VBA;

структура редактора VBA;

характеристика объекта;

действия, совершаемые над объектами.

5. Чтобы стандартно изменить характеристику объекта, надо:

написать новую программу;

применить другое свойство;

применить метод;

изменить значение свойства.

6. Запишите конструкции, которые применяются для установки свойств объектов и доступа к их методам:

1. Запишите в ячейку В9 дату своего рождения, в ячейку В10 - свое имя.

2. Установите размер шрифта 16 в ячейках С4:F4, цвет - красный.

3. Покажите форму № 3.

4. Скройте форму № 2.

5. Выберите диапазон ячеек В2:D7.

Проверочная работа № 1-2

Выберите правильный ответ:

1. Очистить диапазон ячеек от А1 до С10:

Range("А1;C10").Clear

Range("А1:C10").Clear

Range("А1-C10").Clear

Clear. Range("А1:C10")

2. Скрыть форму № 6:

UserForm6.Show

UserForm №6.Сlose

UserForm6.Hide

UserForm №6.Show

3. Выйти из приложения:

Application.Hide

Application.Сlose

Application.Select

Application.Quit

4. Выделить диапазон ячеек от D6 до E8 на листе “География”:

Sheets("География").Range("D6-E8").Show

Sheets "География".Range("D6:E8").Select

Sheets "География".Range("D6-E8").Select

Sheets("География").Range("D6:E8").Select

5. Присвоить диапазону ячеек от А4 до А16, расположенных на листе “11 класс”, имя “Информатика”:

Sheets("11класс").Range("А4:А16").Name = "Информатика"

Sheets("11класс").Range("А4-А16").Name = "Информатика"

Sheets("Информатика").Range("А4-А16").Name = "11класс"

Sheets("Информатика").Range("А4:А16").Name = "11класс"

6. Установить источником строк для поля со списком два в пользовательской форме три данные из диапазона от А12 до А15:

UserForm3.ComboBox2.Rowsource = "А12-А15"

UserForm2.ComboBox3.Rowsource = "А12:А15"

UserForm3.ComboBox2.Rowsource = "А12:А15"

UserForm2.ComboBox2.Rowsource = "А12:А15"

7. Очистить значение поля для поля ввода со списком номер три в пользовательской форме номер два:

UserForm3.ComboBox2.Text = "Очистить"

UserForm2.ComboBox3.Text = " "

UserForm3.ComboBox2.Capture = "0 "

UserForm2.ComboBox3.Text = "-"

8. Установить флажок номер пять в пользовательской форме номер два:

UserForm5.CheckBox2.Value = True

UserForm2.CheckBox2.Text = False

UserForm2.ComboBox5. Capture = " True "

UserForm2.CheckBox5. Value = True

9. Сбросить флажок номер пять в пользовательской форме номер два:

UserForm5.CheckBox2. Value = True

UserForm2. CheckBox5. Value = False

UserForm2.ComboBox5. Capture = " True "

UserForm2.CheckBox5. Text = False

10. Выбрать переключатель номер два в пользовательской форме номер три:

UserForm3.OptionButton2.Value = False

UserForm2. CheckBox3. Value = False

UserForm2.ComboBox5. Capture = True

UserForm3.OptionButton2.Value = True

Практическая работа № 1-3

Установите соответствие между функцией и выражением:

Log(x) Экспонента

Sin(x) х преобразуется в целое

Sqr(x) Натуральный логарифм

Abs(х) Модуль (абсолютная величина числа)

Cos(х) х преобразуется в число с плавающей точкой обычной точности

Exp(х) Синус

CInt(x) х преобразуется в строку

CSng(x) Текущие дата и время

CStr(x) Квадратный корень

Now Косинус

Практическая работа № 1-4

1. Откройте новый файл.

2. Перейдите в редактор Visual Basic.

3. Нажмите клавишу для открытия окна Object Browser.

4. В списке Classes (Классы) найдите объект Range и выделите его.

5. Прокрутите соседний список Members of ‘Range’ для просмотра свойств и методов объекта Range.

6. Выберите метод Activate.

7. Нажмите клавишу . Откроется окно справочной системы Visual Basic с описанием метода Activate.

8. Откройте пример с применением метода Activate к объекту Range. Запишите пример.

9. Для объекта Range выберите свойство Cells. Запишите, для каких объектов, кроме объекта Range, характерно это свойство.

10. Запишите метод, используемый для удаления содержимого диапазона ячеек.

11. Запишите метод, используемый для удаления рабочего листа в книге.

12. В окне проекта выберите Лист1. В окне свойств измените его имя на имя ПРОБА. Запишите название свойства.

13. В окне проекта выберите Лист2. В окне свойств подберите свойство, которое будет делать этот лист невидимым. Запишите название свойства и его значение.

14. В окне проекта выберите Лист3. В окне свойств подберите свойство, которое будет изменять ширину столбцов. Укажите значение - 15. Запишите название свойства.

15. Закончите предложение: окно Object Browser можно использовать для просмотра списков объектов, событий, свойств и...

Продолжение следует