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

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

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

Продолжение. Начало в № 1 – 5, 7/2006

Занятие № 7. Работа с листом и алгоритмические конструкции

Процедуры и модули для кода VBA

Код VBA должен где-то находиться, и это “где-то” называется модулем. Модуль состоит из процедур. Каждая содержащаяся в модуле процедура является либо процедурой-функцией, либо процедурой-подпрограммой.

Модуль — это совокупность объявлений (описательная часть) и процедур, хранящихся как единое целое.

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

Создание процедур

Создание первой процедуры требует выполнения двух последовательных шагов:

  • сначала надо вставить модуль в рабочую книгу;
  • затем в этом модуле создать процедуру.

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

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

2. Выполните команду Сервис | Макрос | Редактор Visual Basic. Откроется окно редактора Visual Basic.

3. Откройте проект VBAProject (Книга1). В меню выберите команду Insert | Module (Вставка | Модуль). В ваше приложение будет добавлен модуль 1.

4. Выполните команду Insert | Procedure (Вставка | Процедура). Откроется диалоговое окно Add Procedure (Добавить процедуру).

5. Введите имя процедуры. В секции Туре (Тип) установите переключатель Sub (Подпрограмма). Щелкните на кнопке “ОК”. Новая процедура будет добавлена в модуль.

В пустой строке тела процедуры находится текстовый курсор, предлагая начать ввод кода процедуры.

VBA требует соблюдения определенных правил при создании имен процедур.

  • Первые три символа имени должны быть буквами.
  • Имя может содержать буквы, цифры и знаки подчеркивания.
  • Имя не может содержать пробелы, точки, запятые, восклицательные знаки и символы “@”, “&”, “$”, “#”.
  • Имя не должно содержать более 255 символов.

Выполнение процедуры

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

1. Выбрать команду меню Run Sub | UserForm (Выполнить подпрограмму | экранную форму) из меню Run (Выполнить) или щелкнуть на кнопке Run Sub | UserForm стандартной панели инструментов.

2. Нажать клавишу .

3. Создать на любом листе Excel автофигуру и привязать к ней подготовленную процедуру.

Переменные

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

Описание данных начинается с оператора DIM

Имя переменной должно начинаться с буквы и может содержать буквы, цифры и другие символы. Имя не может содержать пробелы, точки, запятые, восклицательный знак и символы “@”, “&”, “$”, “#”. Имя не должно содержать более 255 символов.

Тип переменных в операторе Dim можно не указывать. Тогда VBA применит тип данных по умолчанию (Variant). Кажется, что это удобно, но все же лучше тип данных определять. По нескольким причинам. Во-первых — в целях экономии ресурсов памяти. Ни один тип не требует 16 или 22 байтов для сохранения значений переменных. Во-вторых, VBA по-разному обрабатывает данные разных типов. Поэтому, не объявляя тип данных, вы можете получить результат, отличающийся от желаемого. И, наконец, от типов данных в VBA зависит время выполнения процедур.

Пример определения переменных:

Dim A As Integer, B As Byte, С As String

Переменная А определена как целое число (не больше 32 767 и не меньше –32 768); переменная В определена как целое неотрицательное число (не больше 255), а в переменной С может храниться текстовая информация.

Использование констант

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

Const Имя_константы As тип_данных = значение

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

Пример объявления константы:

Const Годы_учебы As Byte = 11

Программа на VBA — это последовательность операторов

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

Оператор With/End with избавляет программиста от большого количества повторений имени одного и того же объекта.

Синтаксис:

With объект

Оператор 1

Оператор 2

..............

Оператор N

End with

Например, вместо последовательности операторов

UserForm1.TextBox1.Text = Date

UserForm1.TextBox2.Text = " "

UserForm1.Label1.Caption = " "

UserForm1.Label2.Caption = "Название"

можно записать так:

With UserForm1

.TextBox1.Text = Date

.TextBox2.Text = " "

.Label1.Caption = " "

.Label2.Caption = "Название"

End with

Управляющие структуры VBA

Управляющие структуры определяют последовательность выполнения программы. Без них все операторы программы будут выполняться слева направо и сверху вниз. Однако иногда требуется многократно выполнить некоторый набор инструкций либо решить задачу по-другому, в зависимости от значения переменных или параметров, заданных пользователем во время выполнения. В этих случаях помогают конструкции управления и циклы.

