Аннотация: Лекция посвящена описанию объектной модели MS Excel и подробному описанию методов, свойств и событий Application.
13.1. Особенности программирования для MS Excel
Microsoft Office Excel - это популярные электронные таблицы. Обычно, программируя для этой программы, преследуют такие цели:
- Автоматизация вычислений.
- Автоматизация ввода и обработки информации.
- Работа с базами данных - вывод, ввод, анализ, визуализация информации.
- Анализ финансовой и другой информации.
- Создание систем для организации автоматизированного ввода данных
- Математическое моделирование .
В общем виде программирование в Excel похоже на работу с Microsoft Word . Однако одно из главных отличий заключается в том, что в Excel рабочая область листа разбита на ячейки, каждая из которых имеет собственное имя. Имена ячеек могут быть двух видов.
- Первый вид (стиль A1) - это имя, состоящее из буквенного имени столбца и номера строки. Например, A1 - ячейка, находящаяся на пересечении столбца А (первого) и первой строки.
- Другой вид - это индексы ячеек (стиль именования R1C1 ). Для адресации ячейки в таком стиле указывают номер строки (R - Row - строка) и номер столбца (C - Column - столбец), на пересечении которых расположена ячейка. Строки изначально пронумерованы, а номера столбцов начинаются с 1 - первому столбцу соответствует столбец A , второму - В и т.д. Например, (2, 3) - это адрес ячейки, находящийся на пересечении второй строки и третьего столбца, то есть, если переложить это на стиль A1 - получим ячейку С2 (рис. 13.1 .)
Рис.
13.1.
Для выполнения большинства операций в MS Excel применяются следующие объекты.
- Excel.Application (Приложение) - объект, представляющий приложение Microsoft Excel, аналогичен Word.Application .
- Workbook ( Рабочая книга ) - представляет рабочую книгу - аналог документа Microsoft Word. Однако, в Word мы работаем с данными, расположенными в документе, а в Excel на пути к данным есть еще один объект - рабочий лист.
- Worksheet (Рабочий лист) - книга в MS Excel разбита на рабочие листы. Именно на листе расположены ячейки, которые могут хранить информацию и формулы.
- Range (Диапазон) - может быть представлен в виде одной ячейки или группы ячеек. Этот объект заменяет множество объектов для работы с элементами документов (character , word и т.д.), которые применяются в Microsoft Word. В результате работа с листом становится очень четкой и удобной - чтобы работать с какой-либо ячейкой, надо знать лишь ее имя (в формате A1) или адрес (R1C1 ).
- QueryTable (Таблица запросов) - этот объект используют для импорта в Microsoft Excel информации из баз данных. Подключение к базе данных , запрос информации и т.д. производятся средствами объекта, а итоги запроса выгружаются на лист MS Excel в виде обычной таблицы.
- PivotTable (Сводная таблица) - это особый вид электронной таблицы Excel - она позволяет в интерактивном режиме обобщать и анализировать большие объемы информации, в частности, взятой из базы данных.
- Chart (Диаграмма) - представляет собой диаграмму. Обычно их используют для визуализации данных.
Давайте начнем рассмотрение объектной модели MS Excel с объекта Application .
13.2. Объект Application
MsgBox Excel.Application.Name Листинг 13.1. Вывести имя приложения
После выполнения программы в окне сообщения отобразится имя приложения - в данном случае - Microsoft Excel . Очевидно, что свойство Name объекта Application возвращает имя приложения.
Теперь рассмотрим наиболее важные методы и свойства Application . Некоторые из них похожи на таковые в MS Word . Например, метод Quit точно так же, как в Word , закрывает приложение , свойство Visible отвечает за видимость окна программы и т.д.
13.3. Методы Application
13.3.1. Calculate - принудительный пересчет
Этот метод, вызываемый для объекта Application , позволяет пересчитать все открытия книги. Его же можно вызывать для отдельных книг (объект Workbook ) листов ( Worksheet ), ячеек и их диапазонов (Range ). Например, код из листинга 13.2 . позволяет пересчитать все открытые книги.
Application.Calculate Листинг 13.2. Пересчитать все открытые книги
13.3.2. GoTo - переход в ячейку
13-02-Excel GoTo.xlsm - пример к п. 13.3.2.
Позволяет выделить любой диапазон ячеек в любой книге, причем, если книга не активна - она будет активирована. Так же метод может запускать макросы Microsoft Excel.
Полный вызов метода выглядит так:
Goto(Reference, Scroll)
В качестве параметра Reference используется ссылка на ячейку или диапазон ячеек , которые должны быть выделены после вызова метода. Так же здесь может быть использовано имя макроса - тогда он будет запущен.
Параметр Scroll отвечает за "перемотку" листа Excel к выделенным ячейкам - так, чтобы левый верхний угол выделения совпадал бы с левым верхним углом отображаемой области листа. Если Scroll установлен в True - лист перематывается, если в False - нет.
Например, такой вызов (листинг 13.3 .) позволяет выделить ячейку H500 на активном листе.
Application.Goto _ Reference:=ActiveSheet.Range("H500"), _ Scroll:=True Листинг 13.3. Выделить ячейку H500
Как видите, обращение к активному листу очень напоминает обращение к активному документу в MS Word. Обратите внимание на то, что мы используем полное обращение к методу - Application.GoTo - как вы знаете, обычно свойства и методы объекта Application можно использовать в коде, не указывая этого объекта. Однако, если в этом случае не указать Application , то вместо метода GoTo программа попытается выполнить оператор безусловного перехода GoTo .
13.3.3. SendKeys - имитация нажатий на клавиши клавиатуры
13-03-Excel SendKeys.xlsm - пример к п. 13.3.3.
Очень интересный метод - позволяет передавать в активное окно приложения нажатия клавиш. Полный вызов метода выглядит так:
SendKeys(Keys, Wait)
Параметр Keys позволяет задавать клавиши, нажатия которых будут переданы приложению. Метод поддерживает эмуляцию как алфавитно-цифровых, так и управляющих клавиш , для которых применяется специальная кодировка. Алфавитно-цифровые клавиши указываются при вызове в своем обычном виде Например, для передачи символа "Ф" нужно указать его при вызове метода и т.д.
Чтобы передать приложению нажатия клавиши Backspace - используйте код {BS} . Для передачи нажатия кнопки Enter используйте значок ~ (
Элементы управления формы (Поле со списком, Флажок, Счетчик и др.) помогают быстро менять данные на листе в определенном диапазоне, включать и выключать опции, делать выбор и пр. В принципе, без них можно обойтись, но они делают управление данными на листе более наглядным и уменьшают вероятность ввода некорректных данных.
Для вставки элементов управления на лист необходимо отобразить вкладку Разработчик.
- В MS EXCEL 2007 это можно сделать через меню Кнопка офис/ Параметры Excel/ Основные/ Показывать вкладку Разработчик на ленте .
- В MS EXCEL 2010 это можно сделать так: Откройте вкладку Файл ; Нажмите кнопку Параметры ; Нажмите кнопку Настроить ленту ; Выберите команду Настройка ленты и в разделе Основные вкладки установите флажок Разработчик .
Теперь вставить элемент управления можно через меню: .
Обратите внимание, что в этом меню можно вставить Элементы ActiveX, которые расположены ниже интересующих нас Элементов управления формы. У обоих типов есть одни и те же элементы Кнопка, Список, Флажок и т.п. Разница между ними следующая: чтобы использовать Элементы ActiveX необходимо использовать VBA, а Элементы управления формы можно напрямую привязать к ячейке на листе.
Для тех, кто не ранее не работал с Элементами управления формы, советуем подробно ознакомиться с ними в следующих статьях:
В этой статье рассмотрим более сложный пример совместного использования элементов управления и .
Пример
Разберем конкретный пример применения сразу нескольких Элементов управления. В файле примера с помощью элементов управления показано как пользователь может отредактировать значения в таблице (диапазон F9:K12 ).
С помощью одного из 3-х элементов управления Поле со списком, Список и Счетчик , пользователь может выбрать столбец таблицы (год). Нужный элемент управления выбирается с помощью группы Переключателей . Название выбранного элемента подсвечивается серым цветом (см. A8:B8 на рис. выше). Выбранный год выделяется в таблице темно серым цветом (см. H9 :H12 на рис. выше). Отображение этого выделения регулируется Флажком (фон флажка - красный). Полосами прокрутки можно редактировать Цену и Количество в выбранном году, но только в определенном диапазоне. Теперь – подробнее.
Переключатели
Сначала вставим на лист элемент . Этим элементом мы будем включать и выключать выделение в таблице столбца выбранного года. Элемент свяжите с ячейкой $G$2 . Если флажок снят, то в этой ячейке будет ЛОЖЬ (этому значению соответствует 0), если установлен, то ИСТИНА (этому значению соответствует 1).
Для настройки выделим диапазон G9:K12 . Так как формула в Условном форматировании будет содержать , то убедимся, что после выделения диапазона активной ячейкой является G9 (т.е. диапазон надо выделять начиная именно с нее. Подсказкой служит поле Имя , находящееся слева от Строки формул . После выделения диапазона оно должно содержать G 9 ).
- вызовите инструмент (Главная/ Стили/ Условное форматирование/ Создать правило );
- выберите Использовать формулу для определения форматируемых ячеек;
- в поле «Форматировать значения, для которых следующая формула является истинной » введите =И(СТОЛБЕЦ(G9)=СмещГода+6;$G$2) Формула примет значение ИСТИНА, когда выполнится одновременно 2 условия:
- значение выражения (СмещГода (изменяется от 1 до 5 (т.е. от 2009 до 2013 года) + 6) совпадет с номером текущего столбца (7, т.е. 2009 год);
- Флажок Условное форматирование установлен.
- выберите требуемый формат, например, серый цвет заливки;
- нажмите ОК.
Тестируем
- убедимся, что флажок Условное форматирование установлен;
- выберем переключатель Список ;
- в элементе управления Список выберем 2010;
- убедимся, что столбец 2010 выделен серым;
- Полосой прокрутки изменим количество в столбце 2010.
Результат показан на рисунке.
К сожалению, у элементов управления формы Флажок, Поле со списком и Список нет возможности отформатировать отображаемый шрифт. Зато это можно сделать у элементов ActiveX (Разработчик/ Элементы управления/ Вставить ). Правда, для работы с этими элементами требуется писать программу на VBA.
Фризен Ирина Григорьевна – кандидат педагогических наук, преподаватель информационных технологий, автор многочисленных работ в сфере информационных технологий.
Настоящее учебное пособие написано в соответствии с государственной программой изучения дисциплины для средних специальных учебных заведений по специальности 230103 «Автоматизированные системы обработки информации и управления».
Офисное программирование
В пособии дается достаточный материал для проведения практических занятий по дисциплине, а также основной теоретический материал, необходимый при изучении того или иного вопроса. На основе предлагаемого материала можно строить проведение различных видов занятий.
Учебное пособие ставит перед собой цель помочь студентам изучить данную дисциплину в полном объеме, отвечающем требованиям государственного стандарта по дисциплине.
Предназначается для преподавателей и студентов средних специальных учебных заведений, а также может быть использовано студентами высших учебных заведений, изучающих данную дисциплину.
В учебном пособии рассматривается более 60 задач, сопровождаемых 130 рисунками и подробными объяснениями.
Книга:
Разделы на этой странице:
Использование объектов 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
Методы объекта 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 Значение левой части решаемого уравнения, не содержащей параметра
Точность, с которой находится корень и предельно допустимое число итераций, используемых для нахождения корня, устанавливается свойствами Maxchange и Maxiterations объекта Application. Например, определение корня с точностью до 0,0001 максимум за 1000 итераций устанавливается инструкцией:
With Application
Maxiterations = 1000
MaxChange = 0.0001
Вручную эти величины устанавливаются на вкладке Вычисления (Calculation) диалогового окна Параметры (Options), вызываемого командой Сервис, Параметры (Tools, Options).
Метод Sort
Сортировка позволяет выстраивать данные в лексикографическом порядке по возрастанию или убыванию. Метод sort осуществляет сортировку строк списков и баз данных, а также столбцов рабочих листов с учетом до трех критериев, по которым производится сортировка. Сортировка данных вручную совершается с использованием команды Данные, Сортировка (Data, Sort).
Синтаксис:
Объект. Sort(key1, order1, key2, order2, key3, order3, header, orderCustom, matchCase, orientaticn)
Аргументы:
Объект Диапазон, который будет сортироваться
Order1
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.
Рис. 92. Разработанная форма примера 41 в рабочем состоянии
Технология выполнения
1. Запустите приложение Excel, сохраните документ.
2. Перейдите в редактор VBA.
3. Создайте форму согласно приведенному рис. 92.
4. На листе Excel расположите необходимый текст (оформление), предусмотрев соответствующие ячейки вывода информации (рис. 93).
Рис. 93. Вывод результатов на лист excel после запуска формы примера 41
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)
Кнопка Закрыть
Private Sub CommandButton2_Click()
Процедура инициализации формы
Private Sub UserForm_initialize()
Worksheets(1).Visible = False
Использование методов AutoFill при заполнении таблиц
Пример 42 . Создать программу, которая по введенным текстовым данным в соотвествующие текстовые поля формы автоматизирует ввод данных на студентов некоторой специальности учебного заведения. Результаты заполнения текстовых полей выводятся на лист excel, что позволяет при необходимости распечатать данные.
