Запуск макроса при сохранении книги excel

Обновлено: 07.07.2024

Сведения о работе функции автосохранения в Excel, PowerPoint и Word ее влиянии на надстройки или макросы.

Общие сведения об автосохранении

Если файл размещен в облаке (т. е. в OneDrive, OneDrive для бизнеса или SharePoint Online), автосохранение позволяет автоматически и постоянно сохранять изменения, вносимые пользователем. Если другим пользователям предоставлен общий доступ к файлу, внесенные ими изменения объединяются в этой версии файла пользователя. Если функция автосохранения отключена, сохранение необходимо выполнять вручную, чтобы сохранить в облаке внесенные пользователем изменения и обеспечить для него получение изменений, вносимых другими пользователями.

В настоящее время Excel, Word и PowerPoint предоставляют событие BeforeSave, позволяющее разработчику выполнять код после запуска действия сохранения пользователем, но до его осуществления. В Excel также доступно событие AfterSave, которое может выполнять код макроса или надстройки после завершения сохранения.

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

Как правило, этих проблем можно избежать, если пользователь отключит функцию автосохранения. Можно сделать это от имени пользователя с помощью свойства AutoSaveOn в Word, Excel и PowerPoint, если оно доступно (см. приведенный ниже пример). Можно также принять меры в качестве разработчика, чтобы устранить эти проблемы для обеспечения безошибочной работы надстроек и макросов даже при включенном автосохранении.

Пример

В этом примере отключается функция автосохранения и пользователь уведомляется о том, что книга не сохраняется автоматически.

Возможные проблемы с событиями сохранения и автосохранением

Может потребоваться решение одной или нескольких из указанных ниже проблем, связанных с взаимодействием между событиями сохранения и автосохранением:

Проблема 1. Код в событиях BeforeSave или AfterSave выполняется слишком долго

Как правило, Word, Excel и PowerPoint не реагируют на действия пользователя при выполнении кода надстройки или макроса. Поэтому если код в обработчике событий BeforeSave или AfterSave выполняется слишком долго, это может значительно ухудшить впечатление от использования.

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

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

Пример сценария

Представьте себе надстройку, позволяющую пользователю создавать настраиваемые сопоставления на основе данных в книге Excel. Такая надстройка может содержать код BeforeSave, который сериализует любые сопоставления, созданные пользователем, и сохраняет их в части CustomXML книги. Завершение этого процесса может занять несколько секунд, и Excel может перестать отвечать на запросы во время его выполнения.

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

Если функция автосохранения включена, этот код BeforeSave запускается автоматически с определенной периодичностью, даже если пользователь находится посреди другого действия (например, вводит данные в ячейку), что может вызывать сильное раздражение.

Временное решение

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

Проблема 2. Код в событиях сохранения вызывает модальное диалоговое окно

Любой код, выполняемый в событии сохранения с отображением пользовательского интерфейса, например модального диалогового окна, может значительно ухудшить впечатление от использования при включенном автосохранении. Так как события BeforeSave и AfterSave выполняются автоматически с определенной периодичностью, эти диалоговые окна могут прерывать обычный рабочий процесс пользователя.

Пример сценария

Надстройка, проверяющая документ Word перед сохранением на предмет применения фирменной символики компании, может запустить диалоговое окно, которое оповещает пользователя о любых найденных проблемах и предлагает способы их решения. Так как событие BeforeSave теперь запускается автоматически и постоянно, это диалоговое окно проверки может внезапно открываться при выполнении пользователем других действий.

Временные решения

Рассмотрите возможность переноса кода, требующего отображения пользовательского интерфейса, в другие области приложения. Например, пользователь может нажать кнопку "Проверить", чтобы запустить процесс проверки, или можно запускать код проверки только в том случае, если пользователь пытается изменить существующие данные.

Если нужно запускать код проверки только при первом сохранении из нового документа (без запуска при последующих автосохранениях), рекомендуется проверка такого свойства, как Workbook.Path приложения Excel, перед отображением любого элемента пользовательского интерфейса во время событий BeforeSave или AfterSave. В приложении Excel свойство Workbook.Path должно быть пустым, если для книги еще не выбрано место сохранения.

