При разработке проекта приложения электронных таблиц, как, впрочем, и любого приложения, первым действием является точное определение потребностей конечного пользователя. И если вы не сможете заранее оценить эти потребности, то позднее это обернётся дополнительной работой по устранению недостатков. Желательно обсуждать детали реализации будущего приложения не только с руководителем фирмы или подразделения, для которого разрабатывается приложение, но и непосредственно с потенциальными пользователями приложения.
Данный этап подразумевает точное и детальное определение того, что должна делать программа, какие ограничения накладываются на исходные данные, какие ошибки могут возникать даже при корректных исходных данных.
Проектирование приложенияСтроители не возводят дом без проекта, а слесарь не изготавливает механизм без чертежей. Программист тоже не должен бросаться к компьютеру и лихорадочно нажимать на клавиши без предварительного проектирования будущего приложения. При проектировании приложения электронных таблиц необходимо обдумать следующие вопросы.
- Файловая структура. Приложение может использовать одну рабочую книгу с несколькими листами, несколько однолистных рабочих книг, файла шаблона. Структура данных. Данные должны быть структурированы таким образом, чтобы это структурирование облегчало их восприятие и позволяло избежать повторного хранения одних и тех же сведений. Формулы или VBA? Обдумайте, что требуется для вычислений – формулы или процедуры VBA. И тот, и другой способ имеют достоинства и недостатки. В реальном приложении скорее всего часть вычислений будет производиться с помощью формул, а другая часть – с помощью процедур VBA. Надстройка или файл рабочей книги. Надстройкой называется программа, внедрённая в приложение Microsoft Excel с целью расширения функциональных возможностей приложения. Обработка ошибок. Всегда возможно возникновение такой ситуации, когда результат не может быть вычислен, или некоторое действие не может быть выполнено, по тем или иным причинам. Например, если приложение применяет форматирование к активному рабочему листу, а активным окажется лист диаграммы, это приведёт к невозможности выполнить необходимое форматирование. Проверка ошибочных состояний повышает надёжность и эргономичность приложения. Использование специальных возможностей. Продумайте использование таких возможностей приложения Microsoft Excel, как проверка данных, сводные таблицы, анализ данных. Вопросы производительности. Если приложение быстро выполняется на суперсовременном компьютере, это не значит, что также быстро оно будет выполняться на любом другом компьютере. Кроме того, большие приложения могут долго выполняться и на современных компьютерах. Существуют средства увеличения производительности VBA-приложения. Уровень безопасности. Приложение Microsoft Excel имеет ряд средств защиты, которые призваны предотвратить доступ к элементам рабочей книги. Хотя, как мы уже говорили, эти средства не являются абсолютно надёжными, их использование позволит повысить уровень безопасности разрабатываемого приложения.
Хорошо спроектированное приложение проще кодировать, отлаживать и модифицировать. Дальнейшая разработка приложения занимает меньше времени и требует меньше усилий.
Возможно, что разработанное приложение будет состоять из одной или нескольких функций, которые будут использоваться в формулах ячеек. Для более сложных приложений необходимо будет задать способ вызова приложения и способ задания/просмотра данных. Для этого в приложении Microsoft Excel существуют следующие возможности:
- использование элементов ленты; использование контекстных меню, которые могут быть изменены с помощью VBA; использование комбинаций клавиш (комбинация клавиш, назначаемая макросу, имеет больший приоритет, чем встроенные комбинации клавиш); использование пользовательских диалоговых форм; размещение элементов управления непосредственно на рабочем листе.
Хороший внешний вид приложения говорит о том, что разработчик заботился о своём продукте. В процессе разработки пользовательского интерфейса следует выполнять следующие рекомендации.
- Добивайтесь единообразия. Разрабатывая, например, диалоговые окна, старайтесь по возможности следовать внешнему виду диалоговых окон приложения Microsoft Excel. Соблюдайте единообразие в формате, шрифтах, размере текста, цветах. Выбирайте простые решения. Интерфейс, перегруженный различными элементами, труднее воспринимается, а украшательства отвлекают от главных элементов. Разбивайте окна ввода. Не следует пытаться выдать всю информацию сразу. Лучше разбить её на логически сгруппированные части. Не переусердствуйте с цветом. Люди воспринимают один и тот же цвет по-разному, поэтому яркие цвета могут раздражать. Лучше использовать нейтральные цвета. Отслеживайте шрифты и графику. Уделите внимание числовым форматам, шрифтам, границам ячеек.
Трудно дать какие-либо рекомендации по этому пункту, т. к. разработка разных приложений требует разных методов. В этом деле может помочь только опыт, опыт и ещё раз опыт.
Тестирование и отладка – самый важный и один из самых трудоёмких этапов разработки приложения. Под тестированием будем понимать ввод тестовых исходных данных и сравнение полученных результатов с тестовыми для определения факта наличия/отсутствия ошибок в программе. Под отладкой будем понимать поиск местонахождения ошибок и их устранение.
Для тестирования необходимо разработать ряд наборов исходных данных и соответствующих им результатов. Затем эти исходные данные вводятся в приложение, и полученные результаты сравниваются с «запланированными». В случае расхождения необходимо найти ошибку, которая приводит к этому расхождению.
Чем больше различных наборов исходных данных вы используете для тестирования, тем меньше будет вероятность того, что в приложении остались ошибки. Хотя, надо сказать, такая вероятность для сложных приложений никогда не бывает равной 0. Разрабатывая наборы тестовых данных, необходимо рассматривать не только случаи корректных исходных данных, но также и некорректных – причём, наборы некорректных исходных данных надо разрабатывать не менее тщательно.
Разработка документации и справочной системыТолько самые простые программы могут позволить себе обойтись без сопроводительной документации. Серьёзное приложение должно иметь два вида документации: документацию разработчика, т. е. описание средств, использованных при разработке, для того, чтобы вы сами или кто-то другой могли при необходимости модифицировать приложение, и документацию пользователя (справочную систему), которая поможет пользователю освоить приложение в полной мере.
Установка приложения может оказаться не тривиальной задачей, т. к. может потребовать указания каких-либо параметров приложения, а также согласования приложения с другими приложениями. В настоящее время существуют специальные средства, создающие приложения-установщики, автоматизирующие этот процесс, а также средства, позволяющие внедрять в приложение цифровую подпись для гарантии подлинности приложения.
Обновление приложенияЖизнь не стоит на месте, поэтому после разработки приложения, даже полностью соответствующего начальной спецификации, может потребоваться модификация приложения, добавление в него новых функций. Этап обновления приложения выявляет все ошибки и недочёты, допущенные на стадии проектирования приложения.
Объектно-ориентированное программированиеСовременное программирование – это, прежде всего, объектно-ориентированное программирование, которое подразумевает создание и обработку программных конструкций, соответствующих предметам реального мира. В объектно-ориентированной программе обычно создаётся класс – обобщенное описание некоторого предмета, затем создаются объекты этого класса, и программа работает с этими объектами. Например, существует класс Workbook, который описывает рабочую книгу приложения Microsoft Excel вообще – её характеристики (имя, формат файла, использование системы дат 1904 года, рабочие листы книги, стили рабочей книги и т. д.), действия, которые можно выполнить над книгой, (открыть, закрыть, сохранить, сохранить под другим именем и т. д.) и события, которые могут происходить в книге, (открытие, добавление листа, изменение листа и т. д.). Затем мы создаём рабочую книгу, которая называется, например, Моя рабочая книга № 000. Теперь этот конкретный объект имеет конкретное имя, конкретный формат, определённое количество листов и т. д.
Объектно-ориентированное программирование подразумевает в первую очередь разработку классов, но это сложная задача, которой мы пока заниматься не будем. Но программирование на языке VBA не возможно без понимания сути объектно-ориентированного программирования, т. к. язык VBA управляет объектами, которые существуют в том или ином приложении (Word, Excel, Access, Power Point). Каждое приложение имеет свой набор объектов. Единственный совпадающий объект – это объект Application, описывающий собственно приложение.
Свойства, методы и событияВсе объекты имеют свойства, методы и события.
Свойство – это характеристика объекта. Свойства всегда возвращают некоторое значение, имеют определённый тип (целый, вещественный, строковый, логический, рабочая книга, рабочий лист, диапазон и т. д.) и могут использоваться либо только для получения некоторой характеристики объекта, либо как для получения, так и для изменения характеристики объекта. Рассмотрим для примера некоторые свойства объекта Workbook – рабочая книга.
- Name – свойство представляет собой строку, содержащую имя рабочей книги, например, Книга1.xlsx. Изменить это свойство нельзя. FullName – свойство представляет собой строку, содержащую полный путь и имя рабочей книги, например, D:\Документы\Книга1.xlsx. Изменить это свойство нельзя. Date1904 – логическое свойство, значение которого равно True, если рабочая книга использует систему дат 1904 года, и значение False в противном случае. С помощью языка VBA можно изменить это свойство и, соответственно, используемую систему дат. ActiveSheet – свойство, определяющее активный рабочий лист книги. Является ссылкой на объект Worksheet – рабочий лист. Изменить это свойство нельзя. Однако это не значит, что с помощью языка VBA нельзя изменить активный рабочий лист. Просто для этого используются другие инструменты.
Метод определяет действие, выполняемое над объектом. Реализуется в виде процедуры или функции. Действие может быть сложным, и соответствующая процедура может быть также сложной и длинной. Методы могут менять свойства объекта. Рассмотрим для примера некоторые методы объекта Workbook.
- Close – закрытие рабочей книги. Save – сохранение рабочей книги. SaveAs – сохранение рабочей с другим именем или в другом формате. Protect – защита рабочей книги. Unprotect – отмена защиты рабочей книги.
Большинство объектов распознают так называемые события, т. е. изменения в состоянии этого объекта. Для каждого события можно написать процедуру на языке VBA, которая будет выполняться при наступлении этого события. Рассмотрим для примера некоторые события объекта Workbook.
- Open – событие, которое происходит при открытии рабочей книги. NewSheet – событие, которое происходит при добавлении нового листа в рабочую книгу. SheetChange – событие, которое происходит при изменении ячейки любого листа рабочей книги.
В приложении Microsoft Excel можно открыть несколько рабочих книг. Каждая рабочая книга содержит несколько листов. Для представления подобных наборов одинаковых объектов используются так называемые коллекции. При этом каждая коллекция – это тоже объект, который имеет свойства и методы.
Методы коллекции Workbooks, хранящей все открытые рабочие книги, позволяет открывать и закрывать рабочие книги, создавать новую рабочую книгу. Методы других коллекций обычно позволяют добавлять новый объект в коллекцию, удалять объект из коллекции, перемещать и копировать объекты коллекции, выделять объекты коллекции.
Свойства коллекций обычно содержат свойство Count, определяющее количество объектов в коллекции, и свойство Item, позволяющее выделить один объект из коллекции по номеру или по имени.
Рабочую книгу невозможно открыть, не запустив приложение Microsoft Excel. Рабочие листы не существуют без рабочей книги. Ячейки не существуют без рабочего листа. Таким образом, объекты и коллекции приложения Microsoft Excel образуют иерархию.
- Application
- Workbooks
- Workbook
- Charts
- Chart
- Axes Hyperlinks Shapes
- Worksheet
- ChartObject Cells Comments Hyperlinks Names Shapes
- Application
- AddIns Charts
- Axes Hyperlinks Shapes
- Panes SelectedSheets
- Charts Names Sheets Styles Windows Worksheets
- ChartObjects Scenarios Comments Hyperlinks Names Shapes
В объектно-ориентированном программировании для обращения к свойствам и методам объектов необходимо записать сначала имя объекта, затем через точку – имя свойства или метода.
Application. Undo
Application. Wait "12:00:00"
Application. Wait Now + TimeValue("0:00:10")
Если свойство представляет собой другой объект, то после него можно также поставить точку и записать имя свойства или метода этого объекта.
Application. Workbooks. Item(1).Close
Application. Workbooks. Item("Книга1.xlsm").Worksheets. Item("Лист2").Activate
Application. Workbooks. Item("Книга1.xlsm").Worksheets. Item("Лист2").Cells(1, 1).Value = 7
Свойство Item является так называемым свойством по умолчанию, поэтому его имя вместе с предшествующей точкой можно опустить и записать сразу параметр этого свойства. Кроме того, объект Application уникален, поэтому его имя также обычно можно опустить. Таким образом, предыдущие операторы можно сократить.
Workbooks(1).Close
Workbooks("Книга1.xlsm").Worksheets("Лист2").Activate
Workbooks("Книга1.xlsm").Worksheets("Лист2").Cells(1, 1).Value = 7
Некоторые полезные свойства объекта ApplicationПри работе в приложении Microsoft Excel активной одновременно может быть только одна рабочая книга. В рабочей книге активен только один лист. На рабочем листе активна только одна ячейка, даже если выделен диапазон ячеек. VBA это известно, поэтому можно ссылаться на активные объекты более простым способом.
Свойство | Возвращаемый объект |
ActiveWindow | Активное окно |
ActiveWorkbook | Активная рабочая книга |
ActiveSheet | Активный лист (рабочий лист или лист диаграммы) |
ActiveChart | Активный лист диаграммы или объект диаграммы, если такого листа или диаграммы нет, то свойство равно Nothing |
ActiveCell | Активная ячейка |
Selection | Выделенный объект – Range, ChartObject, Shape и т. д. |
ThisWorkbook | Рабочая книга, содержащая выполняемую процедуру VBA |
Преимущества использования этих свойств объекта Application состоят в том, что можно выполнить некоторые действия не над одним конкретным объектом, а над любым объектом, предварительно выделенным пользователем.
ActiveCell. Value = 1 'Записываем в активную ячейку значение 1
ActiveCell. ClearContents 'Очищаем активную ячейку
MsgBox ActiveSheet. Name 'Выводим имя активного листа
Selection. Value = -5 'Заполняем все ячейки выделенного диапазона числом -5
Работа с объектами RangeРабота, которая выполняется с помощью VBA, в основном связана с управлением ячейками и диапазонами на рабочих листах, что является главным предназначением редактора электронных таблиц. Объект Worksheet, представляющий рабочий лист, имеет свойство Range, возвращающее одноимённый объект, представляющий одну ячейку или диапазон ячеек. Одна ячейка, возвращаемая, например, свойствами ActiveCell или Cells, также является диапазоном – минимально возможным диапазоном.
Задание диапазонаДля задания диапазона используется следующий синтаксис.
объект. Range(ячейка)
объект. Range(ячейка1, ячейка2)
Ячейка – это параметр, идентифицирующий диапазон, а ячейка1 и ячейка2 – параметры, очерчивающие диапазон, т. е. они задают левый верхний и правый нижний угол диапазона. При этом под ячейкой может подразумеваться не одна ячейка. Чтобы пояснить сказанное, приведём несколько примеров. Поскольку перед именем Range ничего не стоит, подразумевается диапазон на активном рабочем листе – ActiveSheet.
Range("B5").Select 'Выделение одной ячейки B5
Range("A1:D10").Select 'Выделение диапазона ячеек от ячейки A1 до ячейки D10
Range("A1", "D10").Select 'Аналогично предыдущему примеру
Range("A1:B2", "D10:G12").Select 'Выделение диапазона ячеек от ячейки A1 до ячейки G12
Range("A:D").Select 'Выделение первых четырёх столбцов рабочего листа
Range("1:10").Select 'Выделение первых десяти строк рабочего листа
Range("A:D 1:10").Select 'Выделение диапазона ячеек от ячейки A1 до ячейки D10. 'Пробел – оператор пересечения диапазонов
Range("A1,D10").Select 'Выделение двух несвязанных ячеек A1 и D10.
'Запятая – оператор объединения диапазонов
Свойства объекта RangeСвойство | Описание |
Address | Возвращает строку, содержащую ссылку на диапазон |
Cells | Позволяет выделить ячейку диапазона по относительному номеру строки и номеру столбца |
Columns | Возвращает объект Range, представляющий столбцы диапазона |
ColumnsWidth | Возвращает или устанавливает ширину столбцов диапазона |
Count | Возвращает количество объектов в коллекции |
Formula | Возвращает или устанавливает формулу ячейки в формате A1 |
FormulaLocal | Возвращает или устанавливает формулу ячейки в формате A1 на языке пользователя |
FormulaR1C1 | Возвращает или устанавливает формулу ячейки в формате R1C1 |
FormulaR1C1Local | Возвращает или устанавливает формулу ячейки в формате R1C1 на языке пользователя |
HasFormula | Свойство принимает значение True, если все ячейки диапазона содержат формулы, значение False, если ни одна ячейка не содержит формулу, и значение null в противном случае |
MergeCell | Свойство принимает значение True, если диапазон находится внутри объединённых ячеек |
Name | Возвращает или устанавливает имя диапазона |
NumberFormat | Возвращает или устанавливает числовой формат для ячеек диапазона |
Offset | Возвращает объект Range, который смещен от текущего на указанное количество строк и столбцов |
RowHeight | Возвращает или устанавливает высоту строк диапазона |
Rows | Возвращает объект Range, представляющий строки диапазона |
Value | Возвращает или устанавливает значение для диапазона |
Методы объекта Range
Метод | Описание |
Activate | Активация указанной ячейки |
Clear | Очистка диапазона (и содержимое, и формат) |
ClearContents | Очистка содержимого диапазона |
ClearFormats | Очистка формата диапазона |
Copy | Копирование диапазона в буфер обмена или другой диапазон |
CreateNames | Создание имён для диапазона на основе текстовых данных |
Cut | Вырезание диапазона (в буфер обмена) или вставка диапазона в другой |
Merge | Объединение диапазона в одну ячейку |
Select | Выделение диапазона |
Sort | Сортировка ячеек диапазона |
Speak | Озвучивание содержимого ячеек диапазона |
UnMerge | Разделение объединённой ячейки |
Примеры работы с объектом Range
ActiveCell. RowHeight = 50 'Изменяем высоту строки,
'в которой находится активная ячейка
If Range("A1:A3").MergeCells Then 'Проверяем, что диапазон входит в объединённую ячейку
MsgBox "Merged"
Else
MsgBox "Not merged"
End If
Range("A6").Clear 'Очищаем ячейку A6
Range("C6").ClearContents 'Очищаем содержимое ячейки C6
Range("E6").ClearFormats 'Очищаем форматирование ячейки E6
Range("B2:D10").Cells(2, 3).Value = 66
ActiveCell. Offset(1, 0).FormulaLocal = ActiveCell. FormulaLocal
ActiveCell. Offset(1, 0).FormulaR1C1 = ActiveCell. FormulaR1C1
MsgBox Range("A1:D10").Count
MsgBox Range("A1:D10").Rows. Count
MsgBox Range("A1:D10").Columns. Count
Параметры свойств и методов в языке VBAМногие свойства и методы имеют параметры, которые меняют значение, возвращаемое свойством, и управляют выполнением метода. Например, методы Copy и Cut позволяют указывать диапазон, в который надо скопировать текущий диапазон. Метод CreateNames позволяет указывать, откуда брать имена – из верхней или нижней строки диапазона, из левого или правого столбца диапазона. Метод Sort имеет 15 параметров, которые позволяют задавать ключевые поля для сортировки, порядок сортировки, указывать необходимость различать строчные и прописные буквы.
Параметры свойств записываются после имени свойства в скобках и разделяются запятыми. Параметры методов записываются после имени метода через пробел и также разделяются запятыми.
Обычно параметры, передаваемые при вызове свойства или метода, должны совпадать с параметрами, указанными в описании свойства или метода, по следующим критериям:
- по количеству; по типу; по порядку следования.
Однако в языке VBA в отличие от большинства других процедурных языков многие из параметров являются необязательными, т. е. их можно не указывать при обращении к свойству или вызове метода. В этом случае используются так называемые значения по умолчанию, которые подставляются в тот или иной параметр. Чтобы не передавать все параметры, для параметров, которые необходимо задать, указывают имя параметра и затем значение параметра после комбинации символов «двоеточие + равно». При этом параметры можно указывать в любом порядке.
Range("A1:D4").Copy Destination:=Range("E5:H9")
expression. CreateNames(Top, Left, Bottom, Right)
Range("A1:E4").CreateNames True, False, False, False
Range("A1:E4").CreateNames Left:=True
Range("A1:E4").CreateNames, True
Range("A1:E4").CreateNames, , True
Рассмотрим более подробно параметры свойства Address.
expression. Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)
Имя | Обязательный | Тип | Описание |
RowAbsolute | нет | Variant | Если значение параметра равно True (по умолчанию), возвращается абсолютная ссылка на строку. |
ColumnAbsolute | нет | Variant | Если значение параметра равно True (по умолчанию), возвращается абсолютная ссылка на столбец. |
ReferenceStyle | нет | XlReferenceStyle | Формат ссылки – xlA1 (по умолчанию) для ссылок в формате А1, xlR1C1 – для ссылок в формате R1C1. |
External | нет | Variant | Если значение параметра равно False (по умолчанию), возвращается локальная ссылка. В противном случае возвращается внешняя ссылка, т. е. ссылка, содержащая имя файла и имя листа. |
RelativeTo | нет | Variant | Параметр задаёт диапазон, относительно которого указывается ссылка. Имеет значение только для относительных ссылок в формате R1C1. |
Set mc = Worksheets("Лист1").Cells(1, 1)
MsgBox mc. Address '$A$1
MsgBox mc. Address(RowAbsolute:=False) '$A1
MsgBox mc. Address(ReferenceStyle:=xlR1C1) 'R1C1
MsgBox mc. Address(RowAbsolute:=False, ColumnAbsolute:=False, ReferenceStyle:=xlR1C1, _
RelativeTo:=Worksheets("Лист1").Cells(3, 3)) 'R[-2]C[-2]
MsgBox mc. Address(RowAbsolute:=False, ColumnAbsolute:=False, External:=True) '[Книга1.xlsm]Лист1!A1
