- •3 Семестр
- •Оглавление
- •Язык программирования Visual Basic for Application Основные средства и возможности vba Основные элементы vba
- •Основы программирования на vba Типы данных
- •Описание переменных
- •Допустимые имена
- •Инструкция Def Тип
- •Массивы
- •Динамические массивы
- •Функции и процедуры для работы с массивами
- •Константы
- •Тип данных, определенный пользователем
- •Операции vba
- •Приоритеты операций
- •Встроенные функции vba
- •Математические функции
- •Функции проверки типов
- •Функции преобразования форматов
- •Функции обработки строк
- •Функции времени и даты
- •Функции выбора
- •Функции, возвращающие строки
- •Встроенные диалоговые окна
- •Инструкции vba
- •Оператор присвоения
- •Перенос строки
- •Комментарии
- •Расположение нескольких операторов на одной строке
- •Операторы перехода и выбора
- •Операторы повтора
- •Условная компиляция
- •Процедура
- •Переход в подпрограмму и возвращение из подпрограммы
- •Вызов процедуры
- •Рекурсивные процедуры
- •Область определения переменной
- •Время жизни переменной
- •Процедуры обработки ошибок и отладка программ Разработка процедур, предотвращающих появление ошибок
- •Перехват и обработка ошибок
- •Свойства объекта Err
- •Методы объекта Err
- •Отладка программ
- •Ошибки компиляции
- •Ошибки выполнения
- •Логические ошибки
- •Инструкция Option Explicit
- •Пошаговое выполнение программ
- •Точка останова
- •Вывод значений свойств и переменных
- •Работа с файлами Типы файлов в vba
- •Открытие и закрытие файла
- •Ввод данных в файл последовательного доступа
- •Вывод данных из файла последовательного доступа
- •Работа с файлом произвольного доступа
- •Наиболее употребляемые инструкции и функции при работе с файлами
- •Объект FileSearch
- •Элементы управления и пользовательская форма Элементы управления
- •Режим конструктора
- •Установка свойств элемента управления
- •Редактор кода
- •Пользовательская форма UserForm
- •Семейство Controls
- •Создание пользовательской формы
- •Общие свойства элементов управления
- •Соглашения об именах
- •Общие методы и события элементов управления
- •Объект DataObject
- •Надпись
- •Заполнение списка
- •Выбор нескольких элементов из списка
- •Поле со списком
- •Полоса прокрутки и счетчик
- •Переключатель
- •Флажок и выключатель
- •Ссылки на ячейки и диапазоны
- •Набор страниц
- •Набор вкладок
- •Дополнительные элементы управления
- •Последовательность выбора элементов управления
- •Инициализация и отображение диалогового окна
- •Закрытие диалогового окна
- •Отображение встроенных диалоговых окон
- •Открытие документа
- •Объект Application
- •Свойства объекта Application
- •Методы объекта Application
- •События объекта Application
- •Объект Workbook и семейство Workbooks
- •Свойства объекта Workbook и семейства Workbooks
- •Методы объекта Workbook и семейства Workbooks
- •События объекта Workbook и семейства Workbooks
- •Объект Worksheet и семейство Worksheets
- •Свойства объекта Worksheet и семейства Worksheets
- •Методы объекта Worksheet и семейства Worksheet
- •События объекта Worksheet
- •Объекты Range и Selection
- •Адресация ячеек
- •Задание групп строк и столбцов с помощью объекта Range
- •Связь объекта Range и свойства Cells
- •Свойства и методы объекта Range
- •Программирование панели инструментов
- •Объект CommandBar и семейство CommandBars
- •Семейство CommandBarControls и объект CommandBarControl
- •Пример создания панели инструментов пользователя
- •Пример создания строки меню пользователя
- •Создание пользовательской панели инструментов вручную
- •Удаление элемента управления из панели инструментов вручную
- •Удаление пользовательской панели инструментов вручную
- •Назначение вручную макроса кнопке
- •Изменение и создание вручную изображения на кнопке
- •Программирование средств для работы со справочной информацией
- •Структура помощника
- •Типы помощника
- •Свойства объекта Assistant
- •Объект Balloon
Задание групп строк и столбцов с помощью объекта Range
Если в диапазоне указываются только имена столбцов или строк, то объект Range задает диапазон, состоящий из указанных столбцов или строк. Например, Range ("А: с") задает диапазон, состоящий из столбцов А, в и с, а Range {"2: 2") - из второй строки. Другим способом работы со строками и столбцами являются методы ROWS (строки) и Columns (столбцы), возвращающие коллекции строк и столбцов. Например, столбцом А является columns (1), а второй строкой - ROWS (2).
Связь объекта Range и свойства Cells
Так как ячейка является частным случаем диапазона, состоящим только из единственной ячейки, объект Range также позволяет работать с ней. Объект cells (ячейки) — это альтернативный способ работы с ячейкой. Например, ячейка А2 как объект описывается Range ("A2") или cells (1,2). В свою очередь объект ceils, вкладываясь в Range, также позволяет записывать диапазон в альтернативном виде, который иногда удобен для работы, а именно,
Range ("А2:C3") И Range(Cells(1,2), Cells(3,3))
Определяют один и тот же диапазон.
Свойства и методы объекта Range
Объект Range позволяет сочетать гибкость VBA и мощь рабочего листа Excel. Более 400 встроенных функций рабочего листа существенно упрощают и делают более наглядным программирование на VBA.
Далее приводятся наиболее часто используемые свойства и методы объекта
Range.
Перечислим основные свойства объекта Range.
|
|
|
|
|
Value |
Возвращает значение из ячейки или в ячейки диапазона. В данном примере переменной х присваивается значение из ячейки C1 : х = Range ("C1") .Value В следующем примере в диапазон AI : В2 введена 1 : Range ("A1:B2") .Value = 1 |
|
|
Name |
Возвращает имя диапазона. В данном примере диапазону А1:В2 присваивается имя итоги: Range ( "Al :B2") .Name = "Итоги" |
|
|
Count |
Возвращает число объектов в наборе. В данном примере переменной х присваивается значение, равное числу строк диапазона AI : В2 : х = Range ( "Al :B2") .Rows . Count |
|
|
CurrentRegion |
Возвращает число строк текущего диапазона. Текущим является диапазон, ограниченный пустыми строками и столбцами и содержащий данный элемент. В следующем примере переменной у присваивается значение, равное числу строк в текущем диапазоне, содержащем ячейку AI : у = Range ( "Al" ). CurrentRegion. Rows . Count |
|
|
WrapText |
Позволяет переносить текст при вводе в диапазон. Допустимые значения True и False. В следующем примере в ячейку В2 вводится текст длинный текст и в этой ; ячейке устанавливается режим ввода текста с переносом: With Range ("B2") .Value = "Длинный текст" .WrapText = True End With |
|
|
|
|
|
|
|
|
|
|
EntireColumn, EntireRow |
Возвращает столбец и строку соответственно. В данном примере очищается содержимое строки и выделяется столбец с активной ячейкой: ActiveCell . EntireRow. Clear ActiveCell .EntireColumn. Select |
|
|
ColumnWidth, RowHeight |
Возвращает ширину столбцов и высоту строк диапазона соответственно |
|
|
Comment |
Возвращает объект comment (примечание), который связан с левым верхним углом диапазона при отображении на экране. Объект comment является элементом семейства comments. Метод AddComment, примененный к диапазону, создает новое примечание. Среди методов объекта comment отметим только метод Text, который задает текст, выводимый в примечании. Синтаксис: Text (Text, Start, Overwrite)
With Range ("ВЗ") .AddComment .Text Text:= "Чрезвычайно важно!" & Chr(10) & "Про это никак нельзя забыть ! " .Visible = True End With |
|
|
|
|
|
Рис. 2.1. Пример отображения примечания на рабочем листе
|
|
|
|
|
Font |
Возвращает объект Font (шрифт). Объект Font имеет следующие свойства:
|
|
|
|
|
|
|
|
|
|
|
|
Например, в следующем примере устанавливается для диапазона AI : в2 полужирный шрифт, красного цвета и с высотой символов 1 4 : With Range ("A1:B2").Font .Size = 14 .FontStyle = Bold .Colorlndex = 3 End With |
|
|
Formula |
Возвращает формулу в формате Al. Например, следующая инструкция вводит в ячейку с2 формулу =$А$4+$А$ю: Range ("C2") . Formula = "=$А$4+$А$10" |
|
|
FormulaArray 1 |
Возвращает формулу диапазона в формате А1. В отличие от обыкновенной формулы рабочего листа, формула диапазона вводится на рабочем листе не посредством нажатия на клавишу <Enter>, а с помощью комбинации клавиш <Ctrl>+<Shift>+<Enter>. Следующая инструкция вводит в диапазон Е!:ЕЗ формулу {=Sum(Al:A3*Bl:B3) }: Range ( "El :E3") .FormulaArray = "=Sum(Al:A3*Bl:B3) " |
|
|
FormulaHidden |
Допустимые значения: True (формула спрятана, если рабочий лист или книга защищены) и False (в противном случае). Например, следующая инструкция скрывает формулы в столбце А: Columns ("A") . FormulaHidden = True |
|
|
FormulaLocal |
Возвращает неанглоязычные (местные) формулы в формате А1. Например, следующая инструкция вводит в ячейку В2 формулу =СУММ(С1:С4): Range("B2"). FormulaLocal = "=СУММ (С1:С4) " |
|
|
JormulaRlCl |
Возвращает формулу в формате R1C1. Например, Range ("Bl") . FormulaRlCl = "=SQRT (R3C2 ) " |
|
|
FormulaRlCl Local |
Возвращает неанглоязычные формулы в формате R1C1 |
|
|
Text |
Возвращает содержание диапазона в текстовом формате |
|
|
|
|
|
|
|
|
|
|
HorizontalAlignment |
Горизонтальное выравнивание. Допустимые значения:
|
|
|
Vertical Alignment |
Вертикальное выравнивание. Допустимые значения:
|
|
|
Orientation |
Ориентация. Допускается либо угол поворота текста в градусах от —90° до 90°, либо одно из допустимых значений:
|
|
|
ShrinkToFit |
Допустимые значения: True (автоматическое изменение шрифта так, чтобы текст помещался в ячейку) и False (в противном случае) |
|
|
|
|
|
Ниже приведены наиболее часто используемые методы объекта Range.
|
Address |
Возвращает адрес ячейки. Синтаксис: Address (rowAbsolute, coluimAbsolute, referenceStyle, external, relativeTo) Аргументы:
xiR1c1, если используется значение X1A1 или аргумент опущен, то возвращается ссылка в виде формата А1 П external — допустимы два значения True и False, если используется значение False или аргумент опущен, то возвращается относительная ссылка
MsgBox Cells (1, 1). Address () ' ' В диалоговом окне отображается адрес $А$1 MsgBox Cells (1, 1) .Address (rowAbsoiute:=False) ' ' В диалоговом окне отображается адрес $А1 ' MsgBox Cells (1, 1) .Address (ref erenceStyle :=x1R1C1) ' ' В диалоговом окне отображается адрес R1C1 '
|
|
|
|
|
|
|
|
|
|
|
Clear, ClearComments , Clear Contents, ClearFormats И |
Метод clear очищает диапазон. В следующем примере очищается диапазон Al :G37 . Range ("A1:G37") .Clear Методы ClearComments, ClearContents, ClearFormats и ClearNotes очищают В диапазоне |
|
|
AutoFit |
Автоматически настраивает ширину столбца и высоту строки |
|
|
ClearNotes |
комментарии, содержание, форматы и примечания соответственно. |
|
|
Copy |
Копирует диапазон в другой диапазон или в буфер обмена. Синтаксис: Copy (destination)
Worksheets ("Лист1" ) . Range ( "А1 : D4 " ) .Сору destination :=Worksheets ("Лист2") .Range ("E5") |
|
|
Cut |
Копирует диапазон с удалением в указанный диапазон или в буфер обмена, Синтаксис: Cut (destination)
Worksheets ( "Лист1 " ) . Range ( "А1 : D4 " ) . Cut |
|
|
Delete |
Удаляет диапазон. В данном примере удаляется третья строка активной рабочей страницы: Rows (3) .Delete |
|
|
Columns, Rows |
Возвращают соответственно семейства столбцов и строк, из которых состоит диапазон. В следующем примере переменным i и j присваиваются значения, равные количеству столбцов и строк в выделенном диапазоне соответственно: i = Selection. Columns . Count j = Selection. Rows . Count |
|
|
Insert |
Вставка ячейки или диапазона ячеек. В следующем примере вставляется новая строка перед четвертой строкой рабочего листа Лист1: Worksheets ( "Лист1" ) . Rows (4 ) .Insert |
|
|
Offset |
Возвращает диапазон, смещенный относительно данного на величины, специфицированные в аргументах. Синтаксис: Of fset (rowOffset, columnOf f set ) Аргументы:
ActiveCell.Offset (rowOf fset :=3, columnOf fset : =-2) .Activate
|
|
|
|
|
|
|
Select |
Выделение диапазона |
|
|
PasteSpecial |
Специальная вставка из буфера обмена. Синтаксис: BasteSpecial (paste, operation, skipBlanks, transpose) Аргументы:
В приведенном ниже примере данные из диапазона C1:C5 рабочего листа лист1 вставляются в диапазон D1 : D5 того же листа, причем они не заменяют уже существующие данные в диапазоне D1:D5, а прибавляются к ним данные из диапазона C1 : С5 : Worksheets ( "Лист1 " ) . Range ( "С1 : С5 " ) . Сору Worksheets ("Лист1") .Range ("D1: D5") . PasteSpecial operation : =xlAdd Метод PasteSpecial программирует выполнение на рабочем листе команды Правка, Специальная вставка (Edit, Paste Special). Аргументы метода PasteSpecial соответствуют установкам диалогового окна Специальная вставка (Paste Special), отображаемого с помощью этой команды (рис. 3.2) |
|
Рис. 2.2. Диалоговое окно Специальная вставка
|
|
|
|
|
AddComment |
Добавляет примечание к диапазону. Синтаксис: AddComment (Text)
Range ( "А! " ) . AddComment "Внимание!" |
|