Проблема 3. Код в событиях сохранения очищает стек отмены (только в Excel)

Как правило, при запуске определенных операторов VBA в Excel стек отмены очищается. Например, если изменяется значение ячейки с помощью запуска кода ActiveCell.Value = "myValue" , стек отмены очищается. Если такой код имеется в событии BeforeSave или AfterSave для макроса или надстройки при включенной функции автосохранения, пользователь макроса или надстройки часто не сможет отменять обычные действия должным образом.

Пример сценария

Надстройка, которая может содержать код, выполняемый в ответ на событие BeforeSave, проверяющий документ и записывающий значения в таблицу "журнал" в книге. Если функция автосохранения включена, это будет приводить к периодическому очищению стека отмены, что может раздражать пользователей.

Временное решение

Рассмотрите возможность удаления кода, записывающего значения в книгу в событиях BeforeSave или AfterSave. Например, описанная в примере сценария надстройка может быть изменена, чтобы журнал изменений сохранялся в отдельном файле или базе данных.

Проблема 4. Код в событии AfterSave загрязняет книгу (только в Excel)

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

Временное решение

Код, загрязняющий книгу в событии AfterSave, должен быть перемещен в событие BeforeSave или полностью другое расположение (см. раздел Проблема 3). На настоящий момент это решение не рекомендуется, даже без автосохранения, так как оставляет книгу в постоянном "грязном" состоянии, что приводит к появлению приглашения при закрытии, запрашивающего сохранение изменений у пользователя, даже если он не вносил дополнительных изменений.

Проблема 5. Код в событии BeforeSave отменяет сохранение файла (устанавливая для аргумента Cancel значение True)

На сегодняшний день можно отменить сохранение в событии BeforeSave, установив для аргумента Cancel значение True:

Если функция автосохранения включена, приложение (т. е. Excel, Word или PowerPoint) запускает действия сохранения автоматически на постоянной основе, пока в файле остаются несохраненные изменения. После изменения файла, внесенного пользователем, приложение пытается сохранить его.

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

Пример сценария

Надстройка, которая может полностью переопределить используемый по умолчанию код сохранения Word, чтобы файл сохранялся в корпоративной базе данных вместо диска или расположения SharePoint. Такая надстройка сначала отменит предпринятое действие сохранения перед попыткой выполнить сохранение в другом месте.

Временное решение

Такие надстройки должны проверять отключение функции автосохранения путем присвоения свойству AutoSaveOn значения False. Так как для включения автосохранения файл уже должен быть сохранен в расположении OneDrive или SharePoint, функция автосохранения должна быть отключена в большинстве версий этого сценария.

См. также

Поддержка и обратная связь

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

Макрос - это код, написанный на встроенном в Excel языке VBA (Visual Basic for Application). Макросы могут создаваться как вручную, так и записываться автоматически с помощью так называемого макрорекодера.

Макрорекодер - это инструмент в Excel, который пошагово записывает все что вы выполняете в Excel и преобразует это в код на языке VBA. Макрорекодер создает очень подробный код (как мы увидим позже), который вы сможете при необходимости отредактировать в дальнейшем.

Записанный макрос можно будет запускать неограниченное количество раз и Excel повторит все записанные шаги. Это означает, что даже если вы ничего не знаете о VBA, вы можете автоматизировать некоторые задачи, просто записав свои шаги и затем повторно использовать их позже.

Теперь давайте погрузимся и посмотрим, как записать макрос в Excel.

Отображение вкладки "Разработчик" в ленте меню

Перед тем как записывать макрос, нужно добавить на ленту меню Excel вкладку "Разработчик". Для этого выполните следующие шаги:

Щелкните правой кнопкой мыши по любой из существующих вкладок на ленте и нажмите «Настроить ленту». Он откроет диалоговое окно «Параметры Excel».
В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». Справа на панели «Основные вкладки» установите флажок «Разработчик».
Нажмите «ОК».

