Введение в систему программирования VBA. Объектная модель Excel, основные объекты Е. Краткая их характеристика. — КиберПедия 

Общие условия выбора системы дренажа: Система дренажа выбирается в зависимости от характера защищаемого...

Кормораздатчик мобильный электрифицированный: схема и процесс работы устройства...

Введение в систему программирования VBA. Объектная модель Excel, основные объекты Е. Краткая их характеристика.



Sub MainProcedure()

Call GetProblemSize

Call Initialize

Exit Sub

End Sub

Sub GetProblemSize()

Ncities = Range("DistMatrix").Rows.Count

ReDim Visited(Ncities)

ReDim Route(Ncities + 1)

End Sub

Sub Initialize()

Dim I As Integer

Route(1) = 1

Route(Ncities + 1) = 1

Visited(1) = True

For I = 2 To Ncities

Visited(I) = False

Next

TotDist = 0

End Sub

Синтаксис процедуры пользователя:

[Private/Public] Sub ИмяПроцедуры (Параметры)

<Тело процедуры>

End Sub

Элементы списка параметров имеют следующий вид: ИмяЭлемента As ТипДанных

 

7. Типы подпрограмм и их определения: определение и виды процедур. Примеры процедур разных видов. Логические фрагменты кода, которые выполняют определенную задачу, называются подпрограммами. В вба подпрограммами называют макросы, процедуры и даже функции. Функция – это подпрограмма, которая действует в пределах своего блока и возвращает только одно значение. Функции: 1)ф. пользователя 2) ф. модулей классов. Функция имеет следующий синтаксис:

[Private/Public] Function Имя функции(список параметров) As ТипДанных

<тело функции>

End Function

Тип данных для функции предназначен для определения типа и возвращения функцией зн-я

Чтобы создать функцию пользователя, надо: 1. если нет модуля Insert/Module 2. Insert/Procedure 3. в открывшемся окне выбрать переключатель “Function”, в поле Name ввести имя функции («Доход») 4. в этом же окне установить переключатель «общие», чтобы был тип – Public 5. Ок. Откроется окно редактора. Ввести параметры функции и код.

Option explicit

Option Base 1

Function Doxod (procent as double, platezh as variant, god as variant) as double

Dim i, j, n as integer, s as double

n=platezh.rows.count

s=0

for i=1 to n

s=s+platezh(i)/(1+procent)^((god(i)-god(1))/365)

next i

doxod=s

end fuction

Не закрывая окна View/Object Browser. Откроется окно просмотра объектов. В левом верхнем углу выбрать VBAProject и в окне Классы отобрать элементы текущего проекта. Выбрать модуль, в котором ваша функция. Затем в окне Компоненты отобрать все элементы, в том числе и функцию Доход. Щелкнуть правой кнопкой мыши на Доход и выбрать значение Свойства. Откроется окно «Параметры компонента», в нем ввести описание(назначение) функции. Закрыть окно и после этого ваша функция попадет в библиотеку стандартных функций Excel.

 

8. Объявление переменных. Объявление переменных в модулях и процедурах. Область действия переменных и процедур. Пример передачи в процедуру аргументов.

Рекомендуется заранее объявлять все переменные, которые будут использоваться в процедурах. Оператор Dim позволяет объявить все переменные и массивы.

Dim I As Integer, j As Integer, k As Integer

Чтобы не забывать объявить переменные Tools/Options/ вкл Editor, где установить флажок Require Variable Declaration.



Option Explicit – общая область. Этот оператор будет контролировать описание переменных. Существует очень важный тип переменных, которого в VB не было. Это переменные объекта. Dim A As Object. Частный случай: Dim A As Range. Например, мы хотим обратиться к диапазону ячеек в течение процедуры. D

Dim SRange As Range

Set SRange=ActiveWorkBook.WorkSheets(“Продажи”).

Range(“SalesRange”)

SRange.Font.Size=14

Ключевое слово Set используется только при присвоении значения объектной переменной.

Для переменных определяется область видимости. Переменные могут быть: глобальные (для объявления используется Public) и локальные (используется оператор Private, Dim). Переменная типа Public – переменная уровня модуля, Dim – уровня процедуры. Переменная, определяемая в модуле оператором Dim, может быть переопределена этим же оператором внутри процедуры, принадлежащей этому модулю.

Передача в процедуру аргументов. Можно передавать аргументы (имя, фамилию) из процедуры Main в процедуру DisplayName. Тогда процедура Main называется главной, а DisplayName – вызываемой. В этом случае переменная FirstName и LastName не объявляются переменными уровня модуля, а объявляются как локальные переменные процедуры Main.

Sub Main ()

Dim FirstName As String, LastName As String, I As Integer

For i=1 to 50

FirstName=Range(“Names”).cells(i, 1)

LastName= Range(“Names”).cells(i, 2)