VBA поддерживает следующие конструкции принятия решений:

If ... Then

If ... Then ... Else

Select Case

Конструкция If . . . Then

Конструкция If ... Then применяется, когда необходимо выполнить один или группу операторов в зависимости от некоторого условия. Синтаксис этой конструкции позволяет задавать ее в одной строке или в нескольких строках программы:

1-й способ:

If условие Then выражение

2-й способ:

If условие Then

выражение

End If

Обычно условие является простым сравнением, но оно может быть любым выражением с вычисляемым значением. Если условие истинно, то выполняются все выражения, стоящие после ключевого слова Then.

Следующие два оператора эквивалентны:

If A >= B Then A = A * 2

If A >= B Then

A = A * 2

End If

Заметим, что синтаксис оператора If ... Then для одной строки не использует оператор End If. Чтобы в случае истинности условия выполнить последовательность операторов, следует использовать блоковую конструкцию If ... Then ... End If.

If A >= B Then

A = A * 2

Sheets("Задача").Range("B2") = A

Rem На листе Задача в ячейку B2 поместить значение А

End If

Если условие ложно, то операторы после ключевого слова Then не выполняются, а управление передается на следующую строку (или строку после оператора End If в блочной конструкции).

Конструкции If . . . Then . . . Else и If ... Then ... ElseIf

Конструкция If ... Then ... Else определяет несколько блоков операторов, один из которых будет выполняться в зависимости от условия:

If условие Then

Выражение 1

...

Else

Выражение 2

...

End If

Сначала проверяется условие. Если оно истинно, VBA выполняет соответствующий блок операторов и затем передает управление инструкции, следующей за оператором End if. В противном случае выполняется блок оператора Else.

Конструкция If ... Then ... ElseIf в действительности всего лишь специальный случай конструкции If ... Then ... Else, который применяется в случае вложенности таких конструкций. Рассмотрим пример сравнения двух чисел

Sub Задача1()

Dim Rez As String

a = (InputBox("Введи a", "Ввод данных", 0))

Dim a As Single, b As Single

Ввод данных", 0))

b = (InputBox("Введи b", "Ввод данных", 0))

If a < b Then

Rez = "a < b"

ElseIf a = b Then

Rez = "a = b"

Else Rez = "a > b"

End If

Msgbox Rez,64,"Информация"

End Sub

В конструкцию If ... Then можно добавить любое число блоков ElseIf. Однако при большом количестве блоков ElseIf конструкция If ... Then станет очень громоздкой и неудобной. В подобной ситуации следует применять другую конструкцию принятия решения — Select Case.

Конструкция Select Case

Конструкция Select Case является альтернативой конструкции If ... Then ... Else: она делает код легче читаемым при наличии нескольких вариантов выбора.

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

Select Case проверяемое_выражение

[Case список_выражений 1

[блок_операторов 1]]

[Case список_выражений 2

[блок_операторов 2]]

...

[Case Else

[блок_операторов n]]

End Select

Каждый список выражений может содержать одно или более значений. В этом случае они отделяются запятыми. Каждый блок операторов может содержать несколько операторов или ни одного. Если окажется, что проверяемое выражение соответствует значениям из нескольких операторов Case, то выполняются операторы, совпадающие с первым оператором Case из всех найденных соответствий. VBA выполняет блок операторов Case Else (заметим, что он необязателен), если не найдено ни одного соответствия проверяемого значения выражения и значений из всех списков операторов Case.

Еще раз отметим, что конструкция Select Case вычисляет выражение только один раз при входе в нее, тогда как в конструкции If ... Then ... Else вычисляются различные выражения для каждого оператора ElseIf. Конструкцию If ... Then ... Else можно заменить конструкцией Select Case, только если оператор If и каждый оператор Elself вычисляют одно и то же выражение.

Операторы цикла

Операторы цикла предназначены для программирования повторяющихся фрагментов, т.е. для реализации циклических алгоритмов.

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

Оператор цикла For (фиксированное число повторений)

Синтаксис:

For переменная = M1 To M2 [Step M3]

операторы

Next

