Содержание

Язык Visual Basic for Applications (VBA) 4

Практическое задание №1. Создание и выполнение макросов 15

Практическое задание №2. Использование макросов 22

Практическое задание №3. Использование инструмента "Таблица данных" для решения типовых задач. 26

Практическое задание №4. Использование инструмента "Подбор параметра" для решения типовых задач. 32

Практическое задание №5. Использование инструмента «Поиск решения». 41

Список рекомендуемой литературы: 45

Язык Visual Basic for Applications (vba)

Назначение языка VBA

Существует целый ряд систем программирования, позволяющих в той или иной степени реализовать концепцию объектно-ориентированного подхода при разработке программных средств. К ним относятся Cu ++, Java, Visual Basic. В отличие от Visual Basic (VB), Visual Basic for Applications (VBA) не является языком объектно-ориентированного программирования в строгом смысле этого слова, но в нем широко используются элементы объектно-ориентированного подхода и связанные с ним понятия.

Язык программирования VBA стал применяться сначала как средство, которое позволило Excel, а затем и другим приложениям Microsoft Office программно управлять их собственной средой. Он взаимодействовал с другими приложениями, используя механизм автоматизации OLE.

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

Язык VBA позволяет работать с огромным набором объектов – по существу, в нем определены все объекты приложений MS Office.

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

Мы будем рассматривать создание приложений на языке VBA для Microsoft Excel.

Проект VBA

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

Проект VBA включает две части:

• интерфейсную, то есть видимую при выполнении программы,

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

Весь проект представляет собой один файл–рабочую книгу и сохраняется вместе с ней.

Проект VBA состоит из объектов, имеющих иерархическую структуру и включает:

• объекты Excel,

• формы,

• стандартные модули,

• модули класса.

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

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

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

Структура программы на VBA

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

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

Объекты Excel

Рис. 1 Взаимосвязь объектов Excel

Объект Приложение (Application)

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

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

Здесь перечислены только важнейшие свойства и методы. В действительности и у объекта Application 120 свойств и 40 методов. Полный их список – в справочной системе VBA.

• Свойства:

Caption отображается в строке заголовка окна Excel. Аргументы: String Доступно для чтения/записи.

Пример: Application.Caption= “Мое приложение”

DisplayAlerts– управляет выводом на экран встроенных предупреждений Excel во время выполнения программы. Аргументы: True или False; доступно для чтения/записи.

Пример: Application.DisplayAlerts = False

Path– путь к папке, в которой установлена программа Excel. Аргументы: String; Доступно только для чтения.

Пример: ExcelPath = Application. Path

ScreenUpdating– управляет обновлением экрана при выполнении программы. По умолчанию имеет значение True (экран обновляется). Измененное значение действует только во время выполнения программы, в которой это свойство было изменено. Аргументы: True или False; доступно для чтения/записи.

Пример: Application.Screen Updating = False

WindowState– состояние окна приложения. Может принимать одно из значений:

xlNormal Окно обычного размера

xlMaximized Окно развернуто

xlMinimized Окно свернуто

Доступно для чтения/записи.

Пример: Application. Window State = xlNormal

DisplayStatusBar– управляет отображением и сокрытием строки состояния. Аргументы: True или False; доступно для чтения/записи.

Пример: Application.Display Status Bar = False

DisplayFormulaBar–управляет отображением и сокрытием строки формулы. Аргументы: True или False; доступно для чтения/записи.

Пример: Application.Display Formula Bar = False

• Методы

Calculate– вызывает пересчет всех формул на всех листах всех рабочих книг. Аргументов нет.

Пример: Application.Calculate

Help– отображает раздел справочной системы из выбранного файла. Аргументы:

helpFile Строка с именем файла, при необходимости указывается и полный путь к нему.

helpContextld Целое число, представляющее номер раздела.

Пример: Application.Help help File: = “MAINXL. HLP”, help Contextld: = 100

Quit– закрывает приложение Excel. Помните, что, если в момент вызова метода Quit значение свойства DisplayAlerts равно False, Excel не выводит на экран запрос на сохранение открытых рабочих книг. Аргументов нет.

Пример: Application.Quit

Run– выполняет подпрограмму VBA или макрос на макроязыке Excel. Первый аргумент – строка с именем подпрограммы или макроса, который нужно выполнить. Количество и тип дополнительных аргументов определяются структурой подпрограммы и макроса.

Пример: Application.Run macro = “Old Macro”, arg1: = 100, arg2: = “Revenue”.