В результате на ленте меню появится вкладка "Разработчик"

Запись макроса в Excel

Теперь давайте запишем очень простой макрос, который выбирает ячейку и вводит в нее текст, например "Excel".

Вот шаги для записи такого макроса:

Поздравляем! Вы только что записали свой первый макрос в Excel. Хотя макрос не делает ничего полезного, но он поможет нам понять как работает макрорекордер в Excel.

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

Вы увидите, что как только вы нажмете кнопку "Выполнить", текст "Excel" будет вставлен в ячейку A2 и выбрана ячейка A3. Это происходит за миллисекунды. Но на самом деле макрос последовательно выполнил записанные действия.

Примечание. Вы также можете запустить макрос с помощью сочетания клавиш Ctrl + Shift + N (удерживайте клавиши Ctrl и Shift, а затем нажмите клавишу N). Это тот же самый ярлык, который мы назначили макросу при его записи.

Что записывает макрос?

Теперь перейдем к редактору кода и посмотрим что у нас получилось.

Вот шаги по открытию редактора VB в Excel:

Перейдите на вкладку "Разработчик".
В группе "Код" нажмите кнопку "Visual Basic".

Вы также можете использовать комбинацию клавиш Alt + F11 и перейти в редактор кода VBA.

Рассмотрим сам редактор кода. Далее коротко опишем интерфейс редактора.

Панель меню: содержит команды, которые можно использовать во время работы с редактором VB.
Панель инструментов - похожа на панель быстрого доступа в Excel. Вы можете добавить к ней дополнительные инструменты, которыми часто пользуетесь.
Окно проектов (Project Explorer) - здесь Excel перечисляет все книги и все объекты в каждой книге. Например, если у нас есть книга с 3 рабочими листами, она появится в Project Explorer. Здесь есть несколько дополнительных объектов, таких как модули, пользовательские формы и модули классов.
Окно кода - собственно сам код VBA размещается в этом окне. Для каждого объекта, указанного в проводнике проекта, есть окно кода, например, рабочие листы, книги, модули и т. д. В этом уроке мы увидим, что записанный макрос находится в окне кода модуля.
Окно свойств - вы можете увидеть свойства каждого объекта в этом окне. Я часто использую это окно для обозначения объектов или изменения их свойств.
Immediate Window (окно предпросмотра) - На начальном этапе оно вам не пригодится. Оно полезно, когда вы хотите протестировать шаги или во время отладки. Он по умолчанию не отображается, и вы можете его отобразить, щелкнув вкладку «View» и выбрав опцию «Immediate Window».

Когда мы записали макрос "ВводТекста", в редакторе VB произошли следующие вещи:

Был добавлен новый модуль.
Макрос был записан с именем, которое мы указали - "ВводТекста"
В окне кода добавлена новая процедура.

Поэтому, если вы дважды щелкните по модулю (в нашем случае модуль 1), появится окно кода, как показано ниже.

Вот код, который записан макрорекодером:

Теперь давайте пробежим по каждой строке кода и опишем что и зачем.

Код начинается с Sub, за которым следует имя макроса и пустые круглые скобки. Sub - сокращение для подпрограммы. Каждая подпрограмма (также называемая процедурой) в VBA начинается с Sub и заканчивается End Sub.

Range("A2").Select - эта строка выбирает ячейку A2.
ActiveCell.FormulaR1C1 = «Excel» - эта строка вводит текст "Excel" в активной ячейке. Поскольку мы выбрали ячейку A2 в качестве первого шага, она становится нашей активной ячейкой.
Range("A3").Select - выбор ячейки A3. Это происходит, когда мы нажимаем клавишу Enter после ввода текста, результатом которого является выбор ячейки A3.

Надеюсь, что у вас есть некоторое базовое понимание того, как записывать макрос в Excel.

Обращаем внимание, что код, записанный через макрорекордер, как правило, не является эффективным и оптимизированным кодом. Макрорекордер часто добавляет дополнительные ненужные действия. Но это не значит, что не нужно пользоваться макрорекодером. Для тех, кто только изучает VBA , макрорекордер может быть отличным способом проанализировать и понять как все работает в VBA.

