11.2 Объект Application
Объект Excel.Application, запуск нового экземпляра Excel из VBA, события объекта Excel.Application
Dim oExcel As New Excel.Application
Точно так же, как и в Word, если вы работаете из уже запущенного Excel, создавать объект Application вам не потребуется. Он будет доступен всегда. Если вы обращаетесь к какому-либо свойству без указания вышестоящего объекта, то редактор Visual Basic в Excel будет считать, что вы обращаетесь к свойству объекта Application. Поэтому эти две строки кода в Excel равнозначны:
Вообще объекты Application в большинстве приложений Office очень похожи, и к ним применяются те же соображения, что для объекта Word.Application. Точно так же многие разработчики считают, что удобнее и надежнее работать со скрытым окном Excel, точно так же открывать новый экземпляр Excel удобнее, чем разыскивать экземпляр, уже открытый пользователем. Для того, чтобы в окне редактора кода для форм появился объект Application, точно так же необходимо в разделе Declarations кода формы объявить объект Application с ключевым словом WithEvents, например, так:
Public WithEvents App As Excel.Application
В этом случае в окне редактора кода для форм у вас появится новый объект App, и вы сможете использовать событийные процедуры объекта Application (см. рис. 11.1)
Рис. 11.1 В списке объектов появился новый объект App со своим набором событий
Метод Application.Evaluate (Excel)
Преобразует имя Microsoft Excel в объект или значение.
Синтаксис
выражения. Оценка (Имя)
выражение: переменная, представляющая объект Application.
Параметры
| Имя | Обязательный или необязательный | Тип данных | Описание |
|---|---|---|---|
| Name | Обязательный | Variant | Формула или имя объекта с использованием конвенции именования Microsoft Excel. Длина имени должна быть меньше или равна 255 символам. |
Возвращаемое значение
Примечания
Следующие типы имен в Microsoft Excel можно использовать с помощью этого метода:
Формулы.
Ссылки в стиле A1. Вы можете использовать любую ссылку на одну ячейку в нотации в стиле A1. Все ссылки считаются абсолютными ссылками.
Диапазоны. Вы можете использовать диапазон, интерсект и операторы профсоюзов (двоеточие, пространство и запятая соответственно) со ссылками.
Определенные имена. Можно указать любое имя на языке макроса.
Объекты диаграммы. Вы можете указать любое имя объекта диаграммы, например Legend, Plot Area или Series 1, чтобы получить доступ к свойствам и методам этого объекта. Например, Charts(«Chart1»).Evaluate(«Legend»).Font.Name возвращает имя шрифта, используемого в легенде.
Использование квадратных скобок (например, «[A1:C5]») идентично вызову метода Оценка с аргументом строки. Например, следующие пары выражений эквивалентны.
Преимущество использования квадратных скобок заключается в том, что код короче. Преимущество использования Оценки заключается в том, что аргумент — это строка, поэтому можно либо создать строку в коде, либо использовать переменную Visual Basic.
Пример
В этом примере включается жирный форматирование в ячейке A1 на листе1.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Excel миф excel application
Объект Excel.Application, свойства, методы и события
Многие свойства, методы и события объекта Excel.Application совпадают с аналогичными в Word.Application. Однако поскольку информация приводится здесь для справки для тех пользователей, которым трудно свободно читать по английски, здесь будут приведены наиболее часто используемые свойства и метода объекта Application в Excel, вне зависимости от того, встречались ли они нам в Word, или нет.
Вначале — о свойствах объекта Application:
В данном случае мы выделили полужирным ячейку на пересечении первой строки и второго столбца.
а для строки (второй) можно воспользоваться очень похожей командой
Еще раз отметим, что свойства Cells, Columns и Rows возвращают вовсе не наборы объектов Cell, Column и Row, как считают многие пользователи, а наборы объектов Range. На использовании объекта Range построена в Excel почти вся работа с ячейками и их значениями. Ниже в этой главе объекту Range будет посвящен отдельный раздел.
Похожий пример с возможностью выбрать сразу несколько файлов приведен в справке по этому свойству.
Самые важные методы объекта Excel.Application перечислены ниже:
Эту функцию можно вызвать и неявно — просто заключив имя объекта в квадратные скобки. Например, такие строки будут абсолютно одинаковыми по значению:
Поскольку синтаксис с квадратными скобками короче, чаще всего используется именно он.
Таким образом, метод Evaluate() — это самый простой и естественный метод обратиться к ячейке или диапазону в своей или чужой книге Excel.
If Filename <> False Then
If Application.Wait(Now + TimeValue(«0:00:5»)) Then
MsgBox «Пять секунд прошло»
Excel. Application class
Представляет приложение Excel, которое управляет книгой.
Комментарии
Свойства
Возвращает версию модуля вычислений Excel, использованного для последнего полного пересчета.
Возвращает состояние вычисления приложения. Подробные Excel.CalculationState сведения см. в материале.
Контекст запроса, связанный с объектом. Это связывает процесс надстройки с процессом Office хост-приложения.
Возвращает параметры итеративных вычислений. В Excel на Windows Mac параметры будут применяться к приложению Excel. В Excel в Интернете и других платформах параметры будут применяться к активной книге.
Методы
Пересчитывает данные во всех открытых в текущий момент книгах Excel.
Пересчитывает данные во всех открытых в текущий момент книгах Excel.
Задает несколько свойств объекта одновременно. Можно передать либо обычный объект с соответствующими свойствами, либо другой объект API того же типа.
Задает несколько свойств на объекте одновременно на основе существующего загруженного объекта.
Приостанавливать вычисление, пока не context.sync() будет вызван следующий. После этого за пересчет книги и распространение всех зависимостей несет ответственность разработчик.
Приостанавливать обновление экрана до context.sync() следующего.
Примечание*: Не звони suspendScreenUpdatingUntilNextSync несколько раз (например, в цикле). Повторные вызовы при Excel окне.
Сведения о свойстве
calculation Engine Version
Возвращает версию модуля вычислений Excel, использованного для последнего полного пересчета.
Автоматизация рутины в Microsoft Excel при помощи VBA
В этом посте я расскажу, что такое VBA и как с ним работать в Microsoft Excel 2007/2010 (для более старых версий изменяется лишь интерфейс — код, скорее всего, будет таким же) для автоматизации различной рутины.
VBA (Visual Basic for Applications) — это упрощенная версия Visual Basic, встроенная в множество продуктов линейки Microsoft Office. Она позволяет писать программы прямо в файле конкретного документа. Вам не требуется устанавливать различные IDE — всё, включая отладчик, уже есть в Excel.
Еще при помощи Visual Studio Tools for Office можно писать макросы на C# и также встраивать их. Спасибо, FireStorm.
Сразу скажу — писать на других языках (C++/Delphi/PHP) также возможно, но требуется научится читать, изменять и писать файлы офиса — встраивать в документы не получится. А интерфейсы Microsoft работают через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием COM.
Поэтому, увы, будем учить Visual Basic.
Чуть-чуть подготовки и постановка задачи
Итак, поехали. Открываем Excel.
Для начала давайте добавим в Ribbon панель «Разработчик». В ней находятся кнопки, текстовые поля и пр. элементы для конструирования форм.
Теперь давайте подумаем, на каком примере мы будем изучать VBA. Недавно мне потребовалось красиво оформить прайс-лист, выглядевший, как таблица. Идём в гугл, набираем «прайс-лист» и качаем любой, который оформлен примерно так (не сочтите за рекламу, пожалуйста):
То есть требуется, чтобы было как минимум две группы, по которым можно объединить товары (в нашем случае это будут Тип и Производитель — в таком порядке). Для того, чтобы предложенный мною алгоритм работал корректно, отсортируйте товары так, чтобы товары из одной группы стояли подряд (сначала по Типу, потом по Производителю).
Результат, которого хотим добиться, выглядит примерно так:
Разумеется, если смотреть прайс только на компьютере, то можно добавить фильтры и будет гораздо удобнее искать нужный товар. Однако мы хотим научится кодить и задача вполне подходящая, не так ли?
Кодим
Для начала требуется создать кнопку, при нажатии на которую будет вызываться наша програма. Кнопки находятся в панели «Разработчик» и появляются по кнопке «Вставить». Вам нужен компонент формы «Кнопка». Нажали, поставили на любое место в листе. Далее, если не появилось окно назначения макроса, надо нажать правой кнопкой и выбрать пункт «Назначить макрос». Назовём его FormatPrice. Важно, чтобы перед именем макроса ничего не было — иначе он создастся в отдельном модуле, а не в пространстве имен книги. В этому случае вам будет недоступно быстрое обращение к выделенному листу. Нажимаем кнопку «Новый».
И вот мы в среде разработки VB. Также её можно вызвать из контекстного меню командой «Исходный текст»/«View code».
Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так:
Напишем Hello World:
Sub FormatPrice()
MsgBox «Hello World!»
End Sub
И запустим либо щелкнув по кнопке (предварительно сняв с неё выделение), либо клавишей F5 прямо из редактора.
Тут, пожалуй, следует отвлечься на небольшой ликбез по поводу синтаксиса VB. Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.
Примеры синтаксиса
Dim res As sTRING ‘ Регистр в VB не важен. Впрочем, редактор Вас поправит
Dim i As Integer
‘ Цикл всегда состоит из нескольких строк
For i = 1 To 10
res = res + CStr(i) ‘ Конвертация чего угодно в String
If i = 5 Then Exit For
Next i
Dim x As Double
x = Val( «1.234» ) ‘ Парсинг чисел
x = x + 10
MsgBox x
On Error GoTo Err ‘ При ошибке перейти к метке Err
x = 5 / 0
MsgBox «OK!»
GoTo ne
ne:
On Error GoTo 0 ‘ Отключаем обработку ошибок
‘ Циклы бывает, какие захотите
Do While True
Exit Do
Loop ‘While True
Do ‘Until False
Exit Do
Loop Until False
‘ А вот при вызове функций, от которых хотим получить значение, скобки нужны.
‘ Val также умеет возвращать Integer
Select Case LengthSqr(Len( «abc» ), Val( «4» ))
Case 24
MsgBox «0»
Case 25
MsgBox «1»
Case 26
MsgBox «2»
End Select
Грабли-1. При копировании кода из IDE (в английском Excel) есь текст конвертируется в 1252 Latin-1. Поэтому, если хотите сохранить русские комментарии — надо сохранить крокозябры как Latin-1, а потом открыть в 1251.
Грабли-2. Т.к. VB позволяет использовать необъявленные переменные, я всегда в начале кода (перед всеми процедурами) ставлю строчку Option Explicit. Эта директива запрещает интерпретатору заводить переменные самостоятельно.
Грабли-3. Глобальные переменные можно объявлять только до первой функции/процедуры. Локальные — в любом месте процедуры/функции.
Еще немного дополнительных функций, которые могут пригодится: InPos, Mid, Trim, LBound, UBound. Также ответы на все вопросы по поводу работы функций/их параметров можно получить в MSDN.
Надеюсь, что этого Вам хватит, чтобы не пугаться кода и самостоятельно написать какое-нибудь домашнее задание по информатике. По ходу поста я буду ненавязчиво знакомить Вас с новыми конструкциями.
Кодим много и под Excel
В этой части мы уже начнём кодить нечто, что умеет работать с нашими листами в Excel. Для начала создадим отдельный лист с именем result (лист с данными назовём data). Теперь, наверное, нужно этот лист очистить от того, что на нём есть. Также мы «выделим» лист с данными, чтобы каждый раз не писать длинное обращение к массиву с листами.
Sub FormatPrice()
Sheets( «result» ).Cells.Clear
Sheets( «data» ).Activate
End Sub
Работа с диапазонами ячеек
Вся работа в Excel VBA производится с диапазонами ячеек. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range.
Примеры работы с Range
Sheets( «result» ).Activate
Dim r As Range
Set r = Range( «A1» )
r.Value = «123»
Set r = Range( «A3,A5» )
r.Font.Color = vbRed
r.Value = «456»
Set r = Range( «A6:A7» )
r.Value = «=A1+A3»
Теперь давайте поймем алгоритм работы нашего кода. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:
Для упрощения работы рекомендую определить следующие функции-сокращения:
Function GetCol(Col As Integer ) As String
GetCol = Chr(Asc( «A» ) + Col)
End Function
Далее определим глобальную переменную «текущая строчка»: Dim CurRow As Integer. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная-«текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста».
Глобальные переменные
Option Explicit ‘ про эту строчку я уже рассказывал
Dim CurRow As Integer
Const GroupsCount As Integer = 2
Const DataCount As Integer = 3
FormatPrice
Sub FormatPrice()
Dim I As Integer ‘ строка в data
CurRow = 1
Dim Groups(1 To GroupsCount) As String
Dim PrGroups(1 To GroupsCount) As String
Теперь надо заполнить массив Groups:
На месте многоточия
И создать заголовки:
На месте многоточия в предыдущем куске
For I2 = 1 To GroupsCount
If Groups(I2) <> PrGroups(I2) Then
Dim I3 As Integer
For I3 = I2 To GroupsCount
AddHeader I3, Groups(I3)
Next I3
Exit For
End If
Next I2
Не забудем про процедуру AddHeader:
Перед FormatPrice
Теперь надо перенести всякую информацию в result
Подогнать столбцы по ширине и выбрать лист result для показа результата
После цикла в конце FormatPrice
Sheets( «Result» ).Activate
Columns.AutoFit
Всё. Можно любоваться первой версией.
Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:
Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:
Поэтому чуть-чуть меняем код с добавлением стиля границ:
Select Case Ty
Case 1 ‘ Тип
.Font.Bold = True
.Font.Size = 16
.Borders(xlTop).Weight = xlThick
Case 2 ‘ Производитель
.Font.Size = 12
.Borders(xlTop).Weight = xlMedium
End Select
.Borders(xlBottom).Weight = xlMedium ‘ По убыванию: xlThick, xlMedium, xlThin, xlHairline
End With
CurRow = CurRow + 1
End Sub
Осталось лишь добится пропусков перед началом новой группы. Это легко:
В начале FormatPrice
Dim I As Integer ‘ строка в data
CurRow = 0 ‘ чтобы не было пропуска в самом начале
Dim Groups(1 To GroupsCount) As String
В цикле расстановки заголовков
If Groups(I2) <> PrGroups(I2) Then
CurRow = CurRow + 1
Dim I3 As Integer
В точности то, что и хотели.
Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки «ID, Название, Цена» в результат. Подсказка: CurRow = 0 CurRow = 1.
Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. Если кто-нибудь подскажет человеческих файлохостинг, залью туда.
Спасибо за внимание.
Буду рад конструктивной критике в комментариях.
UPD: Перезалил пример на Dropbox и min.us.
UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.