Как работать с Excel через VBA или объектная модель Excel
Перед созданием своей первой программы в Excel необходимо разобраться в объектную модели приложения. Имея четкое понимания об объектной модели Excel вы сможете управлять практически всеми объектами, создавать/изменять листы, графики, отдельные файлы и многое другое. Возможно на первый взгляд она покажется довольно сложной, но не пугайтесь на конкретных примерах у вас сложится целостная картина.
Структура объектов
Проще всего рассматривать объектную модель как некое дерево или иерархическую структуру, так как каждый объект имеет свое ответвление. Кусочек этой структуры вы можете увидеть на рисунке далее.
Самый главный объект, вершина нашей структуры, это собственно само приложение Excel — объект Application. Какие объекты входят в Excel? правильно книги (файлы Excel), поэтому в объекте Application содержится коллекция из книг — объект Workbooks. Из чего состоят книги — из листов, диаграмм, соответственно внутри объекта Workbooks мы видим объект Worksheets и Charts. Идем дальше, лист в свою очередь состоит из строк, столбцов, ячеек это объекты Rows, Columns, Range.
Это, конечно, как вы понимаете только часть объектной модели Excel, мы перечислили только одни их самых основных объектов. Полное дерево объектов исчисляется сотнями объектов. Возможно она сейчас кажется сложной, не переживайте со временем вы начнете быстро в ней ориентироваться. Главное сейчас — это понять, что есть некие объекты, которые могут состоять из других объектов.
Объекты и коллекции объектов
Многие объекты, которые мы перечислили выше являются коллекциями. Коллекция — это группа однотипных объектов (или классов). Например Workbook s — это коллекция всех открытых в данный момент книг (объектов Workbook). А коллекция Worksheet s состоит из листов книги (объектов Worksheet), и так далее. Как правило все коллекции заканчиваются буквой S.
Давайте посмотрим, как обращаться к конкретным элементам коллекции в коде VBA.
Мы можем указать порядковый номер (начиная с единицы) элемента в коллекции. Worksheets(3) — в этом случае мы обращаемся к 3-му листу книги.
Мы также можем указать название листа в кавычках Worksheets("Название листа").
Аналогичным образом можно обратиться к конкретной книге Workbook("Названием книги").
Чтобы обратиться к объекту Excel (к листу, или ячейке) необходимо указать ссылку на этот объект, перечислив полный путь к нему в соответствии с объектной моделью.
Например, для ячейки A1 полная ссылка будет выглядеть следующим образом:
На самом деле полный путь писать не обязательно. Application — можно практически всегда не указывать. Workbooks("Название книги") — можно не указывать, если необходимо обратиться к ячейке из активной книги, а Worksheets("Название листа") можно не писать в случае если код должен выполнять действия на активном листе. Т.е. в можно упростить до:
Но всегда имейте ввиду, что это будет ячейка на активном листе.
Свойства объектов
Все объекты имеют свойства и методы. Например, объект Range (диапазон ячеек) имеет свойство Value, в котором содержится значение.
Вы можете получать значения из свойств объекта, просто указав путь к нужному свойству. Следующий код выведет значение ячейки A1 на активном листе.
А следующий код присвоит свойству Value новое значение 12345.
Протестируйте эти функции в своем файле.
Кстати, есть свойства объектов, которые доступны только для чтения. Т.е. вы сможете только получить значения этих свойств, но не сможете присвоить им другие значения. Например, свойство Range("A1").Address которое содержит адрес ячейки. При попытке записать новое значение в такое свойство будет возникать ошибка.
Или же свойство Worksheets.Count — вы можете только получить количество листов, но не можете изменить количество листов, задав новое свойство Count. Для этого существуют методы объектов.
Методы объектов
В отличие от свойств, которые просто сохраняют или передают значение, методы выполняют какие-либо действия с объектом. Например метод Worksheets.Add создает новый пустой лист в книге. Еще один пример это метод Clear, который позволяет очистить содержимое ячеек. Следующий код очистит данные и форматы из диапазона ячеек A1:C10.
Есть аналогичная функция, которая не удаляет при этом форматирование ячеек, а только очищает их от значений
Методы могут иметь дополнительные аргументы. Например, метод AddComment имеет обязательный аргумент Text, в котором необходимо передать текст комментария к ячейке.
Также можно использовать следующий код для вызова методов. Возможно это более привычная запись, когда по аналогией с функцией Excel мы в скобках передаем аргументы этой функции.
Приемы и лайфхаки
Автоматическое заполнение кода по начальным символам
Часто, особенно на первых порах, вы не запомните точное название всех объектов, но будете помнить их первые символы. В редакторе кода вы можете использовать отобразить список названий и автоматически записать объект по первым символам. Для этого используйте сочетание клавиш Ctrl+ J и увидите список из предложенных вариантов.
Просмотр свойств и методов у объекта
Когда вы будете писать свои программы, то редактор кода VBE будет подсказывать список свойств и методов у объекта. Чтобы отобразить этот список достаточно ввести объект, например, Worksheets и поставить точку, отобразится список свойств и методов. Выберите нужный с помощью мышки или же используйте стрелки. Вы также можете начать писать название свойства и редактор кода подберет подходящее. Когда выбор сделан — нажимайте клавишу Tab.
Явно указывайте тип переменных
Предыдущий лайфхак не будет работать для созданных локальных переменных до тех пор, пока вы явно не укажите их тип. Например, если вы захотели создать переменную MySheet в которой хотите хранить текущий лист. То список свойств и методов у такой переменной вы не увидите. Но стоит добавить строчку кода и явно указать тип переменной:
И подсказки снова заработают.
Быстрый просмотр справки
Все свойства и методы знать невозможно, так как их тысячи. Первое время вы будете активно пользоваться справкой. Советую использовать официальную справку на сайте Microsoft.
Чтобы быстро посмотреть информацию по интересующему вас объекту, свойству или методу просто поставьте курсор на интересующий элемент кода и нажмите клавишу F1. В окне браузера откроется официальная справка по этому элементу.
Поиск по объектной модели
Также вначале пути у вас постоянно будут возникать вопрос "А как называется свойство, которое отвечает за. ". Иногда мы и не заем есть ли такое свойство/метод вообще. Хороший вариант изучения — просмотр объектной модели. Переходите в редактор кода VBA и нажимайте клавишу F2. Откроется окно с поиском по объектной модели. Тут вы найдете все имеющиеся свойства, методы, события и прочие элементы в модели. Просматривайте их, как правило в названии элемента заложена его суть и вы найдете то что искали. А в процессе поиска будете запоминать и другие элементы, которые будут попадаться на глаза.
Давайте разберем на примере. Допустим нам надо защитить лист, но мы не знаем как точно зазывается свойство или метод.
- Открываем объектную модель клавишей F2.
- Так как мы хотим защитить лист, то логично предположить, что это метод в объекте Worksheet. Введем его в строку поиска и нажмем бинокль.
- В результатах поиска выбираем наш объект Worksheet и просматриваем его элементы.
- Находим метод Protect (Защита).
- Мы также можем просмотреть описание всех аргументов этого метода
- Конечно если что-то не понятно, то жмем клавишу F1 и открываем справку по этому методу с подробным описанием каждого аргумента.
На этом данную статью завершим, а в следующих мы более подробно поговорим про самый распространенный объект Excel — объект Range.
Обратитесь к ячейкам и диапазонам с помощью нотации A1
Обратитесь к ячейке или диапазону ячеек в стиле ссылки A1 с помощью свойства Range . Следующий подраутин изменяет формат ячеек A1:D5 на жирный.
В следующей таблице иллюстрируют некоторые ссылки в стиле A1 с помощью свойства Range .
| Reference | Смысл |
|---|---|
| Range("A1") | Ячейка A1 |
| Range("A1:B5") | Ячейки A1 через B5 |
| Range("C5:D9,G9:H16") | Выбор нескольких площадей |
| Range("A:A") | Столбец A |
| Range("1:1") | Строка 1 |
| Range("A:C") | Столбцы A через C |
| Range("1:5") | Строки от 1 до 5 |
| Range("1:1,3:3,8:8") | Строки 1, 3 и 8 |
| Range("A:A,C:C,F:F") | Столбцы A, C и F |
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Чтение и запись значения ячейки в VBA
В приложении Excel все данные как правило находятся в ячейках на листах, с которыми макросы работают как с базой данных. Поэтому, начинающему программисту VBA важно понимать как читать значения из ячейки Excel в переменные или массивы и, наоборот, записывать какие-либо значения на лист в ячейки.
Обращение к конкретной ячейке
Прежде чем читать или записывать значение в ячейке, нужно определиться с тем, как можно указать какая именно ячейка нам необходима.
Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:
- С помощью Range
- С помощью Cells
Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1
Однако, как правило, полный путь редко используется, т.к. макрос работает с Книгой, в которой он записан и часто на активном листе. Поэтому путь к ячейке можно сократить и написать просто:
Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе
Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале «.» (точку).
Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.
Так же, можно обратиться и к активной (выбранной в данный момент времени) ячейке.
Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.
Чтение значения из ячейки
Есть 3 способа получения значения ячейки, каждый из которых имеет свои особенности:
- Value2 — базовое значение ячейки, т.е. как оно хранится в самом Excel-е. В связи с чем, например, дата будет прочтена как число от 1 до 2958466, а время будет прочитано как дробное число. Value2 — самый быстрый способ чтения значения, т.к. не происходит никаких преобразований.
- Value — значение ячейки, приведенное к типу ячейки. Если ячейка хранит дату, будет приведено к типу Date. Если ячейка отформатирована как валюта, будет преобразована к типу Currency (в связи с чем, знаки с 5-го и далее будут усечены).
- Text — визуальное отображение значения ячейки. Например, если ячейка, содержит дату в виде «число месяц прописью год», то Text (в отличие от Value и Value2) именно в таком виде и вернет значение. Использовать Text нужно осторожно, т.к., если, например, значение не входит в ячейку и отображается в виде «#####» то Text вернет вам не само значение, а эти самые «решетки».
По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.
Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат «14 марта 2001 г.». Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.
Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат «Денежный» с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.
При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка «Type mismatch». Как определить тип значения в ячейке, рассказано в следующей статье.
Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.
Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.
Запись значения в ячейку
Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.
Пример 8: Записать в ячейку A1 активного листа значение 123,45
Все три строки запишут в A1 одно и то же значение.
Пример 9: Записать в ячейку A2 активного листа дату 1 марта 2018 года
В данном примере тоже запишется одно и то же значение в ячейку A2 активного листа.
Визуальное отображение значения на экране будет зависеть от того, какой формат ячейки выбран на листе.