Страница:
End If
End If
OptionButton1.Value = False
OptionButton2.Value = False
CommandButton1.Enabled = False
End Sub
Кнопка Выходзавершает игру, выдает итоговые сообщения (см. рис. 83).
Рис. 83.Итоговые расчеты игры примера 38
Примечание.Для корректной работы игры необходимо создать модуль, в котором объявить глобальные переменные, содержащие сведения о значении начальной ставки и имени игрока, а также запускающий первую форму игры при помощи кнопки на панели инструментов. Кроме того, необходимо доработать все модули для соответствующих форм (рис. 84).
Рис. 84.Объявление глобальных переменных и создание модуля запуска главной формы игры в примере 38
6.2. Создание и автоматическое заполнение бланков стандартных документов
Технология выполнения
Интерфейс шаблона и приложения
В качестве интерфейса сначала рассмотрим самую простую форму приложения, отвечающего условиям задачи и включающего в себя соответствующие текстовые поля ввода и рабочие кнопки (рис. 85).
Рис. 85.Форма примера 39 в рабочем состоянии
Далее рассмотрим, как автоматизировать печать квитанции об оплате за обучение.
Начнем с разработки бланка стандартного документа (печатной формы). Эту форму можно сделать на базе шаблона с текстовыми полями, в которые будут заноситься изменяющиеся сведения. Для этого выполните следующие команды: Файл +Создать.В правой части окна нового документа выберите раздел Шаблоныи гиперссылку На моем компьютере(рис. 86). После чего появится диалоговое окно Шаблоны(рис. 87), в котором на вкладке Общиенеобходимо выделить пиктограмму Новый документи поставить переключатель Шаблонв рамке Создать.
Включите панель Формы(Вид + Панели инструментов + Формы). Для дальнейшей работы понадобится только один элемент этой панели – Текстовое поле
которое позволяет создать на форме изменяющееся поле (либо посредством кода, либо самим пользователем). Выбирая курсором место в документе Word для расположения соответствующего поля и расположив на нем элемент
Поле, можно получить необходимую форму. Для того чтобы расширить поле, надо поставить в него курсор и несколько раз нажать
Tab. Для того чтобы подчеркнуть поле, выделите его как текст и примените к нему подчеркивание.
Рис. 87.Диалоговое окно Шаблоны
Создайте шаблон документа, придерживаясь рис. 88.
Рис. 88.Шаблон документа word, в который вносятся данные для печати
В некоторых полях (поля выделены серым фоном) можно ввести значения по умолчанию. Значение по умолчанию устанавливается при помощи диалогового окна Свойства, которое можно вывести на экран следующим образом:
•щелкнуть правой клавишей мыши по необходимому текстовому полю;
•в появляющемся контекстном меню выбрать Свойства ;
•указать значение по умолчанию (рис. 89).
Значения по умолчанию позволяют менять только те данные, которые должны изменяться нечасто. Например, оплата за обучение в основном одна и та же – 1500 руб., поэтому значение этого поля будет меняться не очень часто.
Рис. 89.Установка параметров текстового поля «сумма_опл»
Рассмотрим и другие необходимые элементы рабочего окна «Параметры текстового поля». В поле Типуказывается тип значения, которое можно помещать в поле. Можно заполнить поле Максимальная длина, хотя наиболее важным для дальнейшего использования кодом VBA является поле Закладка. Значение этого поля будет служить меткой, в которую должен устанавливаться курсор, перед помещением в документ очередной порции информации. Поэтому, как только все необходимые поля определены, нужно указать для каждого из них осмысленные метки. Для бланка документа Word, приведенного на рис. 86, зададим (последовательно) такие метки:
Фамилия – фамилия студента;
Имя – имя студента;
Отчество – отчество студента;
Группа – наименование группы;
Месяц_опл – название месяца, за который вносится оплата;
Сумма_опл – внесенная сумма;
ФИО_бух – фамилия принявшего бухгалтера;
Дата_опл – дата оплаты.
После окончания разработки формы документа сохраним его под именем Оплата за учебув каталоге Шаблоны. На рис. 88 представлен шаблон созданного документа с изменяющимися полями.
Создание модуля Печать для шаблона документа
Далее необходимо перейти в редактор VBA и создать форму приложения, отвечающего условиям задачи (рис. 85).
В листинге представлен код процедуры печати бланка квитанции об оплате, сопутствующие и отладочные процедуры.
Листинг(занести в Модуль1 проекта Оплата за учебу)
Обратите внимание на то, что здесь все данные имеют тип String. Такие однотипные данные позволяют записывать их в поля формы в цикле, содержащем всего два оператора (см. описание листинга).
Создание модулей работы кнопок формы приложения
Работа кнопки Выдать справкусостоит в считывании введенной информации в текстовые поля формы приложения, передаче их значений переменным формы шаблона документа с дальнейшим выводом информации в шаблон документа.
Готовый шаблон документа и форма даются на рис. 90 и 91.
Рис. 90.Пример упрощенного бланка почтового перевода с текстовыми изменяющимися полями (шаблон документа)
Рис. 91.Форма
Рассылкав режиме выполнения для примера 40
Часть III
Глава 7
7.1. Основные объекты VBA в Excel
В иерархии Excel объект Range(диапазон) идет сразу после объекта worksheet.Объект Rangeявляется одним из ключевых объектов VBA. Объект selection (выбор) возникает в VBA двояко – либо как результат работы метода Select, либо при вызове свойства selection. Тип получаемого объекта зависит от типа выделенного объекта. Чаще всего объект Selection принадлежит классу Range, и при работе с ним можно использовать свойства и методы объекта Range. Интересной особенностью объектов Range и Selection является то, что они не являются элементами никакого семейства объектов.
При работе с объектом Range необходимо помнить, как в Excel ссылаются на ячейку рабочего листа.
Задание групп строк и столбцов с помощью объекта Range
Если в диапазоне указываются только имена столбцов или строк, то объект Range задает диапазон, состоящий из указанных столбцов или строк. Например, Range («а: с») задает диапазон, состоящий из столбцов а, в и с, а Range(«2:2») – из второй строки. Другим способом работы со строками и столбцами являются методы Rows (строки) и columns (столбцы), возвращающие коллекции строк и столбцов. Например, столбцом а является columns (1), а второй строкой – Rows (2).
Связь объекта Range и свойства Cells
Так как ячейка является частным случаем диапазона, состоящим только из единственной ячейки, объект Range также позволяет работать с ней. Объект Cells (ячейки) – это альтернативный способ работы с ячейкой. Например, ячейка А2 как объект описывается Range («A2») или Cells (l, 2). В свою очередь, объект cells, вкладываясь в Range, также позволяет записывать диапазон в альтернативном виде, который иногда удобен для работы, а именно Range(«А2:СЗ») и Range(Cells(1,2), Cells(3,3)) определяют один и тот же диапазон.
Таблица 19
Таблица 20
Метод AutoFill
Метод AutoFill (автозаполнение) автоматически заполняет ячейки диапазона элементами последовательности. Метод AutoFill отличается от метода DataSeries тем, что явно указывается диапазон, в котором будет располагаться прогрессия. Вручную этот метод эквивалентен расположению указателя мыши на маркере заполнения выделенного диапазона (в который введены значения, порождающие создаваемую последовательность) и протаскиванию маркера заполнения вдоль диапазона, в котором будет располагаться создаваемая последовательность.
Синтаксис:
Диапазон Диапазон, с которого начинается заполнение тип Допустимые значения: xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues,xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend. По умолчанию xlFillDefault
Метод AutoFilter
Метод AutoFilter (автофильтр) представляет собой простой способ запроса и фильтрации данных на рабочем листе. Если AutoFilter активизирован, то каждый заголовок поля выделенного диапазона данных превращается в поле с раскрывающимся списком. Выбирая запрос на вывод данных в поле с раскрывающимся списком, осуществляется вывод только тех записей, которые удовлетворяют указанным условиям. Поле с раскрывающимся списком содержит следующие типы условий: Все (All), Первые десять (Тор 10), Условие (Custom), конкретный элемент данных, Пустые (Blanks) и Непустые (NonBlanks). Вручную метод запускается посредством выбора команды Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter).
При применении метода AutoFilter допустимы два синтаксиса.
Синтаксис 1:
Синтаксис 2:
Аргументы:
fieldЦелое, указывающее поле, в котором производится фильтрация данных
Criteria1Задают два возможных условия фильтрации и criteria2 поля. Допускается использование строковой постоянной, например 101, и знаков отношений >, <,>=, <=, =, <>
operatorДопустимые значения: X1And (логическое объединение первого и второго критериев); X1or (логическое сложение первого и второго критериев)
При работе с фильтрами полезны метод showAllData и свойства FilterMode и AutoFilterMode.
Метод ShowAllData Показывает все отфильтрованные и неотфильтрованные строки рабочего листа
свойство FilterMode Допустимые значения: True (если на рабочем листе имеются отфильтрованные данные со скрытыми строками), False (в противном случае)
Свойство AutoFilterMode Допустимые значения: True (если на рабочем листе выведены раскрывающиеся списки метода AutoFilter), False (в противном случае)
Метод GoalSeek
Метод GoalSeek (подбор параметра) подбирает значение параметра (неизвестной величины), являющееся решением уравнения с одной переменной. Предполагается, что уравнение приведено к виду: правая часть является постоянной, не зависящей от параметра, который входит только в левую часть уравнения. Вручную метод GoalSeek выполняется с помощью команды Сервис, Подбор параметра (Tools, Goal Seek). Метод GoalSeek вычисляет корень, используя метод последовательных приближений, результат выполнения которого, вообще говоря, зависит от начального приближения. Поэтому для корректности нахождения корня надо позаботиться о корректном указании этого начального приближения.
Синтаксис:
ОбъектЯчейка, в которую введена формула, являющаяся правой частью решаемого уравнения. В этой формуле роль параметра (неизвестной величины) играет ссылка на ячейку, указанную в аргументе ChangingCell
GoalЗначение левой части решаемого уравнения, не содержащей параметра
ChangingCellСсылка на ячейку, отведенную под параметр (неизвестную величину). Значение, введенное в данную ячейку до активизации метода Goalseek, рассматривается как начальное приближение к искомому корню
Точность, с которой находится корень и предельно допустимое число итераций, используемых для нахождения корня, устанавливается свойствами Maxchange и Maxiterations объекта Application. Например, определение корня с точностью до 0,0001 максимум за 1000 итераций устанавливается инструкцией:
Метод Sort
Сортировка позволяет выстраивать данные в лексикографическом порядке по возрастанию или убыванию. Метод sort осуществляет сортировку строк списков и баз данных, а также столбцов рабочих листов с учетом до трех критериев, по которым производится сортировка. Сортировка данных вручную совершается с использованием команды Данные, Сортировка (Data, Sort).
Синтаксис:
ОбъектДиапазон, который будет сортироваться
Key1Ссылка на первое упорядочиваемое поле
Order1Задает порядок упорядочивания. Допустимые значения: xlAscending (возрастающий порядок); xlDescending (убывающий порядок)
key2Ссылка на второе упорядочиваемое поле
order2Задает порядок упорядочивания. Допустимые значения: xlAscending (возрастающий порядок); xlDescending (убывающий порядок)
headerДопустимые значения: xlYes (первая строка диапазона содержит заголовок, который не сортируется); xlNo (первая строка диапазона не содержит заголовка, по умолчанию считается данное значение); xlGuess (Excel решает, имеется ли заголовок)
orderCustomПользовательский порядок сортировки. По умолчанию используется Normal
matchCaseДопустимые значения: True (учитываются регистры) и False (регистры не учитываются)
orientationДопустимые значения: xlTopToBottom (сортировка осуществляется сверху вниз, т. е. по строкам); xlLeftToRight (слева направо, т. е. по столбцам)
Например, диапазон А1:С20 рабочего листа лист1 сортируется следующей командой в порядке возрастания так, что первоначальная сортировка происходит по первому столбцу этого диапазона, а второстепенная – по второму:
1 способ
Функция Round
Пример:
Поэтому часто не используется.
2 способ
Функция Format
Пример:
Функция FormatNumber
Примечание.Переменная, в которую помещается округленное значение, должна иметь тип string, single, double, decimal, currency или variant, но не тип integer или long.
Для выведения значений непосредственно в ячейки книги Excel удобно использовать объект Range:
Функция Trim (строка) возвращает копию строки, из которой удалены пробелы, находящиеся в начале и конце строки.
Пример 41. Разработать программу, которая по введенным числовым значениям некоторого уравнения решает данное уравнение и находит неизвестную переменную х. Результат вычисления выводится в текстовое окно на форме и на лист Excel.
Рис. 92.Разработанная форма примера 41 в рабочем состоянии
Технология выполнения
1. Запустите приложение Excel, сохраните документ.
2. Перейдите в редактор VBA.
3. Создайте форму согласно приведенному рис. 92.
4. На листе Excel расположите необходимый текст (оформление), предусмотрев соответствующие ячейки вывода информации (рис. 93).
Рис. 93.Вывод результатов на лист excel после запуска формы примера 41
5. Обработайте кнопки.
Кнопка Вычислить
Технология выполнения
1. Запустите приложение Excel, сохраните документ.
2. Перейдите в редактор VBA. Создайте форму согласно приведенному рис. 94.
Рис. 94.Разработанная форма примера 42 в режиме конструктора
3. На листе Excel расположите необходимый текст (оформление), предусмотрев соответствующие ячейки вывода информации (рис. 95).
Рис. 95.Вывод результатов на лист excel после запуска формы
4. Обработайте кнопки.
Кнопка Создать таблицу
Пример 43 . Разработать программу, которая по введенным переменным в соответствующие поля формы решает простейшее линейное уравнение y = a*x + b*x, находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.
Пример 44 . Разработать программу, которая по введенным переменным в соответствующие поля формы решает уравнение вида y = a*x^3 + 3b*sinx, находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.
Пример 45 . Разработать программу, которая по введенным переменным в соответствующие поля формы решает уравнение вида y = 5a*x^1/3 + 3b*tg4x, находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.
Пример 46 . Разработать программу, которая по введенным переменным в соответствующие поля формы решает уравнение вида y = ln(a*x^3) + 3b*cos(e^x), находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.
7.2. Использование возможностей VBA при непосредственных расчетах
End If
OptionButton1.Value = False
OptionButton2.Value = False
CommandButton1.Enabled = False
End Sub
Кнопка Выходзавершает игру, выдает итоговые сообщения (см. рис. 83).
Private Sub CommandButton2_Click()
MsgBox («Партий» + TextBox1.Value + (Chr(13)) + «в банке „ + TextBox4.Value + (Chr(13)) + „ваш максимум“ + TextBox2.Value + (Chr(13)) + „ваш минимум“ + TextBox3.Value + (Chr(13)) + „счет“ + TextBox5.Value +“:» + TextBox6.Value)
UserForm4.Show
End Sub
Private Sub UserForm Initialize()
Unload UserForm2
OptionButton1.Value = True
TextBox4.Value = a
Label6.Caption = imya
TextBox2.Value = TextBox4.Value
TextBox3.Value = TextBox4.Value
End Sub
Примечание.Для корректной работы игры необходимо создать модуль, в котором объявить глобальные переменные, содержащие сведения о значении начальной ставки и имени игрока, а также запускающий первую форму игры при помощи кнопки на панели инструментов. Кроме того, необходимо доработать все модули для соответствующих форм (рис. 84).
6.2. Создание и автоматическое заполнение бланков стандартных документов
Создание VBA-программ
Пример 39. При оплате за обучение студент вносит деньги в кассу, где бухгалтер заполняет от руки квиток об оплате. Автоматизировать ручную работу бухгалтера, создав форму, заполняющую карточку оплаты, которую можно при необходимости отправить на печать.Технология выполнения
Интерфейс шаблона и приложения
В качестве интерфейса сначала рассмотрим самую простую форму приложения, отвечающего условиям задачи и включающего в себя соответствующие текстовые поля ввода и рабочие кнопки (рис. 85).
Далее рассмотрим, как автоматизировать печать квитанции об оплате за обучение.
Начнем с разработки бланка стандартного документа (печатной формы). Эту форму можно сделать на базе шаблона с текстовыми полями, в которые будут заноситься изменяющиеся сведения. Для этого выполните следующие команды: Файл +Создать.В правой части окна нового документа выберите раздел Шаблоныи гиперссылку На моем компьютере(рис. 86). После чего появится диалоговое окно Шаблоны(рис. 87), в котором на вкладке Общиенеобходимо выделить пиктограмму Новый документи поставить переключатель Шаблонв рамке Создать.
Включите панель Формы(Вид + Панели инструментов + Формы). Для дальнейшей работы понадобится только один элемент этой панели – Текстовое поле
Создайте шаблон документа, придерживаясь рис. 88.
В некоторых полях (поля выделены серым фоном) можно ввести значения по умолчанию. Значение по умолчанию устанавливается при помощи диалогового окна Свойства, которое можно вывести на экран следующим образом:
•щелкнуть правой клавишей мыши по необходимому текстовому полю;
•в появляющемся контекстном меню выбрать Свойства ;
•указать значение по умолчанию (рис. 89).
Значения по умолчанию позволяют менять только те данные, которые должны изменяться нечасто. Например, оплата за обучение в основном одна и та же – 1500 руб., поэтому значение этого поля будет меняться не очень часто.
Рассмотрим и другие необходимые элементы рабочего окна «Параметры текстового поля». В поле Типуказывается тип значения, которое можно помещать в поле. Можно заполнить поле Максимальная длина, хотя наиболее важным для дальнейшего использования кодом VBA является поле Закладка. Значение этого поля будет служить меткой, в которую должен устанавливаться курсор, перед помещением в документ очередной порции информации. Поэтому, как только все необходимые поля определены, нужно указать для каждого из них осмысленные метки. Для бланка документа Word, приведенного на рис. 86, зададим (последовательно) такие метки:
Фамилия – фамилия студента;
Имя – имя студента;
Отчество – отчество студента;
Группа – наименование группы;
Месяц_опл – название месяца, за который вносится оплата;
Сумма_опл – внесенная сумма;
ФИО_бух – фамилия принявшего бухгалтера;
Дата_опл – дата оплаты.
После окончания разработки формы документа сохраним его под именем Оплата за учебув каталоге Шаблоны. На рис. 88 представлен шаблон созданного документа с изменяющимися полями.
Создание модуля Печать для шаблона документа
Далее необходимо перейти в редактор VBA и создать форму приложения, отвечающего условиям задачи (рис. 85).
В листинге представлен код процедуры печати бланка квитанции об оплате, сопутствующие и отладочные процедуры.
Листинг(занести в Модуль1 проекта Оплата за учебу)
Option ExplicitВ связи с различием переменных, принимаемых и передаваемых на печать, объявим как Public переменные двух видов: получаемые интерфейсной частью приложения и печатаемые в форме шаблона.
Option Base 1
' переменные формы приложения
Public фам1 As String
Public имя1 As String
Public отчество1 As String
Public групп1 As String
Public месяц As String
Public сумма As String
Public бух As String
Public дата As String
' переменные формы шаблона документа
Public фамилия As String
Public имя As String
Public отчество As String
Public группа As String
Public месяц_опл As String
Public сумма_опл As String
Public фио_бух As String
Public дата_опл As String
Dim book(8) As String
Dim dataMas(8) As String
Dim i As Integer
Sub Печать() 'процедура печати бланка
'Заносим информацию в массивы bookmarmas, datamas
book(1) = «фамилия»: dataMas(1) = фамилия
book(2) = «имя»: dataMas(2) = имя
book(3) = «отчество»: dataMas(3) = отчество
book(4) = «группа»: dataMas(4) = группа
book(5) = «месяц_опл»: dataMas(5) = месяц_опл
book(6) = «сумма_опл»: dataMas(6) = сумма_опл
book(7) = «фио_бух»: dataMas(7) = фио_бух
book(8) = «дата_опл»: dataMas(8) = дата
'заполнение полей квитанции:
For i = 1 To 8
ActiveDocument.FormFields(ActiveDocument.Bookmarks(book(i)).Name).Result = dataMas(i)
Next
ActiveDocument.PrintPreview Выполняется предварительный просмотр печати.
'Для того чтобы реально распечатать документ, применяется метод PrintOut объекта Application (см. Help)
UserForm1.Hide
End Sub
Обратите внимание на то, что здесь все данные имеют тип String. Такие однотипные данные позволяют записывать их в поля формы в цикле, содержащем всего два оператора (см. описание листинга).
Создание модулей работы кнопок формы приложения
Работа кнопки Выдать справкусостоит в считывании введенной информации в текстовые поля формы приложения, передаче их значений переменным формы шаблона документа с дальнейшим выводом информации в шаблон документа.
Option ExplicitПроцедуры присваивания переменным формы приложения введенных значений в текстовые поля формы.
Private Sub CommandButton1_Click()
фамилия = фам1
имя = имя1
отчество = отчество1
группа = групп1
месяц_опл = месяц
сумма_опл = сумма
фио_бух = бух
дата_опл = дата
Call Печать
End Sub
Private Sub textBox1_change()Откомпилируйте программу и запустите на проверку.
фам1 = TextBox1.Value
End Sub
Private Sub textBox2_change()
имя1 = TextBox2.Value
End Sub
Private Sub textBox3_change()
отчество1 = TextBox3.Value
End Sub
Private Sub textBox4_change()
групп1 = TextBox4.Value
End Sub
Private Sub textBox5_change()
месяц = TextBox5.Value
End Sub
Private Sub textBox6_change()
сумма = TextBox6.Value
End Sub
Private Sub textBox7_change()
бух = TextBox7.Value
End Sub
Private Sub textBox8_change()
дата = TextBox8.Value
End Sub
Задача на закрепление материала
Пример 40. [5] Разработать форму бланка почтового перевода для облегчения труда сотрудников, связанных с частым оформлением денежных переводов.Готовый шаблон документа и форма даются на рис. 90 и 91.
Часть III
Программирование на VBA в Excel
Глава 7
Особенности использования VBA в Excel
7.1. Основные объекты VBA в Excel
Использование объектов Range и Selection
В Excel наиболее важным является объект Application. Объект Application (приложение) является главным в иерархии объектов Excel и представляет само приложение Excel. Он имеет более 120 свойств и 40 методов. Эти свойства и методы предназначены для установки общих параметров приложения Excel. В иерархии Excel объект Workbook (рабочая книга) идет сразу после объекта Application и представляет файл рабочей книги. Рабочая книга хранится либо в файлах формата XLS (стандартная рабочая книга), либо XLA (полностью откомпилированное приложение). Свойства и методы рабочей книги позволяют работать с файлами. Однако наиболее «употребляемым» на практике является объект Range, который наилучшим образом отображает возможности использования VBA в Excel (о свойствах объекта Range см. табл. 19, о методах – табл. 20).В иерархии Excel объект Range(диапазон) идет сразу после объекта worksheet.Объект Rangeявляется одним из ключевых объектов VBA. Объект selection (выбор) возникает в VBA двояко – либо как результат работы метода Select, либо при вызове свойства selection. Тип получаемого объекта зависит от типа выделенного объекта. Чаще всего объект Selection принадлежит классу Range, и при работе с ним можно использовать свойства и методы объекта Range. Интересной особенностью объектов Range и Selection является то, что они не являются элементами никакого семейства объектов.
При работе с объектом Range необходимо помнить, как в Excel ссылаются на ячейку рабочего листа.
Задание групп строк и столбцов с помощью объекта Range
Если в диапазоне указываются только имена столбцов или строк, то объект Range задает диапазон, состоящий из указанных столбцов или строк. Например, Range («а: с») задает диапазон, состоящий из столбцов а, в и с, а Range(«2:2») – из второй строки. Другим способом работы со строками и столбцами являются методы Rows (строки) и columns (столбцы), возвращающие коллекции строк и столбцов. Например, столбцом а является columns (1), а второй строкой – Rows (2).
Связь объекта Range и свойства Cells
Так как ячейка является частным случаем диапазона, состоящим только из единственной ячейки, объект Range также позволяет работать с ней. Объект Cells (ячейки) – это альтернативный способ работы с ячейкой. Например, ячейка А2 как объект описывается Range («A2») или Cells (l, 2). В свою очередь, объект cells, вкладываясь в Range, также позволяет записывать диапазон в альтернативном виде, который иногда удобен для работы, а именно Range(«А2:СЗ») и Range(Cells(1,2), Cells(3,3)) определяют один и тот же диапазон.
Таблица 19
Свойства объекта Range
Таблица 20
Методы объекта Range
Методы объекта Range, использующие команды Excel
Встроенные в Excel команды и методы позволяют эффективно работать с диапазоном: заполнять его элементами по образцу, сортировать, фильтровать и консолидировать данные, строить итоговую таблицу и создавать сценарии, решать нелинейное уравнение с одной переменной.Метод AutoFill
Метод AutoFill (автозаполнение) автоматически заполняет ячейки диапазона элементами последовательности. Метод AutoFill отличается от метода DataSeries тем, что явно указывается диапазон, в котором будет располагаться прогрессия. Вручную этот метод эквивалентен расположению указателя мыши на маркере заполнения выделенного диапазона (в который введены значения, порождающие создаваемую последовательность) и протаскиванию маркера заполнения вдоль диапазона, в котором будет располагаться создаваемая последовательность.
Синтаксис:
объект. AutoFill(диапазон, тип)Аргументы:
Диапазон Диапазон, с которого начинается заполнение тип Допустимые значения: xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues,xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend. По умолчанию xlFillDefault
Метод AutoFilter
Метод AutoFilter (автофильтр) представляет собой простой способ запроса и фильтрации данных на рабочем листе. Если AutoFilter активизирован, то каждый заголовок поля выделенного диапазона данных превращается в поле с раскрывающимся списком. Выбирая запрос на вывод данных в поле с раскрывающимся списком, осуществляется вывод только тех записей, которые удовлетворяют указанным условиям. Поле с раскрывающимся списком содержит следующие типы условий: Все (All), Первые десять (Тор 10), Условие (Custom), конкретный элемент данных, Пустые (Blanks) и Непустые (NonBlanks). Вручную метод запускается посредством выбора команды Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter).
При применении метода AutoFilter допустимы два синтаксиса.
Синтаксис 1:
Объект. AutoFilterВ этом случае метод AutoFilter выбирает или отменяет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter), примененную к диапазону, заданному в аргументе объект.
Синтаксис 2:
Объект. AutoFilter (field, criteria1, operator, criteria2)В этом случае метод AutoFilter выполняет команду Данные, Фильтр, Автофильтр (Data, Filter, AutoFilter) по критериям, указанным в аргументе.
Аргументы:
fieldЦелое, указывающее поле, в котором производится фильтрация данных
Criteria1Задают два возможных условия фильтрации и criteria2 поля. Допускается использование строковой постоянной, например 101, и знаков отношений >, <,>=, <=, =, <>
operatorДопустимые значения: X1And (логическое объединение первого и второго критериев); X1or (логическое сложение первого и второго критериев)
При работе с фильтрами полезны метод showAllData и свойства FilterMode и AutoFilterMode.
Метод ShowAllData Показывает все отфильтрованные и неотфильтрованные строки рабочего листа
свойство FilterMode Допустимые значения: True (если на рабочем листе имеются отфильтрованные данные со скрытыми строками), False (в противном случае)
Свойство AutoFilterMode Допустимые значения: True (если на рабочем листе выведены раскрывающиеся списки метода AutoFilter), False (в противном случае)
Метод GoalSeek
Метод GoalSeek (подбор параметра) подбирает значение параметра (неизвестной величины), являющееся решением уравнения с одной переменной. Предполагается, что уравнение приведено к виду: правая часть является постоянной, не зависящей от параметра, который входит только в левую часть уравнения. Вручную метод GoalSeek выполняется с помощью команды Сервис, Подбор параметра (Tools, Goal Seek). Метод GoalSeek вычисляет корень, используя метод последовательных приближений, результат выполнения которого, вообще говоря, зависит от начального приближения. Поэтому для корректности нахождения корня надо позаботиться о корректном указании этого начального приближения.
Синтаксис:
Объект. GoalSeek(Goal, ChangingCell)Аргументы:
ОбъектЯчейка, в которую введена формула, являющаяся правой частью решаемого уравнения. В этой формуле роль параметра (неизвестной величины) играет ссылка на ячейку, указанную в аргументе ChangingCell
GoalЗначение левой части решаемого уравнения, не содержащей параметра
ChangingCellСсылка на ячейку, отведенную под параметр (неизвестную величину). Значение, введенное в данную ячейку до активизации метода Goalseek, рассматривается как начальное приближение к искомому корню
Точность, с которой находится корень и предельно допустимое число итераций, используемых для нахождения корня, устанавливается свойствами Maxchange и Maxiterations объекта Application. Например, определение корня с точностью до 0,0001 максимум за 1000 итераций устанавливается инструкцией:
With ApplicationВручную эти величины устанавливаются на вкладке Вычисления (Calculation) диалогового окна Параметры (Options), вызываемого командой Сервис, Параметры (Tools, Options).
Maxiterations = 1000
MaxChange = 0.0001
End With
Метод Sort
Сортировка позволяет выстраивать данные в лексикографическом порядке по возрастанию или убыванию. Метод sort осуществляет сортировку строк списков и баз данных, а также столбцов рабочих листов с учетом до трех критериев, по которым производится сортировка. Сортировка данных вручную совершается с использованием команды Данные, Сортировка (Data, Sort).
Синтаксис:
Объект. Sort(key1, order1, key2, order2, key3, order3, header, orderCustom, matchCase, orientaticn)Аргументы:
ОбъектДиапазон, который будет сортироваться
Key1Ссылка на первое упорядочиваемое поле
Order1Задает порядок упорядочивания. Допустимые значения: xlAscending (возрастающий порядок); xlDescending (убывающий порядок)
key2Ссылка на второе упорядочиваемое поле
order2Задает порядок упорядочивания. Допустимые значения: xlAscending (возрастающий порядок); xlDescending (убывающий порядок)
headerДопустимые значения: xlYes (первая строка диапазона содержит заголовок, который не сортируется); xlNo (первая строка диапазона не содержит заголовка, по умолчанию считается данное значение); xlGuess (Excel решает, имеется ли заголовок)
orderCustomПользовательский порядок сортировки. По умолчанию используется Normal
matchCaseДопустимые значения: True (учитываются регистры) и False (регистры не учитываются)
orientationДопустимые значения: xlTopToBottom (сортировка осуществляется сверху вниз, т. е. по строкам); xlLeftToRight (слева направо, т. е. по столбцам)
Например, диапазон А1:С20 рабочего листа лист1 сортируется следующей командой в порядке возрастания так, что первоначальная сортировка происходит по первому столбцу этого диапазона, а второстепенная – по второму:
Worksheets(«Лист»).Range(«A1: C20»).Sort _
key1:=Worksheets(«Sheet1»).Range(«A1»), _
key2:=Worksheets («Sheet1»).Range («B1»)
Округление чисел
Округлять десятичные числа приходится часто, особенно при работе с денежными значениями. VBA не предлагает прямого решения таких задач, но обсуждаемые ниже приемы помогут решить эти проблемы.1 способ
Функция Round
Пример:
X= round(2.505, 2)Значение х будет 2,5, а не 2,51.
Поэтому часто не используется.
2 способ
Функция Format
Пример:
sngОкругление=Format(SngНеокругленное, “#, 0.00”)3 способ
Функция FormatNumber
SngОкругление= FormatNumber(sbgНеокругленное, 2)Для изменения знаков после запятой измените число нулей после десятичной точки в аргументе Format, либо измените число, задающее значение второго аргумента, на нужное.
Примечание.Переменная, в которую помещается округленное значение, должна иметь тип string, single, double, decimal, currency или variant, но не тип integer или long.
Приведение данных
Для приведения введенных данных к нужному типу в VBA включен обширный набор функций, одна из которых – CDBL. Синтаксис:CDbl(выражение)Обязательный аргумент выражениеявляется любым строковым или числовым выражением. Для считывания информации, введенной в текстовое поле в созданной форме, вводят переменную и прописывают выражение:
А = Cdbl(textBoxN.text)После чего с данной переменной можно работать.
Для выведения значений непосредственно в ячейки книги Excel удобно использовать объект Range:
range(«A5»).value = aФункцией, обратной по действию к CDbl, является функция CStr – она переводит числа в строки и удобна для вывода результата либо в ячейку на лист, либо в то или иное текстовое окно.
TextBoxN.text = CStr(.Range(«A8»).value)– считывание значения с ячейки и вывод его в текстовое окно.
Функция Trim (строка) возвращает копию строки, из которой удалены пробелы, находящиеся в начале и конце строки.
Создание VBA-программ
Использование метода GoalSeekПример 41. Разработать программу, которая по введенным числовым значениям некоторого уравнения решает данное уравнение и находит неизвестную переменную х. Результат вычисления выводится в текстовое окно на форме и на лист Excel.
Технология выполнения
1. Запустите приложение Excel, сохраните документ.
2. Перейдите в редактор VBA.
3. Создайте форму согласно приведенному рис. 92.
4. На листе Excel расположите необходимый текст (оформление), предусмотрев соответствующие ячейки вывода информации (рис. 93).
5. Обработайте кнопки.
Кнопка Вычислить
Private Sub CommandButton1_Click()Кнопка Закрыть
Dim a, b, c As Double
a = CDbl(TextBox1.Text)
b = CDbl(TextBox2.Text)
c = CDbl(TextBox3.Text)
With ActiveSheet
Range(«b3»).Value = a
Range(«b4»).Value = b
Range(«b5»).Value = c
Range(«b6»).FormulaLocal = «=b3*b7^3+b4*sin(b7)»
Range(«b6»).GoalSeek Goal:=c, changingCell:=Range(«b7»)
TextBox4.Text = CStr(.Range(«b7»).Value)
TextBox4.Text = FormatNumber(TextBox4.Text, 2)
End With
End Sub
Private Sub CommandButton2_Click()Процедура инициализации формы
UserForm1.Hide
End Sub
Private Sub UserForm_initialize()
Worksheets(1).Visible = False
End Sub
Использование методов AutoFill при заполнении таблиц
Пример 42 . Создать программу, которая по введенным текстовым данным в соотвествующие текстовые поля формы автоматизирует ввод данных на студентов некоторой специальности учебного заведения. Результаты заполнения текстовых полей выводятся на лист excel, что позволяет при необходимости распечатать данные.Технология выполнения
1. Запустите приложение Excel, сохраните документ.
2. Перейдите в редактор VBA. Создайте форму согласно приведенному рис. 94.
3. На листе Excel расположите необходимый текст (оформление), предусмотрев соответствующие ячейки вывода информации (рис. 95).
4. Обработайте кнопки.
Кнопка Создать таблицу
Const strNomer = 3 количество строк для заголовкаКнопка Добавить строку
Dim strName1 As String 'строка для адресации ячеек
Dim strName2 As String
Dim nomer As Long номер очередной строки таблицы
Private Sub CommandButton1_Click()
ActiveWorkbook.SaveAs («работа с базой данных. xls»)
nomer = 1 End Sub
Private Sub CommandButton2_Click()Кнопка Закончить таблицу
strName1 = Trim(Str(strNomer + nomer))
With ActiveSheet 'ввод данных для новой отчетной таблицы
Range("A" + strName1).Value = nomer
Range("B" + strName1).Value = TextBox1.Text
Range("C" + strName1).Value = TextBox2.Text
Range("D" + strName1).Value = TextBox3.Text
'автозаполнение с текущей строки таблицы
strName2 = Trim(Str(strNomer + nomer + 1))
Set range1 =.Range("A" + strName1 +":D" + strName1)
Set range2 =.Range("A" + strName1 +":D" + strName2)
range1.AutoFill Destination:=range2
Range("A" + strName2 +":D" + strName2).Clear
End With
' очистка полей формы для ввода очередной записи
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox1.SetFocus
nomer = nomer + 1
End Sub
Private Sub CommandButton3_Click()5. Откомпилируйте программу и запустите на выполнение.
'закрытие формы подведение итогов и вывод фамилии преподавателя
UserForm1.Hide
With ActiveSheet
strName2 = Trim(Str(strNomer + nomer + 2))
Range("A" + strName2).Value = «классный руководитель»
Range("D" + strName2).Value = TextBox4.Text
End With
End Sub
Пример 43 . Разработать программу, которая по введенным переменным в соответствующие поля формы решает простейшее линейное уравнение y = a*x + b*x, находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.
Пример 44 . Разработать программу, которая по введенным переменным в соответствующие поля формы решает уравнение вида y = a*x^3 + 3b*sinx, находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.
Пример 45 . Разработать программу, которая по введенным переменным в соответствующие поля формы решает уравнение вида y = 5a*x^1/3 + 3b*tg4x, находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.
Пример 46 . Разработать программу, которая по введенным переменным в соответствующие поля формы решает уравнение вида y = ln(a*x^3) + 3b*cos(e^x), находит неизвестную переменную х и выводит результат вычислений на рабочий лист Excel.