Дополнительные сведения о программировании для MS Excel

16.1. Вычисления и формулы

16-01-Formula.xlsm - пример к п. 16.1.

Как вы знаете, MS Excel поддерживает огромное количество формул. Однако, с их использованием в VBA есть одна небольшая сложность. В локализованной версии VBA, в частности, в русскоязычной, формулы, которые отображаются в ячейках, имеют русскоязычное написание. Например, такая формула: =сумм(A1:A10) посчитает сумму ячеек с A1 по A10. Чтобы передать ту же формулу в ячейку программно, нужно использовать ее англоязычное написание (листинг 16.1.)

Range("B1").Formula = "=sum(A1:A10)"

Как мы уже упоминали выше, есть особый объект - Application.WorksheetFunction - его методы представляют собой функции рабочего листа (более 250), которые можно использовать в коде VBA. Например, функция Fact вычисляет факториал переданного ей числа. Вот как выглядит работа с ней (листинг 16.2.)

Dim num_F As Integer
    num_F = InputBox("Введите число")
    MsgBox ("Факториал числа "  num_F  " равен "  _
        WorksheetFunction.Fact(num_F))

В качестве аргументов функций можно использовать и объекты Range - то есть диапазоны ячеек или ячейки. Таким образом, например, можно проводить все необходимые расчеты в VBA, а на лист выгружать лишь готовые значения, без выгрузки формул.

16.2. Работа с MS Excel из MS Word и наоборот

16-02-Excel to Word.xlsm и 16-03-Word to Excel.docm - примеры к п. 16.2.

Давайте рассмотрим взаимодействие различных приложений друг с другом. Сначала, мы займемся переносом данных из книги Microsoft Excel в документ Microsoft Word, затем - переносом данных в обратном направлении и использованием ресурсов Excel для проведения расчетов.

Напомню, что для такого взаимодействия нам понадобится подключить соответствующую объектную модель в редакторе VBA с помощью средства Tools o References.

Напишем макрос в MS Excel, который копирует содержимое выделенного диапазона в Microsoft Word, причем каждая строка диапазона собирается в одну текстовую строку, части которой, представляющие собой содержимое отдельных ячеек, разделяются запятыми и пробелами, а отдельные текстовые строки разделяются знаками перевода строки. Перед данными из таблицы выводится строка, содержащая информацию об имени книги и имени листа, откуда взята информация (листинг 16.3.)

'Переменная для хранения ссылки
    'на экземпляр MS Word
    Dim obj_Word As Word.Application
    'Для хранения ссылки на документ
    'MS Word
    Dim obj_WDoc As Word.Document
    'Для хранения ссылки на лист
    'книги Excel
    Dim obj_ESheet As Worksheet
    'Для хранения ссылки на
    'выделеный диапазон
    Dim obj_Range As Range
    'Для хранения ссылки на
    'книгу MS Excel
    Dim obj_Excel As Workbook
    'Для строки формирования вывода
    Dim str_Str As String
    'Записываем в переменные
    'ссылки на соответствующие им
    'объекты
    Set obj_Range = Selection
    Set obj_ESheet = ActiveSheet
    Set obj_Excel = ActiveWorkbook
    'Запускаем экземпляр MS Word
    Set obj_Word = New Word.Application
    'Делаем его видимым
    obj_Word.Visible = True
    'Создаем новый документ
    Set obj_WDoc = obj_Word.Documents.Add
    'Активируем документ
    obj_WDoc.Activate
    'Собираем строку для вывода информации
    'об имени книги и листа
    str_Str = "Скопировано из книги " + _
        obj_Excel.Name + ", с листа " + _
        obj_ESheet.Name
    'Выводим строку в документ
    obj_Word.Selection.TypeText (str_Str)
    obj_Word.Selection.TypeParagraph
    'Очищаем строку
    str_Str = ""
    'Цикл для построчного прохода
    'выделенного диапазона и сбора
    'строк для вывода
    For i = 1 To obj_Range.Rows.Count
        For j = 1 To obj_Range.Columns.Count
            str_Str = str_Str  obj_Range.Cells(i, j)  ", "
        Next j
        'Удаляем из строки последних 2 символа
        str_Str = Mid(str_Str, 1, Len(str_Str) - 2)
        'Активируем документ Word
        obj_WDoc.Activate
        'Выводим текст и очищаем переменную
        obj_Word.Selection.TypeText (str_Str)
        obj_Word.Selection.TypeParagraph
        str_Str = ""
    Next i

