Краткая справка по VBA

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

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

Процедура - это наименьшая единица программного кода, на которую можно ссылаться по имени. Это также наименьшая единица программного кода, которая может выполняться независимо. VBA распознает два главных типа процедур - Sub и Function. Любая процедура содержит один или несколько операторов, помещенных между двумя специальными операторами, - объявлением процедуры в начале и оператором завершения процедуры (End Sub или End Function) в конце. 

Модуль - это именованная процедура, состоящая из одной или нескольких процедур, а также объявлений, относящихся ко всем процедурам в модуле. Хотя VBA и допускает размещение всех процедур в одном модуле, имеет смысл разместить процедуры в нескольких модулях в соответствии с выполняемыми этими процедурами задачами, чтобы с ними было проще работать. 

В VBA два типа модулей. Чаще всего используется стандартный модуль, который содержит программный код, предназначенный непосредственно для выполнения. Другим типом модуля является модуль класса, в котором определяются пользовательские объекты с их свойствами и методами. 

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

Планирование и создание модулей 

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

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

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

Создание нового модуля.

Для добавления нового модуля, необходимо убедиться, что вы находитесь в нужном проекте. И выбрать команду из меню Insert=>Моdule.

В окне программного кода для нового модуля есть только один раздел - Declarations (Объявления). Чтобы выяснить, в каком из разделов вы находитесь, взгляните на текст в окне списка справа вверху программного кода. 

Два типа операторов можно поместить в раздел объявлений модуля.

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

Операторы присваивания или выполняемые операторы нельзя размещать в разделе объявлений. Например, в разделе объявлений нельзя задать значение переменной, поскольку для этого требуется оператор присваивания, который можно разместить только внутри процедуры где-нибудь в другом месте модуля. 

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

Стандартные модули и модули с классом

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

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

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

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

ОсновнымиVBA-процедурами являются процедуры двух типов - Sub (подпрограммы) и Function (функции).

Создание новой процедуры выполняется с помощью команды Insert-Procedure

Процедуры типа Sub 

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

В операторе объявления процедуры первый термин Public определяет область видимости процедуры. Для области видимости можно указать либо  Public, либо Private. При этом Public подразумевается по умолчанию, так что это ключевое слово можно пропустить. Логично, что пропуск ключевого слова Public делает область видимости такой, которой вообще не нужно интересоваться. 

Далее идет ключевое слово Sub, которое просто указывает на то, что здесь определяется процедура типа Sub (подпрограмма). За ним следует имя процедуры. которое может быть каким угодно, подчиняясь лишь правилам присвоения имен.

Завершается объявление парой скобок. Эти скобки могли бы содержать аргументы, если бы они были. Аргументы - это элементы данных, которые процедура намеревается получить при вызове. 

Чтобы выполнить процедуру типа Sub, у которой аргументы есть, ее нужно вызвать из другой процедуры. 

Процедуры типа Function 

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

Главное отличие процедуры типа Function - в процедуре обязательно должен присутствовать по крайней мере один оператор, задающий значение этой функции. При этом используется имя процедуры, как будто это обычная переменная.

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

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

Объявления

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

Операторы присваивания. 

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

Выполняемые операторы

Выполняемые операторы делают главную работу в программе и используются для выполнения следующих задач. 

• Вызов процедуры
• Активизация метода некоторого объекта
• Управление порядком, в котором должны выполняться другие операторы, с помощью организации циклов или выбора участка программного кода (из нескольких вариантов) для последующего выполнения
• Выполнение одного из встроенных операторов VBA или функции

Параметры компилятора

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

• Option Base число - установка правила нумерации массивов переменных - начинать нумерацию по умолчанию с 0 или 1
• Option Compare метод - выбор метода, используемого VBA для сравнения строковых переменных (текста). Вместо слова "метод" можно указать Binary для сравнения на основе числового кода символов, Text - для сравнения в порядке, отвечающем порядку сортировки соответствующей базы данных
• Option Private Module - в результате помещения такого оператора Declarations модуля другие проекты не смогут получить доступ к процедурам, переменным и константам этого модуля, даже если эти объекты объявлены как открытые
• Option Explicit - это единственный из операторов, задающих параметры компилятора, о котором следует знать и который стоит использовать. В результате помещения этого оператора в модуль VBA запрещает использовать переменные без их предварительного явного объявления

Имена в VBA

При создании имен лучше использовать определенный метод и придерживаться его.

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

Некоторые правила написания кода:

- отступы в программе. Компилятор игнорирует все пробелы в начале строк, так что можно свело использовать отступы для наведения порядка. Нужно установить отступы одного размера для связанных по смыслу операторов, чтобы связь между такими операторами была зрительно очевидной. Конкретнее говоря, операторы, выполняющиеся вместе при каком-то общем условии, должны иметь и одинаковые отступы.
- использование символа продолжения строки. Чтобы продолжить оператор на следующую строку, поместите в конце текущей строки символ подчеркивания (_). Важно не забыть перед символом подчеркивания, который ещё называют символом продолжения строки, оставить пробел, поскольку без такого пробела будет получено сообщение об ошибке типа "недопустимы символ" (invalid character).
- не использовать операторы в несколько строк.Практически всегда можно разделить длинный оператор на несколько достаточно коротких, в совокупности выполняющих ту же работу, что и исходный. Возможно, для этого потребуется создать несколько дополнительных переменных, в которых будут храниться результаты промежуточных вычислений, но это не слишком большая цена за достижение лучшей прозрачности программного кода. В случае неправильных вычислений, ошибку локализовать проще.
- используйте комментарии. Комментарий начинается с напечатанного вами апострофа. Чтобы разместить в программе многословный комментарий в несколько строк, необходимо поставить по апострофу в каждой строке, занятой этим комментарием. Все, что напечатано в строке программного кода справа от апострофа, считается комментарием. Важно добавлять комментарии по каждой строке программного кода и подробно функциональное назначение каждой отдельной строки программного кода и описывать подробно функциональное назначение групп операторов. При объявлении переменной добавьте комментарий о том, для чего эта переменная создается и где будет использоваться. При объявлении процедуры запишите, что она делает, какие аргументы и для чего использует, какие другие процедуры вызывает.

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

Обычно в объявлениях переменной используется ключевое слово Dim, возникшее как сокращение от слова Dimension (размер), но как глагол в том смысле, что оператор Dim задает в VBA пространство для хранения данных, на которое будет ссылаться переменная. Точно так же для объявления переменных можно использовать ключевые слова Private, Public и Static, тем самым сразу объявляя и область видимости таких переменных. Ключевое слово Static в объявлении переменной следует использовать тогда, когда вы хотите, чтобы переменная оставалась в памяти, даже когда процедура завершила свою работу. Объявлять переменные Static можно только внутри процедур. Если нужно, чтобы все переменные в некоторой процедуре сохраняли свои значения, необходимо поместить ключевое слово Static в объявлении самой процедуры непосредственно перед ключевым словом, определяющим тип процедуры.
После объявления переменной ее можно использовать в других частях программы. Как правило, сначала с помощью оператора присваивания в переменную помещается некоторая информация, потом эту информацию можно будет извлечь в любой другой части программы.
В одной строке программного кода можно объявить и несколько переменных. Ключевое слово Dim печатается один раз, переменные через запятую и для каждой переменной указывается тип данных.

