Создание макросов, форматирующих ячейки

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

Например: при выделении текста курсивом, если данный текст к моменту включения макроса уже был выделен, то при нажатии кнопки «Курсив» панели инструментов Форматирование данное выделение снимется. В результате макрос сработал правильно, но не корректно была осуществлена его исходная запись.

Настройка меню и панели инструментов

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

Рисунок 5. Панель быстрого доступа.

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

1. Откройте документ, содержащий локальный макрос.

2. Нажмите правой кнопкой мыши на любой вкладке ленты инструментов и выберите в контекстном меню команду Настройка панели быстрого доступа.

Рисунок 6. Окно параметров Excel.

3. В открывшемся диалоговом окне «Параметры Excel»на вкладкеНастройкапосле строки Выбрать команды извыберите из выпадающего списка Макросы.

4. В поле, под выпадающим меню, появятся все макросы текущего документа. Выберите нужный вам макрос.

5. Нажмите на кнопку Добавить.После выполнения действия ваш макрос перенесется в соседнее поле, находящееся под строкой Настройка панели быстрого доступаи выпадающим списком. В выпадающем списке можно выбрать, для каких документов будет создана кнопка вашего макроса в панели быстрого доступа: Для всех документов (по умолчанию) или же только для вашего текущего документа.

6. Для сохранения вашей настрой нажмите на кнопку OK.
По умолчанию рисунок на кнопкевыглядит . Для изменения рисунка кнопки необходимо во вкладке Настройкаокна «Параметры Excel»выбрать добавленный вами макрос вправом поле и нажать на кнопкуИзменить. В результате откроется окно «Изменение кнопки», в котором будет отображена палитра символов для вашей кнопки, а так же поле, в котором можно изменить Отображаемое имя кнопки. После внесения изменений нажмите OK.

Рисунок 7. Окно изменения кнопки.

7. Закройте диалоговое окно «Параметры Excel».

Для удаления кнопки запуска макроса с панели быстрого доступа откроите диалоговое окно «Параметры Excel», вкладку Настройка.Выберите созданную вами кнопку для макроса и нажмите на кнопку Удалить.Закройте диалоговое окно «Параметры Excel».

Удаление макросов

Удалить макрос можно несколькими способами:

1) Нажмите на кнопку Макросыво вкладкеРазработчик. В открывшемся окне «Макрос» выберите из списка нужный макрос. Нажмите на кнопку Удалить.В открывшемся окне «MicrosoftOfficeExcel» вас спросят, действительно ли вы хотите Удалить макрос.Нажмите на кнопкуДа;

2) Переписать макрос заново под тем же именем, если не устраивает работа первоначального макроса.

Процедуры

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

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

Внимание!Макросу при запуске не могут быть переданы никакиепараметры. Однако во время выполнения он может находить нужные ему значения в подпрограммах или функциях.

Любая подпрограмма имеет следующий синтаксис:

Sub name (arguments)

списокоператоров VBA

EndSub

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

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

Синтаксис функции:

Functionname (arguments)

списокоператоров VBA

name = expression

EndFunction

Где name - имя функции, a arguments - это список переменных, представляющих значения, которые переданы этой функции. Строка name = expression говорит о том, что имени, определённому в первой строке функции, присваивается значение выражения expression. Оно и становится возвращаемым значением функции.

Переменные в VBA

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

Существует несколько видов переменных. Это:

¾ Локальные переменные, которые объявляются в одной отдельной процедуре;

¾ Переменные модульного уровня- это переменные, которые может использовать любая процедура отдельного модуля;

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

Создать переменную можно двумя способами:

1) Простым упоминанием о переменной. (Т.е. переменная создаётся на «ходу» и она объявляется в процессе использования);

2) В начале процедуры объявить необходимые переменные.

Для объявления переменныхнеобходимо написать оператор Dim. имя переменной и тип переменных. Если заранее неизвестен тип переменных, то его можно не писать. Полностью объявление переменной выглядит так:

Dim ИмяПеременной asТуре

Где Туре - это тип вводимой переменной. Основные типы переменных перечислены ниже:

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

Integer - используется для хранения целых чисел. Например:

-375,375

String - объявляет строковые переменные. Например: МоёИмя.

Boolean - логические переменные, которые принимают одно из двух значений:

Истина (Тrue) либо Ложь (False).

Date - тип переменных для хранения даты и времени.

Object - объявляет объектную переменную.

Объекты в VBA

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

У объектов VBA есть свойства, которые могут принимать различные значения. Фактически свойствами являются атрибуты объекта. Например: имя объекта, количество листов (для рабочих книг).

