Как программировать в excel

Обновлено: 02.07.2024

Некоторое время назад меня попросили «помочь с Экселем», а потом и работа подвернулась такая, так что за последние пару месяцев я узнал много полезного, чем и хочу поделиться в догонку к недавней статье.

Предполагается, что вы знаете основы Visual Basic. Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.

Visual Basic

Опции

Во-первых, в VB массивы могут начинаться с индекса 1, что для многих странно, поэтому в начале модулей можно прописывать:

Так же рекомендуется прописать:

В этом случае интерпретатор потребует заблаговременного объявления всех переменных. Переменные объявлять нужно потому, что:
— VB запомнит их нАпиСание и не будет исправлять во всём коде на последний введенный вариант;
— иногда возникают ошибки с передачей переменных byRef, если они не объявлены (то есть надо или объявить переменную, или приписать в функции/процедуре перед ней byVal).

Ещё одним важным оператором является ON ERROR. Привожу варианты:

Возможности языка

Хотя VB довольно прост, полезно почитать документацию по его синтаксису. Я, например, с удивлением узнал, что можно прописывать сложные усолвия в SELECT'ах (аналог switch):

Ускорение работы макросов

Часто макросы требуют долгого времени выполнения, которое можно значительно сократить. В начале и в конце каждой ресурсоёмкой функции вызвать Prepare и Ended.

Важно понимать, что VBA выполняет все действия так же, как и пользователь. Поэтому для того, чтобы установить параметры страницы, он каждый раз открывает и закрывает окно параметров. У меня выставлялись параметры для 10 листов, это реально не быстро. Поэтому делаем так:


Далее, часто нужно просмотреть различные диапазоны ячеек и что-то с ними сделать. Тут важно не использовать циклы for с перебором индексов, они медленные. Можно использовать встроенные функции Экселя, но удобнее всего такой вариант:
Данный код просматривает указанный диапазон, выбирает в нем «специальные ячейки», в данном случае все, в которых есть формулы (т.е. начинаются со знака равно). Для каждой ячейки смотрится, если она не закрашена, то её надо защитить (см. далее) и покрасить. Такой код работает очень быстро.

Для любых переменных, которым вы собираетесь присвоить книгу, лист, диапазон (ячейку) нужно предварительно объявить как Variant.

Естественно, что если вам нужны однотипные значения в ячейках, нужно использовать автозаполнение, всё равно как «растягивание» ячеек пользователем.

Второй диапазон должен включать первый, а второй необязательный параметр указывает тип автозаполнения.

Загрузка книги и события

При открытии книги каждый раз срабатывает процедура.
В данном случае настройки печати (поля, ориентация) сбрасываются на дефолтные. Можно и другую инициализацию выполнять. Важно, что если макросы отключены, то и не выполнится ничего. Если в Экселе вылезла вверху панелька с предупреждением о макросах и пользователь нажал «Включить», то именно в этот момент выполнится процедура Workbook_open().

Список доступных событий можно посмотреть вверху редактора VB. Например, я делал на событие Change проверку, где лежит ячейка, в которой было изменения, и если это нужный диапазон, то делалась запись в лог со старым и новым значением.

Защита

Во-первых сразу отмечу, что MS Office не исполняет макросы на компьютерах, где он не нашел антивируса, если книга зашифрована. Сталкивался на компьютерах, где антивирус был, но видимо Windows XP об этом не знала.

Ещё антивирус может странным образом мешать работе, вызывать ошибки, не совсем объяснимые. Показал айтишникам, сказали ок, что-то сделали, не знаю.

Итак, нам надо защитить книгу, чтобы ввод был разрешен только в нужные ячейки (формулы и заголовки поменять нельзя). Во-первых, нужно сделать соответствующие ячейки «не защищенными». Для этого делаем одно из:
— выделяем диапазон, формат ячеек, снять галочку «Блокировать ячейку»;
— выводим кнопку «Блокировать ячейку» в быстрый доступ и нажимаем её, очень удобно смотреть на неё чтобы понять, защищена ячейка или же нет;
— а это пригодится, чтобы проверить третий вариант — написать макрос, который снимает защиту с нужных ячеек сам.

