Лекция Основы программирования на vba - shikardos.ru o_O
Главная
Поиск по ключевым словам:
страница 1
Похожие работы
Лекция Основы программирования на vba - страница №1/1

Лекция 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

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

[Else



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

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 <условие1> Then

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

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

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

...


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} <условие>]

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


Конструкция WithEnd 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


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

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


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