Абсолютная и относительная запись макроса

Вы уже знаете про абсолютные и относительные ссылки в Excel? Если вы используете абсолютную ссылку для записи макроса, код VBA всегда будет ссылаться на те же ячейки, которые вы использовали. Например, если вы выберете ячейку A2 и введете текст "Excel", то каждый раз - независимо от того, где вы находитесь на листе и независимо от того, какая ячейка выбрана, ваш код будет вводить текст "Excel" в ячейку A2.

Если вы используете параметр относительной ссылки для записи макроса, VBA не будет привязываться к конкретному адресу ячейки. В этом случае программа будет "двигаться" относительно активной ячейки. Например, предположим, что вы уже выбрали ячейку A1, и вы начинаете запись макроса в режиме относительной ссылки. Теперь вы выбираете ячейку A2, вводите текст Excel и нажмите клавишу Enter. Теперь, если вы запустите этот макрос, он не вернется в ячейку A2, вместо этого он будет перемещаться относительно активной ячейки. Например, если выбрана ячейка B3, она переместится на B4, запишет текст "Excel" и затем перейдет к ячейке K5.

Теперь давайте запишем макрос в режиме относительных ссылок:

Макрос в режиме относительных ссылок будет сохранен.

Теперь сделайте следующее.

Как вы заметите, макрос записал текст "Excel" не в ячейки A2. Это произошло, потому что вы записали макрос в режиме относительной ссылки. Таким образом, курсор перемещается относительно активной ячейки. Например, если вы сделаете это, когда выбрана ячейка B3, она войдет в текст Excel - ячейка B4 и в конечном итоге выберет ячейку B5.

Вот код, который записал макрорекодер:

Обратите внимание, что в коде нет ссылок на ячейки B3 или B4. Макрос использует Activecell для ссылки на текущую ячейку и смещение относительно этой ячейки.

Не обращайте внимание на часть кода Range(«A1»). Это один из тех случаев, когда макрорекодер добавляет ненужный код, который не имеет никакой цели и может быть удален. Без него код будет работать отлично.

Что нельзя сделать с помощью макрорекодера?

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

Вы не можете выполнить код без выбора объекта. Например, если вы хотите, чтобы макрос перешел на следующий рабочий лист и выделил все заполненные ячейки в столбце A, не выходя из текущей рабочей таблицы, макрорекодер не сможет этого сделать. В таких случаях вам нужно вручную редактировать код.
Вы не можете создать пользовательскую функцию с помощью макрорекордера. С помощью VBA вы можете создавать пользовательские функции, которые можно использовать на рабочем листе в качестве обычных функций.
Вы не можете создавать циклы с помощью макрорекордера. Но можете записать одно действие, а цикл добавить вручную в редакторе кода.
Вы не можете анализировать условия: вы можете проверить условия в коде с помощью макрорекордера. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и запуска кода, если true (или другой код, если false).

Расширение файлов Excel, которые содержат макросы

Когда вы записываете макрос или вручную записываете код VBA в Excel, вам необходимо сохранить файл с расширением файла с поддержкой макросов (.xlsm).

До Excel 2007 был достаточен один формат файла - .xls. Но с 2007 года .xlsx был представлен как стандартное расширение файла. Файлы, сохраненные как .xlsx, не могут содержать в себе макрос. Поэтому, если у вас есть файл с расширением .xlsx, и вы записываете / записываете макрос и сохраняете его, он будет предупреждать вас о сохранении его в формате с поддержкой макросов и покажет вам следующее диалоговое окно:

Если вы выберете "Нет", Excel сохранить файл в формате с поддержкой макросов. Но если вы нажмете "Да", Excel автоматически удалит весь код из вашей книги и сохранит файл как книгу в формате .xlsx. Поэтому, если в вашей книге есть макрос, вам нужно сохранить его в формате .xlsm, чтобы сохранить этот макрос.

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

Исходные данные

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

Примечание! Я использую Excel 2013.

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

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