Call DisplayName

End Sub

Sub DisplayName

Dim FulllName As String

FulllName= FirstName+ LastName

MsgBox “Полное имя сотрудника”_&_ FulllName

End Sub

9. Встроенное диалоговое окно сообщения. Пример функциии оператора MsgBox.Существует несколько типов диалоговых окон, которые необходимы для поддержания в программе интерактивного режима работы конечного пользователя (вывод сообщений пользователю, прием и интерпритация указаний, введенных пользователем и т.д.). Окно сообщений обозначается MessageBox (MsgBox) и окно ввода InputBox. Их можно рассматривать как фукнции и как оператор. Окно MsgBox- окно сообщения. Оно не требует проектирования и вызывается из программы командой MsgBoх и создается с помощью функции MsgBox(). Она имеет следующий синтаксис: MsgBox (Promt [, buttons] [,title,helpFile], [context]), где promt - обязательный параметр, эта строка в окне сообщения. Ее максимальная длина 1024 символа. Параметр Buttons не обязателен, его значение - целое число, равное сумме значений, определяющих наличие кнопок в окне сообщения. По умолчанию равно 0. Параметр title- заголовок окна сообщений. Help file – необязательный, ссылка на файл в справочной системе и на конкретное место в этом файле. пример



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

MsgBox "в регионе " & i & " объем продаж превышал " & s & " в " & ks & " месяцах"

 

10. Встроенное диалоговое окно ввода. Пример функцииInputBox. Существует несколько типов диалоговых окон, которые необходимы для поддержания в программе интерактивного режима работы конечного пользователя (вывод сообщений пользователю, прием и интерпритация указаний, введенных пользователем и т.д.). Окно ввода информации InputBox требуется только как функция. Часто необходимо осуществить не только набор действия, но и ввести определенную информацию, которая будет восприниматься программой. Для этого используется функция InputBox. Синтаксис: InputBox (ptomt, [title], [default], [xpos,ypos], [helpfile], context])

Promt – обязательная строка, которая выдается в окне сообщения, 1024 символа Title – необязательный, заголовок окна сообщения Default - строка в текстовом поле, если его нет, то строка пуста Xpos,Ypos- позиция левого верхнего угла ввода на экран Helpfile- ссылка на файл справочной информации. В этом окне всегда стоят по умолчанию две кнопки: ОК и Саncel. Пример:

InputBox("Введите цену для сравнения", "окно для ввода критерия")

Окно для ввода критерия
Введите цену для сравнения

13. Основные свойства и методы объекта Range. Примерыпрограммных кодов.Объект Range одновременно является и объектом и коллекцией. Рассмотрим наиболее важные свойства и методы объекта Range: 1. Свойство Address – возвращает адрес диапазона в виде строки. «В2:Р4» А=Range(“Sales”).Address “В2:Р4”

2. Свойство Cells – возвращает ссылку на объект Range и обычно используется к определенной ячейке диапазона. Указывает на конкретную ячейку внутри диапазона.

Range (“A1:A10”).Cells(3) ‘A3

Range (“A1:D10”).Cells(3, 4) ‘D3

3. Свойство Offset – возвращает ссылку относительно диапазона (в этом случае представляется единственной ячейкой).

Range (“A1”). Offset(3, 4) – E4

4. Font – выступает как в роли объекта, так и в роли свойства. В данный момент выступает как свойство, а как объект имеет свойства: Size, Name, Bold, Italic.

5. Horizontal Alignment – горизонтальное выравнивание ячеек в диапазоне. Свойства: xlCenter – по центру; xlLigh – по правому краю; xlLeft – по левому краю.

6. Interior – возвращает ссылку на заливку в ячейке. Range (“A1”).ColorInterior=3

7. Name – возвращает имя диапазона. Это свойство позволяет задать имя диапазона в программном коде.

Range (“A1:D10”).Name=”Sales”

8. Value – возвращает значение в ячейку диапазона(используется относительно только одной ячейки диапазона).

Range (“A5”). Value=«Отчет о командировке»

Методы диапазона: Clear – удаляет содержимое и форматирование диапазона ячеек. ClearContents – удаляет только содержимое. Copy – копирует содержимое одного диапазона в другой. В этом случае он используется с единственным аргументом CopyDestination. C его помощью указывается, куда вы копируете.

Range(“B4:G25”). CopyDestination:=Range(“E4:F25”). Копируются формулы и содержимое. В случае, когда надо скопировать только числовые значения, а не формулы, используется метод PastSpecial. Сначала вы копируете в буфер обмена, затем переносите в диапазон. 1)Range(“B4:G25”).Copy 2)Range(“E4:F25”).PastSpecial Paste:=xlPasteValue

Select – выделение диапазона.