— где M1, M2, M3 — выражения. Оператор цикла повторяет выполнение группы операторов, пока переменная (счетчик) изменяется от начального значения М1 до конечного М2 с указанным шагом М3. Если шаг не указан, то он полагается равным 1.

Пример:

Sum = 0

For I = 1 To 31

Sum = Sum + Sheets("Температура").Cells(I,1)

Next

ST = Sum/31

В приведенном примере предполагается, что на листе “Температура” в первом столбце записаны показатели температур за июль месяц. Надо рассчитать среднюю температуру за месяц.

Оператор цикла While (переменное число повторений)

Синтаксис:

Do While условие

операторы

Loop

Все операторы будут выполняться между Do While и Loop до тех пор, пока условие будет истинным. Если при входе в цикл условие ложно, то операторы выполняться не будут.

Пример:

Rem Удвоение числовых переменных массива А с четными номерами индексов и вывод на лист "Цикл".

Dim A(10) As Byte, i As Byte, j As Byte

Rem Массив надо заполнить

i = 0

j = 0

Do While i < 10

j = j + 1

i = i + 2

A(i) = A(i) * 2

Sheets("Цикл").Cells(j, 1) = A(i)

Loop

Алгоритмы обработки информации на листе Excel

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

Практическая работа № 7–1 “Разнеси числа по листам”

1. На Листе1 (Числа) в ячейки А1–А20 занести случайным образом значения из интервала (–50; 50).

2. На Лист1 (Числа) в ячейку С1 записать “Количество +”, а в ячейку D1 поместить подсчитанное значение с количеством положительных чисел.

3. На Лист1 (Числа) в ячейку С2 записать “Количество –”, а в ячейку D2 поместить подсчитанное значение с количеством отрицательных чисел.

4. На Лист1 (Числа) в ячейку С3 записать “Количество 0”, а в ячейку D3 поместить подсчитанное значение с количеством чисел, равных нулю.

5. На Лист2 (Положительные) в ячейку В1 записать “Положительные” и, начиная с ячейки B2, в столбик поместить все положительные числа.

6. На Лист3 (Отрицательные) в ячейку С1 записать “Отрицательные” и, начиная с ячейки D1, в строку поместить все отрицательные числа.

7. Создать кнопку “Количество” на листе “Числа”.

8. Создать кнопку “Перенос” на листе “Числа”.

9. Создать кнопку “Очистить” на листе “Положительные”.

10. Создать кнопку “Очистить” на листе “Отрицательные”.

План работы

1. Переименуйте: Лист1 в “Числа”, Лист2 в “Положительные”, Лист3 в “Отрицательные”.

2. Примените к столбцу А (лист “Числа”) условное форматирование. После заполнения диапазона ячеек числами к положительным числам будет применяться такой формат: полужирный курсив, красный цвет; к отрицательным — полужирный курсив, синий цвет; нулевые значения — полужирный курсив, зеленый цвет. Для этого:

  • Выделите столбец А;
  • Выполните команду Формат | Условное форматирование.

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

3. Перейдите в редактор VBA.

4. Создайте модуль с помощью команды Insert | Module.

5. Создайте в модуле процедуру с помощью команды Insert | Procedure. Присвойте имя процедуре “Числа”.

6. Напишите текст программы для занесения чисел на лист.

Public Sub Числа()

Dim I As Integer

Randomize Timer

For I = 1 To 20

Sheets("Числа").Cells(I, 1) =

Int(Rnd * 100) - 50

Next I

7. Нарисуйте на листе “Числа” автофигуру. Назначьте ей процедуру выполнения программы Числа. Для этого:

  • Выделите фигуру.
  • Вызовите контекстно-зависимое меню.
  • Выполните команду “Назначить макрос”.
  • Выберите в открывшемся диалоговом окне только что созданную программу “Числа”.

8. Проверьте работоспособность программы.

9. Создайте макрос “Очистка_Чисел” для очистки диапазона ячеек А1:D20 листа “Числа”.

10. Подготовьте автофигуру и привяжите к ней макрос.

11. Создайте в этом же модуле еще одну процедуру KolPolOtr для подсчета количества положительных, отрицательных и нулевых значений.

Public Sub KolPolOtr()