Объявить переменную можно в двух частях программы:

• в разделе объявления модуля (на уровне модуля)
• внутри любой процедуры (на уровне процедуры)

От места объявления зависит, какие процедуры смогут получить доступ к переменной, т.е. от места объявления переменной зависит ее область видимости. Если переменная объявляется на уровне модуля, использовать переменную сможет любая процедура в данном модуле. Если переменная объявляется внутри некоторой процедуры, переменной можно будет  пользоваться только внутри этой процедуры. Лучше всего размещать все объявления в самом начале процедуры. 

Выбор и использование типов данных

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

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

Выражение может состоять из одного или нескольких следующих элементов в любой их комбинации:

• переменные
• буквальные значения
• константы (для объявления используется ключевое слово Const)
• функции VBA
• процедуры типа Function

Если  в выражении не меньше двух таких элементов, то они должны быть связаны либо знаками операций, либо функциями и процедурами типа Function, вложенными внутрь других функций и процедур типа Function.

Знаки операций 

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

При этом они выполняются в следующем порядке: 

1. Арифметические операции и конкатенации
2. Операции сравнения
3. Логические операции

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

Чтобы при сравнении использовался другой, интуитивно более понятный метод, необходимо поместить в раздел объявлений модуля оператор Option Compare Text. В результате этого сравнения строк будет выполняться по алфавиту без учета регистра букв.

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

Дополнительные сведения о типах данных

Тип Variant

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

Тип Boolean

Переменные этого типа могут хранить только два значения: True (в числовом представлении это 1) или False (0). Используйте переменные типа Boolean, когда нужно выяснить, какое из двух альтернативных условий имеет место в данный момент.

Тип Currency(Денежный)

Главная цель использования типа данных - получение точного результата. Хотя типы данных Single и Double с плавающей запятой и могут хранить числа с дробной частью - какими обычно бывают денежные значения - вычисления, выполняемые над числами с плавающей запятой, часто порождают небольшие ошибки, а это заставляет сильно нервничать тех, кто занят подсчетом денежных знаков. 

Так же данный тип переменной может быть полезен в следующих случаях:

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

Значения типа Currency могут иметь до 19 значащих цифр, из них 15 - до запятой и 4 - после (положение десятичного разделителя фиксировано).

Тип Date

Этот тип используется для удобства при работе со значениями дат и времени. 