Для того чтобы написать законченную строку кода VBA, включающую объект, необходимо указать объект и что необходимо с ним сделать. Объект в VBA указывается при помощи ссылки. Иногда ссылка состоит из нескольких компонентов. Причём каждый компонент ограничивает область возможных объектов, пока не укажет точно конкретный объект. Каждая ступень ограничения области отделяется точкой (.). Например:

Workbooks (“Книга 2”). Worksheets (“Лист 1”)

Данная строка ссылается на объект с именем Лист 1 в рабочей книге ''Книга 2”. Ссылка на ''Книга 2” дана для того, чтобы отличить данный объект от других листов с таким же именем в различных книгах. В этом примере оператор VBAне закончен, так как не указано, что необходимо с ним сделать. Вот пример законченного оператора VBA:

Workbooks(“Книга 2”) . Worksheets(“Лист 1”) . Delete

Данная строка указывает VBA удалить заданный лист из рабочей книги.

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

Range (“В6”)

Ссылка на ячейку В6 любого рабочего листа любой открытой рабочей книги.

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

Range - если необходимо сослаться на какую-либо конкретную ячейку или диапазон ячеек. Например:

Range(“В4”")

Cells - если нужно работать с ячейками, меняющимися в процессе работы программы (каждый раз они определяются заново). Причём в скобках данного метода указывается сначала номер строки, а затем номер столбца. Например: Cells (7,2)- ссылка на ячейку В7.

Методы Row и Column нужны при работе со всей строкой или столбцом.

ActiveCell - свойство, которое возвращает единственную активную ячейку в рабочей книге.

Например:(Эти примеры являются законченными операторами VBA.)

1) Range(“В4 : Е6”). Select

Данный оператор выделяет диапазон ячеек В4 : Е6.

2) For m = 1 to 3

Cells (m,2 ) = m* 12

Next m

В данном примере переменная m изменяется от 1 до 3. Каждой ячейке В1, В2 иВЗ присваивается вычисленное значение.

3) Row (4). Select

Данный оператор выделяет всю четвёртую строку рабочего листа.

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

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

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

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

Оператор условного перехода IFимеет следующий синтаксис:

If условие Then 1й_оператор Else 2й_оператор

Условный оператор If можно использовать в трёх видах:

1) IfусловиеThenоператор

Пример:

Sub example1()

If 100 = 100 Then MsgBox True

EndSub

В данном примере приведена краткая форма записи, что означает: если (if) 100 = 100(условие) тогда(then) Msgbox True(оператор).

2) If условие Then 1й_оператор Else 2й_оператор EndIf

Пример:

Subexample2()

If100<10Then

MsgBoxTrue

Else

MsgBoxFalse

End If

End Sub

В данном примере приведена полная форма записи с двумя операторами, что означает: если(if) 100 < 10(условие) тогда(then) Msgbox True(1й_оператор) иначе(else) Msgbox False(2й_оператор) конец(endif).

3) If 1е_условие Then 1й_оператор ElseIf 2е_условие Then 2й_оператор EndIf.Пример:

Subexample3()

If 100 = 120 Then

MsgBox 120

ElseIf 100 = 100 Then

MsgBox 100

EndIf

В данном примере приведена самая гибкая форма условного оператора If (структура с двумя операторами и двумя условиями), что означает: если(if) 100 = 120(1е_условие) тогда(then) Msgbox 120(1й_оператор) иначе если(ElseIf) 100 = 100(2е_условие) тогда(then) Msgbox 100(2й_оператор).

Оператор выбора SelectCase – альтернативаоператоруIF.Санглийского Select Case можно перевести как «Выбор Ситуации».И если IF оператор в каждом своём ElseIf вынужден обращаться к проверяемым значениям снова и снова (допустим, выражение каждый раз одинаковое), то Select Case делает это только один раз, что позволяет последнему на больших массивах данных работать быстрее. Этот оператор позволяет удобно задать ветвление программы из одной точки в большое количество веток. То есть в основном применяется при множественных условиях проверки, когда проверяемых условий больше двух.

Синтаксис оператора Select Case выглядит так:

SelectCaseпроверяемое выражение

Caseконкретное значение

Некоторое действие

CaseElse

Некоторое действие X

EndSelect

В качестве кускаЗначение можно вставить любую переменную или свойство, значение которой или которого Вы можете проверить. Можно также проверять значение конкретной ячейки. При этом работать можно не только с числами, но и с текстами. И даже с булевыми значениями TRUE/FALSE («Правда» и «Ложь»), о чем знают не все.

КонкретноеЗначение- это то, с чем сравниваетсяПроверяемоеЗначение. И, если одно удовлетворяет другому, то выполняется НекотороеДействие. Есть несколько вариантов записи для блока КонкретноеЗначение. Для текстовых и числовых значений можно записывать разные значения через запятую.Например:

Case 3, 4, 5, "да", "нет"

