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

СОДЕРЖАНИЕ

 

Язык 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”.

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”

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

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

 

ü Свойства

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

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

 

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

 

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

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?

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

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

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

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

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

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

 

Цель работы

Ознакомиться со способами создания и выполнением макросов.

 

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

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

 

Содержание отчета

- наименование и цель работы;

- задание на работу;

- результаты выполнения работы.

 

Выполнение работы

1. Назначение макросов кнопкам.

Используя вкладку «Разработчик» выбираем панель «элементы управления формы», кнопка «вставить», в открывшемся окне выбираем кнопку и назначаем ей макрос «СрарыйАдрес»

1.1.Щелкнув на поверхности панели инструментов правой кнопкой мыши, активизировать контекстное меню и выбрать пункт "Формы".

1.2.Выбрав на панели "Формы" элемент типа "Кнопка", разместить на поверхности рабочего листа две кнопки. С помощью автоматически открывающегося окна "Назначить макрос объекту" связать каждую кнопку с одним из двух созданных Вами макросов.

1.3.Отформатировать объекты (кнопки) таким образом, чтобы их размеры не превышали 2 ячеек в ширину и 2 ячеек в высоту. На каждой кнопке должна быть надпись, поясняющая назначение кнопки и оформленная жирным шрифтом размером 10 пунктов (рис.2.5).

1.4.Проверить правильность выполнения макросов с помощью созданных кнопок.

Рис.2.5

2. Назначение макросов графическим объектам.

2.1.С помощью вкладки «Разработчик», «Фигуры» «Звезды и ленты» создать на рабочем листе два объекта, подобные приведенным на рис.2.6.

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

8.2.3.Проверить правильность выполнения макросов с помощью созданных графических объектов.

Рис.2.6

3 Задание для самостоятельной работы

3.1.Создать таблицу, приведенную на (рис.2.7). К аждый студент создает свой список имен и значений!!!.

Рис.2.7

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

Sub Расчеты()

Range("B6").Select

ActiveCell.FormulaR1C1 = (Range("B2") + Range("B3") + Range("B4") + Range("B5")) / 4

Range("C6").Select

ActiveCell.FormulaR1C1 = (Range("C2") + Range("C3") + Range("C4") + Range("C5")) / 4

End Sub

 

4.Создать свой макрос с именем «сессия».

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

4.2. Создать свой графический объект для вызова макроса «сессия».

5.Предъявить результаты преподавателю.

 

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

 

Цель работы

Ознакомиться со способами таблиц данных.

 

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

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

 

Содержание отчета

- наименование и цель работы;

- задание на работу;

- результаты выполнения работы.

4 Таблица данных. Создание таблицы данных с одной переменной

4.1.В соответствии с таблицей, приведенной на рис. 3.1, создайте "таблицу данных", которая позволит вычислить значения функции Y=SIN(X) для X, меняющегося в диапазоне от 0 до 6,280 с шагом 0,628.

4.1.1.Пользуясь автозаполнением, задайте численные значения входного диапазона данных (ячейки B3:B13).

4.1.2.В ячейку С2, в которую будут подставляться изменяемые данные (значения Х), введите формулу =SIN(B2). Значения Х будут передаваться в формулу через ячейку В2.

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

Далее в появившемся окне выполнить четыре действия, которые показаны на рисунке 1. «Настройка». 2.«Все команды». 3. «Таблица данных». 4. «Добавить». После этого на панели быстрого доступа появится соответствующий значок.

Выделите диапазон ячеек В2:С13, вызовите диалог "Таблица данных" Воспользовавшись этой командой определим, что входные данные диапазона В3:В13 будут передаваться в формулу через ячейку В2,(подставлять значения по строкам) рис. 3.2.

Рис. 3.2.

4.2.Выполните подстановку, проверьте правильность результата, сопоставив полученные данные с приведенными на рис.3.1.

4.3. Постройте график рассчитанной функции, разместив его так, как показано на рис.3.1.

Рис. 3.1

Цель работы

Ознакомиться со способами подбора параметров.

 

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

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

 

Содержание отчета

