double arrow

Visual Basic for Applications (VBA)

Лабораторная работа №1

 

Цель работы:

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

 

Содержание занятия:

Задание 1. Начало работы с VBA.

Задание 2. Изучение подпрограмм и процедур VBA

Задание 3 Объекты VBA

Задание 4 Циклы

Задание 5 Объявление переменных

 

В приложения, такие как Excel, интегрирован Visual Basic для приложений (VBA), язык программирования, позволяющий расширять возможности этих приложений.

VBA работает, выполняя макросы, пошаговые процедуры, написанные на языке Visual Basic. Овладение даже небольшими навыками программирования на VBA предоставляет пользователю возможность выполнять в Office действия, которые раньше представлялись им невозможными.

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

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

Но VBA предназначен не только для повторяющихся задач. VBA также можно использовать для создания новых возможностей в Excel (например, можно разработать новые алгоритмы анализа данных, а затем воспользоваться возможностями построения диаграмм в Excel для отображения результатов) и для выполнения задач, интегрирующих Excel с другими приложениями Office, такими как Microsoft Access 2010.

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

 

Порядок выполнения работы:

Задание 1 Начало работы с VBA

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

1. На вкладке Файл выберите Параметры, чтобы открыть диалоговое окно Параметры Excel.

2. Щелкните Настройка ленты в левой части диалогового окна.

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

4. В разделе Настройка ленты, который находится справа в диалоговом окне, выберите Основные вкладки, а затем установите флажок Разработчик.

5. Нажмите кнопку ОК.

Рис.1 Вкладка "Разработчик" в Excel 2010

Когда вкладка Разработчик появится в интерфейсе Excel, обратите внимание на местонахождение на вкладке кнопок Visual Basic, Макрос и Безопасность макросов.

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

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

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

Пример 1 Создание новой пустой книги:

На вкладке Разработчик нажать кнопку Макрос;

В открывшемся диалоговом окне Макрос в поле Имя макроса ввести его имя, например, Hello;

Нажмите кнопку Создать, чтобы открыть редактор Visual Basic с уже введенной структурой нового макроса (рис.2):

Рис.2 Окно редактора VBA

Sub – это сокращение от Subroutine (подпрограмма), которую в данном случае можно определить как " макрос ". Вызов макроса Hello приведет к выполнению любого кода между строчками Sub Hello() и End Sub.

Дополним макрос оператором MsgBox, чтобы он был похож на следующий код:

Sub Hello()

MsgBox ("Привет, друг!")

End Sub

Вернитесь на вкладку Разработчик в Excel, а затем снова нажмите кнопку Макрос.

Выберите макрос Hello в появляющемся списке, а затем нажмите кнопку Выполнить, чтобы вывести небольшое окно сообщения, содержащее текст "Привет, друг!".

Только что был создан и запущен пользовательский код VBA в Excel. Нажмите кнопку ОК в окне сообщения, чтобы закрыть его и закончить выполнение макроса. Если окно сообщения не появляется, проверьте настройки безопасности макросов и перезапустите Excel.

 

Задание 2 Изучение подпрограмм и процедур VBA

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

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

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

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

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

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

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

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

Sub name (arguments)

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

End Sub

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

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

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

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

Function name (arguments)

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

name = expression

End Function

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

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

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

Dim ИмяПеременной as Type

Где Type – это тип вводимой переменной:

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

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

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

Boolean – логические переменные, которые принимают одно из двух значений: Истина (True) либо Ложь (False);

Date – тип переменных для хранения даты и времени;

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

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

У объектов VBA есть свойства, которые могут принимать различные значения. Фактически свойствами являются атрибуты объекта. Например, имя объекта, количество листов (для рабочих книг) и др. Объект в VBA указывается при помощи ссылки. Иногда ссылка состоит из нескольких компонентов, например, Workbooks (“Книга2”). Worksheets (“Лист1”). Каждая ступень ограничения области отделяется точкой (.).

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

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

Многие объекты поставляются в версиях единственного и множественного числа — Workbook и Workbooks, Worksheet и Worksheets и т. д. Версии множественного числа называются коллекциями.

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

Range (“B6”)

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

При работе со всей строкой или столбцом используются Row и Column.

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

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

