Лекция 6. Основы программирования на VBA

1.Ввод кода VBA

Код VBA состоит из инструкций. Обычно каждая инструкция вводится на отдельной строке. Можно перенести длинную инструкцию на следующую строку с помощью оператора продолжения строки, который состоит из пробела и знака подчёркивания.

После ввода инструкции редактор VBA выполняет следующие действия.

• 
  Вставляет пробелы между операторами и операндами.
  
• 
  Изменяет регистр символов ключевых слов, свойств, методов и переменных.
  
• 
  Проверяет инструкцию на наличие синтаксических ошибок. При обнаружении ошибки изменяется цвет строки и выдаётся сообщение об ошибке. Чтобы сообщения об ошибках не отображались, что бывает слишком навязчиво, можно снять флажок Auto Syntax Check, который находится на вкладке Editor в диалоге установки параметров редактора VBA (Tools  Options).
  

2.Комментарии

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

Используйте комментарии:

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

3.Типы данных

Тип является характеристикой переменной, которая определяет:

• 
  какие данные могут храниться в переменной;
  
• 
  какие операции могут выполняться над переменной;
  
• 
  размер ячейки памяти для переменной.
  

Тип	Хранимые данные	Операции
Boolean	Логические значения True и False	Логические – not, and, or
Byte	Целые числа в диапазоне 0..255	Арифметические – -, ^, *, /, \, mod, +, - Отношения – =, <>, >, >=, <, <=
Integer	Целые числа в диапазоне -32768..32767
Long	Целые числа в диапазоне -2147483648..2147483647
Single	Вещественные числа, от -3.402823E38 до -1.401298E-45 для отрицательных значений и от 1.401298E-45 до 3.402823E38 для положительных значений
Double	Вещественные числа, от -1.79769313486231E308 до -4.94065645841247E-324 для отрицательных значений и от 4.94065645841247E-324 до 1.79769313486232E308 для положительных значений
Currency	Вещественные числа в диапазоне от -922,337,203,685,477.5808 до 922,337,203,685,477.5807
Date	Значения даты и времени в диапазоне от 1 января 100 года до 31 декабря 9999 года	Арифметические (вычитание) DateAdd DateDiff DatePart DateValue
String	Строки длиной до 2 млрд. символов	Конкатенация – & Отношения
Variant	Значения любого типа	Зависит от реального типа данных

Для проведения математических вычислений в рабочих листах приложения Microsoft Excel использует тип Double. Его же рекомендуется применять и для обработки вещественных чисел в VBA для обеспечения той же точности вычислений. При управлении номерами строк рабочих листов следует применять тип данных Long, т.к. количество строк в рабочем листе превышает максимальное значение, хранимое переменной типа Integer.

Тип Variant представляется удобным, но у него есть ряд недостатков. Во-первых, переменные типа Variant требуют больше памяти. Во-вторых, работа с переменными типа Variant требует больше времени – примерно в 3 раза. В-третьих, использование конкретных типов позволяет компилятору раньше находить ошибки, связанные с неверными типами данных, а также позволяет избегать таких ошибок.
Dim v1 As Variant, v2 As Variant

v1 = "123"

v2 = 45

MsgBox v1 + v2 '168

MsgBox v1 & v2 '12345
Для определения типа данных, хранящихся в переменной типа Variant, используются стандартные функции VarType и TypeName. Первая возвращает число, соответствующее типу данных, а вторая – строку, хранящую имя типа данных.
Dim v As Variant

v = 46.7

MsgBox VarType(v) '5

MsgBox TypeName(v) 'Double

Чтобы не запоминать числа, соответствующие тому или иному типу, в VBA введены константы, имена которых запомнить намного проще – vbBoolean, vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDate, vbString, vbArray.
If VarType(v) = vbDouble Then

...
Функция TypeName удобна тем, что она возвращает в виде строки имена для типов объектов, существующих в VBA, – таких как Range, Worksheet, Workbook и т.д.

Таким образом, тип Variant имеет смысл использовать, только если вы сами хотите разделить обработку данных разных типов.

