Работа с объектами Excel на уровне приложения, рабочей книги и листа
1. Цель работы:
1.1. изучение особенностей использования VBA в Excel.
1.2. Изучение возможностей VBA при непосредственных расчетах в Excel.
2. Приборы и оборудование:
2.1. Методические указания.
2.2. ПЭВМ типа IBM PC/XT.
2.3. MS Excel.
2.4. Visual Basic for Application.
3. Порядок выполнения работы:
3.1. Изучить основные теоретические сведения (Приложение А).
3.2. Задание 1.Разработать программу, которая по введенным числовым значениям некоторого уравнения решает данное уравнение и находит неизвестную переменную х. Результат вычисления выводится в текстовое окно на форме и на лист Excel.
3.2.1. Запустите приложение Excel, сохраните документ. Перейдите в редактор VBA. Создайте форму согласно приведенному рисунку 1.
Рисунок 1 – Разработанная форма в рабочем состоянии.
3.2.2. На листе Excel расположите необходимый текст (оформление), предусмотрев соответствующие ячейки вывода информации (см. рисунок 2).
Рисунок 2 – Вывод результатов на лист Excel после запуска формы.
3.2.3. Обработайте кнопки.
Кнопка Вычислить
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
3.2.4. Откомпилируйте программу и запустите на выполнение.
3.2.5. В соответствии с номером ПК, создайте свою программу, исходя из задания (см. Приложение Б). Создайте кнопку или панель в Вашем проекте для непосредственного вызова приложения.
3.3. Задание 2.Создать программу, которая по введенным текстовым данным в соответствующие текстовые поля формы автоматизирует ввод данных на студентов некоторой специальности учебного заведения. Результаты заполнения текстовых полей выводятся на лист Excel, что позволяет при необходимости распечатать данные.
3.3.1. Запустите приложение Excel, сохраните документ. Перейдите в редактор VBA. Создайте форму согласно приведенному рисунку 3.
Рисунок 3 – Разработанная форма в режиме конструктора.
3.3.2. На листе Excel расположите необходимый текст (оформление), предусмотрев соответствующие ячейки вывода информации (см. рисунок 4).
Рисунок 4 – Пример организации данных на лист Excel.
3.3.3. Обработайте кнопки.
Кнопка Создать таблицу
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() 'закрытие формы подведение итогов и вывод фамилии преподавателя
UserForm1.Hide
With ActiveSheet
strName2 = Trim(Str(strNomer + nomer + 2))
Range("A" + strName2).Value = «Куратор»
Range("D" + strName2).Value = TextBox4.Text
End With
End Sub
3.3.4. Откомпилируйте программу и запустите на выполнение.
3.4. Задание 3. Создать программу, которая по введенным данным в соответствующие поля формы автоматизирует ввод данных на студентов некоторой специальности учебного заведения. Результаты заполнения полей выводятся на лист Excel, что позволяет при необходимости распечатать данные (см. рисунки 5-6). Создайте кнопку или панель в Вашем проекте для непосредственного вызова приложения.
Рисунок 5 – Пример организации данных на лист Excel.
Рисунок 6 – Разработанная форма в режиме конструктора.
3.5. Добавьте комментарии. Текст и результат выполнения программ занести в отчёт.
3.6. Сформулируйте выводы по проделанной работе.
4. Содержание отчёта:
4.1. Тема работы.
4.2. Цель работы.
4.3. Приборы и оборудование.
4.4. Порядок выполнения работы.
4.5. Выводы.
4.6. Контрольные вопросы.
5. Контрольные вопросы:
5.1. Какой объект является главным в иерархии объектов Excel? Какие объекты Вам ещё известны? Описание.
5.2. Как задаётся группа строк и столбцов с помощью объекта Range?
5.3. Как осуществляется связь объекта Range и свойства Cells?
5.4.Перечислите свойства и действия объекта Range.
5.5.Перечислите методы и действия объекта Range.
5.6.Какие методы объекта Range, используют команды Excel?
5.7. Каким образом происходит округление десятичных чисел в VBA Excel?
5.8. Какие функции используются для приведения введенных данных к нужному типу в VBA?
5.9. Каким образом определить количество строк для заголовка?
5.10. Как назначить форме кнопку или пункт меню для непосредственного вызова приложения из Word?
Приложение А
Теоретические сведения
Использование объектов Range и Selection
В Excel наиболее важным является объект Application. Объект Application (приложение) является главным в иерархии объектов Excel и представляет само приложение Excel. Он имеет более 120 свойств и 40 методов. Эти свойства и методы предназначены для установки общих параметров приложения Excel. В иерархии Excel объект Workbook (рабочая книга) идет сразу после объекта Application и представляет файл рабочей книги. Рабочая книга хранится либо в файлах формата XLS (стандартная рабочая книга), либо XLA (полностью откомпилированное приложение). Свойства и методы рабочей книги позволяют работать с файлами. Однако наиболее «употребляемым» на практике является объект Range, который наилучшим образом отображает возможности использования VBA в Excel (о свойствах объекта Range см. таблицу 1, о методах – таблицу 2).
В иерархии 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)) определяют один и тот же диапазон.
Таблица 1 – Свойства объекта Range
| Свойство | Действия |
| Value | Возвращает значение из ячейки или в ячейки диапазона. В данном примере переменной x присваивается значение из ячейки с1: x = Range('C1').Value В следующем примере в диапазон a1:b2 введена 1: Range('A1:B2').Value = 1 |
| Name | Возвращает имя диапазона. В данном примере диапазону a1:b2 присваивается имя "итоги": Range("A1:B2").Name = "Итоги" |
| Count | Возвращает число объектов в наборе. В данном примере переменной x присваивается значение, равное числу строк диапазона a1:b2: x = Range("A1:B2").Rows.Count |
| CurrentRegion | Возвращает число строк текущего диапазона. Текущим является диапазон, ограниченный пустыми строками и столбцами и содержащий данный элемент. В следующем примере переменной у присваивается значение, равное числу строк в текущем диапазоне, содержащем ячейку a1: y = Range("A1").CurrentRegion.Rows.Count |
| ColumnWidth, RowHeight | Возвращает ширину столбцов и высоту строк диапазона |
| WrapText | Позволяет переносить текст при вводе в диапазон. Допустимые значения True и False. В следующем примере в ячейку b2 вводится текст "длинный текст", и в этой ячейке устанавливается режим ввода текста с переносом: With Range("B2").Value = "Длинный текст".WrapText = True End With |
| EntireColumn, EntireRow | Возвращает столбец и строку соответственно. В данном примере очищается содержимое строки и выделяется столбец с активной ячейкой: ActiveCell.EntireRow.Clear ActiveCell.EntireColumn.Select |
| Comment | Возвращает объект comment (примечание), который связан с левым верхним углом диапазона при отображении на экране. Объект comment является элементом семейства comments. Метод AddComment, примененный к диапазону, создает новое примечание. Среди методов объекта Comment отметим только метод Text, который задает текст, выводимый в примечании. Синтаксис: Text(Text,Start,Overwrite) Здесь Text - строка, выводимая в качестве примечания; Start - с какого символа вводится текст в уже существующее примичание. Если аргумент опущен, то из примечания удаляется весь ранее введенный текст; overwrite - допустимые значения: True (вводимый текст записывается поверх уже существующего) и False (вводимый текст вставляется в уже существующий). Среди свойств объекта comment отметим только свойство visible, устанавливающее отображение примечания при активизации диапазона, имеющего определенное примечание |
| VerticalAlignment | Вертикальное выравнивание. Допустимые значения: xlBottom (выравнивание по нижнему краю); xlCenter (выравнивание по центру); xlJustify (выравнивание по высоте); xlTop (выравнивание по верхнему краю) |
| Orientation | Ориентация. Допускается либо угол поворота текста в градусах от -90 до 90 |
| ShrinkToFit | Допустимые значения: True (автоматическое изменение шрифта так, чтобы текст помещался в ячейку) и False (в противном случае) |
| Font | Возвращает объект Font (шрифт). Объект Font имеет следующие свойства: Name - строка, указывающая имя шрифта, например "Arial Cyr"; FontStyle - стиль, возможен Regular (обычный), Bold (полужирный), Italic (курсив), Bold Italic (полужирный курсив); Size - размер; Strikethrough - допустимы два значения: True (буквы имеют линию по центру, как будто они перечеркнуты) и False (не имеют линии по центру); Superscript - допустимы два значения: True (текст используется как верхний индекс) и False (не используется как верхний индекс); Subscript - допустимы два значения: True (текст используется как нижний индекс) и False (не используется как нижний индекс); Underline - устанавливает тип подчеркивания xlNone (нет подчеркивания) xlSingle (одинарное подчеркивание) |
| Horizontal Alignment | Горизонтальное выравнивание. Допустимые значения: xlGeneral (обычное выравнивание, зависит от типа вводимых значений); xlCenter (выравнивание по центру); xlRight (выравнивание по правому краю); xlLeft (выравнивание по левому краю); xlJustify (выравнивание по ширине); xlCenterAcrossSelection (выравнивание по центру в выделенном диапазоне); xlFill (выравнивание по ширине). |
Таблица 2 – Методы объекта Range
| Метод | Действие |
| AutoFit | Автоматически настраивает ширину столбца и высоту строки |
| Clear, ClearComments, ClearContents, ClearFormats, ClearNotes | Метод clear очищает диапазон. В следующем примере очищается диапазон A1:G37. Range("A1:G37").Clear Методы ClearComments, ClearContents, ClearFormats и ClearNotes очищают в указанном диапазоне комментарии, содержание, форматы и примечания соответственно |
| Insert | Вставка ячейки или диапазона ячеек. В следующем примере вставляется новая строка перед четвертой строкой рабочего листа Лист1: Worksheets("Лист1").Rows(4).Insert |
| Select | Выделение диапазона |
| Copy | Копирует диапазон в другой диапазон или буфер обмена. Синтаксис: Copy(destination) Аргумент destination определяет диапазон, куда копируется данный диапазон. Если аргумент destination опущен, то копирование происходит в буфер обмена. В данном примере диапазон a1:d4 рабочего листа копируется в диапазон e5 листа 2: Worksheets("Лист1").Range("A1:D4").Copy destination:= Worksheets("Лист2").Range("E5") |
| Delete | Удаляет диапазон |
| AddComment | Добавляет примечание к диапазону. Синтаксис: AddComment(Text) Text - строковое выражение, добавляемое в качестве примечания |
| Address | Возвращает адрес ячейки. Синтаксис: Address(rowAbsolute, columnAbsolute, referenceStyle, external, relativeTo). Аргументы: rowAbsolute - допустимы два значения True и False, если используется значение True или аргумент опущен, то возвращается абсолютная ссылка на строку; columnAbsolute - допустимы два значения True и False, если используется значение True или аргумент опущен, то возвращается абсолютная ссылка на столбец; referenceStyle - допустимы два значения xllA1 и xlRld, если используется значение xlAl или аргумент опущен, то возвращается ссылка в виде формата A1;external - допустимы два значения True и False, если используется значение False или аргумент опущен, то возвращается относительная ссылка. Следующий пример показывает различные результаты адресации: MsgBox Cells(1,1).Address - В диалоговом окне отображается адрес $A$1. MsgBox Cells(1,1).Address(rowAbsolute:=False) - В диалоговом окне отображается адрес $A1. MsgBox Cells(1,1).Address(referenceStyle:=xlR1C1) - В диалоговом окне отображается адрес R1C1 |
| Cut | Копирует диапазон с удалением в указанный диапазон или буфер обмена. Синтаксис: Cut(destination). Аргумент destination определяет диапазон, который копируется в данный диапазон. Если аргумент destination опущен, то Диапазон копируется в буфер обмена |
| Columns, Rows | Возвращает соответственно семейства столбцов и строк, из которых состоит диапазон. В следующем примере переменным i и j присваиваются значения, равные количеству столбцов и строк в выделенном диапазоне соответственно: i = Selection.Columns.Count j= Selection.Rows.Count |
Методы объекта 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. Показывает все отфильтрованные и неотфильтрованные строки рабочего листа. Cвойство 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
Maxiterations = 1000
MaxChange = 0.0001
End With
Вручную эти величины устанавливаются на вкладке Вычисления (Calculation) диалогового окна Параметры (Options), вызываемого командой Сервис, Параметры (Tools, Options).
Метод 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 (строка) возвращает копию строки, из которой удалены пробелы, находящиеся в начале и конце строки.
Приложение Б