Range (“ B4: E6 ”). Select – данный оператор выделяет диапазон ячеек B4: E6.

For m = 1 to 3

Cells (m, 2) = m * 12

Next m

Row (4). Select - данный оператор выделяет всю четвёртую строку рабочего листа.

Пример 5 Записи макроса, переименовывающего лист:

Код в редакторе Visual Basic должен быть похож на следующий код:

Sub RenameWorksheets()

'

' RenameWorksheets Macro

'Переименовать макрос рабочий лист

'

Sheets("Лист1").Select

Sheets("Лист1").Name = "Новое Имя"

End Sub

 

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

· Упростить понимание кода, если впоследствии может понадобиться изменить код;

· Чтобы временно отключить строку кода (закомментировать) при отладке программы.

Следующая строка использует метод Select, чтобы выбрать член Sheet1 (Лист1) объекта коллекции Sheets. В коде VBA обычно не нужно выбирать объекты перед работой с ними, поэтому эта строка кода является избыточной, и её можно удалить.

Последняя строка записанного макроса изменяет свойство "Name" члена Sheet1 коллекции Sheets. Эту строку нужно сохранить.

Выполните макрос. Имя должно измениться обратно на "Новое имя".

 

Задание 3 Программирование на VBA

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

Рис.3 Справочник по объектной модели Excel на сайте MSDN

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

• Непосредственное использование справочного руководства по объектной модели;

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

Выбираемый подход зависит от личных предпочтений.

Попробуйте изучить коллекцию Sheets, используемую при записи макроса. Раздел "Листы" в справочном руководстве по объектной модели содержит следующий текст:

"Коллекция Sheets может содержать объекты Chart или Worksheet. Если нужно работать только с листами одного типа, просмотрите раздел об объекте этого типа листа". В нашем примере используется только коллекция Worksheets, поэтому можно изменить код следующим образом:

Sub RenameWorksheets()

Worksheets("Лист1").Name = "Новое Имя"

End Sub

Порядок действий по записи макроса:

1. Запишите действия, которые нужно кодировать;

2. Просмотрите код и найдите строки, выполняющие действия;

3. Удалите оставшуюся часть кода;

4. Измените записанный код;

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

 

Задание 4 Циклы

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

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

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

В VBA есть конструкция, называемая циклом For Each и идеально подходящая для этого случая. Цикл For Each проверяет все элементы в объекте коллекции, таком как Worksheets, и может использоваться для выполнения действия (например, изменения имени) над некоторыми или над всеми этими элементами.

Дополнительные сведения о цикле For Each смотрите в справочном руководстве по языку VBA. Щелкните "Visual Basic Conceptual Topics" (Концептуальные темы Visual Basic), затем "Using For Each...Next Statements" (Использование инструкций For Each...Next).

Используя третий пример в разделе "Using For Each...Next Statements" (Использование инструкций For Each...Next), измените макрос так, чтобы он стал похож на следующий код:

Sub RenameWorksheets()

For Each myWorksheet In Worksheets

myWorksheet.Name = "Новое Имя"

Next

End Sub

Здесь myWorksheet является переменной, то есть ее значение меняется. В этом случае переменная myWorksheet последовательно представляет каждый лист в коллекции Worksheets. Необязательно использовать myWorksheet, можно использовать "x", "ws", "WorksheetToRenameAfterTheContentsOfCellB1" или, с небольшими ограничениями, практически любое желательное имя. Желательно использовать имена переменных таким образом, чтобы они могли напомнить о смысле этой переменной, но не настолько длинных, т.к. это загромождать код.

Выполнение макроса в его текущем состоянии приведет к ошибке, так как Excel требует, чтобы имя каждого листа в книге было уникальным, а строка myWorksheet.Name = "Новое Имя" указывает Excel присваивать всем листам одно и то же имя.

Чтобы исправить строку так, чтобы можно было проверить работу цикла For Each, измените строку следующим образом.

myWorksheet.Name = myWorksheet.Name & "-changed"

Вместо попытки присвоить каждому листу одно и то же имя, эта строка изменяет текущее имя каждого листа (myWorksheet.Name) на текущее имя с добавлением суффикса "-changed", "-изменённый":