Rem Объявление переменных

Rem Pol — переменная для подсчета количества положительных чисел

Rem Otr — переменная для подсчета количества отрицательных чисел

Rem Nul — переменная для подсчета нулевых значений

Dim I As Integer, Pol As Integer, Otr As Integer, Nul As Integer

Rem обнуление переменных

Pol = 0

Otr = 0

Nul = 0

Rem Открытие цикла для проверки чисел

For I = 1 To 20

If Sheets("Числа").Cells(I, 1) > 0 Then

Pol = Pol + 1

ElseIf Sheets("Числа").Cells(I, 1) < 0 Then

Otr = Otr + 1

Else

Nul = Nul + 1

End If

Next I

Rem Вывод на лист "Числа" результатов подсчета

With Sheets("Числа")

.Range("C1") = "Количество +"

.Range("D1") = Pol

.Range("C2") = "Количество -"

.Range("D2") = Otr

.Range("C3") = "Количество 0"

.Range("D3") = Nul

End With

End Sub

12. Создайте автофигуру с именем “Количество” на листе “Числа” и привяжите к ней программу KolPolOtr.

13. Создайте в этом же модуле еще одну процедуру Perenos для переноса положительных и отрицательных чисел по разным листам.

Public Sub Perenos()

Rem Объявление переменных

Dim I As Integer, IndPol As Integer, IndOtr As Integer

Rem переменная-индекс для формирования адреса ячейки положительных элементов

IndPol = 2

Rem переменная-индекс для формирования адреса ячейки отрицательных элементов

IndOtr = 4

Rem Занесение в ячейку B1 слово "Положительные"

Sheets("Положительные").Range("B1") = "Положительные"

Rem Занесение в ячейку B1 слово "Отрицательные"

Sheets("Отрицательные").Range("C1") = "Отрицательные"

For I = 1 To 20

If Sheets("Числа").Cells(I, 1) > 0 Then

Sheets("Положительные").Cells(IndPol, 2) =

Sheets("Числа").Cells(I, 1)

IndPol = IndPol + 1

ElseIf Sheets("Числа").Cells(I, 1) < 0 Then

Sheets("Отрицательные").Cells(1, IndOtr) =

Sheets("Числа").Cells(I, 1)

IndOtr = IndOtr + 1

End If

Next I

End Sub

14. Создайте автофигуру с именем “Перенос” на листе “Числа” и привяжите к ней программу Perenos.

15. Создайте автофигуры для очистки перенесенных значений на листах “Положительные” и “Отрицательные”.

16. Сохраните работу.

Практическая работа № 7–2 “Сумма”

Найти максимальный и минимальный элементы, сумму значений из ячеек диапазона, среднее значение можно обычным способом, используя стандартные алгоритмы. Но приложение Excel содержит более 400 встроенных функций рабочего листа. Вызвать их позволяет объект Application: Application.ФункцияРабочегоЛиста(Аргументы).

Задача. Нахождение суммы в диапазоне ячеек

Напишите программу вычисления суммы чисел, находящихся в диапазоне ячеек А1:А10, и выведите результаты вычисления с поясняющими надписями в ячейки С2:D2.

Решим эту задачу двумя способами.

1-й способ (стандартный алгоритм)

Подготовка исходных данных.

1. Переименуйте Лист1 в “Пример1”.

2. Заполните диапазон ячеек А1:А10 целыми числами.

Подготовка текста программы.

3. Войдите в редактор VBA. На экране появится окно проекта; вставьте модуль.

4. Вставьте в модуле новую процедуру с именем Сумма1способ.

5. Наберите текст программы.

Public Sub Сумма1способ()

Dim i As Byte, Sum As Integer

Sum = 0 'Вычисление суммы

With Sheets("Пример1")

For i = 1 10

Sum = Sum +.Cells(i,1)

Next i

'Вывод результата

.Cells (2,3)="Сумма =": .Cells (2,4) = Sum

End With

End Sub

6. Сохраните работу.

2-й способ (применение функции)

Текст программы будет таким:

Public Sub Сумма2способ()

Dim i As Byte, Sum As Integer

Sum=0 'Вычисление суммы

Sum = Application.Sum(Sheets("Пример 1").Range("A1:A10"))