Теперь напишем макрос в MS Word, выполняющий заявленные выше функции. Мы перенесем на лист MS Excel выделенный текст, разбив его на слова, после чего вычислим сумму нескольких чисел, используя ресурсы MS Excel. При этом слова будут скопированы каждое в отдельную ячейку, а весь этот материал будет размещен в таблице шириной 10 ячеек и высотой, которая зависит от количества слов в выделенном тексте (листинг 16.4.).

'Объектные переменные для MS Excel
    Dim obj_Excel As Excel.Application
    'Для книги
    Dim obj_Workbook As Excel.Workbook
    'Для листа
    Dim obj_Worksheet As Excel.Worksheet
    'Для текста в MS Word
    Dim obj_Range As Word.Range
    'Для цикла, выгружающего слова на
    'лист
    Dim num_Counter
    'Для подсчета номера очередного слова
    Dim num_Words
    'Присваиваем переменной ссылку
    'на выделенную область
    Set obj_Range = Selection.Range
    'Запустим MS Excel
    Set obj_Excel = New Excel.Application
    'Добавим в Excel новую книгу
    Set obj_Workbook = obj_Excel.Workbooks.Add
    'Присвоим переменной ссылку на первый лист книги
    Set obj_Worksheet = obj_Workbook.Worksheets(1)
    'Вычислим результат от деления количества слов
    'нацело - то есть - сколько полных строк
    'по 10 слов удастся выделить из нашего текста
    num_Counter = obj_Range.Words.Count \ 10
    'Цикл выгрузки
    For i = 1 To num_Counter
        For j = 1 To 10
            'Увеличиваем на 1 номер
            'выводимого слова
            num_Words = num_Words + 1
            'выводим это слово на лист
            obj_Worksheet.Cells(i, j) = _
                obj_Range.Words.Item(num_Words)
        Next j
    Next i
    'Помимо полных строк по 10 слов
    'может оказаться так, что некоторые
    'слова образуют неполную строку
    'вычислим ее длину
    num_Counter = obj_Range.Words.Count - _
       (obj_Range.Words.Count \ 10) * 10
    'Цикл выгрузки последних слов
    For j = 1 To num_Counter
        num_Words = num_Words + 1
        obj_Worksheet.Cells(i, j) = _
            obj_Range.Words.Item(num_Words)
    Next j
    'Настроим ширину столбцов, содержащих
    'выгруженные слова
    obj_Worksheet.Columns("A:J").AutoFit
    'Теперь - вычисления
    MsgBox ("Сумма чисел 1,2,3,4 равна "  _
        obj_Excel.WorksheetFunction.Sum(1, 2, 3, 4))
    MsgBox ("Данные выгружены в книгу " + _
        obj_Workbook.Name)
        'В конце работы отобразим
        'MS Excel, до этого скрытый
    obj_Excel.Visible = True

16.3. Работа с базами данных

В примерах этого раздела используется файл Database.accdb, который должен быть расположен в корневом каталоге диска C.

Для работы с базами данных могут быть использованы различные инструменты. Одним из распространенных инструментов такого взаимодействия являются QueryTable - таблицы, которые отображают информацию, полученную из базы данных.

16.3.1. OpenDatabase и QueryTable

16-04-Excel OpenDatabase.xlsm - пример к п. 16.3.1.