Sort – Применяется для сортировки диапазона ячеек. Key1 – по какому столбцу будет сортировка в диапазоне. Order1 – порядок сортировки(по возрастанию, по убыванию). Header – «да», «нет» (Если «да», то заголовок в сортировке участия не принимает, если «нет», участвует). Range(“A1:F25”).Sort

Key1:=Range(“C2”)

Order1:=xlAscending

Header:=xlYes

 

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

14. Способы указания диапазонов в вба. Примеры программных кодов с указанием диапазонов.В процессе составления программного кода нужно уметь правильно использовать ссылку на диапазон. Рассмотрим следующие способы: 1. Использование адреса Range(“A1:B4”). 2. Применение имени диапазона Range(“Sales”). 3. Назначение переменной с именем диапазона NSales=Range(“Информация о продажах”).Name. 4.Использование свойства Cells Range(“A1:A10”).Cells(3) – A3

Range(“A1:D10”).Cells(3, 4) – D3

5.Настройка свойства Offset. Свойство имеет 2 аргумента. Когда указывается Offset для диапазона, то в роли диапазона выступает адрес только одной ячейки. Range(“A5”).Offset(3, 4) – E8

6. Указание левого верхнего и нижнего правого угла диапазона. Range(Range(“C1”), Range(“D10”)).

7.Применение свойства End. Для выделения диапазона относительно которого известен только левый верхний угол, можно воспользоваться свойством End. Оно указывает на нижний правый угол диапазона.

With Range(“A1”)

Range(.Cells(1, 1),.End(xlRight).End(xlDown)).Select

End With

Пример:

With Range(“A1”)

Range(.Offset(1, 1),.End(xlRight)).Name=”Sales”

Range(.Offset(2, 1),.End(xlDown)).Name=”Region”

Range(.Offset(2, 2),.End(xlRight)).End(xlDown).Name=”NameSales”

End With

 

Работа с массивами. Оператор Option Base. Динамическая модель индексации и оператор ReDim. Примеры использования этих операторов. Функция управления массивами Array. Пример использования.

По аналогии с работой с информацией в списках Excel, массивы тоже представляют собой списки, в которых каждый элемент имеет свой индекс. Элементы массива воспринимаются программой в виде обычных переменных, но представленных в виде индексированного списка. В вба массивы используются для обработки списков, поскольку массивами управлять в программном режиме гораздо проще, чем списками. Правила работы с массивами. Option Base – определяет нижнюю границу изменения индекса в массиве. Например, Option Base1 – нижняя граница – 1. Эту строку в программном коде пишут в общей области модуля следом за оператором Option Explicit.

В большинстве экономических задач при написании программного кода невозможно заранее узнать количество элементов. Для этого существует возможность вначале программного кода не указывать при объявлении массива точного размера. (Dim ProdCode () As Integer, NProducts As

Integer.) Затем в теле процедуры, когда уже известно необходимое количество элементов массива, можно воспользоваться оператором ReDim, который выделяет для массива строго необходимый объем памяти. With ActiveWorkBook.WorkShits(«Продажи»).Range(“A3”)

NProducts=Range(.Offset(1,0),.End(xlDown)).

ReDim ProdCode(NProducts),UnitPrise(NProducts)

For i=1 to NProducts

ProdCode(i)=.Offset(i,0)

UnitPrise(i)=.Offset(i,1)

Next i

End With

Оператор ReDim относительно конкретного массива может использоваться в программном коде столько раз, сколько необходимо. Единственной проблемой является то, что при таком использовании массива, все, что было в массиве, теряется. Чтобы этого избежать, нужно в записи оператора записать ключевое слово Preserve. (ReDim Preserve Sum(NS)). Обычно продолжением данного программного кода является размещение данных, взятых из ст. А и В списка в массивах ProdCode и UnitPrice. Для переноса данных из столбцов списка в массив, нужно организовать цикл. Если нужно перенести данные в столбцы списка, размещенного на рабочем листе:

(NFound – количество найденных записей)

For i=1 to NFound

With Range(“E3”)

.Offset(j, 0)=ProdCodeFound(j) ‘код товара

.Offset(j, 1)=Quontity(j) 'количество товаров

.Offset(j, 2)=DollarsTotal(j) ‘стоимость продукции

End With

End Sub

Рассмотрим конструкцию Array. Рассмотрим работу этой функции на примере. Данная функция используется для заполнения массива. Ее применении е рассмотрим в программе «Ипотечный кредит».

Option Base1

Sub Array Function ()

Dim Days As Variant

Days=Array(«Пн», «Вт», «Ср», «Чт», «Пт», «Сб», «Вс»)

MsgBox “Первый день недели: ” & Days(1)

End Sub

Ключевое слово Array, после которого указывается список в скоках, используется для заполнения значениями переменной Days. Эта переменная – обычный массив, но в операторе Dim она указывается как обычная переменная типа Variant, а VBA самостоятельно с использованием функции Array определяет эту переменную как массив.

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