Далее нужно защитить лист. На вкладке Рецензирование есть такая кнопка. Окошко просит ввести пароль и установить исключения (что можно будет делать пользователю). К сожалению, список исключений маловат. Самое обидное, что нельзя разрешить сворачивать/разворачивать группы столбцов/строк. Поэтому действуем так, на загрузку книги прописываем:
Знак подчеркивания продолжает логическую строку на следующей физической строке. Итак, здесь мы:
1. Сняли защиту.
2. Включили группировку.
3. Поставили защиту, при этом:
— защита только от юзера, макросы продолжают иметь полный доступ (!), крайне важно;
— разрешили сортировку, фильтрацию и форматирование строк/столбцов (высота/ширина);
— DrawingObject в данном случае снимает защиту с примечаний к ячейкам, может и ещё с чего.

Тут мы сталкиваемся с парой сюрпризов. Во-первых, не все макросы будут работать даже так. Известный баг, ничего не сделаешь. Нельзя вставить строку, например. Приходится снимать и тут же ставить защиту. Если «злоумышленник» в этот момент нажмет ctrl+break, то защита слетит.

Во-вторых, скажем никаким способом нельзя удалять строки (AllowDeletingRows), в которых есть защищенные ячейки, хоть одна. Подробнее вот тут.

Решением (костылем) является добавление кнопки или сочетания клавиш для удаления. Заодно можно проверить, чтобы пользователь не удалил чего не надо. В Workbook_open добавляем:

Теперь процедура будет вызываться при нажатии shift+delete.
Знаю, код некрасивый, простите. Здесь я пытался проверить, что выделена строка, то есть строк там 1, а ячеек не меньше тысячи. Чтобы удалить не то, придется выделить тысячу ячеек начиная не с первого столбца. Далее проверяется имя листа и номера строк. Вместо 50 был расчет последенй строки (ведь их число меняется, если мы их удаляем и добавляем).

Заключение

VBA — весьма глючная вещь, которая позволяет сворачивать горы в MS Office. Многие предприятяи используют модели на Excel годами, и если они сделаны хорошо, то всё работает.

Для изучения VBA подходит он сам, во-первых там хорошая справка. Например, чтобы узнать все варианты что можно разрешить в методе Protect, нажимаем F1, Protect, ввод. И вуаля.

Во-вторых, можно проделать требуемые действия вручную, записав макрос, а потом просмотрев его код. Код будет ужасен (например, при изменении параметров страницы, макрос запишет значения всех параметров и полей, а не только измененного вами), но ответы найдутся. Хотя, например, .AutoFit, который записывается при изменении высоты ячейки по содержимому (двойной клик на границе слева), на самом деле не работает.

Предлагаю знатокам поделиться своим опытом, дать советы в комментариях. Спасибо за внимание, удачных разработок вам.


В левой части редактора VBA расположен проводник проекта, в котором отображены все открытые книги Excel. Верхней строки, как на изображении, у вас скорее всего не будет, так как это – личная книга макросов. Справа расположен модуль, в который записываются процедуры (подпрограммы) с кодом VBA. На изображении открыт модуль листа, мы же далее создадим стандартный программный модуль.


После нажатия кнопки «Module» вы увидите ссылку на него, появившуюся в проводнике слева.

Первая программа на VBA Excel

Добавляем на стандартный модуль шаблон процедуры – строки ее начала и завершения, между которыми мы и будем писать свою первую программу (процедуру, подпрограмму).

  1. Откройте стандартный модуль двойным кликом по его ссылке в проводнике. Поместите в него курсор и нажмите кнопку «Procedure…» во вкладке «Insert» главного меню. Та же ссылка будет доступна при нажатии на вторую кнопку после значка Excel на панели инструментов.


В результате откроется окно добавления шаблона процедуры (Sub).



Имя процедуры может быть написано как на латинице, так и на кириллице, может содержать цифры и знак подчеркивания. Оно обязательно должно начинаться с буквы и не содержать пробелы, вместо которых следует использовать знак подчеркивания.

  1. Вставьте внутрь шаблона процедуры следующую строку: MsgBox "Привет" .


  1. Проверьте, что курсор находится внутри процедуры, и запустите ее, нажав клавишу «F5». А также, запустить процедуру на выполнение можно, нажав на треугольник (на изображении под пунктом меню «Debug») или на кнопку «Run Sub/UserForm» во вкладке «Run» главного меню редактора VBA Excel.

Работа с переменными

Чтобы использовать в процедуре переменные, их необходимо объявить с помощью ключевого слова «Dim». Если при объявлении переменных не указать типы данных, они смогут принимать любые доступные в VBA Excel значения. Комментарии в тексте процедур начинаются со знака «'» (апостроф).

32 комментария для “VBA Excel. Содержание рубрики”

Здравствуйте! У Вас нет темы по работе с фильтром. Можно проконсультироваться на этот счёт?

Здравствуйте, Сергей!
Задавайте вопрос, постараюсь ответить.

Спасибо, Евгений! Имеется в Excel столбец с включённым автофильтром. Можно ли программно запустить окно фильтра для выбора значений? Т.е. не руками нажимать на иконку фильтра, а заставить окно открываться с помощью макроса. Спасибо за ответ.

Да, это возможно:

Сергей, уточните вопрос: нужно программно нажать кнопки Ok и Отмена или отследить, какая из них была нажата?

Евгений, я разобрался, почему не выводилось окно с фильтром.

Пример отслеживания нажатий кнопки «OK» автофильтра. На нажатие кнопки «Отмена» код не реагирует.

myString = Range ( "A1" ) . CurrentRegion . SpecialCells ( xlCellTypeVisible ) . Address myString1 = Range ( "A1" ) . CurrentRegion . SpecialCells ( xlCellTypeVisible ) . Address If Not Intersect ( ActiveCell , Range ( "A1" ) ) Is Nothing Then

Range("A1").CurrentRegion можно заменить на имя таблицы.

Евгений, а формулу в поле B2 оставлять или её можно удалить?

Сергей, событие Worksheet_Calculate() не зависит от команды SendKeys "%" . Ограничение стоит здесь:

If Not Intersect ( ActiveCell , Range ( "A1" ) ) Is Nothing Then

Код в процедуре Worksheet_Calculate() срабатывает только когда активна ячейка Range("A1") . Если заменить в этой строке Range("A1") на диапазон строки заголовков таблицы, тогда код будет срабатывать при любой активной ячейке в заголовке.

В столбце «B» не должно быть пустых ячеек до последней строки таблицы.

Как определить в VBA есть узор в ячейке?

If ActiveCell . Interior . Pattern = xlPatternNone Then

Добрый день! Никак не могу решить проблему с заблокированным автофильтром на защищенном макросом (нашел на форумах) листе. Подскажите пожалуйста, куда и какую строчку нужно добавить в макрос, чтобы при открытии файла был активен автофильтр:

Set ws = ThisWorkbook . Sheets ( 1 ) 'можно указать любой лист книги 'устанавливаем защиту на все ячейки рабочего диапазона листа 'снимаем защиту только с пустых ячеек рабочего диапазона листа Set rr = ws . UsedRange . SpecialCells ( xlCellTypeBlanks ) Private Sub Workbook_BeforeClose ( Cancel As Boolean )

Добрый день, Владислав!
Замените строку

Добрый день, Евгений!
ОГРОМНОЕ СПАСИБО. :))