Объект Рабочая книга (Workbook)

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

Рабочие книги существуют в двух формах – в виде файлаXLS (стандартная книга Excel) и файла XLA (надстройка Excel), содержащего полностью откомпилированное приложениеVBA. Код VBA можно компилировать и без создания XLA, но только этот файл позволит Вам скрыть код от пользователей. В следующих двух разделах описаны наиболее часто применяемые свойства и методы объекта Workbook. Более полную информацию Вы найдете в справочной системе VBA.

• Свойства

HasRoutingSlip–имеет значениеTrue, если для отправки рабочей книги создан маршрут, по которому ее посылают поMAPI– совместимой электронной почте нескольким пользователям.TrueилиFalse; доступно для чтения/записи.

Пример: Workbooks("КНИГА1.XLS"). Has Routing Slip = True

Name– имя рабочей книги. Обратите внимание, что это свойство нельзя использовать для изменения имени. Для сохранения книги с новым именем применяется метод SaveAs. Аргументы: String; доступно только для чтения.

Пример: WorkbookName=This Workbook.Name

Path– путь к файлу рабочей книги. Аргументы: String; доступно только для чтения.

Пример: WorkbookPath = Active Workbook. Path

Saved–имеет значение True, если с момента последнего сохранения в рабочую книгу не вносились изменения, и False– в противном случае. Аргументы: True или False; доступно для чтения / записи.

Пример:

IfNot(Active Workbook. Saved) Then

Active Workbook. Save

End If

• Методы

Activate– активизирует рабочую книгу и открывает первое связанное с ней. Аргументов нет.

Пример: Workbooks(“MYAPP.XLS”).Activate

Close– закрывает рабочую книгу. Аргументы:

saveChanges Имеет значение True, если книгу перед закрытием нужно сохранить, и False – в противном случае.

file Name Если предыдущий аргумент имеет значение True, представляет имя файла для сохранения.

routeWorkbook - Имеет значение True, если перед закрытием книг нужно разослать по маршруту(при условии, что он создан).

Пример: ActiveWorkbook. Close save Changes: = False

Protect–защищает книгу от редактирования. Аргументы:

Password Пароль для доступа к рабочей книге.

Structure Имеет значение True, если нужно защитить структуру рабочей книги.

Windows Имеет значение True, если нужно защитить структуру окна рабочей книге.

Пример: Workbook(1). Protect “password”, True, True

Save– сохраняет рабочую книгу. Аргументов нет.

Пример: Active Workbook. Save

SaveCopyAs– сохраняет рабочую книгу в новом файле, оставляя исходную книгу в памяти с прежним именем. Аргументы:

filename Строка с именем файла для сохранения копии книги.

Пример: ActiveWorkbook. Save Copy As “КОПИЯ КНИГИ.XLS”

Объект Рабочий лист (Worksheet)

ОбъектWorksheet содержится в объекте Workbook и выполняет в приложении несколько функций, самая важная из которых, вероятно, заключается в том, что служит основой для разработки большинства форм. Этот объект представляет рабочий лист Excel– мощное средство для в вода и изменения данных. Рабочие листы содержат около 400 встроенных функций, позволяющих в мгновение ока производить сложные вычисления, и могут служить миниатюрными базами для них.

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

Ниже обсуждаются самые популярные свойства и методы объекта Worksheet. Более подробную информацию Вы найдете в справочной системеVBA.

• Свойства

Index– номер рабочего листа в семействе. Аргументы: True или False; доступно только для чтения.

Пример: ActiveSheet. Name = “Wk Sheet”&Active Sheet. Index

Name– имя рабочего листа.String; доступно для чтения/записи.

Пример: Worksheets(1).Name= “Мой рабочий лист”

UsedRange– диапазон рабочего листа сданными. Аргументы: Объект Range; доступно только для чтения.

Пример: Dim Диапазон1 As Range

Set Диапазон 1 = Worksheets (1). UsedRange

Visible– имеет значение True, если рабочий лист отображен на экране. В противном случае лист скрыт, но пользователь может вывести его на экран, выбрав из меню Формат(Format) команду Лист(Sheet). Если значение этого свойства равно xlVeryHidden, лист скрыт, и вывести его на экран можно только из программы VBA. Аргументы: True,False или xlVeryHidden; доступно для чтения/записи.

Пример: Worksheets(“Main”). Visible = xlVery Hidden

• Методы

Activate– активизирует рабочий лист. Аргументов нет.

Пример: Worksheets(“Мой рабочий лист”).Activate

Calculate–пересчитывает все формулы на рабочем листе. Аргументов нет.

Пример: Worksheets(1).Calculate

Delete– удаляет рабочий лист из книги. Аргументов нет.

Пример: Worksheets(“Мой рабочий лист”).Delete

Protect– защищает лист от редактирования. Аргументы:

Password Пароль для доступа к рабочему листу.

drawingObjects Имеет значениеTrue, если нужно защитить все рисованные объекты на листе.

Contents значениеTrue, если нужно защитить ячейки рабочего листа.

Scenarios значениеTrue, если нужно защитить сценарии рабочего листа.

userInterfaceOnly Имеет значение True, если нужно запретить любые изменения листа средствами пользовательского интерфейса. Это означает, что лист можно корректировать лишь средствами программы. Если значение этого свойства равно False, запрещены любые изменения листа.

Пример: Worksheets(“Мой рабочий лист”).Protect“password”.

Объект Диапазон (Range)

Объект Range входит в состав объекта Worksheet и представляет одну или несколько ячеек рабочего листа. Его главное назначение – хранить и отображать отдельные фрагменты данных: числа, строки или формулы. Но ячейки, представляемые объектом Range, - не просто ящики для хранения информации. Из них Вы вправе вызывать встроенные функции Excel и функцииVBA. Ячейки можно связывать с другими ячейками на этом же листе, на другом листе и даже в другой рабочей книге. Понимание гибкости и силы объекта Rangeпозволит Вам полнее использовать вычислительную мощь Excel и создавать качественные приложения для анализа данных. Мы упомянем здесь лишь наиболее часто используемые свойства и методы объекта Range. За дополнительной информацией обращайтесь к справочной системе VBA.

• Свойства

Count– число ячеек в диапазоне.Integer; доступно только для чтения.

Пример: Num Of Cells = Worksheet (1).UsedRange.Count

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

Пример: Dim Диапазон1AsRange

Set Диапазон1 = Worksheets(1). Range (“A1”). Dependents

MsgBoxДиапазон1.Address

Name–имя диапазона. Аргументы: String; доступно для чтения/записи.

Пример: Worksheets (1). Range (“A1”).Name= “Первая _ ячейка”

Value– значение, содержащееся в диапазоне. Если он состоит из нескольких ячеек, свойство Value содержит массив значений. Аргументы: Boolean, Byte, Currency, Date, Integer, Long, Single или String; доступно для чтения/записи.

Пример: Worksheets (1). Range (“Первая_ячейка”).Value = 1

Formula– строка с формулой, содержащейся в диапазоне, включая знак свойства. Если диапазон состоит из нескольких ячеек, свойствоFormulaсодержит массив формул. Аргументы: String; доступно для чтения/записи.

Пример: Worksheets (1). Range (“Первая_ячейка”). Formula = $A$4 +$A$10

Text– значение ячейки, представленное в виде текста. Например, свойства ячейки в денежном формате содержит строку “$5.00”, а свойство Value – чтение. Если диапазон состоит из нескольких ячеек, свойство Text содержит массив. Аргументы: String; доступно только для чтения.

Пример: Worksheets (1). Range (“Первая_ячейка”). Value =1

MsgBox Worksheets (1).Range (“Первая _ ячейка”).Text

• Методы

Calсulate–пересчитывает все формулы диапазона. Аргументов нет.

Пример: Worksheets (1).Range (“A1: F20”). Calсulate

ClearContents– удаляет из диапазона все значения и формулы, но оставляет форматирование. Аргументов в нет.

Пример: Worksheets (1).Range (“A1: F20”). ClearContents

Copy– копирует содержимое диапазона в другой диапазон или в буфер обмена. Аргумент:

Destination Диапазон для копирования содержимого исходного диапазона, этот аргумент отсутствует, содержимое копируется в буфер обмена.

Пример: Worksheets (1). Range (“A1”).Copy

Контрольные вопросы:

Назначение языка VBA?

Из каких частей состоит проект VBA?

Что размещается в модулях класса и в модулях рабочих листов?

Что собой представляет объект «Приложение» (методы, свойства)?

Что собой представляет объект «Рабочая книга» (методы, свойства)?

Что собой представляет объект «Рабочий лист» (методы, свойства)?

Что собой представляет объект «Диапазон» (методы, свойства)?