myWorksheet.Name = myWorksheet.Name & " -changed"

Вместо попытки присвоить каждому листу одно и то же имя, эта строка изменяет текущее имя каждого листа (myWorksheet.Name) на текущее имя с добавлением суффикса "-changed".

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

Если открыть справочное руководство по объектной модели и попытаться найти объект Cell (ячейка), окажется, что там никакого объекта Cell нет, но есть объект CellFormat.

Раздел объекта CellFormat в первом примере содержит код

' Set the interior of cell A1 to yellow.

Range("A1").Select

 

Предполагается, что объект Range (диапазон) используется, чтобы задать диапазон ячеек или только одну отдельную ячейку. Понадобится найти, как ссылаться на содержимое объекта Range, а не на сам объект Range. Если перейти к разделу Range, можно прочитать, что у объекта Range есть и Methods, и Properties. Содержимым объекта Range является сущность, не действие, поэтому это, скорее всего, будет Property. Если просмотреть список, можно найти свойство Value. Поэтому попробуем следующий код:

Sub RenameWorksheets()

For Each myWorksheet In Worksheets

myWorksheet.Name = myWorksheet.Range("B1").Value

Next

End Sub

 

Выполнение этого кода для книги, содержащей листы с пустой ячейкой B1, приведет к ошибке, так как значение свойства Value пустого диапазона Range равно "" (пустая текстовая строка), которое не является допустимым именем листа. Для примера сделайте три листа книги похожими на показанные на рис.4, а затем выполните макрос:

 

 

Рис.4. Пример данных для макроса RenameWorksheets

Имена листов изменятся соответствующим образом.

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

If myWorksheet.Range("B1").Value <> "" Then

 

А после строки myWorksheet.Name добавьте следующий текст:

End If

Такой код называется инструкцией If…Then. Инструкция If…Then указывает Excel выполнять весь код между строкой If и строкой End If, но только при выполнении условия, приведенного в строке If. В примере проверяемое условие задается следующей строкой:

myWorksheet.Range("B1").Value <> ""

Знаки <> означают "не равно", а знаки кавычек, между которыми ничего нет, обозначают пустую текстовую строку, то есть полное отсутствие текста. Следовательно, все строки кода между If и End If будут выполнены только если значение в ячейке B1 не пусто, то есть, когда ячейка B1 содержит текст.

Дополнительные сведения об инструкции If…Then см. в справочном руководстве по языку VBA. Полное название раздела — "If…Then…Else statement" (Инструкция If…Then…Else), где Else — это необязательный компонент.

 

Задание 5 Объявление переменных

Хотя объявления переменных в VBA не являются обязательным, в начало макроса целесообразно внести объявления переменной myWorksheet:

Dim myWorksheet As Worksheet

 

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

Dim является сокращением в начало макроса объявления переменной myWorksheet.от "Dimension" (размерность), а Worksheet — это тип этой конкретной переменной. Эта инструкция сообщает VBA, какой тип сущности представляет переменная myWorksheet. Обратите внимание, что после введения As, редактор Visual Basic выводит всплывающую подсказку, содержащую перечень всех доступных типов переменных. Это пример технологии IntelliSense, то есть редактор Visual Basic реагирует на то, что, как он считает, пытается сделать пользователь, и предлагает список соответствующих вариантов. Можно выбрать вариант из списка или продолжить ввод.

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

Добавление комментариев для условия If и для строки, переименовывающей листы, приводит к появлению следующего кода:

Sub RenameWorksheets()

Dim myWorksheet As Worksheet

For Each myWorksheet In Worksheets

'make sure that cell B1 is not empty

If myWorksheet.Range("B1").Value <> "" Then

'rename the worksheet to the contents of cell B1

myWorksheet.Name = myWorksheet.Range("B1").Value

End If

Next

End Sub

 

Чтобы проверить макрос, переименуйте листы обратно в Лист1, Лист2 и Лист3 и удалите содержимое ячейки B1 на одном или нескольких листах. Выполните макрос, чтобы проверить, что он переименовывает листы с текстом в ячейке B1 и оставляет нетронутыми остальные листы. Макрос работает для любого числа листов, с любой комбинацией заполненных и пустых ячеек B1.


Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:  



Сейчас читают про: