Microsoft Excel – это наиболее важная составная часть пакета Microsoft Office, используемая миллионами людей, среди которых и разработчики, и простые пользователи. Согласно статистике, те пользователи (а их около 90% от общего числа), которые применяют только средства рабочего листа MS Excel и не используют возможности, предоставляемых VBA, реализуют лишь не более 10% от истинных способностей MS Excel. Таким образом, большая часть средств и возможностей MS Excel остается невостребованной в связи с тем, что пользователям не всегда известно о наличии VBA, или же они не умеют им пользоваться. Давайте разберемся, как более эффективно использовать объединенную мощь MS Excel и VBA.
Статья: Создание приложений на языке VBA для MS EXCEL
Основные сведения об объектах, методах и свойствах MS EXCEL
В модели объектов Excel имеются более 100 объектов и семейств. Однако достаточно использования только небольшого количества объектов, чтобы выполнить большинство всех возможных действий. Наиболее часто используемыми объектами являются Application, Workbook (Workbooks), Worksheet (Worksheets) и Range.
-
Application (приложение) - само приложение MS EXCEL в целом. Включает глобальные устанавливаемые параметры (стиль ссылок, режим проведения вычислений) и встроенные функции MS EXCEL.
Объект Application.Excel имеет свойство Workbooks, возвращающее Workbooks - коллекцию всех открытых книг. Каждый элемент коллекции - рабочая книга - является объектом класса Workbook.
Свойствами объекта Application являются: ActiveWorkbook (активная рабочая книга) и ActiveWindow (активное окно).
Пример 1MsgBox "имя активной рабочей книги" & ActiveWorkbook.Name Методы объекта Application:
- Quit - завершает работу с EXCEL;
- Undo - отменяет последнее выполненное действие
-
Workbook (рабочий лист) - объект этого класса определяет состояние рабочей книги. Например, определяет не является ли она доступной только для чтения, или какой из листов рабочей книги активен в настоящий момент. К этому классу принадлежит объект ActiveWorkbook (активная в настоящий момент рабочая книга). Объект Workbook, который представляет одну рабочую книгу, имеет свойство Sheets. При обращении к этому свойству возвращается объект Sheets - коллекция листов данной книги. Каждый элемент коллекции - лист - имеет свой тип и поэтому является объектом класса Worksheet (рабочий лист) или Chart (диаграмма).
Свойствами объекта Workbook являются: ActiveSheet (активный рабочий лист) и Name (имя рабочей книги).
К методам объекта Workbook относятся:
- Activate -активизирует рабочую книгу;
- Close - закрывает рабочую книгу;
- Save - сохраняет рабочую книгу;
-
Worksheet (рабочий лист) - объект этого класса используется при копировании и удалении рабочих листов, их скрытии и показе, проведении вычислений для формул рабочего листа. К этому классу принадлежит объект ActiveWorksheet (активный в настоящий момент рабочий лист).
Свойствами объекта являются:
- Name - имя рабочего листа;
- Previous - предыдущий рабочий лист;
- Visible - режим видимости рабочего листа (скрыт или показан).
К методам объекта относятся:
- Activate - активизирует рабочий лист;
- Calculate - заново вычисляет значения в ячейках рабочего листа;
- Delete - удаляет рабочий лист;
- Protect - защищает рабочий лист.
Объект Worksheet, который представляет один рабочий лист, имеет свойство Cells, возвращающее объект Range. Он может задавать как все ячейки рабочего листа, так и любую ее часть.
-
Range (интервал). Объект этого класса позволяет изменять свойства интервала ячеек (например, шрифт), проверять или изменять содержимое ячеек, вырезать или копировать интервал и т.д.
К основным свойствам объекта Range относятся:
- Cells, Columns, Rows - возвращают коллекции ячеек, столбцов или строк, входящие в объект Range;
- Column, Row - возвращают соответственно номер первого столбца или первой строки в области объекта Range;
- Value - значение указанной ячейки. Если она пуста, то возвращается значение Empty, что можно проверить, вызвав функцию IsEmpty;
- Font - используемый в интервале шрифт;
- Formula - формула интервала;
- Name - имя интервала.
Синтаксис установки значения объекта имеет вид:
Объект.Свойство =
В данном случае значение может быть представлено константой или формулой, возвращающей постоянное значение, и принадлежать к одному из трех типов:
- Числовому значению. Например, для установки размера шрифта: ActiveCell.Font.Size = 14
- Строке символов. Например: ActiveCell.Font.Name = "Courier New Cyr"
- Логическому значению: ActiveCell.Font.Italic = True
К основным методам объекта Range относятся:
- Clear - полностью очищает интервал (в том числе и форматирование);
- ClearContents - очищает содержимое ячеек интервала;
- ClearFomats - очищает форматирование ячеек интервала;
- Copy - копирует интервал в буфер обмена;
- Offset - возвращает интервал с указанным смещением относительно первоначального интервала;
- Paste - вставляет содержимое буфера обмена в интервал;
- Select - выделяет интервал.
Синтаксис вызова метода объекта имеет вид:
Объект.Метод[аргументы]
Пример 2Range("Al:B2").Select
Пример разработки приложения
Попробуем построить приложение, которое осуществляет пересчет значений веса из граммов в фунты. Разберем этот процесс поэтапно:
-
Разработка формы (рис.1).
На данном этапе помещаем на форму метку Label1. Компонент Label1 предназначен для вывода статического текста, например, для заголовков. Свойство Caption содержит текст, отображаемый в метке. Текст метки можно изменять в окне свойств или динамически в процессе выполнения программы. Установим свойство Caption равным «Введите значение веса в граммах».
Рядом поместим еще одну метку Label2. Зададим свойству Caption значение «Результат».
Под первой меткой установим компонент TextBox1, под второй - метку Label3, в которой очистим свойство Caption. Компонент TextBox представляет собой текстовое поле. Он имеет фокус ввода и, следовательно, может служить окном ввода данных.
Установим на форму компонент CommandButton. Этот компонент представляет собой командную кнопку. В графическом интерфейсе Windows командные кнопки связывают с определенными действиями. Изменим свойство Caption для кнопки на «Расчет».
Изменим шрифт текста в метках и на кнопке, воспользовавшись свойством Font каждого из компонентов. Затем изменим цвет шрифта с помощью свойства ForeColor. Далее запускаем приложение на выполнение нажатием клавишы . Форма для общего примера:
В данном приложении расчет не выполняется. Для придания форме функциональности нужно написать код.
-
Написание кода.
Каждой форме соответствует окно кода, в котором записывают операторы программы. Текст программы составляется по специальным правилам и описывает алгоритм работы программы. Такая программа делает форму функциональной. Окно кода вызываем командой View – Code или нажатием функциональной клавиши F7, или кнопки View Code (расположенной слева с краю над надписью VBA Project).
Вызовем окно кода двойным щелчком мыши или нажатием F7.
В окне кода в верхней части имеются два ниспадающих списка. В левом отображаются имена компонентов, установленных на форму. В правом — события, на которые может откликаться компонент. В окне кода имеется заготовка процедуры, которая будет выполняться при нажатии на кнопку (обработчик события). Событие Click (по щелчку) возникает в работающей программе при щелчке по кнопке мышью.
Private Sub CommandButton1_Click()
End Sub
В имени процедуры указываются имя компонента и событие, обрабатываемое этим компонентом.
Чтобы вставить в окно новую строку (строки), необходимо в начале с помощью клавиш курсора или щелкнув по окну мышью поставить текстовый указатель на нужное место, а затем с помощью клавиатуры ввести текст. Между служебными словами Sub и End введите следующие операторы языка VBA:
$x = Val(TextBox1.Text)$
$y = x / 400$
$Label3.Caption = Str(y) + " Фунт."$
В первом операторе присваивания переменная Х принимает значение, введенное в окно редактора. Свойство Text компонента TextBox1 содержит то, что в ходе работы программы вводится в окно пользователем. Свойство Text строкового типа, поэтому необходимо преобразовать его в число (функция Val). Второй оператор присваивания — формула пересчета. Для отображения результата расчета используется свойство метки Caption. Функция Str преобразует число в строку. Строки можно складывать. В метке отобразится результат расчета и слово «Фунт».
Вместо трех строк можно написать одну строку кода, опустив свойства Caption и Text, так как они является свойствами по умолчанию:
Label3 = Val(TextBox1) / 400 & " Фунт."
Осуществим прогон программы нажатием функциональной клавиши F5. Вводим в окно редактора любое число, и после нажатия на кнопку «Расчет» получаем результат в метке Label3.
Итак, мы познакомились с некоторыми правилами создания приложений Ms Excel с помощью языка программирования VBA.