Итак, данные у нас есть, теперь необходимо написать процедуру на VBA (макрос), которая брала бы значение из конкретной ячейки, в данном случае это будет ячейка B14, и присваивала бы это значение имени файла.

Если нужно сохранять макрос в каждом файле, т.е. файлы с поддержкой макросов (расширение .xlsm), то необходимо просто указать другой тип файла при сохранении, а именно xlOpenXMLWorkbookMacroEnabled, в процедурах в комментариях я это указываю.

Открываем в Excel редактор Visual Basic, и вставляем код следующей процедуры в исходный код этой книги (ЭтаКнига, открыть двойным кликом) или в модуль, который Вы предварительно должны создать.

Примечание! Для того чтобы открыть редактор Visual Basic в Excel, необходимо перейти на вкладку «Разработчик» и нажать на кнопку «Visual Basic». Файл Excel с кодом процедуры необходимо сохранить с типом «Книга Excel с поддержкой макросов».

Код процедуры

После сохранения файла запустите макрос («Макросы -> Выполнить -> SaveFile»).

Добавление кнопки в Excel для запуска макроса

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

Затем выберите место, где вставить кнопку, и нажмите туда. После этого появится окно назначения действия, т.е. нужно выбрать, какой макрос запускать при нажатии этой кнопки, выбираем наш макрос, т.е. SaveFile, и нажимаем «ОК».

В итоге появится кнопка с названием «Кнопка», это название лучше изменить, например, на «Сохранить файл». Для этого нажмите правой кнопкой мыши на кнопку и выберите настройки «Изменить текст». В итоге у Вас должно получиться что-то вроде этого.

Теперь давайте представим, что заранее мы не можем определить, какая именно ячейка будет формировать название файла (может B14, а может и нет), поэтому мы можем немного скорректировать алгоритм таким образом, чтобы он брал значение из ячейки, которая является активной, но в этом случае Вы, конечно же, предварительно, должны выбрать ее (т.е. встать на нее).

Замените код процедуры следующим кодом, который совсем немного, но изменен.

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

Как видим, все отработало.

Сохранение файла Excel с названием, которое сформировано из значений двух ячеек

Теперь представим, что нам нужно сформировать файл с названием из значений двух ячеек. Например, в нашем случае это может быть «Марка Авто – VIN Номер», в качестве разделителя я указал символ – (дефис), но им может выступать любой символ или вовсе отсутствовать.

В этом примере я покажу, как можно это реализовать с привязкой к конкретным ячейкам, в нашем случае B14 и D14.

Код процедуры в данном случае будет выглядеть следующим образом.

Все ОК, файл создан.

Если вдруг нужно реализовать без привязки к конкретным ячейкам, например, значения хранятся в определённых столбцах, но конкретная строка неизвестна Вам заранее. Например, у меня несколько строк со значениями, и какие конкретно значения взять за основу названия файла, я хочу указывать самостоятельно, непосредственно перед сохранением, но при этом не редактировать код процедуры.

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

Код процедуры

Становитесь на любую ячейку со значением в столбце B, и запускайте макрос.

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

Запускать код VBA при закрытии или открытии книги

1. Включите книгу, нажмите Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Двойной клик ThisWorkbook в проекте - VBAProject панель, чтобы открыть ThisWorkbook (Код) окно.

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

3. Затем скопируйте свой собственный код без первой и последней строки и вставьте между Private Sub Workbook_Open() и End Sub как показано ниже.

4. Затем нажмите Сохраните кнопку, чтобы сохранить код, в появившемся диалоговом окне напоминания нажмите Нет.

5 Тогда Сохранить как появится диалоговое окно, сохраните файл как Excel Macro-Enabled Workbook и выберите папку для размещения файла.

6. Нажмите Сохраните кнопка. Теперь код будет работать, пока рабочая книга открыта.

Если вы хотите запускать код при закрытии книги каждый раз, выберите Деактивировать из правого раскрывающегося списка в Код: окно и скопируйте код, который вы будете запускать, и вставьте между Private Sub Workbook_Deactivate() и End Sub .

Читайте также: