- •Учреждение образования "Гродненский государственный профессионально-технический колледж приборостроения"
- •1. Синтаксис и программные конструкции vba
- •1.1 Основы синтаксиса языка vba
- •1.2 Операторы vba
- •1.3 Переменные и типы данных
- •Задание для самостоятельной работы 1.1: Работа с переменными и операторами
- •1.4 Константы
- •1.5 Операторы условного и безусловного перехода
- •1.5.1 Операторы условного и безусловного перехода. Оператор If… Then... Else
- •1.5.2 Оператор Select Case
- •1.5.3 Оператор GoTo
- •Задание для самостоятельной работы 1.2: Работа с операторами условного перехода
- •1.6 Работа с циклами
- •1.7 Массивы
- •Задание для самостоятельной работы 1.3 Работа с циклами
- •1.8 Процедуры и функции
- •1.8.1 Процедуры и функции. Виды процедур
- •1.8.2 Область видимости процедур
- •1.8.3 Объявление процедур
- •1.8.4 Передача параметров
- •1.8.5 Запуск и завершение работы процедур
- •Задание для самостоятельной работы 1.4 Работа с процедурами и функциями
- •1.9 Встроенные функции языка Visual Basic For Applications
- •1.9.1 Встроенные функции языка Visual Basic For Applications
- •1.9.2 Функции преобразования и проверки типов данных
- •1.9.3 Строковые функции
- •1.9.4 Функции для работы с числовыми значениями
- •1.9.5 Функции для работы с датой и временем
- •1.9.6 Функции для форматирования данных
- •1.9.7 Функции для организации взаимодействия с пользователем
- •1.9.8 Функции — заменители синтаксических конструкций
- •1.9.9 Функции для работы с массивами
- •1.9.10 Функции для работы с файловой системой
- •1.9.11 Прочие функции vba
- •2. Работа с объектами и объектные модели
- •2.1 Что такое классы и объекты
- •2.2 Создание и удаление объектов
- •2.3 Методы объекта
- •2.4 Свойства объекта
- •2.5 События объекта и объявление WithEvents
- •2.6 Просмотр объектов
- •2.7 Объектные модели
- •Задание для самостоятельной работы 2.1 Windows Script Host в приложениях vba
- •3. Формы, элементы управления и события
- •3.1 Для чего нужны формы
- •3.2 Создание форм и самые важные свойства и методы форм
- •3.3 Элементы управления
- •3.3.1 Что такое элементы управления
- •3.3.2 Элемент управления Label (надпись)
- •3.3.3 Элемент управления TextBox (текстовое поле)
- •3.3.4 Элемент управления ComboBox (комбинированный список)
- •3.3.5 Элемент управления ListBox (список)
- •3.3.6 Элементы управления CheckBox (флажок) и ToggleButton (кнопка с фиксацией)
- •3.3.7 Элементы управления OptionButton (переключатель) и Frame (рамка)
- •3.3.8 Элемент управления CommandButton (кнопка)
- •3.3.9 Элементы управления ScrollBar (полоса прокрутки) и SpinButton (счетчик)
- •3.3.10 Элементы управления TabStrip (набор вкладок) и MultiPage (набор страниц)
- •3.3.11 Элемент управления Image (рисунок)
- •3.3.12 Применение дополнительных элементов управления. Элементы управления Microsoft Web Browser, Calendar, RefEdit
- •Задание для самостоятельной работы 3.1 Работа с элементами управления
- •4. Работа с панелями инструментов и меню
- •Задание для самостоятельной работы 4.1 Работа с панелями инструментов, меню и помощником
- •5. Программирование в Word
- •5.1 Зачем программировать в Word
- •5.2 Введение в программирование в Word. Обзор объектной модели Word
- •5.3 Объект Application
- •5.3.1 Как работать с объектом Application
- •5.3.2 Свойства, методы и события объекта Application
- •5.4 Коллекция Documents и объекты Document
- •5.4.1 Как работать с коллекцией Documents
- •5.4.2 Свойства и методы коллекции Documents
- •5.4.3 Работа с объектом Document , его свойства и методы
- •5.5 Объекты Selection , Range и Bookmark
- •5.5.1 Работа с объектом Selection
- •5.5.2 Свойства и методы объекта Selection
- •5.5.3 Работа с объектом Range, его свойства и методы
- •5.5.4 Объект Bookmark
- •5.6 Другие объекты Word
- •5.6.1 Коллекция AddIns и объекты AddIn
- •5.6.2 Объект AutoCorrect
- •5.6.3 Коллекция Languages и объект Language
- •5.6.4 Объект Options
- •5.6.5 Объекты Find и Replacement
- •5.6.6 Объекты Font и ParagraphFormat
- •5.6.7 Объект PageSetup
- •5.6.8 Объекты Table , Column, Rowи Cell
- •5.6.9 Объект System
- •5.6.10 Коллекция Tasks и объект Task
- •5.6.11 Коллекция Windows и объект Window
- •Задание для самостоятельной работы 5.1 Программное формирование документа в Word
- •6. Программирование в Excel
- •6.1 Зачем программировать в Excel
- •6.2 Объект Application
- •6.3 Свойства и методы объекта Application
- •6.4 Коллекция Workbooks и объект Workbook, их свойства и методы
- •6.5 Коллекция Sheets и объект Worksheet , их свойства и методы
- •6.6 Объект Range, его свойства и методы
- •6.7 Коллекция QueryTables и объект QueryTable
- •6.8 Работа со сводными таблицами (объект PivotTable)
- •6.9 Работа с диаграммами: объект Chart
- •6.10 Другие объекты Excel
- •Задание для самостоятельной работы 6.1 Применение Excel для анализа информации из базы данных
- •7. Программирование в Access
- •7.1 Отличительные особенности создания приложений Access
- •7.2 Основные этапы создания приложений Access
- •7.3 Объект Application , его свойства и методы
- •7.4 Макрокоманды и объект DoCmd
- •7.5 Работа с формами Access из vba (объект Form )
- •7.6 Свойства, методы и события форм
- •7.7 Работа с отчетами (объект Report )
- •7.8 Другие объекты Access
- •Задание для самостоятельной работы 7.1 Создание приложения vba в Access
- •8. Программирование в PowerPoint
- •Задание для самостоятельной работы 8.1 Программное добавление элементов в слайды
6.9 Работа с диаграммами: объект Chart
Объект Excel.Chart, программная работа с диаграммами средствами VBA, выбор типа диаграммы, добавление рядов
Одно из основных применений Excel — это анализ данных. А для анализа данных часто удобно использовать диаграммы с их специальными возможностями, такими, как тренды. На практике задачи по автоматизации создания множества похожих друг на друга диаграмм (обычно на основе информации, полученной из базы данных) возникают очень часто.
С диаграммами в Excel существует некоторая терминологическая путаница. То, что на графическом интерфейсе русского Excel называется диаграммой (меню Вставка -> Диаграмма), по английски называется графиком (Chart) и ему соответствует объект Chart. В объектной модели Excel предусмотрен также и объект Diagram, но он представляет скорее схему отношений (то, что при помощи графического интерфейса русского Excel можно добавить при помощи меню Вставка -> Схематическая диаграмма). Под диаграммой в этом разделе будет пониматься то же, что и у создателей русского Excel — график.
Диаграммы в Excel создаются при помощи объекта Chart.
Лучше всего вначале этот объект объявить:
Dim oChart As Chart
Дальше можно создавать диаграмму. Производится создание диаграммы при помощи много раз использованного нами приема — вызова метода Add() коллекции Charts:
Set oChart = ActiveWorkbook.Charts.Add(, ActiveSheet)
В принципе, диаграмма уже создана, но, поскольку никакие ее свойства не определены, она выглядит просто как пустой лист. Чтобы она обрела содержание, необходимо выполнить еще несколько действий:
Первое (и единственное обязательное действие) — определить источник данных для диаграммы, для чего предназначен метод SetSourceData(). В качестве источника может выступать только объект Range (он передается в качестве первого и единственного обязательного параметра этого метода). Второй параметр (необязательный) определяет, в каком порядке считывать данные — сначала по столбцам или сначала по строкам. Например, в нашем случае это может выглядеть так:
oChart.SetSourceData(Sheets("Лист1").Range("A1:A10"))
В принципе, если запустить созданный код на выполнение, то диаграмма уже будет создана. Для всех остальных параметров будут приняты значения по умолчанию. Однако на практике нужно определить еще хотя бы тип диаграммы (по умолчанию она будет выглядеть как "обычная гистограмма", то есть ряд из столбиков разной длины). Для этой цели используется свойство ChartType, для которой разработчиками предусмотрено целых 73 значения. Например, чтобы преобразовать диаграмму в обычный график, можно использовать код вида:
oChart.ChartType = xlLineMarkers
Еще одна очень распространенная задача – добавить дополнительные ряды на диаграмму. Для этой цели необходимо создать и получить ссылку на объект Series – ряд, а потом для ряда определить свойство Values (ему передается в качестве значения объект Range):
Dim oSeries As Series
Set oSeries = oChart.SeriesCollection.NewSeries
oSeries.Values = Worksheets(1).Range("B1:B10")
Пользователи часто говорят, что им необходимо создавать диаграммы не на отдельном листе, а на том же листе, на котором расположены данные. По умолчанию диаграмма создается в оперативной памяти и помещается на отдельный лист. Если нам необходимо поместить ее на уже существующий лист, то в этом случае ее вначале надо создать на отдельном листе, а затем переместить при помощи метода Location. Отдельный лист, созданный для диаграммы, при этом автоматически исчезнет:
oChart.Location xlLocationAsObject, "Лист1"
Обратите внимание, что метод Location принимает в качестве первого параметра одну из констант (xlLocationAsNewSheet – переместить на специально создаваемый новый лист, xlLocationAsObject – переместить на объект, то есть лист), а в качестве второго – не объект листа, как можно было бы подумать, а обязательно его имя. Если код предполагается использовать и в русской, и в английской версии Excel, то предпочтительнее получить имя листа программным образом.
Большая неприятность, связанная с методом Location, заключается в том, что после перемещения диаграммы внутрь листа объектная ссылка на эту диаграмму теряется, и надо находить объект этой диаграммы заново. При попытке повторного обращения к объекту Chart выдается сообщение "Automation Error". Лучше всего поэтому вызов метода Location помещать в самый конец кода, посвященного диаграмме. В противном случае нам придется разыскивать созданную нами диаграмму и заново получать на нее объектную ссылку, например так:
Dim oSeries As Series
Set oSeries = Worksheets(1).ChartObjects(1).Chart.SeriesCollection.NewSeries
oSeries.Values = Worksheets(1).Range("B1:B10")
Так работать, конечно, намного менее удобно.
Остальные многочисленные параметры диаграммы настраиваются при помощи свойств и методов объектов Chart:
свойство ChartArea — это свойство возвращает одноименный объект ChartArea, который представляет собой область, занимаемую диаграммой и используется для настройки внешнего вида диаграммы (свойства Font, Interior и т.п.). Если необходимо настроить внешний вид не всей диаграммы, а той ее части, которая используется непосредственно для вывода графика, используется схожее свойство PlotArea. По умолчанию диаграмма размещается прямо по центру листа. Если необходимо ее переместить в точно определенное место листа, используются знакомые на свойства Top, Height, Left и Width для объекта ChartArea.
свойство ChartTitle возвращает одноименный объект, при помощи которого можно настроить заголовок диаграммы (с такими свойствами, как Text, Font, Border и т.п.);
ChartType — важнейшее свойство, про которое мы уже говорили. Определяет тип диаграммы;
HasDataTable — если установить это свойство в True, то в нижней части диаграммы (по умолчанию) появится таблица с числами, на основе которых была создана диаграмма. Одновременно будет создан программный объект DataTable, при помощи которого можно настроить представление этой таблицы. Схожим образом действуют свойства HasLegend, HasPivotFields и HasTitle.
Name — это свойство позволяет настроить имя диаграммы (как название вкладки в Excel). По умолчанию диаграммы называются последовательно "Диаграмма1", "Диаграмма2" и т.п.
SizeWithWindow — если поставить значение этого свойства в True (по умолчанию False), то размер диаграммы будет подогнан таким образом, чтобы точно соответствовать размеру листа.
Tab — свойство, о котором мало кто подозревает. Оно позволяет настроить при помощи одноименного объекта внешний вид вкладки в книге Excel для диаграммы (или просто листа). Например, чтобы пометить вкладку зеленым, можно воспользоваться кодом
oChart.Tab.Color = RGB(0, 255, 0)
Visible — возможность спрятать диаграмму без ее удаления.
Остальные свойства в основном относятся к настройке отображения трехмерных диаграмм и к защите диаграммы от изменения пользователем.
Теперь — о самых главных методах объекта Chart:
метод Activate() используется очень часто. Он позволяет сделать диаграмму активной (то есть просто перейти на нее);
метод ApplyCustomType() позволяет поместить создать диаграмму своего собственно пользовательского типа (для этого необходимо вначале создать шаблон для этого типа и поместить его в галерею);
метод ApplyDataLabels() позволяет поместить на диаграмму метки для размещенных на ней данных. Этот метод принимает множество параметров, которые позволяют настроить отображение данных меток (показывать или не показывать значения и т.п.);
метод Axes() возвращает объект, представляющий оси диаграммы. Затем этот объект можно использовать для настройки данных осей;
ChartWizard() — этот метод позволяет быстро переформатировать диаграмму, как будто бы прошли на графическом экране при помощи мастера построения диаграмм и передали ему значения. Позволяет при помощи одной строки кода добиться того, что другими способами потребовало бы минимум несколько строк;
Copy() — позволяет скопировать диаграмму в другое место книги (например, чтобы создать новую диаграмму, использовав в качестве основы существующую). Для переноса существующей диаграммы в другое место можно воспользоваться методами Location() или Move().
CopyPicture() — замечательный метод, который позволяет поместить диаграмму в буфер обмена как изображение. Затем это изображение можно вставить, например, в документ Word или в любое другое место. Другой вариант — воспользоваться методом Export(), который позволяет создать рисунок, представляющий диаграмму, в виде файла на диске.
Delete() — просто удаляет диаграмму;
Evaluate() — как обычно, этот метод позволяет найти нужную диаграмму в книге по ее имени;
PrintOut() — возможность отправить диаграмму на печать. Этот метод принимает множество параметров, которые позволяют настроить такой вывод;
Refresh() — возможность обновить диаграмму, если изменились данные, на основе которых она строилась;
Select() — возможность выделить диаграмму (равносильно щелчку по ней мышью). Deselect() — снятие выделения (равносильно нажатию на <Esc>);
SetBackgroundPicture() — возможность "подложить" под диаграмму фоновый рисунок. Конечно, он должен быть не очень ярким;
SetSourceData() — важнейший метод, который позволяет определить данные, на основе которых строится диаграмма. Про него мы уже говорили.
Для объекта Chart предусмотрено также события "на все случаи жизни" — реакция на щелчки мышью, на выделение/снятие выделения, активизацию, пересчет данных, изменение размера и т.п., однако используются такие события на практике нечасто.