Переменные типа Variant могут также хранить ряд специальных значений, таких как Empty (ничего не присвоено), Null (некорректные данные) и Error (признак ошибки). Чтобы создать подобный признак ошибки используется функция CVErr, получающая в качестве аргумента номер ошибки.

Dim v As Variant

v = CVErr(0)

В VBA определён ряд констант, соответствующих ошибкам, возникающим в формулах.

Константа	Номер	Ошибка
xlErrDiv0	2007	#ДЕЛ/0!
xlErrNA	2042	#Н/Д
xlErrName	2029	#ИМЯ?
xlErrNull	2000	#ПУСТО!
xlErrNum	2036	#ЧИСЛО!
xlErrRef	2023	#ССЫЛ!
xlErrValue	2015	#ЗНАЧ!

Если присвоить в переменную типа Variant признак ошибки с одним из вышеперечисленных номеров и поместить значение этой переменной в ячейку рабочего листа, в ячейке будет отображаться соответствующее сообщение об ошибке. Всем остальным номерам соответствует сообщение #ЗНАЧ!. С их помощью можно создавать как пользовательские сообщения об ошибках.

4.Переменные и константы

Переменные

Под переменной в программировании понимается поименованная область памяти, в которой хранится некоторое значение, причём это значение может изменяться в ходе выполнения программы. Для объявления переменных используется оператор Dim.
Dim <имя> [As <тип>]

Dim n As Integer

Dim i, j, k As Integer 'Только переменная k имеет тип Integer!

Dim i As Integer, j As Integer, k As Integer 'Теперь все переменные имеют тип Integer

Необъявленные переменные и переменные, в объявлении которых не указан тип, имеют тип Variant. Однако использование типа Variant является не самым лучшим решением, и, кроме того, необъявленные переменные могут приводить к ошибкам. Если вы неверно напишите имя переменной, то компилятор сочтёт это имя как имя новой переменной, и программа будет работать не так, как задумано. Чтобы избежать подобных ошибок, можно потребовать явного объявления всех переменных. Для этого необходимо включить следующую строку в качестве первой инструкции в модуле VBA:
Option Explicit

Константы

Если некоторое значение используется несколько раз, лучше не вставлять его в несколько инструкций, а объявить в виде константы. Такое объявление позволит, во-первых, задать значению символическое имя, что улучшит читабельность программы, а во-вторых, упростит изменение значения – новое значение необходимо будет ввести только один раз.
Const <имя> [As <тип>] = <значение>

Const count As Integer = 255

Const tax = 0.13, expDate = #12/31/2012#

Массивы

Массив – это переменная, состоящая из нескольких элементов одного типа, для доступа к которым используется один или несколько индексов.
Dim <имя> (<начальный индекс> To <конечный индекс>) As <тип>

Dim a (1 To 100) As Integer

Dim b (1 To 10, 1 To 10) As Double

a(3) = 7

b(5, 2) = -3.568
Динамические массивы – это массивы, которые не имеют предопределённого количества элементов. Для объявления такого массива используются пустые круглые скобки. Однако, прежде чем использовать такой массив, необходимо задать его размер с помощью оператора ReDim. В отличие от обычного объявления, в котором можно использовать только константы, в операторе ReDim можно использовать переменных для указания границ индексов.
Dim a() As Integer

...

ReDim a(1 To n)

Объектные переменные

Объектная переменная – это переменная, представляющая собой объект VBA, например, диапазон или рабочий лист. Объектные переменные важны по следующим причинам:

• 
  они упрощают программу;
  
• 
  они ускоряют выполнение программы.
  

Объектные переменные объявляются так же, как и обычные, – с помощью оператора Dim. Для присваивания объекта переменной необходимо использовать оператор Set.
Dim MyCell As Range

Set MyCell = Worksheets("Лист1").Range("A1")

MyCell.Value = 125

MyCell.Font.Bold = True

MyCell.Font.Italic = True

MyCell.Font.Size = 14

MyCell.Font.Name = Cambria

5.Управляющие конструкции

Условный оператор