Sub MainProcedure()

Call Procedure1

Call Procedure2

End Sub

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

Dim Ncities As Integer, Visited() As Boolean, Route() As Integer, TotDist As Integer

Sub GenDistances()

Dim I As Integer, j As Integer, Ncities As Integer

Randomize

With Range("DistMatrix")

Ncities = .Rows.Count

For I = 1 To Ncities - 1

For j = I + 1 To Ncities

.Cells(I, j) = Int(Rnd * 100) + 1

Next j

Next I

For I = 2 To Ncities

For j = 1 To I - 1

.Cells(I, j) = .Cells(j, I)

Next j

Next I

End With

End Sub

Sub MainProcedure()

Call GetProblemSize

Call Initialize

Call PerformHeuristic

Call DisplayResults

Exit Sub

End Sub

Sub GetProblemSize()

Ncities = Range("DistMatrix").Rows.Count

ReDim Visited(Ncities)

ReDim Route(Ncities + 1)

End Sub

Sub Initialize()

Dim I As Integer

Route(1) = 1

Route(Ncities + 1) = 1

Visited(1) = True

For I = 2 To Ncities

Visited(I) = False

Next

TotDist = 0

End Sub

Sub PerformHeuristic()

Dim Step As Integer, I As Integer, NowAt As Integer, NextAt As Integer, MinDist As Integer

NowAt = 1

For Step = 2 To Ncities

MinDist = 10000

For I = 2 To Ncities

If I <> NowAt And Visited(I) = False Then

If Range("DistMatrix").Cells(NowAt, I) < MinDist Then

NextAt = I

MinDist = Range("DistMatrix").Cells(NowAt, NextAt)

End If

End If

Next I

Route(Step) = NextAt

Visited(NextAt) = True

TotDist = TotDist + MinDist

NowAt = NextAt

Next Step

TotDist = TotDist + Range("DistMatrix"). Cells(NowAt, 1)

End Sub

Sub DisplayResults()

Dim Step As Integer

For Step = 1 To Ncities + 1

Range("B19").Offset(Step, 0) = Route(Step)

Next Step

MsgBox "Общее растояние:" & TotDist, vbInformation, "общее расстояние"

End Sub

Использование переменных уровня модуля. Рассмотрим программу, которая иллюстрирует использование переменных уровня модуля.

На рабочем листе Excel создадим список и дадим ему имя – Name.

А В С
Фамилия Имя, Отчество

Dim FirstName As String, LastName As String

Sub Main ()

Dim I As Integer

For i=1 to 50

FirstName=Range(“Name”).Cells(i, 1)

LastName=Range(“Name”).Cells(i, 2)

Call DisplayName

Next i

End Sub

Sub DisplayName ()

Dim FullName As String

FullName=FirstName+LastName

MsgBox “Полное имя сотрудника: ” & FullName

End Sub

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

В форме присутствуют 3 обработчика событий:

1. UserForm_Initialize

2. OkButton_Click

3. CanselButton_Click

1 разработчик определяет внешний вид формы при открытии, а остальные обеспечивают реакцию на щелчок по кн. «Ок» и «Отмена».

Код обработчика UserForm_Initialize.

В этом приложении при открытии формы нужно добиться следующего поведения: переключатели Москва и Поезд должны быть активны, флажок Скоропортящийся установлен, а Хрупкий – сброшен, список CostumerList должен быть заполнен исходными данными. При работе со списком, его можно заполнить разными способами. Например, заполнить его данными, расположенными в ячейках рабочего листа. Для этого зоздать диапазон на рабочем листе, дать ему имя(Name - Costumer). При заполнении списка воспользоваться AddItem. Перед тем, как писать код для события Initialize, рассмотрим свойства элементов управления: пользовательская форма – Ипотека; текстовое поле – ProductBox; переключатели – MoscowOption, VoronegOption, TrainOption, TruckOption; флажки – PerishBox, FragilBox; список – CostumersList.

Private Sub UserForm_Initialize()

Dim cell As Variant

ProductBox = " "

MoscowOptions.Value = True

TrainOptions.Value = True

PerishBox = True

FragilBox = False

For Each cell In Range("Customers")

CustomersList.AddItem cell.Value

Next cell

End Sub

Существует другой способ заполнения списка. Свойство RowSource, откроется окно, из которого поочереди надо внести элементы.

Отображение формы. В отличии от VB в VBA форма в момент запуска приложения автоматически на экране не появляется. Для вызова формы на рабочий лист нужно применить метод Show. Для этого на рабочем листе создать кнопку, которая выводит форму на рабочий лист, и написать код:

Sub Кнопка1_Щелкнуть ()

Ипотека. Show

End Sub

 

18. Обработка событий формы: создание кода обработки событий UserForm_Initialize, CanselButton_Click на примере обработки этих событий для пользовательской формы «Сведения о товарах».Событие представляет собой действие, распознаваемое объектом, для которого можно запрограммировать отклик.