Самый простой и доступный способ импортировать информацию из базы данных в Microsoft Excel, это - воспользоваться специальным методом рабочего листа. Речь идет о методе OpenDatabase. Он предназначен для создания новой книги, которая содержит лист с информацией, полученной из базы данных. Получение информации из базы данных в Excel может быть полезным, например, для анализа этой информации средствами Excel.

Полный вызов метода выглядит так:

OpenDatabase(Filename, CommandText, CommandType, BackgroundQuery, ImportDataAs)

Рассмотрим параметры метода.

Чтобы рассмотреть пример использования этой команды, создадим простую базу данных, состоящую из двух таблиц. Первая таблица представляет собой список клиентов, вторая - список их покупок, где учитывается лишь сумма покупки на определенную дату. Таблица клиентов имеет имя Клиенты, таблица покупок - имя Покупки. Импортируем с помощью метода OpenDatabase таблицу Покупки в документ MS Excel. Предположим, что база данных хранится на диске C:, ее имя - Database.accdb. Добавим на лист MS Excel кнопку, содержащую такой код (листинг 16.5.)

Workbooks.OpenDatabase _
        Filename:="C:\Database.accdb", _
        CommandText:="Покупки", _
        CommandType:=xlCmdTable, _
        BackgroundQuery:=True, _
        ImportDataAs:=xlQueryTable

После нажатия на кнопку будет создана новая книга, лист которой, названный по имени базы данных, будет содержать импортированные данные (рис. 16.1.).

Чтобы импортировать данные как PivotTable, нам понадобится такой код (листинг 16.6.) - его мы добавим в обработчик события Click другой кнопки на рабочем листе книги-примера.

Workbooks.OpenDatabase _
        Filename:="C:\Database.accdb", _
        CommandText:="Покупки", _
        CommandType:=xlCmdTable, _
        BackgroundQuery:=True, _
        ImportDataAs:=xlPivotTableReport

На рис. 16.2. вы можете видеть результат выполнения команды - сводную таблицу, с которой можно продолжать дальнейшую работу.

Теперь рассмотрим еще один метод получения информации из БД.

16.3.2. ADO

16-05-Excel ADODB Query.xlsm - пример к п. 16.3.2.

QueryTable можно добавить на рабочий лист, предварительно настроив ее параметры.

Объекты QueryTable объединены в коллекцию QueryTables. Важнейший метод этой коллекции - Add - он добавляет новую таблицу в указанную позицию на листе. Вызов метода Add выглядит так:

WorkBook.QueryTables.Add(Connection, Destination)

В качестве параметра Connection обычно используют объект ADODB.Recordset, о котором ниже, а Destination - это объект Range, который указывает на диапазон (или ячейку), куда будет добавлена QueryTable. Если в Destination задана ячейка, левая верхняя ячейка вставляемой таблицы таблицы совпадет с ячейкой.

Для работы с базами данных используется объектная модель ADO. Чтобы подключить ее к проекту, выберите в окне References пункт Microsoft ActiveX Data Object 2.8 Library - обращаться к ней можно, используя имя объекта ADODB.

ADO - это очень мощный механизм для доступа к источникам данных. Здесь мы рассмотрим методику получения информации из БД с использованием ADO. Нас будут интересовать несколько ключевых объектов ADO.

Во-первых - это объект ADODB.Connection, который позволяет установить соединение с базой данных и работать с ней. У объекта Connection есть свойство ConnectionString - оно представляет собой строку, содержащие параметры подключения к базе данных, в частности - адрес файла базы данных и имя драйвера. Метод Open объекта Connection используется для открытия соединения, заданного свойством ConnectionString.

Во-вторых - объект ADODB.RecordSet - он позволяет получать из открытой базы данных определенные порции информации.

Для получения данных используется метод объекта Open, которому передается запрос на получение данных, а так же - открытое соединение.

Давайте рассмотрим пример. Здесь мы подключаемся к базе данных и создаем Query Table на основе объекта RecordSet, в котором хранится информация, полученная из базы (листинг 16.7.).