Sheets("Пример 1").Cells(2,3)="Сумма ="

Sheets("Пример 1").Cells(2,4) = Sum

End Sub

Практическая работа № 7–3 “Функции листа”

Напишите программу вычисления максимума, минимума, среднего значения и произведения чисел, находящихся в диапазоне ячеек А1:А10 на листе “Пример 3”. Выведите результаты вычисления с поясняющими надписями в ячейки С2:D5. Решите эти задачи двумя способами.

Для того чтобы узнать правильное написание функции рабочего листа и ее применение, вспомним работу № 1, в которой мы обращались к окну Object Browser.

  • Войдем в окно просмотра объектов.
  • Выберем в раскрывающемся списке Проект | Библиотека в верхнем левом углу экрана библиотеку объектов Excel.
  • В окне Классы выберем Application.
  • В окне списка Компоненты выберем WorksheetFunction. В нижней части окна появится Property WorksheetFunction As WorksheetFunction (ссылка).
  • Нажмем на ссылку, и в окне Компоненты появится список всех функций.

Используя этот список функций, напишем программу.

Public Sub Пример3()

Dim I As byte, m As Integer, m1 As Integer,

SR As Long, proiz As Long

Randomize Timer

For I = 1 To 10

Sheets("Пример 3").Cells(I, 1) = Int(Rnd * 10)

Next I

'Нахождение минимума

m = Application.Min(Sheets("Пример 3").Range("A1:A10"))

'Нахождение максимума

m1 = Application.Max(Sheets("Пример 3").Range("A1:A10"))

'Произведение

proiz = Application.Product(Sheets("Пример 3").Range("A1:A10"))

'Среднее значение

SR = Application.Average(Sheets("Пример 3").Range("A1:A10"))

'Вывод результатов

With Sheets("Пример 3")

.Cells(2, 3) = "МИН ="

.Cells(2, 4) = m

.Cells(3, 3) = "МАКС ="

.Cells(3, 4) = m1

.Cells(4, 3) = "Произвед ="

.Cells(4, 4) = proiz

.Cells(5, 3) = "Среднее"

.Cells(5, 4) = SR

End With

End Sub

Практическая работа № 7–4 “Сравни числа”

Напишите программу, которая из пары чисел А2 и В2, А3 и В3 и т.д. будет выбирать большее и помещать в столбец D, начиная с ячейки D2. Создайте кнопку для стирания результатов из столбцов А, В и D.

Условия:

  • Количество пар чисел для сравнения, а также сами числа должны генерироваться случайным образом.
  • В ячейки А1, B1, D1 надписи “1-е число”, “2-е число” и “Большее” должны заноситься программно.
  • Программа начинает выполняться при нажатии на кнопку, созданную на этом же листе.

План работы:

1. Программа для кнопки “Запуск”:

Public Sub Большее()

Dim I As Integer, N1 As Integer, A As Integer, B As Integer

Randomize Timer

Rem N1 — количество пар

N1 = 1 + Int(Rnd * 30)

Rem Заполнение ячеек случайными числами в диапазоне от -50 до +50

With Sheets("Большее")

For I = 2 To N1

.Cells(I, 1) = Int(Rnd * 100) - 50

.Cells(I, 2) = Int(Rnd * 100) - 50

Next I

Rem Занесение пояснительных надписей в ячейки и форматирование

.Range("A1:D1").Font.Size = 11

.Range("A1:D1").Font.Bold = True

.Range("A1:D1").Font.Color = vbBlue

.Range("A1") = "1-е число"

.Range("B1") = "2-е число"

.Range("D1") = "Большее"

.Range("A1:D1").Select

Rem Автоматическая настройка ширины столбца

Selection.Columns.AutoFit

Rem Нахождение наибольшего и занесение результата

For I = 2 To N1

A = .Cells(I, 1).Value

B = .Cells(I, 2).Value

If A > B Then

.Cells(I, 4).Value = A

ElseIf A = B Then

.Cells(I, 4).Value = "равны"

Else

.Cells(I, 4).Value = B

End If

Next I

End With

End Sub

2. Создайте кнопку для стирания результатов из столбцов А, В и D любым известным вам способом.

3. Сохраните свою работу.

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

TopList