Как сохранить файл с поддержкой макросов

Как сохранить файл с поддержкой макросов

Создать макрос в Excel можно, запустив средство записи макросов или с помощью редактора Visual Basic для приложений (VBA). После создания макроса следует сохранить его для возможности повторного использования в текущей или в других книгах.

Сохранение макроса в текущей книге

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

Сохранить в виде книги с поддержкой макросов (XLSM-файл), выбрав Нет.

Сохранить в виде книги без поддержки макросов, выбрав Да.

Чтобы сохранить макрос как книгу с поддержкой макросов,

щелкните Нет.

В окне Сохранить как в раскрывающемся списке Тип файла выберите Книга Excel с поддержкой макросов.

Макросы в Microsoft Excel

Одна из примечательнейших функций в табличном редакторе Microsoft Excel – это макросы, средство записи последовательностей пользовательских действий, которые впоследствии можно повторить и применить к выбранным при записи или другим ячейкам. И автоматизировать таким образом множество рутинных операций, на которые, если таблица большая, может уйти просто масса времени. Макросы можно использовать для запуска разных операций на ленте меню Excel, операций сохранения, импорта-экспорта данных, смежных операций с другими приложениями MS Office и пр. Давайте же подробнее узнаем про макросы в Excel.

Макросы в Microsoft Excel

Итак, макросы в Excel – это запись определённой последовательности действий пользователя в табличном редакторе для автоматического выполнения этих же действий уже в дальнейшем. Работа макросов в Excel базируется на инструменте программирования Visual Basic для приложений VBA. А действия пользователей, которые можно автоматизировать с помощью макросов, могут быть разные. Это ввод данных в ячейки, выбор конкретных ячеек, строк или столбцов. Это запуск операций в ленте меню Excel, особенно макросы эффективны еще в применении к операциям форматирования. Это вставка диаграмм, рисунков, иллюстраций и прочих объектов. Это импорт разных данных, например, из Microsoft Access или SQL Server. Это сохранение и экспорт таблицы в определённые форматы файлов, например, в PDF или CSV. Единожды записав последовательность определённых действий, можно потом автоматически запускать эти же действия и для других ячеек, других данных, в отношении других таблиц или файлов Excel. А поскольку Visual Basic для приложений VBA поддерживают и другие приложения Microsoft Office, то в числе записываемых макросами операций могут быть и такие, что взаимодействуют с другими приложениями Microsoft Office. Можно, например, записать макрос открытия таблицы в Microsoft Outlook для отправки по почте.

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

В современных приложениях Microsoft Excel инструменты макросов добавлены во вкладку «Вид». Здесь можно запускать и останавливать создание макросов, и можно открыть окошко макросов для их выполнения или управления ими.

А если у вас в приложении Excel во вкладке «Вид» нет инструментов макроса, то жмите «Файл – Параметры». В параметрах приложения кликните «Настроить ленту» и установите галочку ленты «Разработчик». По итогу нажмите «Ок».

И ищите инструменты макросов в активированной ленте меню «Разработчик».

Впоследствии, если работа с макросами будет уже активной, их операции можно добавить и на панель быстрого доступа. Помимо этого, открыть окно управления макросами можно ещё и горячими клавишами Alt+F8.

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

Как создать макрос в Excel

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

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

Для примера есть таблица Excel с данными рейтинга браузеров. Чтобы зрительно выделить долю рынка только интересующих нас десктопных браузеров, давайте применим к их ячейкам какое-нибудь из витиеватых оформлений. Кликнем на одну из ячеек таблицы. Запускаем запись макроса.

В форме для записи макроса даём ему имя. Можно обратить внимание на опцию назначения горячих клавиш для него. И если вы будете активно пользоваться в дальнейшем конкретно этим макросом, тогда можно назначить любую удобную вам строчную или прописную букву, которая будет использоваться в сочетании с клавишей Ctrl. По итогу, нажимаем «Ок».

Запись макроса пошла. Применим к ячейке форматирование и зададим стиль.

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

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

Как выполнить макрос в Excel

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

Запустим окошко управления макросами (либо жмём клавиши Alt+F8). Выберем наш макрос, жмём «Выполнить».

И в мгновение ока записанные в макросе операции будут сразу же применены ко всем выделенным ячейкам таблицы.

Особенности макросов Excel с другими действиями

По рассмотренному принципу можно записывать макросы для любых действий в Excel. Но нужно понимать, что макросы — это всё же топорное и примитивное программирование. Записанная определённая последовательность из действий будет применяться только в определённых и конкретных условиях. Т.е., если мы запишем, например последовательность действий для нескольких ячеек, тогда макрос будет работать только для них и только в исходной таблице. Или другой пример – сохранение таблицы в определённом формате, макрос будет работать только по указанному пути сохранения на диске компьютера.

Сохранение файла Excel с макросами

Чтобы записанные макросы были доступными в дальнейшем в исходном файле Excel и других, исходный файл нужно сохранять как книгу Excel и с поддержкой макросов. Для этого, нажимаем «Файл – Сохранить как».

Указываем путь сохранения.

В типе сохраняемого файла выбираем «Книга Excel с поддержкой макросов».

Если у вас остались вопросы, оставляйте их в комментариях ниже. А пока… пока.