Рассмотрим форму «Сведения о товарах». В ней присутствуют 3 обработчика событий:

1. UserForm_Initialize

2. OkButton_Click

3. CanselButton_Click

1 разработчик определяет внешний вид формы при открытии, а остальные обеспечивают реакцию на щелчок по кн. «Ок» и «Отмена».

Код обработчика UserForm_Initialize.

В этом приложении при открытии формы нужно добиться следующего поведения: переключатели Москва и Поезд должны быть активны, флажок Скоропортящийся установлен, а Хрупкий – сброшен, список CostumersList должен быть заполнен исходными данными. Для заполнения списка на рабочем листе создадим диапазон и дадим ему имя Costumers. В программном коде воспользуемся методом AddItem. Перед тем, как писать код для события Initialize, рассмотрим некоторые свойства элементов управления: пользовательская форма – Ипотека; текстовое поле – ProductBox; переключатели – MoscowOption, VoronegOption, TrainOption, TruckOption; флажки – PerishBox, FragilBox; список – CostumersList.

Private Sub UserForm_Initialize()

Dim cell As Variant

ProductBox = " "

MoscowOptions.Value = True

TrainOptions.Value = True

PerishBox = True

FragilBox = False

For Each cell In Range("Customers")

CustomersList.AddItem cell.Value

Next cell

End Sub

Для кнопки «отмена» запишем следующий код:

Private Sub CanselButton_Click ()

Unload.Me

End

End Sub

Unload.Me – этот метод выгружает форму из оперативной памяти и убирает ее с экрана.

 

19. Обработка событий формы: создание кода обработки события OkButton_Clickна примере обработки этого события для пользовательской формы «Сведения о товарах».Событие представляет собой действие, распознаваемое объектом, для которого можно запрограммировать отклик.

Рассмотрим форму «Сведения о товарах». В ней присутствуют 3 обработчика событий:

1. UserForm_Initialize

2. OkButton_Click

3. CanselButton_Click

1 разработчик определяет внешний вид формы при открытии, а остальные обеспечивают реакцию на щелчок по кн. «Ок» и «Отмена».

Код процедуры OkButton_Click. Обычно данный обработчик событий используется для сохранения данный, введенных в элементы управления. Как правило, введенная информация в элементы управления заносится в общедоступные переменные, которые определены в модуле, далее эти переменные используются в программных кодах модуля. ProductСode – код, введенный в поле; Region – пункт отправления; Shipping – транспорт; IsPerish – скоропортящийся; IsFragil – хрупкий; Customers – смисок.

Dim ProductCode As Integer, Region As String, Shipping As String, IsPerish As Boolean, IsFragile As Boolean, Customers As String

Private Sub OkButton_Click()

With ProductBox

If .Value = " " or not IsNewmeric(.Value) Then

MsgBox "Код товара не введен или не числовой"

.SetFocus

Exit Sub

End If

ProductCode=ProductBox. Value

If ProductCode < 1 Or ProductCode > 1000 Then

MsgBox "Код товара должен быть в диапазоне от 1 до 1000"

.SetFocus ‘ставит курсор в поле

Exit Sub ‘выход из процедуры

End If

If TrainOption.Value = True Then

Shipping = "Poezd"

Else

Shipping = "Gruzovik"

End If

If MoscowOption.Value = True Then

Region = "Moscow"

Else

Region = "Voroneg"

End If

IsPerish = PerishBox.Value

IsFragile = FragilBox.Value

With CostomerList ‘работа со списком

If .ListIndex <> -1 Then

Customers = CustomersList.Value

Else

MsgBox "Элемент в списке не выбран"

.SetFocus

Exit Sub

End If

End With

End Sub

20. Структура принятия решения If-Then-Else

Условный оператор If-Then-Else изменяет порядок выполнения про-граммы в зависимости от результатов проверки некоторого условия.

Sub LookUpPrice()

Dim ProdCode() As String, UnitPrice() As Currency, ReguestedPrice As Currency, Nproducts As Integer, i As Integer, Found As Boolean, ReguestedCode As String

With ActiveWorkbook.Worksheets("Коды товаров”).Range("A3")

Nproducts = Range(.Offset(1, 0), .End(xlDown)).Rows.Count

ReDim ProdCode(Nproducts)

ReDim UnitPrice(Nproducts)

For i = 1 To Nproducts

ProdCode(i) = .Offset(i, 0)

UnitPrice(i) = .Offset(i, 1)

Next i

End With

ReguestedCode = InputBox("Введите код товара (большая латинская буква и 4 цифры)")

Found = False

For i = 1 To Nproducts

If ProdCode(i) = ReguestedCode Then

Found = True

ReguestedPrice = UnitPrice(i)

Exit For

End If

Next i

If Found Then

MsgBox "Товар с кодом" & ReguestedCode & " стоит " & Format(ReguestedPrice, "0,00р."), vbInformation, "Товар не найден"

Else

MsgBox "Товара с кодом " & ReguestedCode & " нет в списке ", vbInformation, "Товар не найден”

End If

End Sub

 

21. Дополнительное условие ElseIf

Средствами структуры принятия решенья If-Then-Else можно орга-низовать выполнение операторов в зависимости от соблюдения опреде-ленного условия. Рассмотрим другой вариант ее использования, на этот раз с ключевым словом ElseIf. Программа Proc31 отображает запрос на ввод пароля. Если пароль введен правильно, программа предоставляет пользо-вателю определенные возможности работы с рабочей книгой и сообщает ему об этом.

 

Sub Proc31_IfThenElseIf ( )

Dim Password As String, ws As WorkSheet

Password = GetPassword

If Password = “level1” Then

For Each ws In ActiveWorkbook.WorkSheets

ws.Visible = True

ws. Unprotect

Next

MsgBox “Вы получили доступ ко всем листам книги.”

ElseIf Password = “level2” Then

ActiveWorkbook.Worksneets (1). Visible = True

ActiveWorkbook.Worksneets (1). Unprotect

MsgBox “Вы получили доступ только к первому листу рабочей книги.”

ElseIf Password = “level3” Then

ActiveWorkbook.Worksneets (1). Visible = True

MsgBox “Вы получили доступ только для чтения содержимого первого листа рабочей книги.”

Else

MsgBox “Пароль не корректен. Повторите ввод пароля.”

End If

End Sub

 

Function GetPassword ( )

GetPassword = Lcase (InputBox(“Enter Password:”.” Password”))

End Function

 

В Proc31 ключевое слово Elself с последующим условием использу-ется дважды. Новое условие вносит в порядок выполнения команд допол-нительные изменения, если проверка первого условия закончилась неуда-чей. В Proc31 первое условие указано сразу за ключевым словом If. В нем проверяется равенство переменной Password строке ”levell”. Если пере-менная не равна строке, VBA переходит к первому ключевому слову Elself, где проверяет следующее условие – равенство переменной Password строке “level2”. Начиная с этого момента, программа использует только новое ус-ловие, “забывая” о старом. Если равенства опять нет, управление перехо-дит к следующему ключевому слову Elself и т. д.

В строке 3 процедуры Proc31 вызывается функция GetPassword, в ко-торой использованы новые элементы. Обратимся к ней еще раз.

 

Function GetPassword ( )

GetPassword = Lcase (InputBox(“Enter Password:”.” Password”))

End Function

 

Здесь во второй строке заданы обращения к двум встроенным функ-циям VBA-Lcase и InputBox. Первая преобразует все буквы переданной в нее строки в строчные. Вторая выводит на экран диалоговое окно с запро-сом на ввод данных пользователем. У этой функции есть несколько необя-зательных аргументов, из которых нас интересуют только первые два. Один из них – строка, отображаемая в окне над полем для ввода. Второй содержит строку заголовка диалогового окна. При вызове функции Input-Box на экране появляется диалоговое окно с заданными заголовком и тек-стом приглашением, кроме того оно содержит пустое поле, в котором пользователь может ввести данные. Значение, возвращаемое InputBox, за-висит от того, какую кнопку щелкнет пользователь. Если это – ОК, воз-вращается введенная строка, если – Отмена (Cancel), то пустая. В нашем примере буквы этой строки передаются в функцию Lcase, которая преоб-разует их в строчные. В блоке, начинающемся сразу за ключевым словом If, помещен цикл For-Each-Next (он подробно описан ниже в разделе "Управляющая структура For-Each-Next”). В этом цикле перебираются все листы активной рабочей книги, с каждым из которых выполняются два действия – свойству Visible присваивается значение True и вызывается ме-тод Unprotect. Первое действие позволяет пользователю видеть лист, вто-рое – снимает с листа защиту паролем. У метода Unprotect есть один аргу-мент – строка с паролем. Но в данном случае пароль для защиты листов мы не использовали, поэтому Unprotect вызван без аргументов. В других бло-ках программы свойство Visible и метод Unprotect вызываются выборочно, в зависимости от введенного пароля.

22. Управляющая структура For-Next

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

Sub LookUpPrice()

Dim ProdCode() As String, UnitPrice() As Currency, ReguestedPrice As Currency, Nproducts As Integer, i As Integer, Found As Boolean, ReguestedCode As String

With ActiveWorkbook.Worksheets("Коды товаров”).Range("A3")

Nproducts = Range(.Offset(1, 0), .End(xlDown)).Rows.Count

ReDim ProdCode(Nproducts)

ReDim UnitPrice(Nproducts)

For i = 1 To Nproducts

ProdCode(i) = .Offset(i, 0)

UnitPrice(i) = .Offset(i, 1)

Next i

End With

ReguestedCode = InputBox("Введите код товара (большая латинская буква и 4 цифры)")

Found = False

For i = 1 To Nproducts

If ProdCode(i) = ReguestedCode Then

Found = True

ReguestedPrice = UnitPrice(i)

Exit For

End If

Next i

If Found Then

MsgBox "Товар с кодом" & ReguestedCode & " стоит " & Format(ReguestedPrice, "0,00р."), vbInformation, "Товар не найден"

Else

MsgBox "Товара с кодом " & ReguestedCode & " нет в списке ", vbInformation, "Товар не найден”

End If

End Sub

 

23. Управляющая структура While-Wend

Действие ее подобно действию For-Next, но группа операторов вы-полняется не заданное число раз, а до соблюдения определенного условия. В программе Proc34 инструкция While-Wend использована для выделения определенного значения из последовательности случайных чисел.

 

Sub Proc34_WhileWend ( )

Dim LotteryEntry As Integer

LotteryEntry = 0

While LotteryEntry <> 7

LotteryEntry = Int (10*Rnd ( ))

Beep

Wend

MsgBox “Ваше число равно “& LotteryEntry &”. Вы выиграли!!”

End Sub

 

Программа Proc34 гарантирует, что в информационном окне всегда отображается заданное число. Цикл While-Wend выполняется, пока значе-ние переменной LotteryEntry не станет равным 7 (в условии использован оператор неравенства <>). При каждом выполнении цикла этой перемен-ной присваивается случайное значение от 1 до 9, а затем с помощью функ-ции VBA Beep, подается звуковой сигнал через внутренний динамик ком-пьютера. При запуске этой программы несколько раз, можно услышать разное количество сигналов, в зависимости от того, на каком шаге генера-тор случайных чисел вернет число 7.

Рассмотрим структуру цикла While-Wend подробнее.

 

While Ключевое слово, начало структуры While-Wend

LotteryEntry <> 7 Условие, определяющее, будет или нет вы-полнен цикл. Если оно соблюдено, цикл вы-полняется, если нет – управление передается оператору, стоящему перед ключевым сло-вом Wend

LotteryEntry=Int(10*Rnd( )) Первый оператор тела

Beep Второй оператор тела

 

Wend Ключевое слово, отмечающее конец струк-туры While-Wend

 

Введение в систему программирования VBA. Объектная модель Excel, основные объекты Е. Краткая их характеристика.

VB и VBA не одно и то же. VB представляет собой язык для разработки программного обеспечения, которое используется отдельно от MS Office. VBA входит в состав пакета MS Office. Устанавливая его, вы получаете доступ к VBA. Он необходим для управления данными MS Office.

Язык VBA поддерживает все базовые элементы любого другого языка программ.: 1)переменные 2)массивы 3)подпрограммы 4)управляющие структуры 5)возможность создания пользоват. типов данных и т.д.

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

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

Объектная модель Excel является основой структуры программы. Без создания иерархической структуры объектов программы, нельзя использовать VBA в Еexcel.

Все элементы Excel – объекты, с которыми приходится работать в программе. Чаще всего используются такие объекты, как рабочие книги WorkBooks, рабочие листы WorkSheets, диапазоны Range. Объект WorkSheets имеет свойство Name, только объект Range не имеет ярко выраженной коллекции. Выступает как коллекция и как объект. Чаще исп. В программном коде, обладает огромными возможностями.

 

2. Объектная модельExcel: понятие объекта, метода, свойства, события.

Объектная модель Excel является основой структуры программы. Без создания иерархической структуры объектов программы, нельзя использовать VBA в Еexcel.

Все элементы Excel – объекты, с которыми приходится работать в программе. Чаще всего используются такие объекты, как рабочие книги WorkBooks, рабочие листы WorkSheets, диапазоны ячеек Range ckarts.

Свойства определяют внешний вид и состояние объекта. Для определения свойства его нужно указать справа от имени объекта через точку. Range(“A1”).Value – свойство описывает содержимое ячейки А1. Существует 2 вида опереций при работе со свойствами: 1)присваивание свойству значения. Объект.Свойство=<значение>

Range(“A1”).Value= «Отчет о командировке». 2)полученное свойство присваивается переменной. Переменная=Объект.Свойство

Var= Range(“A1”).Value

Методы объекта открывают задачи, которые может выполнять объект. Объект.Метод – когда у метода нет параметров. Range(“A1”).CleanContenst – очищает диапазон, но сохраняет форматирование. Если метод имеет аргументы, то они перечисляются через , после названия метода.

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

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

 