Для чисел можно выбирать диапазоны, например:

Case 3 to 10 'От 3-х до 10-ти, включая сами 3 и 10

Также для чисел можно использовать логический оператор сравнения вместе с частицей "Is":

Case Is < 2 'Меньше 2, НЕ включая 2

Case Is = 3 'Равно 3-м. Избыточная запись, достаточно Case 3

Case Is >= 4 'Больше либо равно 4

Case Is <> 0 'Не равно нулю

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

Case ... And ...

Case Not ...

НекотороеДействие может быть абсолютно любым. Если вы его пропускаете - то для данного случая программа будет бездействовать.Case КонкретноеЗначение вместе с частью НекотороеДействие складываются в один блок:

Case Конкретное Значение

Некоторое Действие

Таких блоков может быть любое количество, которое уложится в предельные размеры процедуры (она должна весить не более 64 килобайт). Полезно знать, что VBA просматривает соответствиеКонкретного Значения иПроверяемогоЗначения вдоль по блокам сверху вниз. То есть, у Вас может быть два блока с одинаковым Case, но выполнится только тот, который будет раньше найден программой при просмотре кода сверху вниз.

Case Else - это все другие случаи, которые не подошли ни под одно другоеКонкретное Значениево всех блоках оператора Select Case. Если блок Case Else отсутствует и ни один другой блок не подошёл, то программа делает логичное "ничего". Case Else должен быть последним проверяемым случаем среди всех блоков проверки в операторе. После него других блоков быть не должно, иначе получим синтаксическую ошибку Case without Select Case.

В конце оператора должен стоять End Select, который служит "точкой" в "предложении" оператора.

ПримериспользованияSelect Case:

Sub SelectCase_example_1()

Dim X As Long

X = 1 'Можете изменять эту цифру и смотреть, что получится.

Select Case X

Case 1

MsgBox "Один"

Case 2

MsgBox "Два"

Case 3

MsgBox "Три"

Case Else

MsgBox "Выбрано что-то другое"

End Select

End Sub

Подводя итог, можно сказать, что оператор Select Case по структуре довольно прост и удобен в использовании. Он менее гибок по сравнению с If … End, если по ходу проверок требуется менять проверяемое значение, но значительно выигрывает при разнообразных проверках одного и того же выражения. Для чего собственно и был создан.

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

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

1) Цикл выполняется, пока соблюдается некоторое условие.

DoWhileусловие

список операторов VBA число = Sqr(число ) -1

Loop

DoWhileчисло> = 0,01

число = Sqr (число) - 1

повторения = повторения + 1

Loop

Условие в данном цикле проверяется до начала выполнения повторяющихся операторов. Если условие не выполняется, то операторы, заключённыемежду Do и Loop, не выполняются, а управление переходит к следующему оператору за Loop.

2) Цикл выполняется до тех пор, пока условное выражение не станет равным значению Истина (True).

DoUntilусловие

список операторов VBA

Loop

DoUntilчисло<0,01

число = Sqr (число) – 1

повторения = повторения + 1

Loop

3) Циклы третьего и четвёртого типов отличаются от предыдущих только тем, что условие проверяется уже после выполнения тела цикла. Таким образом, эти циклы выполняются хотя бы один раз.

Do

список операторов VBA

Loop Whileусловие

Do

число = Sqr (число) – 1

повторения = повторения +1

LoopWhile число >= 0,01

Do

список операторов VBA

LoopUntilусловие

Do

число= Sqr (число) – 1

повторения = повторения +1

LoopUntilчисло < 0,01

Циклы со счётчиком. В циклах со счётчиком используется специальная переменная - счётчик, значение которой при каждом повторении тела цикла увеличивается или уменьшается на заданную величину (шаг цикла). Цикл завершается после того, как значение счётчика достигнет конечного значения счётчика цикла (или превысит его).

Синтаксис цикла со счётчиком:

Forсчётчик = начальное_значение То конечное_значение Step шаг_цикпа список операторов VBANextсчётчик

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

Например:

Forb= 1 То 10 Step 2

S = S + Cells ( b, 1 )

Next b

Циклы по структуре данных. В циклах по структуре данных тело цикла поочерёдно выполняется для всех однородных объектов, составляющих массив или семейство. В этом случае в роли счётчика выступает объектная переменная. Синтаксис данного цикла:

ForEach элемент In структура_данных

тело цикла (список операторов VBA)

Next элемент

Например:Впримеревыводятся напечатьимена всех рабочихлистоввтекущей рабочей книге Excel и приведён синтаксис цикла по структуре данных.

Sub Цикп_по_структуре_данных ()

Dim S As Worksheet

ForEash S In Application. Worksheets

Debug.Print S.Name

Next S

EndSub