Здравствуйте, помогите, пожалуйста, решить задачу((

Здравствуйте, Рафия!
С разработкой кредитного калькулятора вам помогут только за плату на бирже фриланса.

Добрый день, Евгений! Я только начинаю работать с VBA. Помогите, пожалуйста, прописать код для автоматического перемещения курсора на ячейку вправо после заполнения предыдущей.

Здравствуйте, Ева! Для перемещения фокуса в ячейку справа код VBA не нужен: используйте для этого клавишу «Tab» или настройте клавишу «Enter» для перехода вправо (Файл >> Параметры >> Дополнительно).

Спасибо за ответ! Да, но это надо делать вручную; речь идёт об автоматическом перемещении курсора.

Вкладка с инструментами, связанными с VBA Excel, называется «Разработчик». Если этой вкладки нет на вашей ленте, добавьте ее через окно «Параметры»:

Окно «Параметры Excel»

  • Откройте окно «Параметры» через меню «Файл».
  • Выберите вкладку «Настроить ленту».
  • В правой колонке установите галочку у пункта «Разработчик» и нажмите «OK».

Кнопка – элемент управления формы

Вставка кнопки на лист

  1. Выберите вкладку «Разработчик» и нажмите на кнопку «Вставить».
  2. Нажмите на значок кнопки в коллекции «Элементы управления формы».

Добавление кнопки на рабочий лист (элемент управления формы)

  1. Кликните в любом месте на рабочем листе Excel.
  2. Откроется окно «Назначить макрос объекту». Нажмите «Отмена», так как макрос для этой кнопки еще не готов.
  3. После нажатия кнопки «Отмена», на рабочем листе появится новая кнопка из коллекции «Элементы управления формы» в режиме редактирования.

Кнопка (элемент управления формы)

Ухватив мышкой за один из кружочков, можно изменить размер кнопки. Ухватив кнопку за границу, можно перетащить ее в другое место. Также, в режиме редактирования, можно изменить название кнопки прямо на ее поверхности.

Чтобы выйти из режима редактирования кнопки из коллекции «Элементы управления формы», кликните в любом месте на рабочем листе.

Чтобы вернуться в режим редактирования кнопки, кликните по ней правой кнопкой мыши и выберите из контекстного меню нужный пункт. Если вы хотите изменить размер или размещение кнопки перетаскиванием, кликните левой кнопкой мыши в любом месте рабочего листа. После первого клика контекстное меню закроется, а кнопка останется в режиме редактирования.

Создание процедуры для кнопки

Кнопке из коллекции «Элементы управления формы» можно назначить макрос (процедуру), размещенную в стандартном программном модуле.

Создайте или откройте файл Excel с расширением .xlsm (Книга Excel с поддержкой макросов) и перейдите в редактор VBA, нажав сочетание клавиш «Левая_клавиша_Alt+F11».

Если вы не создавали ранее в этом проекте VBA стандартный программный модуль, нажмите кнопку «Module» во вкладке «Insert» главного меню. То же подменю откроется при нажатии на вторую кнопку (после значка Excel) на панели инструментов.

Вставка модуля в редакторе VBA Excel

Ссылка на модуль появится в проводнике слева. Если модуль создан ранее, дважды кликните по его ссылке в проводнике, и он откроется справа для редактирования.

Вставка процедуры в редакторе VBA Excel

В открывшемся окне добавления шаблона процедуры оставьте выбранным переключатель «Sub», вставьте в поле «Name» название процедуры «NovayaProtsedura» и нажмите «OK».

Окно добавления процедуры в редакторе VBA Excel

В стандартный программный модуль будет вставлен шаблон процедуры «NovayaProtsedura».

Поводом для заметки послужила статья на Хабре, в которой автор описывал, как он решал на Python задачу сбора и анализа метаданных из файлов Excel.

Эта заметка более подробно раскрывает всем известный тезис: Под конкретную задачу надо выбирать наиболее подходящий инструмент применимо к офисной автоматизации.

VBA и Python

VBA (Visual Basic for Applications), де-факто, самый популярный язык для автоматизации Microsoft Office. Доступен из коробки, помимо Excel, работает в PowerPoint, Outlook, Access, Project и других приложениях.

Если задать вопрос: «Какой язык программирования выбрать первым», то где-то в 90% всех случаев будет предложен Python. На практике здесь может быть и любой другой язык, но, исходя из популярности языка и своего опыта, буду сравнивать с ним.

В общем виде можно описать ситуацию через подобный график:


Детального сравнения не будет, рассмотрим основные killer-фичи, в ситуации, когда junior-программист/офисный сотрудник хочет автоматизировать что-либо, связанное с MS Office, и у него есть возможность выбора между языками.

Если в силу разных причин возможности выбора нет, то и сравнивать нечего.

В пользу VBA


  • Отличная работа с объектной моделью Excel и других приложений MS Office. Написание кода на VBA для большинства внутренних операций тривиально. У Python, в сравнении с VBA, поддержка объектной модели Office очень слабая.
  • Поддержка разных форматов MS Office. Самая большая проблема для внешних языков — это работа с разными форматами файлов MS Office. Например, xls, xlsx, xlsm файлы могут требовать разных библиотек, так как каждая хорошо работает только со своим форматом файла. Для VBA — это все "файл Excel", работа с которыми в целом одинаково хороша.
  • Работа с MS Exchange. Если необходимо обеспечить работу с корпоративной почтой/календарем на Exchange, то далеко не каждом языке есть нормальная библиотека для работы протоколом Exchange. В VBA это решается относительно просто через использование в макросе объектной модели MS Outlook.
  • Легкая установка и дистрибуция. К уже установленному офису не надо ничего устанавливать. Чтобы коллега мог воспользоваться программой, достаточно передать ему файл с макросом. Легко сделать надстройку, которая позволит "установить" модель макроса в фон офиса.
  • Интерактивность внутри приложений MS Office. Внутри офисных программ можно как просто поставить кнопки запуска макросов, так и (чуть сложнее) сделать целый отдельный UI. Сюда же относится написание своих формул в Excel и то, что макросы могут воздействовать на объекты внутри документов Office в реальном времени.
  • Запись макросов. Удобный инструмент, который позволяет записать действия человека в готовый код, для последующего редактирования использования.

В пользу Python (и других внешних языков программирования)


  • Приятный синтаксис и синтаксический сахар. Если коротко, то VBA не отличается выразительностью и удобством. Это вопрос личного вкуса, но для меня Python намного удобнее.
  • Богатая экосистема библиотек. Огромный выбор готовых библиотек для работы с внешним миром. Пытаться сделать на VBA программу, взаимодействующую с каким-нибудь внешним API, та еще боль. Занимательно, что как раз для работы с файлами Office библиотеки того же Python — откровенно "на троечку".
  • Хорошие средства разработки. Можно выбрать из огромного выбор программ, которые облегчают процесс разработки. Стандартный редактор VBA из Office предлагает очень бедный функционал и, в сравнении с альтернативами из мира Python, откровенно неудобен. Писать код VBA в внешнем редакторе, а потом копировать внутрь офиса для отладки — тоже неудобно.
  • Скорость работы. Не проверял скорость однопоточной работы, но, предположу, что в случае однопоточной работы преимущество будет за Python. В любом случае, достаточно тривиально организуется многопоточная обработка данных/файлов, что позволяет говорить в большей достижимой скорости.

Кейсы


Далее приведены конкретные задачи, которые я сам решал или автоматизировал, и когда мне надо было выбрать стек: VBA или Python. Для каждой задачи указан выбранный стек и даны пояснения почему:

  • Задача: Программа для проверки всех файлов Excel в директории на предмет наличия скрытых листов
    • Мой выбор: VBA. Причины: простота работы с разными форматами файлов Excel и отсутствие внешних взаимодействий.
    • Сервис был реализован как почтовый бот, на адрес которого пользователь может переслать файлы Office, а в ответ пользователю по почте приходит ответ с файлами PDF.
    • Мой выбор: Логика VBA + Python для мониторинга
      • Во-первых, внутренние функции гарантированно сохраняли PDF, аутентичный файлу PowerPoint (внешние библиотеки плохо справляются с рендером PowerPoint).
      • Во-вторых, реализация почтового бота, как макроса в MS Outlook решала проблемы работы с корпоративной Exchange почтой. Так, в Python нет нормальной библиотеки для работы с MS Exchange.
      • Python использовался для организации мониторинга работы сервиса и нотификации о возможных проблемах
      • Мой выбор: VBA. Задача решалась через конвертацию двух файлов в PDF и их объединением с Riffle Shuffle. Так как важно качество конвертации в PDF, то использовались встроенные функции офиса для экспорта в PDF.
      • Мой выбор: Python.
        • Чтобы вытаскивать из html карточек данные пригодилась библиотека для парсинга html BeautifulSoup.
        • Excel-файл создает программа, поэтому мы сами можем решать, какую аналитику рассчитывать уже в нем, а какую еще на стадии подготовки данных в Python.
        • Мой выбор: VBA. Важно было аккуратно работать с текстом во внутренних объектах файла PowerPoint. Для перевода использовался API от Яндекса, так как он бесплатен для небольших объемов и прост в подклюении. Например, API переводчика Bing я так и не смог заставить работать в VBA, так как там для работы нужен OAuth со своими заморочками. Если бы пришлось работать с Bing, то, наверное, я бы делал сервис-посредник на Python.
        • Мой выбор: Python. Хотя API — простой (не требует какой-либо подписи запросов или авторизации) и выдает данные в CSV, выбран Python, так как нет причин выбирать VBA, а на Python писать удобнее.