3. редактор VB. Окно проекта. Окно редактирования кода. Окно редактирования формы. Панель элементов. Окно свойств. Окно просмотра объектов. Окна Immediate, Watch.Редактор VB=Visual Basic Editor. Редактора VBA не бывает! Для запуска этого редактора нужно открыть приложение Excel и выполнить команду Сервис/Макрос/Редактор VB или Alt+F11. Чтобы вернуться в Excel, нужно повторно нажать Alt+F11. В открывшемся окне VBA: строка заголовка, меню, панели инструментов и несколько окон.

По умолчанию в верхнем левом углу находится окно проекта. Вывести его можно командой View/Project Explorer. Это окно содержит список всех открытых проектов и список всех их компонентов. В проекте всегда располагаются 3 модуля(на каждом листе) и модуль книги. Все модули делятся на: 1) стандартные – добавляются к проекту Insert/Module. Они содержат макросы, функции и процедуры. 2) модули объектов – модули, связанные с рабочими книгами, раблчими листами и формами. Проект VBA состоит из объектов, имеющих иерархическую структуру, и включает: объекты Excel, формы, стандартные модули и модули классов. Окно редактирования кода. В окне проекта выделяете объект, для которого будет введен программный код. Затем выполнить команду View/Code. Откроется окно, в которое нужно ввести программный код. Окно редактирования формы. Для вывода формы: Insert/User Form. На экране появится форма. Окно свойств. Для открытия: View/Properties Window. В левой части окна перечислены свойства объекта, в правой – значения свойств, которые можно изменять. Окно просмотра объектов. View/Object Browser или F2. В верхнем левом углу открывшегося окна – список библиотек объектной модели Excel. Оно дает спраку о всех объектах Excel, их свойствах и методах.

Панель элементов (View/Dwbug)в стандартном варианте включает различные классы управляющих элементов: CommandButton (командная кнопка), TextBox, Label и др. Существует возможность дополнить панель с помощью команды Tools/Additional Controls.

Окно Immediate – один из инструментов отладки. Предназначено для непосредственного ввода команд. Окно Watch – порядок выполнения программы и отслеживание ошибок.

4. Создание простой программы на примере приложения «Информация о продажах по регионам».Задача: создать программу, которая будет отслеживать продажи в каждом регионе. Для каждого региона отразить сообщение с количеством месяцев, когда продажи превышали заданный объем, введенный пользователем. Порядок выполнения: открыть Excel и на первом листе создать список следующего вида:

А В С G
месяц Регион1 Регион2   Регион3
Янв2005        
….        
Дек2006        

Отформатировать столбцы(1 – дата, остальные – формат денежный). Заполнить список. Затем выделить диапазон ячеек B2:G25 и присвоить ему имя SalesRange. Перейти в редактор вба: Alt+F11 или Сервис/Макрос/Редактор Вб. Если окна нет, выполнить команду View/Project Explorer. Далее выполнить Insort/Module, откроется окно редактора. Ввести следующий программный код:

Sub CountHighSales()

Dim i As Integer, j As Integer, ks As Integer, s As Currency

s = InputBox("Введите цену для сравнения", "окно для ввода критерия")

For i = 1 To 6

ks = 0

For j = 1 To 24

If Range("SalesRange").Cells(j, i) >= s Then

ks = ks + 1

End If

Next j

MsgBox "в регионе " & i & " объем продаж превышал " & s & " в " & ks & " месяцах"

Next i

End Sub

Запустить программу на выполнение: F5 или Run/Run Sub. На экране появится окно InputBox. Запуск программы с помощью кнопки, расположенной на рабочем листе. Находясь на рабочем листе, выполнить команду Вид/Панели инструментов/Формы. В появившемся окне выбрать элемент Кнопка и нарисовать на рабочий лист. Откроется окно, в котором предлагается создать макрос, выбрать название CountHighSales и закрыть. Дать свое название кнопке. Для запуска программы щелкнуть на этой кнопке.

Создать программу можно и другим способом, начиная с размещения кнопки. Появляется окно «Назначить макрос объекту». Нажать на кнопку Создать. Запустится редактор VBE и откроется окно редактирования кода макроса. Ввести программный код.

 

5. Стандартные элементы управления VB: понятия, свойства, методы, события общие для этих элементов.Краткая характеристика элементов управления. Элементы управления - это объекты, которые можно разместить на форме. Как и все объекты, они имеют свойства, методы и события. Свойства определяют внешний вид и поведение.






Организация стока поверхностных вод: Наибольшее количество влаги на земном шаре испаряется с поверхности морей и океанов (88‰)...

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

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

Опора деревянной одностоечной и способы укрепление угловых опор: Опоры ВЛ - конструкции, предназначен­ные для поддерживания проводов на необходимой высоте над землей, водой...





© cyberpedia.su 2017-2020 - Не является автором материалов. Исключительное право сохранено за автором текста.
Если вы не хотите, чтобы данный материал был у нас на сайте, перейдите по ссылке: Нарушение авторских прав

0.114 с.