При этом нужно только помнить, что значения дат и времени - их буквальное значения - необходимо помещать в ограничивающую их пару знаков "решетки"(#).

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

Управляющие структуры можно разбить на три главные группы - условные операторы, циклы и операторы With.

• условный оператор определяет, какую из ветвей программного кода выполнять, в зависимости от того, какое значение (True или False) принимает некоторое условие. К условным операторам относятся  If..Then и Select Case
• цикл повторяет выполнение некоторого блока программного кода, либо заданное число раз, либо до тех пор, пока некоторое условие не примет значение True или False. Если известно заранее, сколько раз необходимо выполнить цикл, то используйте For...Next, а если продолжение повторения программного кода зависит от выполнения некоторого условия, используйте Do....Loop (конец Lcop) (этот оператор доступен в нескольких вариантах). Чтобы повторить некоторые действия по отношению к объектам в коллекции, используйте цикл For Each...Next.
• оператор With позволяет выполнить множество действий с одним и тем же объектом без необходимости каждый раз указывать объект

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

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

Условное выражение

Структуры If..Then, Select Case и Do....Loop принимают решение о последующих действиях на основе простого теста: какое значение принимает выражение - True (Истина) или False (Ложь)? Условием может быть любое выражение. Однако чаще всего условные выражения строятся на основе операции сравнения. Нельзя использовать строки и объекты в качестве условий сами по себе, хотя они вполне годятся для использования в качестве компонентов выражений, построенных на основе подходящей операции сравнения. 

Основные логически операции

Логические операции сначала оценивают значения значений- компонентов, а затем получают конечный результат - True или False. Следующие операторы возвращают True, в случае выполнения условий:

• And - только если оба выражения принимают значение True
• Or - если хотя бы одно или все выражения принимают значение True
• Xor - если только одно из двух выражений принимает значение True

Условные операторы If...Then

Оператор If...Then выполняет некоторый блок программного кода, если условие, которое вы предложили этому оператору, принимает значение  True, и не делает ничего, если условие принимает значение False. 

Обратите внимание на следующие моменты:

• Если условие принимает значение True, то VBA выполнит операторы, размещенные между If...Then и End If.
• Обратите внимание, ключевое слово Then размещается в одной строке с If и выражением условие. При размещенииThen в следующей строке (без указания переноса с помощью символа подчеркивания в первой) VBA генерирует ошибку.
• Не забывайте печатать завершающий оператор End If, при его отсутствии VBA не поймет, какой оператор должен быть последним в блоке. 

Если структура If...Then должна выполнить только один оператор, когда условие принимает значение True, всю эту структуру можно уместить в одну строку.  В таком случае оператор End If не требуется - точнее, его быть не должно. Оператор 

If curЦена > 20 Then MsgBox "Цена слишком высока!"

в результате идентичен структуре 

If curЦена > 20 Then

     MsgBox "Цена слишком высока!"

End If

Операторы If...Then...Else

Данная конструкция используется, если нужно, чтобы на основании одного условия программа выбирала между двумя альтернативными блоками программного кода. В случае, если условие принимает значение True, то выполняется программный код, следующий за операторов End If. Иначе выполняются только операторы, следующие за оператором Else.

Использование If...ElseIf

Используйте его, чтобы проверить дополнительное условие, если нужно выполнять определенные операторы только в случае, когда первое условие не принимает значение True. Структура: 

If условие1 Then

(операторы, выполняющиеся, когда условие1 = True)

Elself условие2 Then

(операторы, выполняющиеся, когда условие1 = False,

                а условие2 = True)

Elself условиеЗ Then

(операторы, выполняющиеся, когда условие!

и условие2 = False, а условиеЗ = True)

. .. (другие операторы Elself)

Else ' необязательное ключевое слово

(операторы, выполняющиеся, когда все условия = False)

End If

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

Использование логических операций в условных выражениях может быть более элегантным альтернативной использованию ElseIf и вложенных If...Then, когда нужно выполнять лишь одну ветвь пути, определяемого множеством условий. Однако условные выражения не помогут, когда необходимо использовать несколько ветвей пути решения. 

Оператор SelectCase

Данный оператор следует применять, если приходится проверять одно и то же значение, сравнивая его с различными выражениями. Синтаксис:

S e l e c t Case значение

     Case критерий1

              (операторы, выполняемые, когда значение

               удовлетворяет критерий1}

     Case критерий2

              (операторы, выполняемые, когда значение

              удовлетворяет критерий2)

. . . ' дополнительные операторы Case

      Case Else ' необязательный

                       (операторы, выполняемые, когда значение

                       не удовлетворяет ни одному из приведенных критериев)

End Select

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

Заметьте, что знака операции, присутствие которого кажется на первый взгляд логичным в критериях нет. Причина в том, что в операторах Select Case равенство в качестве операции сравнения просто подразумевается.  Оператор Case Else необязателен, так как обычно достаточно отсутствия каких-либо действий. Однако, если вы хотите проинформировать себя о не предусмотренных значениях, хранящихся в программе, то лучше использовать его. Также можно использовать несколько критериев в одной строке, но нужно не забывать разделять их запятой. 

Повторение с помощью циклов

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

VBA предлагает три типа структур, организующих циклы

Do...Loop - пока или до тех пор, пока некоторое условие имеет значение True

For....Next - заданное число раз

For Each....Next - для каждого объекта из коллекции объектов

При работе с циклами важно помнить простое правило: внутренний цикл должен закончиться прежде, чем закончится внешний.

Циклы Do

Все возможные версии оператора Do....Loop предназначены для повторения заданного блока программного кода неопределенно долго, пока не будет выполнено некоторое условие. Для того чтобы выяснить, продолжать цикл или нет, оператор Do...Loop оценивает заданное условное выражение типа условных выражений, используемых для оператора If...Then.

Типы операторов Do....Loop

Для выхода из цикла используется Exit Do. В этом случае действие внутри цикла немедленно прекращается и передает управление оператору, следующему сразу после цикла. 

Цикл For...Next

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

Синтаксис:

For счетчик = начало To конец

(операторы, выполняющиеся при каждом проходе цикла)

Next счетчик

В случае необходимости можно задать шаг цикла Step. Например: For F = 1 To 33 Step 2

Цикл Go To

Оператор Go To в совокупности со специальным оператором метки в месте назначения позволяет по желанию перемещаться от одной точки в процедуре к другой. Метка - это оператор, просто отмечающий некоторое место в программном коде. Чтобы задать метку, напечатайте ее имя, а  после него - обязательно двоеточие. Иначе VBA выдаст ошибку.

Отладка и устранение ошибок

Типы ошибок:

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

Советы по тестированию программы на логические ошибки:

1. Если программа предназначена для работы с разными типами документов, то и попробуйте ее в работе с разными типами документов. Попытайтесь работать с программой, когда в VBA-приложении открыто несколько документов и когда - ни одного
2. Посмотрите, как выполняется программа, когда окно документа находится в разных состояниях (развернуто, свернуто или восстановлено)
3. Попробуйте выполнить программу, когда в окне документа выделены различные элементы или группы элементов
4. Если программа требует ввода информации от пользователя в специальном окне или в специальной форме, опробуйте все вероятные и невероятные типы ввода и посмотрите на результат. Например, если вводимое значение предполагается целым, попробуйте ввести значение с плавающей запятой, дату и строку символов, чтобы узнать, как отреагирует на это программа.
5. Если программа работает со значениями дат и времени, попробуйте задать ей самые разные даты (в том числе и 29 февраля) и самое разное время суток. Для этого просто достаточно установить для своего компьютера подходящие показания системных времени и даты. 

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

Комбинации клавиш для отладки

Подробное описание работы с отладчиком в режиме паузы описано в книге "VBA для чайников 3-е издание" Стив Каммингс,  начиная с 189 страницы.

Операторы и функции для обработки строк

Операторы и функции для работы с датами и временем

Смысл отображения кнопок в окне сообщения заключается в предоставлению пользователю возможности выбора действий. В данном случае пользователю не нужно ничего печатать - следует только щелкнуть на одной из кнопок. Нужно суметь выяснить, на какую из кнопок щелкнул пользователь. 

Это просто, поскольку функция MsgBox возвращает целое значение, как раз соответствующее той кнопке, на которой щелкнул пользователь. Можно сравнивать возвращаемое значение не с конкретными числами, а с предлагаемыми VBA именованными константами. Константы с их действительными значениями ниже. 

Некоторые математические функции

Пример финансовых функций

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

Свойства (для понимания, что такое объект):

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

Класс объекта - в классе планируются типы данных, которые могут быть использованы объектом, и определяются методы, свойства и события объекта.
На основе этого класса создается экземпляр - тот объект, который будет содержать реальные данные.
Коллекция - это VBA-объект специального назначения. Они предназначены для упрощения работы с набором объектов, когда этот набор объектов нужно использовать как одно целое.
Объектная модель. Объекты VBA существуют в иерархической зависимости один от другого. Кроме обладания своими собственными свойствами, методами и событиями, объекты высших ступеней иерархии служат в качестве контейнеров для одного или целого множества подчиненных объектов. Эти вложенные объекты содержат другие. Вся эта система и является объектной моделью. Причем на вершине объектной модели находится объект Application (приложение), являющийся контейнером для всех других объектов приложения, в котором размещаются все модули программного кода и формы, равно как и документ проекта.




Краткая итоговая справка по ООП
Объектно-ориентированным программированием (ООП) называется особая модель написания кода, которая помогает упорядочить работу с данными в среде программирования. В основе объектно-ориентированного программирования пять базовых элементов:

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

При указании свойств и методов объектов используется так называемая точечная запись: сначала указывается имя объекта, за ним - точка, а после неё имя свойства или метода.


В программах, созданных на VBA можно использовать вообще любые приложения и "компоненты", удовлетворяющие стандарту Component Object Model (COM)(объектная модель компонента)

Информация из учебника Мэтью Харрис "Освой самостоятельно программирование для Microsoft Excel 2000 за 21 день"

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

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

Объявление процедуры - это первая строка процедуры (содержащая ключевое слово), которая указывает на имя процедуры

Тело процедуры - это часть процедуры, которая содержит команды и программные конструкции, выполняемые процедурой. Тело процедуры можно определить как часть процедуры между строкой, содержащей объявление процедуры, и строкой End Sub, которая завершает процедуру.

Аргумент - это информация, которая передается VBA-процедуре для дальнейшего использования. Некоторые процедуры могут использовать несколько аргументов. В этом случае аргументы записываются в виде списка и отделяются друг от друга запятыми, образуя, таким образом, список аргументов.

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

Когда в окне программы курсор переводится из только что отредактированной строки, то она сначала анализируется на предмет правильности синтаксиса, а затем компилируется в форму, которую можно использовать уже без повторного анализа.
Синтаксическая ошибка - это ошибка, которая образуется в результате неправильного построения программной инструкции VBA. Синтаксические ошибки обычно возникают из-за пропущенных или поставленных "не в том месте" запятых, круглых скобок, кавычек и пр. Кроме того, синтаксические ошибки часто появляются в результате пропуска таких ключевых слов, как Sub, или случайного удаления целых строк с ключевыми словами.
VBA обычно уведомляет вас о наличии синтаксических ошибок по мере написания каждой строки программы. Если вы не исправите синтаксическую ошибку при ее первом обнаружении, VBA снова отобразит сообщение об ошибке при попытке выполнить процедуру, в которой она содержится.
Ошибка времени выполнения (динамическая ошибка) - это ошибка, возникающая в процессе выполнения процедуры. У динамических ошибок много различных причин. Чаще всего они возникают в результате использования инструкций, которые с точки зрения синтаксиса корректны, но содержат данные "не того" типа или пытаются работать с файлами или частями файла, которые отсутствуют в момент выполнения процедуры.

Переменная - это имя, которое дали участку компьютерной памяти для того, чтобы хранить в нем данные некоторого типа. Имя переменной - это ярлык, по которому осуществляется доступ к участку памяти. Содержимое участка может меняться, но имя остается неизменным. В переменной могут храниться данные любого из типов. При неявном создании переменной, то есть присваивании переменной значения внутри процедуры, ей присваивается формат Variant. Однако, такой способ таит в себе опасность "не узнавания" переменной. Лучше использовать явное объявление переменной. Для этого используется инструкция Dim (dimension- размерность). При необходимости объявления нескольких переменных все они указываются через запятую. Объявлять переменную можно только один раз. Это может быть сделано в любом месте программы, но обязательно до первого упоминания переменной в процедуре. Однако хорошей практикой следует считать вынесение всех объявлений в один блок в начале программы.
Переменные, объявленные в некоторой процедуре, доступны только внутри этой процедуры. Такая переменная существует только во время выполнения процедуры. Это называется областью видимости. Правила выбора имен переменных требует, чтобы переменная имела уникальное имя внутри области видимости. Однако, если область видимости переменных ограничена процедурой, ничто не мешает вам объявить две переменные с одинаковыми именами в разных процедурах.
Иногда необходимо, чтобы одна переменная была доступна из нескольких процедур. Обычно это применяется для того, чтобы вычислить некоторое значение и сохранить его в переменной, доступной для многих процедур, вместо того чтобы вычислять его в каждой из процедур. Про такую переменную говорят, что область ее видимости ограничена модулем, в котором объявлена переменная. Для того, чтобы объявить такую переменную необходимо поместить инструкцию  Dim в самое начало модуля.
Имя переменной должно быть уникальным в области ее видимости. Однако можно объявить две переменные с одинаковыми именами, если у них разные уровни видимости. В этом случае VBA выбирает переменную с меньшей областью видимости.Переменные, объявленные внутри процедуры (в таком случае), называются локальными по отношению к этой процедуре.

Говоря о времени жизни переменной (persistence), имеют в виду время, в течение которого переменная сохраняет присвоенное ей значение. Русский термин время жизни хотя и общепринят, не совсем точен: на самом деле речь идет не о времени, а об области программы. Обычно время жизни переменной совпадает с областью её видимости. но не всегда.
Когда объявляется переменная в процедуре, значение этой переменной сохраняется только до тех пор, пока выполняется процедура. При выполнении процедуры VBA создает переменную, и независимо от того, объявлена она явно или неявно, по окончании процедуры освобождает память, занятую переменной. Значение переменной при этом безвозвратно теряется.
Переменные, объявленные в процедуре, не определены до начала выполнения процедуры и по ее окончания.
Переменные уровня модуля сохраняют свое значение до тех пор, пока выполняется хоть одна процедура из этого модуля. Когда VBA выполняет процедуру, фактически просматривается весь модуль, и все переменные, объявленные в начале этого модуля, создаются и сохраняют свое значение, пока выполняется хоть одна процедура из этого модуля.
Для того чтобы запретить неявное определение переменных в модуле необходимо в области описания модуля использовать команду Option Explicit. Если в таком случае при этом определить переменную неявно, то VBA выдаст сообщение об ошибке.
Такие команды, как Option Explicit, называются директивами компиляторами. Они не вызывают никаких конкретных действий, но служат указаниями компилятору, как выполнять этот модуль. Компилятор - это часть программы VBA, которая преобразует текст вашей программы в машинные инструкции, необходимые для выполнения конкретного задания. Директивы компилятора просто сообщают ему, как следует компилировать ваш исходный текст.
Для того чтобы объявить тип переменной в инструкции  Dim, добавьте за именем переменной ключевое слово As и укажите тип переменной. Синтаксис следующий Dim имя As тип, где имя - идентификатор переменной, а тип - один из типов переменных.
Переменная фиксированной длины всегда имеет постоянное число символов. Это обеспечивает уверенность в том, что в строке сообщения всегда находится правильное число символов.  Строки фиксированной длины удобны для вывода информации на экран в колонках, или для того чтобы длина строки не превысила определенного значения. Синтаксис:
Dim Имя As String * N, где Имя - идентификатор переменной, а N- число от 1 до максимальной длины строки (около 2 млн знаков).

Константа - это такая величина в VBA-программе, которая никогда не меняется. Константы типа "Hello, World!" называются непоименованными, так как их текст непосредственно вносится в программу. Поименованная константа, как и переменная, имеет имя. Это имя связано с конкретным, не изменяющимся значением. В этом случае вместо текста константы вставляется значение, связанное с константой. Разница лишь в том, что значение константы не может быть изменено программой, единственный способ изменить его - это отредактировать текст программы.
Константы используются для того, чтобы сделать текст программы более понятным и легким для восприятия. В общем случае следует использовать поименованную константу всегда, когда в программе встречается повторяющееся значение, которое трудно запомнить, или если само значение на момент написания программы неизвестно.
Для объявления констант служит ключевое слово Const. При объявлении констант можно пользоваться ранее объявленными константами, арифметическими выражениями и операторами сравнения, но нельзя применять операцию конкатенации, использовать переменные и оператор Is. Область видимости поименованных констант определяется так же, как и у переменных.
Если есть такая необходимость, то можно указать тип константы явно, для повышения точности вычислений.
Так же существуют внутренние константы. Это константы, значение которых определено заранее. Например, для VBA vbOKOnly, vbOKCancel, для Excel xlChart.

Программа называется интерактивной, если она получает введенные данные от пользователя. Для получения данных от пользователя применяется функция InputBox. Функция InputBox выводит на экран окно с текстом, предлагающим ввести некоторое значение, и полем ввода для этого значения. Так же присутствуют кнопки ОК и Cancel. Синтаксис:
Строка = InputBox (Приглашение [, Заголовок]), где Строка - это любая переменная, в которой может храниться текстовая строка; может иметь тип String или Variant. Приглашение - поименованная или непоименованная константа или переменная, содержащая текст. Это обязательный аргумент.
Результат функции InputBox, или ее возвращаемое значение - это всегда строка. Именно поэтому переменная Temp(введенное пользователем значение) была определена с типом String.
После её необходимо перевести в число (для использования в математических расчетах). Ввод пользователя преобразуется в число с помощью встроенной функции CSng, одной из нескольких функций преобразования типов.

Выражение - это величина или группа величин, объединенных в единую сущность. Каждое выражение имеет определенное и причем единственное значение одного из типов (освещенных выше). А именно Byte, Integer, Long, Single, Double, Currency - для числовых и т.д.
Выражение - это значение или группа значений, представляющих единую величину. Результат выражения - это значение, полученное после выполнения всех операций, заданных в выражении, т.е. когда выражение сводится к единственной величине.
Для объединения, сравнения или иного манипулирования значениями, входящими в выражение, используются операторы. Величины, входящие в выражение (это могут быть переменные или константы), называются операндами; большинство операторов требует наличия двух операндов.
Бывают следующие типы выражений: дата; числовое выражение; строковое выражение; логическое выражение и объектное выражение. Объектное выражение - это выражение, значением которого является ссылка на объект. Не все типы данных совместимы друг с другом. Нельзя комбинировать несовместимые типы в одном выражении, иначе выдастся сообщение о несоответствии типов.

Для выбора способа сравнения строк существует директива компилятора Option Compare.
Option Compare [Text : Binary]
 Для выбора бинарного сравнения предназначено ключевое слово Binary, а для текстового сравнения - Text. Директива Option Compare должна быть помещена на отдельной строке  в начале модуля, в области описания. Данная директива используется только на уровне модуля и влияет на способ сравнения во всех процедурах этого модуля. Обычно ее помещают перед всеми объявлениями переменных и процедур. Если такая директива отсутствует, сравнение выполняется бинарным способом.

Некоторые операторы сравнения:
Is - Является. Оба операнда должны иметь тип Object. True, если указывают на один и тот же объект, False в противном случае.
Выражения с оператором Is всегда имеют тип Boolean.
Like - Подобно. Оба операнда должны иметь тип String. True, если один соответствует образцу другого, False в противном случае.
Оператор Like служит для сравнения строк особым способом. Этот оператор применим только к строкам. Т.е. можно искать в тексте все вхождения слов или даже фраз, соответсвующих образцу. Образец для сравнения задается с помощью различных специальных символов:

• # - любая цифра
• * - любая строка
• ? - любой одиночный символ
• [список] - любой символ из списка
• [!список] - любой символ, не входящий в список

Логические операторы(сложные на мой взгляд):

• Xor - исключающая дизъюнкция. Выражение истинно, если истинен только один его операнд
• Eqv - эквивалентность. Выражение истинно, если значение совпадают
• Imp - импликация. Выражение ложно, если одно истинно,а второе - ложно

Порядок. в котором VBA вычисляет выражения:

1. Арифметические операции
2. Конкатенации строк
3. Операторы сравнения
4. Логические операторы

Ниже приведена таблица:

Понятие о функциях

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

• Преобразование текстовых строк в данные другого типа
• Получение информации о текстовых строках
• Преобразование других типов данных в текстовые строки
• Форматирование чисел или других типов данных для вывода на экран
• Выполнение тригонометрических, логарифмических, статистических, финансовых и других вычислений
• Получение информации о файлах, дисководах, или о среде, в которой в данный момент выполняется VBA

Свойства функций:

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

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

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

Например, MsgBox Prompt:=AnyMsg, Title:=AnyTitle

Здесь используются поименованные аргументы и для вывода на экран сообщения (или приглашения), которое выводит функция MsgBox, и для аргумента Title (название), который определяет заголовок диалогового окна, присваивая значения каждому поименованному аргументу. Имя аргумента для заголовка функции MsgBox - Title; а выражение Title:-AnyTitle присваивает содержимое переменной AnyTitle аргументу, который VBA передает функции MsgBox для использования в качестве диалогового окна. 

Далее Prompt является названием аргумента, отвечающего за текст приглашения функции  MsgBox, а выражение Prompt:=AnyMsg присваивает содержимое переменной AnyMsg аргументу, который VBA передает функции MsgBox для использования в качестве текста, выводимого на экран в диалоговом окне сообщения. 

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

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

Процедура-функция - наиболее общий термин для функции, которую создает пользователь. Целью функции является выполнение некоторых вычислений или обработка определенных данных и возврат результата для обработки. Информация передается встроенной функции через список аргументов. При объявлении процедуры-функции перечисляются все аргументы, отделяемые запятой.
Краткий синтаксис:
Function Имя[список аргументов]
[Имя = выражение]
End Function
Необязательный синтаксический элемент [Имя = выражение] представляет собой присваивание результата функции, которое указывает VBA, какое значение функция должна возвратить. Где Имя - это имя функции, а выражение - это любое выражение, значение которого функция будет возвращать. Хотя эта часть функции не является обязательной, следует всегда включать оператор присваивания в процедуру-функцию.
Названия, которые используются в списке аргументов, похожи на переменные: они ссылаются на любое значение, которое передается функции во время её вызова. Всякий раз, когда происходит обращение к названию аргумента в инструкции внутри функции, VBA ведет себя так, будто название аргумента - переменная, содержащая величину, приведенную в списке аргументов оператора, который вызвал функцию.
Имена аргументов имеют такую же область определения, как и переменные, объявленные в функции локально, т.е. переменные аргументов недоступны за пределами списка аргументов процедуры-функции, в которой они объявлены.

VBA передает информацию в процедуру-функцию с помощью аргументов и делает это двумя способами: по ссылке и по значению. По умолчанию все аргументы передаются по ссылке. При передаче по ссылке фактически передается адрес величин, указанных в списке аргументов при вызове функции. Это означает, что если функция в процессе работы изменит значение одного из двух аргументов, то и исходные данные, переданные в качестве этого аргумента, изменяются.
При передаче аргумента по значению, делается копия исходных данных и передается функции копия этого аргумента для обработки. При изменении значения аргумента, переданного по значению, изменяется только переданная функции копия; исходные данные не изменяются. Передача данных по ссылке позволяет изменять исходные данные, переданные функции через аргумент; а передача с помощью значения не позволяет изменять значение исходных данных.
Для того, чтобы обозначить метод передачи аргумента - по значению или по ссылке, - перед этим аргументом следует вставить ключевое слово ByVal или ByRef. Ключевое слово ByVal определяет передачу аргумента по значению, а ByRef - по ссылке. (By value - по значению, by reference - по ссылке).
Важно отслеживать, чтобы функция не изменяла передаваемые ей аргументы. Если есть необходимость изменить значения аргумента, то лучше использовать процедуру.
Процедуры-функции, находящиеся в одном приложении, доступны для любого открытого документа этого приложения, поэтому можно собрать функции общего назначения в одном проекте и создать библиотеку функций.
Минимальные правила:
Дважды проверьте каждую написанную функцию, чтобы убедиться, что она содержит присвоение результата функции. Т.о. можно быть уверенным, что функция не вернет по умолчанию пустое значение типа Variant, строку нулевой длины String или просто нулевое значение.
Избегайте побочных эффектов: каждый аргумент, значение которого изменяется в процессе выполнения процедуры-функции, описывайте с помощью ключевого слова ByVal.
Если вы собираетесь использовать функции в ячейках рабочего листа Excel, убедитесь, что написанная функция  соответствует всем правилам нестандартных функций, которые рассматривались в начале этой главы.
Нестандартная функция, которая пересчитывается каждый раз при изменении любой ячейки рабочего листа, называется функцией типа Volatile. Для того чтобы задать нестандартную функцию такого типа, сразу после её объявления нужно добавить инструкцию
Application.Volatile
Однако не стоит им злоупотреблять, так как это может привести к ненужному пересчету, что увеличит общее время, необходимое для пересчета рабочего листа.

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


Для того чтобы воспользоваться свойствами или методами объекта, следует определить объект, чьи свойства или методы хотите использовать, а также конкретное свойство или метод объекта. Синтаксис: Object.Identifier, где Object - любая допустимая ссылка на объект. Ссылка на объект создается путем объявления переменной, указывающей на объект или с помощью методов, возвращающих ссылку на объект. Identifier - это любое допустимое свойство или имя метода.

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

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

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

Основы SQL(певрые шаги)

Так уж получилось, что в процессе изучения Access неожиданно выяснилось, что для работы с этим приложением нужно ещё владеть базовыми знаниями SQL и Access. В данном сообщение начнем знакомство с SQL. 

Первой книгой, которой я воспользуюсь для написания будет книга Бена Форта "Освой самостоятельно SQL. 10 минут на урок" и потом перейдем к другим книгам. Писать буду тезисы, заинтересовавшие меня по мере прочтения. Так что прошу прощения за отрывочность и перескоки.

Начнем с простого определения, что же такое SQL.
SQL (Structured Query Language) - язык структурированных запросов. Он был разработан для взаимодействия с базами данных. 

Основные задачи и  операторы:

1. Для выборки данных из таблицы используется SELECT.
   Синтаксис:
   SELECT name
   FROM table;
   Операторы нечувствительны к разным регистрам, однако переменные да. Поэтому нужно следить за корректностью введенных переменных.
   При необходимости вывести несколько столбцов, необходимо их записать в запросе через запятую.
2. Сортировка данных ORDER BY.
   Синтаксис:
   SELECT name
   FROM table
   ORDER BY name;
   Данный оператор используется для сортировки. Важно следить, чтобы он был последним.
   Для точной сортировки используется порядок сортировки, который задается словами ASC(ascending) и DESC(descending). По умолчанию используется сортировка ASC, то есть восходящая А-Я, поэтому прописывать ASC необязательно. Так же тип сортировки можно указывать для каждого столбца отдельно. 
3. Фильтрация данных с помощью условия WHERE.
   Синтаксис: (например)
   SELECT name
   FROM table
   WHERE name>0.6;
   В результате работы этого оператора показываются не все результирующие строки, а только те, которые удовлетворяют условию.
   Так же можно использовать логические операторы, как AND и OR.
   Ключевое слово IN используется для указания диапазона условий, любое из которых может быть выполнено. При этом значения, заключенные в скобки, перечисляются через запятую.
   Ключевое слово IN обрабатывается быстрее, чем логическое слово OR.
   Так же существует логический оператор NOT. Он служит для того, чтобы отрицать все предложения, следующие за ним. Обычно он вставляется перед названием столбца, значения которого нужно отфильтровать, а не после.
   Оператор LIKE используется совместно с метасимволами для поиска строк, содержащих заданные символы. При этом знак %(процента) может заменить бесконечно много символов, а знак _(нижнее подчеркивание) только одно.

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

В СУБД Access, SQL Server и Sybase для конкатенации используется знак плюс ("+"). В СУБД DB2, Oracle, PostgreSQL и Sybase используется знак ||. Оператор || более предпочтителен, так как он поддерживается большим количеством СУБД.

В MySQL не поддерживается конкатенация при помощи оператора + или ||. Здесь необходимо использовать функцию CONCAT().

Добавленные пробелы в SQL можно убирать с помощью функций RTRIM() - убирает пробелы справа; LTRIM() - удаляет левую часть строк, а также TRIM() - "обрезает" строку слева и справа.

Псевдоним - это альтернативное имя для поля или значения (чаще всего используется для переименования полей). Псевдоним присваивается при помощи ключевого слова AS (alias). При указании псевдонимов важно не использовать реальных названий столбцов в таблице, поскольку во многих реализациях SQL такое поведение не приветствуется - можно получить сообщение об ошибке.

Статистические функции

Функция AVG используется для возвращения среднего значения определенного столбца путем подсчета числа строк в таблице и суммирования их значений. Эту функцию можно использовать для возвращения среднего значения всех столбцов или определенных столбцов или строк.
Однако данную функцию можно использовать только для вычисления среднего значения определенного числового столбца, имя этого столбца должно быть указано в качестве параметра функции. Чтобы получить среднее значение нескольких столбцов, необходимо использовать несколько функций AVG (). Строки NULL игнорируются. При этом важно учитывать что использование оператора DISTINCT (убирает дублирующие строки) делает результат подобного запроса некорректным.

Функция COUNT () подсчитывает число строк:

• В виде COUNT(*) для подсчета числа строк в таблице независимо от того, содержат столбцы значения NULL или нет.
• В виде COUNT(column) для подсчета числа строк, которые имеют значения в указанных столбцах, причем значения NULL игнорируются.

Функция MAX () возвращает самое большое значение из указанного столбца. Для этой функции необходимо указывать имя столбца. 

Несмотря на то что функция MAX обычно используется для поиска наибольшего числового значения или даты, многие (но не все) СУБД позволяют использовать ее для возвращения наибольшего значения из всех столбцов, включая текстовые. При использовании с текстовыми данными функция возвращает строку, которая была последней, если бы данные были отсортированы по этой строке. Значения столбцов со значениями NULL игнорируются.

Функция MIN () возвращает самое маленькое значение из указанного столбца.

Функция SUM () возвращает сумму (общую) значений в определенном столбце.Значения NULL игнорируются.
Все статистические функции можно использовать для выполнения вычислений над несколькими столбцами при помощи стандартных математических операторов.

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

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

Итоговые данные

Группирование дает возможность разделить все данные на логические наборы, благодаря чему становится возможным выполнение статистических вычислений отдельно по каждой группе.
Группы создаются с помощью предложения GROUP BY оператора SELECT.
Предложение GROUP BY указывает СУБД группировать данные и затем выполнять вычисление по каждой группе, а не по всему набору результатов.

Пример:
SELECT vend_id, COUNT (*) AS num_prods
FROM Products
GROUP BY vend_id;
В данном примере оператор SELECT предписывает вывести два столбца - vend_id. содержащий идентификатор поставщика продукта, и num_prods, содержащий вычисляемые поля (он создается с помощью функции COUNT(*)). Предложение GROUP BY указывает СУБД сортировать данные и группировать их по столбцу vend_id. В результате значения num_prods будет вычисляться по одному разу для каждой группы записей vend_id, а не один раз для всей таблицы products.

Однако есть важные правила для использования GROUP BY:

• В предложениях GROUP BY можно указывать только столбцов, сколько вам необходимо. Это позволяет вкладывать группы одна в другую, благодаря чему обеспечивается тщательный контроль за тем, какие данные подлежат группированию.
• Если вы используете вложенные группы в предложении GROUP BY, данные суммируются для последней указанной вами группы. Другими словами, если введено группирование, вычисления осуществляются для всех указанных столбцов (вы не сможете вернуть данные для каждого отдельного столбца)
• Каждый столбец, указанный в предложении GROUP BY, должен быть столбцом выборки или выражением (но не функцией группирования). Если в операторе SELECT используется какое-то выражение, то же самое выражение должно быть указано в предложении GROUP BY. Псевдонимы применять нельзя. 
• В большинстве реализаций SQL нельзя указывать в предложении GROUP BY столбцы, в которых содержатся данные переменной длины (т.е. столбцы, содержащие текстовые поля или поля комментариев).
• За исключением операторов статистических вычислений, каждый столбец, упомянутый в операторе SELECT, должен быть представлен в предложении GROUP BY. 
• Если столбец, подлежащий группированию, содержит строку со значением NULL, оно будет возвращено в качестве группы. Если имеется несколько строк со значениями NULL, они будут сгруппированы вместе. 
• Предложение GROUP BY должно следовать после предложения WHERE и до какого-либо предложения ORDER BY.

В дополнение к способности группировать данные с помощью предложения GROUP BY, SQL так же позволяет осуществлять фильтрацию - указывать, какие группы должны быть включены в результат. а какие исключены из него. Следует использовать предложение HAVING. Оно похоже на предложение WHERE. но существует одна разница. А именно WHERE проверяет и фильтрует данные построчно, а HAVING - в группах. 

Иначе, WHERE фильтрует до того, как данные будут сгруппированы, а HAVING фильтрует после того, как данные были сгруппированы. Это важное различие. Строки, которые были выброшены по предложению WHERE, не будут включены в группу, иначе это могло бы изменить вычисляемые значения, которые, в свою очередь, могли бы повлиять на фильтрацию групп в предложении  HAVING. 

Используйте предложение HAVING только вместе с предложением GROUP BY, а предложение WHERE - для стандартной фильтрации на уровне строк. 

ORDER BY используется для сортировки полученных результатов.

Обновление и удаление данных
Обновление с помощью UPDATE. Синтаксис:
UPDATE table
SET column.name = 'something'
WHERE (условие);
Оператор UPDATE всегда начинается с имени таблицы, подлежащей обновлению. Затем используется команда  SET, чтобы ввести в столбец новое значение. Заканчивается он предложением WHERE, которое сообщает СУБД какая строка подлежит обновлению. При отсутствии условия можно обновить все строки таблицы на одно и то же значение. Для обновления нескольких столбцов необходимо указать их через запятую. При этом используется только одна команда SET.

Удаление с помощью DELETE. Синтаксис:
DELETE FROM table
WHERE (условие);
Требуется указать имя таблицы, из которой должны быть удалены данные. Этот оператор удаляет строки целиком, а не отдельные столбцы. Для удаления определенного столбца следует использовать оператор UPDATE. Оператор DELETE удаляет из таблицы отдельные строки или даже все строки за один раз, но он никогда не удаляет саму таблицу.
Для более быстрого удаления значений из всех строк таблицы лучше использовать оператор TRUNCATE TABLE, который делает это быстрее, так как не регистрирует изменения в данных.

Добавление данных
Обновление с помощью INSERT. Синтаксис:
INSERT INTO table
VALUES (' ', ' ',' ');
Данные, которые должны быть сохранены в каждом столбце таблицы, указываются в условии VALUES, значения должны быть приведены для каждого столбца. Если для какого-то столбца не имеется соответствующего значения, следует использовать значение NULL. Столбцы должны заполняться в порядке, котором они появились в определении таблицы.
Однако нет гарантий, что столбцы будут расположены в том же самом порядке, когда таблица будет реконструироваться в следующий раз. Более безопасный способ - указать явно все имена столбцов в круглых скобках, следующих после имени столбца. В этом случае так как предоставлены имена столбцов, то условие VALUES должно подобрать названные имена столбцов в порядке, в котором указаны столбцы, причем не обязательно в порядке, в каком они следуют в реальной таблице. Это удобно даже, если расположение столбцов в таблице изменится.

Для вставки в таблицу данных из другой таблицы следует использовать  INSERT SELECT. Синтаксис:
INSERT INTO table1 (c1, c2,c3)
SELECT (c1,c2,c3)
FROM table 2;
Однако необходимо учитывать, что к именам столбцов не предъявляются никакие требования. В действительности СУБД вообще не обращает внимания на имена столбцов, возвращаемых оператором SELCT. Точнее, ею используется положение столбца, так что первый столбце в SELECT (независимо от имени) будет использован для заполнения первого указанного столбца таблицы и т.д.

Для копирования данных из одной таблицы в новую можно выполнить с помощью оператора SELECT INTO. Разница между INSERT SELECT и SELECT INTO состоит в том, что первый оператор экспортирует данные, а второй - импортирует.
Этот оператор создает новую таблицу и копирует в нее все содержимое другой таблицы. Можно использовать операторы WHERE и GROUP BY. Для добавления данных из нескольких таблиц можно использовать объединения. Данные можно добавить только в одну таблицу независимо от того, из скольких таблиц они были извлечены.

Создание таблицы
Чтобы создать таблицу необходимо использовать оператор CREATE TABLE. При этом нужно указать следующие данные:

• имя новой таблицы. оно выводится после ключевого слова CREATE TABLE
• имена и определения столбцов таблицы. разделенные запятыми
• в некоторых СУБД также требуется, чтобы было указано место размещения таблицы

Желательно разбивать код на несколько строк (для каждой переменной) для удобства чтения.

Для обновления таблицы следует использовать оператор ALTER TABLE.

Добавление столбца

ALTER TABLE table

ADD column.name;

 Удаление столбца

ALTER TABLE table

DROP COLUMN column.name;

Удаление всей таблицы (а не значений)

DROP TABLE table;

Подзапросы.
Это запросы, которые вложены в другие запросы. Их также называют вложенные запросы, или подчиненные запросы. Подзапросы всегда обрабатываются, начиная с самого внутреннего оператора в направлении "изнутри наружу". Чаще всего подзапросы используют в операторах IN предложения WHERE и для заполнения вычисляемых столбцов.

Представление - это виртуальные таблицы. В отличие от таблиц, содержащих данные, представления содержат запросы, которые динамически выбирают данные, когда это необходимо. Следует обратить внимание, что представления тормозят системы, так как они не содержат данные, то им нужно каждый раз запрашивать данные. Используется CREATE VIEW для создания и DROP VIEW для удаления.
Представления чаще всего используются для упрощения выполнения сложных операций. После того как запрос подготовлен, его можно с легкостью использовать повторно, для этого не нужно разбираться в особенностях его работы.

Хранимые процедуры

Это совокупность нескольких операторов, сохраненная для последующего использования. Использование:

• для упрощения сложных операций за счет инкапсуляции процессов в один блок, простой для выполнения
• для обеспечения непротиворечивости данных и вместе с тем без необходимости снова и снова воспроизводить одну и ту же последовательность шагов. Если все разработчики и приложения используют одни и те же хранимые процедуры, значит один и тот же код будет использоваться всеми
• побочным эффектом является предотвращение ошибок. Чем больше шагов необходимо выполнить, тем выше вероятность появления ошибок. Предотвращение ошибок обеспечивает целостность данных
• для упрощения управления изменениями. Если таблицы, имена столбцов, деловые правила изменяются, обновлять приходится только код хранимой процедуры и ничего больше. Побочный эффект - повышение безопасности. Ограничение доступа к основным данным только через хранимые процедуры снижает вероятность повреждения данных
• поскольку хранимые процедуры обычно сохраняются в компилированном виде, СУБД тратит меньше времени на обработку их команд. Это приводит к повышению производительности
• существуют элементы языка и некоторые возможности, реализуемые только в хранимых процедурах

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

Для исполнения хранимой процедуры используется оператор EXECUTE. Необходимо прописать имя хранимой процедуры и некоторые параметры, необходимые для перехода к ней. 

Транзакции
Обработка транзакций обеспечивает сохранение целостности базы данных за счет того, что пакеты операций SQL выполняются полностью или не выполняются вовсе. Основные термины

• Транзакция (Transaction). Блок операторов SQL
• Отмена (Rollback). Процесс аннулирования указанных операторов SQL (такой  процесс иногда называют "откат")
• Фиксация (Commit). Запись несохраненных операторов SQL в таблицы БД
• Точка сохранения (Savepoint). Временное состояние в ходе выполнения транзакции, в которое можно вернуться после отмены части операций пакета (в отличие от отмены всей транзакции). Иногда это состояние называется "точка отката"

Обработка транзакций используется в ходе управления действием операторов INSERT, UPDATE и DELETE. Нельзя отменить действие оператора SELECT, CREATE и DROP. Эти операторы можно использовать в блоке операторов транзакции, но если вам понадобится выполнить отмену (откат), действие этих операторов аннулировано не будет.

Чтобы сделать транзакцию управляемой, нужно разбить ее SQl-операторы на логические части и явно указать, когда может быть выполнена отмена, а когда нет.

Курсоры
Курсор представляет собой запрос к базе данных, хранящийся на сервере СУБД, - это не оператор SELECT, но результирующее множество, выборка, полученная в результате действия оператора SELECT. После того как курсор сохранен, приложения могут "прокручивать" (просматривать) данные в прямом или обратном направлении, как только возникает такая потребность.
Курсоры используются главным образом интерактивными приложениями, предоставляющими пользователям возможность прокручивать отображаемые на экране данные вперед и назад, просматривать их или изменять.
Работу с курсором можно разделить на несколько четко выраженных стадий:

• Прежде чем курсор может быть использован, его следует объявить (определить). В ходе этого процесса выборка данных не производится, просто определяется оператор SELECT, который будет использован, и некоторые опции курсора
• После объявления курсор может быть открыт для использования. В ходе этого процесса уже производится выборка данных согласно предварительно определенному оператору SELECT
• После того как курсор заполнен данными, могут быть извлечены (выбраны) отдельные необходимые строки
• После того как это сделано, курсор должен быть закрыт и, возможно, должны быть освобождены ресурсы, которые он занимал (в зависимости от СУБД)

Курсоры создаются с помощью оператора DECLARE. Он дает курсору имя и принимает оператор SELECT. Синтаксис для разных SQL разный.

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

Для Oracle и PostgreSQL:

DECLARE CURSOS CustCursor

IS

SELECT * FROM Customers

WHERE cust_email IS NULL

Другие операторы при использовании курсоров OPEN, CLOSE и FETCH.

OPEN CURSOR открывает курсор. CLOSE закрывает курсор. Оператор FETCH указывает строки, которые должны быть выбраны, откуда они должны быть выбраны и где их следует сохранить.

Далее буду использовать книгу: "SQL для простых смертных" Мартин Грабер.
Будем восполнять пробелы и дополнять предыдущую информацию.

Подразделы SQL
Язык определения данных (Data Definition language, DDL; в ANSI он называется также языком определения схемы (Schema Definition Lаnguage)) состоит из трех команд, которые создают объекты (таблицы, индексы,  представления) в БД, Язык манипулирования данными (Data Manipulation Language, DML) - это множество команд, определяющих, какие данные представлены в таблицах в любой момент времени. Язык управления данными (Data Control Language, DCL) состоит из предложений, определяющих, может ли пользователь выполнить отдельное действие. Согласно ANSI, DCL является частью DDL. Важно не путать эти названия. Речь идет не о различных языках как таковых, а о разделах команд SQL, сгруппированных в соответствии с их функциональным назначением.

Использование агрегатной функции COUNT
COUNT со звездочкой (COUNT(*)) включает как NULL-значения, так и повторяющиеся значения. Также можно использовать аргумент ALL вместо звездочки. Однако в этом случае из расчета будут исключены NUUL-значения.

Нельзя использовать вложенные агрегатные функции. В случае необходимости дополнительного расчета внутри агрегатной функции следует использовать подзапрос. 
Однако следует учесть, что существует возможность использовать в подзапросе оператор IN (операторы BETWEEN, LIKE, IS NULL в подзапросах применять нельзя). К тому же вложенные подзапросы выполняются быстрее, чем несколько запросов.

Привилегии - это те действия, которые может выполнять пользователь.
Привилегии SQL, определенные ANSI, являются объектными привилегиями (object privileges).Это означает, что пользователь имеет привилегию выполнить команду только для определенного объекта БД. Объектные привилегии связаны как с пользователями, так и с таблицами. Привилегия дается отдельному пользователю для отдельной таблицы либо для базовой таблицы, либо для представления. Пользователь, создавший таблицу, является её владельцем.
Привилегии назначаются с помощью команды GRANT.
Например, GRANT SELECT ON Customers TO Adrian;
Возможна передача группы привилегий или группе пользователей. В таком случае привилегии или пользователи перечисляются через запятую.
Для команд UPDATE и REFERENCES отдельно можно задать названия столбцов.

Можно сразу дать права какому-то пользователю на всё.
Например, GRANT ALL PROVILEGES ON Customers TO Stephen;
Или дать определенные права, но всем.
Например, GRANT SELECT ON Orders TO Public;

Но все эти варианты передают привилегии только от владельца другому пользователю. В случае, если необходимо предусмотреть дальнейшую передачу иному пользователю без участия владельца, то в конце необходимо указать WITH GRANT OPTION.
Например, GRANT SELECT ON Customers TO Adrian
                   WITH GRANT OPTION;

Для того, чтобы лишить привилегий пользователя необходимо использовать такой же синтаксис, но вместо GRANT прописывать REVOKE. Обычно отмена привилегий осуществляется каскадно.

Вставка из онлайн курс "Основы SQL" 
Будем дальше восполнять пробелы и дополнять предыдущую информацию.
Ссылка на курс: http://www.intuit.ru/studies/courses/5/5/info

Основные категории команд языка SQL:

• DDL - язык определения данных
• DML - язык манипулирования данными
• DQL - язык запросов
• DCL - язык управления данными
• команды администрирования данных
• команды управления транзакциями

Определение структур базы данных (DDL)

Язык определения данных (Data Definition Language, DDL) позволяет создавать и изменять структуру объектов БД, например, создавать и удалять таблицы. Основными командами языка DDL являются: CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX.

Манипулирование данными (DML) 

Язык манипулирования данными (Data Manipulation Language. DML) используется для манипулирования информацией внутри объектов реляционной базы данных посредством трех основных команд:  INSERT, UPDATE, DELETE.

Выборка данных (DQL)

Язык запросов DQL наиболее известен пользователям реляционной базы данных, несмотря на то, что он включает всего одну команду SELECT. Эта команда со своими многочисленными опциями и предложениями используется для формирования запросов к реляционной БД.

Язык управления данными (DCL- Data Control Language)

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

Команды администрирования данных

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

 Команды управления транзакциями

Существуют следующие команды, позволяющие управлять транзакциями БД: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION.

Методы  оптимизации запросов.
1) Переписать запрос
2) Индексы
3) Материализованное представление

Что может помочь при оптимизации:
- избегать коррелирующих и скалярных запросов
- не увлекаться заворачиванием запросов в функции
- не увлекаться сортировкой. Сортировка нужна обычно только конечным пользователям
- выбирать только нужную информацию
- использовать CTE. Табличные выражения вычисляются только раз и делают код запроса понятней
- использовать средства профилирования. EXPLAIN ANALYZE - второй друг
- индексы  повысят производительность, если запросы выполняются часто, выбирают мало записей или из большой таблицы

О материализации и избыточности
Когда использовать? Тяжелые долгие запросы к read-only данным, индексы не помогают? Материализуйте и используйте избыточную информацию. Однако, если данные постоянно меняются, запросы быстрые или редкие, то вводить материализацию или избыточность излишне.