Условный оператор позволяет осуществлять выбор действий, которые надо выполнить, в зависимости от некоторого условия.
If <условие> Then

<инструкции₁>

[Else

<инструкции₂>]

End If
If k <> 0 Then

s = s / k

End If
If k <> 0 Then s = s/ k
If x > y Then

s = s + x

Else

s = s + y

End If
If <условие₁> Then

<инструкции₁>

Elseif <условие₂> Then

<инструкции₂>

...

Elseif <условие_n> Then

<инструкции_n>

Else

<инструкции_n+1>

End If
Dim quantity As Integer, discount As Double

If quantity > 100 Then

discount = 0.25

ElseIf quantity > 75 Then

discount = 0.2

Elseif quantity > 50 Then

discount = 0.1

Else

discount = 0

End If

Циклы

Циклы позволяют многократно выполнять некоторую последовательность действий, используя логические выражения для определения момента прекращения выполнения цикла. Наиболее часто используемым является параметрический цикл. Параметрический цикл имеет следующий синтаксис (по умолчанию шаг равен 1).
For <счётчик> = <начальное значение> To <конечное значение> [Step <шаг>]

<инструкции>

[Exit For]

<инструкции>

Next <счётчик>
'Сумма квадратов всех чисел от 1 до 100

Dim sum As Double, i As Integer

sum = 0

For i = 1 to 100

sum = sum + sqr(i)

Next i
'Сумма квадратов чисел, кратных 3, от 3 до 100

Dim sum As Double, i As Integer

sum = 0

For i = 3 to 100 Step 3

sum = sum + sqr(i)

Next i
'Удаляем чётные строки с номерами от 2 до 10

Dim r As Long

For r = 10 To 2 Step -2

Rows(r).Delete

Next r
'Сумма квадратов чисел от 1 до 100. Выходим, если сумма превышает пороговое значение

Dim sum As Double, threshold As Double, i As Integer

sum = 0

threshold = 100

For i = 1 To 100

sum = sum + sqr(i)

If sum > threshold

Exit For

End If

Next i
'Сумма значений ячеек диапазона

Dim r as Range

Dim sum As Double

Dim i As Integer, j As Integer

sum = 0

For i = 1 To r.Rows.Count

For j = 1 To r.Columns.Count

sum = sum + r.Cells(i, j)

Next j

Next i
'Количество положительных значений в ячейках диапазона

Dim r as Range

Dim k As Integer

Dim i As Integer, j As Integer

k = 0

For i = 1 To r.Rows.Count

For j = 1 To r.Columns.Count

If r.Cells(i, j) > 0 Then k = k + 1

Next j

Next i
В языке VBA можно также использовать ещё четыре разновидности цикла.

Do [{While | Until} <условие>]

<инструкции>

[Exit Do]

<инструкции>

Loop
Do

<инструкции>

[Exit Do]

<инструкции>

Loop [{While | Until} <условие>]

Работа с объектами и коллекциями

Конструкция With – End With позволяет выполнить несколько операций над одним объектом.
With <объект>

<инструкции>

End With

With Selection.Font

.Bold = True

.Italic = True

.Size = 14

.Name = Cambria

End With
Selection.Font.Bold = True

Selection.Font.Italic = True

Selection.Font.Size = 14

Selection.Font.Name = Cambria

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

Конструкция For Each – Next позволяет обработать все элементы какой-либо коллекции.

For Each <элемент> In <коллекция>

<инструкции>

[Exit For]

<инструкции>

Next <элемент>
'Вывод имён всех рабочих листов в активной рабочей книге

Dim item As Worksheet

For Each item In ActiveWorkbook.Worksheets

MsgBox item.Name

Next item
'Закрытие всех рабочих книг, кроме активной

Dim book As Workbook

For Each book In Workbooks

If book.Name <> ActiveWorkbook.Name Then book.Close

Next book
'Выделение первой ячейки в диапазоне, имеющей отрицательное значение

Dim cell As Range

For Each cell In Selection

If cell.Value < 0 Then

cell.Select

Exit For

End If

Next cell