- наименование и цель работы;

- задание на работу;

- результаты выполнения работы.

Подбор параметра.

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

4.2. Для того, чтобы воспользоваться командой "Подбором параметра", нужно щелкнуть правой кнопкой мыши рядом с последней вкладкой в высветившемся меню нажать «Настройка панели быстрого доступа»:

Далее в появившемся окне выполнить четыре действия, которые показаны на рисунке 1. «Настройка». 2.«Все команды». 3. «Подбор параметра». 4. «Добавить». После этого на панели быстрого доступа появится соответствующий значок.

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

 

• при какой общей стоимости работ прибыль достигнет величины 500 т.р.;

 

• при какой общей стоимости работ прибыль достигнет величины 1000 т.р.;
• при какой общей стоимости работ отношение прибыли к общей стоимости достигнет величины 50%.

Рис 4.1

4.3.Изменяя значения общей стоимости работ (Q) и копируя результаты на в диапазон ячеек (E1:K3), построить таблицу, отражающую зависимость величин (П/Q)% и прибыли (П) от общей стоимости работ (Q). Аналог такой таблицы приведен на рис.4.2. При копировании необходимо указывать абсолютный адрес ячеек, т.е. в ячейке F2, формула будет выглядеть следующим образом: =F3-$C8-$C10-$C9, в ячейке F2: =F2/F3.

4.4.Построить графики зависимостей (П/Q)% и прибыли (П) от общей стоимости работ (Q) аналогично графикам приведенным на рис.4.2.

 

Рис. 4.2

4.5.Создать таблицу, приведенную на рис.4.3. В этой таблице представлены гипотетические поквартальные сведения о сбыте некоторых товаров, себестоимости продукции, доходах от реализации и величине прибыли. Объем сбыта зависит от некоторого сезонного коэффициента (Кi), а также не линейно зависит от затрат на рекламу продукции при прочих фиксированных факторах. При создании таблицы исходными данными являются величины Кi, Qi, C и R. Остальные величины вычисляются по формулам, приведенным в комментариях к таблице (диапазон A14:D17). Создав таблицу, убедиться, что полученные Вами результаты расчетов по формулам совпадают с приведенными в таблице рис.4.3. Точность представления данных в таблице – два десятичных знака после запятой, для процентных величин – младший разряд целой части числа.

Рис. 4.3

4.6.Пользуясь "подбором параметра", определить:

• можно ли получить в IV квартале прибыль P₄=100000 т.р., изменяя расходы на рекламу Q₄;
• определить величину расходов на рекламу в IV квартале (Q₄), необходимую для получения прибыли P₄=150000 т.р.;

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

4.7.1.Задать величину затрат на рекламу Q₄=2000 т.р

4.7.2.Подбирая параметр Q₄, определить, при каком значении Q4 будет достигнута величина прибыли P₄=30000 т.р.

4.7.3.Задать величину затрат на рекламу Q₄=60000 т.р

4.7.4.Подбирая параметр Q4, определить, при каком значении Q4 будет достигнута величина прибыли P₄=32000 т.р.

4.8.Найти близкие к максимальным значения:

• прибыли для всех четырех кварталов (Pi);
• суммарную (годовую) прибыль (P);
• квартальные значения Qi;
• суммарные годовые расходы на рекламу (Q);
• долю расходов на рекламу в общем доходе от реализации продукции (Q/D).

Результат разместить в ячейках F11:G11. Для определения названных величин рекомендуется последовательно воспользоваться несколько раз подбором параметра.

4.9.Пользуясь расчетными формулами (A14:D18), построить на отдельном листе таблицу, отражающую зависимости величин V₄, D₄, P₄ от величины Q₄ (для значений Q₄ меняющихся от 10000 до 100000 с шагом 10000). Построить два графика (подобных представленным на рис.4.2), на которых будут отображены зависимости V₄=f(Q₄) для первого графика и D₄=f(Q₄) и P₄=f(Q₄) для второго графика рис.4.4.

Рис. 4.4

4.10.Точность подбора параметра.

