Опубликован: 25.03.2009 | Уровень: для всех | Доступ: платный | ВУЗ: Институт управления, бизнеса и права
Лекция 16:

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

< Лекция 15 || Лекция 16: 12 || Лекция 17 >
Аннотация: Лекция посвящена описанию некоторых дополнительных вопросов программирования в MS Excel. В частности, здесь мы рассмотрим использование формул, работу с документами MS Word из 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)"
Листинг 16.1. Запись формулы в ячейку

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

Dim num_F As Integer
    num_F = InputBox("Введите число")
    MsgBox ("Факториал числа " & num_F & " равен " & _
        WorksheetFunction.Fact(num_F))
Листинг 16.2. Использование формул MS Excel в коде VBA

В качестве аргументов функций можно использовать и объекты 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
Листинг 16.3. Работа с MS Word из MS Excel

Теперь напишем макрос в 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.4. Работа с MS Excel из MS Word
< Лекция 15 || Лекция 16: 12 || Лекция 17 >
Евгений Ушаков
Евгений Ушаков

Добрый день! Поясните, пожалуйста, 3000 р. стоит документ об окончании курса. Если он не нужен, то можно учиться бесплатно?

Александр Новиков
Александр Новиков

Добрый день! Очень нужен справочник по объектам VBA c описание их методов и свойств.

Кто-нибудь знает, где можно приобрести?

(Он конечно есть в VBA - Object browser, но не очень удобный)

Евгений Кленов
Евгений Кленов
Россия
Пизда Хуй
Пизда Хуй
Россия, Зимбабве