|
Читайте также: |
При составлении программного кода в VBA нужно придерживаться следующих правил: 1) определение члена коллекции. Для указания отдельного объекта коллекции используется имя коллекции во мн. ч. и имя члена коллекции в ед.ч.
Application является средой в которой выполняется приложение VBA. У Application 120 свойств и 40 методов. На рабочем листе Excel из ячеек можно вызывать свыше 400 встроенных функций.
Основные свойства
Caption.
Application.caption = “Ипотека”
DisplayAlerts (true или false) – управляет выводом на экран встроенных предупреждений Excel
WindowState (xlMaximized, xlMinimized, xlNormal)
Основные методы.
Calculate – вызывает пересчет всех формул на всех рабочих листах, книгах
Quit – закрытие приложения
Run – запускает программу в VBA
Wait – приостанавливает выполнение приложения VBA.
WorkSheets(“Data”).Range(“A1:B4”). Исключение – объект Range. 2) определение объектов. Представлены в виде иерархической структуры. Чтобы указать объекты, нахлдящиеся ниже в иерархической структуре, их нужно разделить (.), при этом объекты нижних уровней будут находиться в ссылке правее.
WorkBooks(“Регионы”).WorkSheets(“Воронеж”).Range(“Продажи”). Для определения свойства объекта его нужно указать справа от имени объекта через (.).
Range(“A1”).Value. Существует 2 вида операций при работе со свойствами:
1)присваивает свойству значение
Объект.Свойство=<значение>
Range(“A1”).Value= «Информация»
2)получ. свойство присваевается переменной. Переменная=Объект.Свойство
Val=Range(“A1”).Value Определение метода.
Объект.Метод – когда у метода нет параметров.
Range(“A1:A5”).CleanContenst – очищает диапозан, но сохраняет форматирование. Если метод имеет аргументы, то они перечисляются через (,) после названия метода. Пример: копирование А1:А10 в С1:С10. Range(“A1:A10”).CopyDestination:= Range(“C1:C10”). Определение события. Событие представляет собой действие, распозноваемое объектом, для котороно можно запрограммировать отклик. Свойства и методы объекта могут быть связаны между собой. Выполнение метода приводит к изменению свойства объекта, а изменение некоторого свойства может вызвать наступление события. Суть программирования в VBA заключается в 2 понятиях: событие и отклик на него.
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 j=1 to NFound
With Range(“E3”)
.Offset(j, 0)=ProdCodesFound(j) ‘код товара
.Offset(j, 1)=Quantity(j) 'количество товаров
.Offset(j, 2)=DollarsTotal(j) ‘стоимость продукции
End With
Next j
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
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
Дата добавления: 2015-11-14; просмотров: 36 | Нарушение авторских прав
| <== предыдущая страница | | | следующая страница ==> |
| Sub DisplayName | | | Юлия Петрушина |