Как сохранить файл с поддержкой макросов

21.11.2007 00:03 | | |

Макросы создаются при помощи кода VBA Visual Basic lor Applications. Visual Basic для приложении. Если вы добавляете макрос в документ, файл документа необходимо будет сохранить с расширением, оканчивающимся на «m» (newi): либо в виде рабочей книги Excel с поддержкой макросов (xlsm), либо в виде шаблона Excel с поддержкой макросов (.xltm). Если вы попытаетесь сохранить рабочую книгу, содержащую макрос, под именем, с расширением, оканчивающимся на «х» (например,.xlsx или. хИх), Excel выведет на экран предупреждение о запрете операции. При создании этих типов файлов Excel код VBA не используется.

Чтобы сохранить рабочую книгу, содержащую макросы:

Нажмите кнопку Office, а затем выберите Сохранить как

БЫСТРЫЙ СПОСОБ

Чтобы выбрать тип файла Excel с поддержкой макросов, нажмите кнопку Office, выберите Сохранить как. а затем выберите Книга Excel с поддержкой макросов

Нажмите стрелку списка Папка и выберите накопитель или папку, где вы хотите сохранить файл.

Введите имя файла рабочей книги.

В случае необходимости нажмите стрелку списка Тип файла, а затем выберите нужный вам формат с поддержкой макросов:

Книга Excel с поддержкой макросов — рабочая книга с расширением, которая содержит код VBA.

Шаблон Excel с поддержкой макросов — шаблон с расширением xltm, который включает в себя утвержденные макросы.

Как записать макрос в Excel? Пошаговая инструкция.

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

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

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

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

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

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

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

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

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

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

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

1. Перейдите на вкладку "Разработчик".
2. В группе "Код" нажмите кнопку "Запись макроса". Откроется одноименное диалоговое окно.
   
3. В диалоговом окне "Запись макроса" введите имя для своего макроса, например "ВводТекста". Есть несколько условий именования, которые необходимо соблюдать при назначении макроса. Например, вы не можете использовать пробелы между ними. Обычно я предпочитаю сохранять имена макросов как одно слово, с разными частями с заглавным первым алфавитом. Вы также можете использовать подчеркивание для разделения двух слов — например, "Ввод_текста".
   
4. Если вы хотите, то можете задать сочетание клавиш. В этом случае мы будем использовать ярлык Ctrl + Shift + N. Помните, что сочетание, которое вы указываете, будет отменять любые существующие горячие клавиши в вашей книге. Например, если вы назначили сочетание Ctrl + S, вы не сможете использовать это для сохранения рабочей книги (вместо этого, каждый раз, когда вы его используете, он выполняет макрос).
   
5. В поле "Сохранить в" убедитесь, что выбрана опция "Эта книга". Этот шаг гарантирует, что макрос является частью рабочей книги. Он будет там, когда вы сохраните его и снова откроете, или даже если вы поделитесь файлом с кем-то.
   
6. Введите описание при необходимости. Обычно я этого не делаю, но если у вас много макросов, лучше указать, чтобы в будущем не забыть что делает макрос.
7. Нажмите "ОК". Как только вы нажмете OK, Excel начнет записывать ваши действия. Вы можете увидеть кнопку "Остановить запись" на вкладке "Разработчик", которая указывает, что выполняется запить макроса.
8. Выберите ячейку A2.
9. Введите текст "Excel" (или вы можете использовать свое имя).
10. Нажмите клавишу Enter. Вы попадете на ячейку A3.
11. Нажмите кнопку "Остановить запись" на вкладке "Разработчик".
    

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

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

1. Удалите текст в ячейке A2. Это нужно, чтобы проверить будет ли макрос вставлять текст в ячейку A2 или нет.
2. Выберите любую ячейку — кроме A2. Это нужно проверить, выбирает ли макрос ячейку A2 или нет.
3. Перейдите на вкладку "Разработчик".
4. В группе "Код" нажмите кнопку "Макросы".
5. В диалоговом окне "Макрос" щелкните макрос "ВводТекста".
6. Нажмите кнопку "Выполнить".
   

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Код начинается с 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.

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

1. Выберите ячейку A1.
2. Перейдите на вкладку "Разработчик".
3. В группе "Код" нажмите кнопку "Относительные ссылки". Он будет подсвечиваться, указывая, что он включен.
   
4. Нажмите кнопку "Запись макроса".
   
5. В диалоговом окне "Запись макроса" введите имя для своего макроса. Например, имя "ОтносительныеСсылки".
   
6. В опции "Сохранить в" выберите "Эта книга".
7. Нажмите "ОК".
8. Выберите ячейку A2.
9. Введите текст "Excel" (или другой как вам нравится).
10. Нажмите клавишу Enter. Курсор переместиться в ячейку A3.
11. Нажмите кнопку "Остановить запись" на вкладке "Разработчик".

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

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

1. Выберите любую ячейку (кроме A1).
2. Перейдите на вкладку "Разработчик".
3. В группе "Код" нажмите кнопку "Макросы".
4. В диалоговом окне "Макрос" кликните на сохраненный макрос "ОтносительныеСсылки".
5. Нажмите кнопку "Выполнить".

Как вы заметите, макрос записал текст "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, чтобы сохранить этот макрос.