4.10.1.Записать в ячейках С2, С3 и С4 (рис.4.5) исходные данные и формулу для вычисления произведения двух чисел Y=A*X. Исходные значения сомножителей: А=0,5 и Х=2,35.

4.10.2.Выполнить следующие действия:

• скопировать исходные данные и формулу (С2:С4) в диапазоны Е2:Е4, G2:G4 и I2:I4;
• для столбцов E, G и I установить разрядность отображаемых значений равную соответственно 4, 6 и 16 разрядов после десятичной запятой, как показано на рис.4.5.

4.10.3.Выполнить "подбор параметра" для нахождения первого сомножителя (А), расположенного в ячейке C2 при C3=2,35, искомом значении целевой ячейки C4=4,3758 и начальном значении C2=0,5.

4.10.4.Повторить "подбор параметра" для той же формулы, записанной в диапазонах Е2:Е4, G2:G4 и I2:I4.

Рис 4.5

4.10.5.Выполнить умножение вручную для данных в ячейках Е2:Е3, G2:G3 и I2:I3 с заданной разрядностью и записать результаты в ячейки Е6, G6 и I6 соответственно.

4.10.6.Сравнить данные, полученные в результате ручных вычислений и "подбора параметров". Оценить величину и знак погрешности вычислений.

4.11.Создать таблицу, в которой выполняется возведение числа Х в степень Y по образцу, приведенному на рис.4.6. Формула Z=X^Y, обеспечивающая вычисления, записана в ячейке D12, а исходные данные Х=2 и У=2 - в ячейках D10 и D11 соответственно. Скопируйте формулу в ячейки B12 и C12.

Рис.4.6

4.11.1.Пользуясь "подбором параметров", выполнить поиск такого значения Х (при неизменном Y=2), которое обеспечит Z=4. Результат поиска должен быть представлен в ячейках В10:В12.

4.11.2.Аналогично п.4.11.1 выполнить поиск значения Y (при неизменном Х=2), которое обеспечит Z=4. Результат поиска должен быть представлен в ячейках С10:С12.

4.11.3.Сравнить результаты непосредственного вычисления (D10:D12) и результаты двух "подборов параметров" (B10:B12 и C10:C12). Сделать выводы относительно возможности использования инструмента "подбор параметра".

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

 

Цель работы

Ознакомиться со способом поиска решения.

 

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

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

 

Содержание отчета

- наименование и цель работы;

- задание на работу;

- результаты выполнения работы.

 

Поиск решения

4.1.Определить максимальное значение годовой прибыли (P), которое может быть получено за счет изменения месячных величин расходов на рекламу (Qi), пользуясь инструментом "поиск решения".

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

Далее в появившемся окне во вкладке «Надстройка» нажать кнопку «Перейти»

После этого в появившемся окне поставить галочку около Поиска решения и нажать OK.

Во Вкладке «Данные» нажать «Поиск решения» просмотреть все варьируемые параметры поиска, задаваемые с помощью диалогового окна "поиск решения", представленного на рис.5.1.

4.1.2.Задать в качестве целевой ячейку, содержащую величину годовой прибыли (P).

4.1.3.Определить, будет ли в результате поиска достигаться определенное значение цели (прибыли) или ее экстремальное (максимальное или минимальное) значение.

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

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

а) затраты на рекламу в каждом квартале (Qi) не могут быть отрицательными;

Рис.5.1

б) цена продукции не должна быть меньше себестоимости.

4.1.6.Выполнить поиск решения. Убедиться, что решение найдено и результаты поиска совпадают с результатами, приведенными на рис.5.2.

Рис.5.2

4.1.7.Занести полученные результаты в таблицу и сохранить на отдельном листе отчет по результатам поиска решения.

4.2.Предявить результаты преподавателю.

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

 

1. Ковальски С. Excel 2003 без проблем. – М.: Бином, 2005.

2. Харис М. Программирование для Microsoft Excel 2003 за 21 день. – М.: Вильямс, 2005

3. Уокенбах Д. Профессиональное программирование на VBA в Excel 2003 – М., 2005

СОДЕРЖАНИЕ

 

Язык 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, но только этот файл позвол