'Для хранения ссылки на
    'Query Table
    Dim obj_Query As QueryTable
    'Для ссылки на соединение с
    'базой данных
    Dim obj_ADOConn As ADODB.Connection
    'Для ссылки на набор записей,
    'полученный из БД
    Dim obj_ADORec As ADODB.Recordset
    'Если в ячейке A5 есть данные     'значит мы уже вставляли сюда Query Table
    'если данных нет - начинаем работу с БД
    If ActiveSheet.Range("A5") <> "" Then
        MsgBox "Уже есть Query Table в этом диапазоне"
    Else
    'Создаем новое соединение
    Set obj_ADOConn = New ADODB.Connection
    'Вносим в ConnectionString параметры
    'соединения. В Provider - имя драйвера,
    'который используется для доступа к
    'БД, а так же - параметр Data Source
    'который отвечает за адрес источника данных
    obj_ADOConn.ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;"  _
        "Data Source=C:\Database.accdb"
    'Подключаемся к базе данных
    obj_ADOConn.Open
    'Создаем новый объект RecordSet     'Он хранит результат запроса к БД
    Set obj_ADORec = New ADODB.Recordset
    'Выполняем запрос
    'В параметре Source хранится строка
    'SQL-запроса
    'В ActiveConnnection - открытое соединение
    obj_ADORec.Open _
        Source:="SELECT * FROM Покупки", _
        ActiveConnection:=obj_ADOConn
    'Создаем новую Query Table, в качестве
    'источника данных передаем заполненный
    'данными RecordSe
    Set obj_Query = _
        ActiveSheet.QueryTables.Add _
        (obj_ADORec, Range("A5"))
    'Используем метод Refresh для того
    'чтобы таблица, заполненная данными
    'была отображена на листе
    obj_Query.Refresh
    End If

Если вы хотите эффективно работать с базами данных - вам придется научиться строить SQL-запросы, изучить особенности взаимодействия с различным видами БД и так далее.

16.4. Работа с диаграммами

16-06-Excel Chart.xlsm - пример к п. 16.4.

Для работы с диаграммами используют объект Chart. Чтобы добавить диаграмму на лист можно применить методом AddChart коллекции Shapes..

Такой код (листинг 16.8.) добавляет диаграмму на активный лист:

ActiveSheet.Shapes.AddChart

Когда диаграмма добавлена, можно настроить ее свойства, в частности, при помощи метода SetSourceData задать диапазон (объект типа Range ), содержащий информацию, которая должна быть визуализирована. Этот метод принимает два параметра. Первый - Source - отвечает за источник данных, второй - PlotBy - определяет, как берутся данные для диаграммы - по столбцам ( xlColumns ) или по строкам ( xlRows ).

Так же после добавления диаграммы обычно настраивают ее тип - это делается с помощью свойства CharType. Оно может принимать одно из более чем 70 значений типа xlChartType. Например, xlConeCol - это трехмерная коническая диаграмма, xlPie - круговая диаграмма, xlLineMarkers - график с маркерами.

Рассмотрим пример (листинг 16.9.). Добавим на рабочий лист обычную линейную диаграмму, используя диапазон значений, выделенных пользователем.

'Для хранения ссылки
    'на диаграмму
    Dim obj_Chart As Chart
    'Для хранения ссылки на
    'диапазон входных значений
    Dim obj_Range As Range
    Set obj_Range = Selection
    'Добавляем новую диаграмму и
    'тут же выделяем ее
    ActiveSheet.Shapes.AddChart.Select
    Set obj_Chart = ActiveChart
    'Настраиваем исходные данные
    'для диаграммы
    obj_Chart.SetSourceData _
        Source:=obj_Range, _
        PlotBy:=xlRows
    'Устанавливаем тип для диаграммы
    obj_Chart.ChartType = xlLine

16.5. Выводы

В этой лекции мы рассмотрели некоторые дополнительные возможности программирования для MS Excel. Наше следующее занятие посвящено практическим примерам программирования для MS Excel.