Как сделать таблицу с макросами

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

Что такое макрос?

Для начала немного о терминологии.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Как автоматизировать рутинные задачи в Excel с помощью макросов

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

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

Что такое Макрос?

Макрос в Microsoft Office (да, этот функционал работает одинаково во многих приложениях пакета Microsoft Office) – это программный код на языке программирования Visual Basic for Applications (VBA), сохранённый внутри документа. Чтобы было понятнее, документ Microsoft Office можно сравнить со страницей HTML, тогда макрос – это аналог Javascript. То, что Javascript умеет делать с данными в формате HTML, находящимися на web-странице, очень похоже на то, что макрос может делать с данными в документе Microsoft Office.

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

Создание макроса – практический пример

Для примера возьмём самый обычный файл CSV. Это простая таблица 10х20, заполненная числами от 0 до 100 с заголовками для столбцов и строк. Наша задача превратить этот набор данных в презентабельно отформатированную таблицу и сформировать итоги в каждой строке.

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

Чтобы создать макрос, откройте View (Вид) > Macros (Макросы) > Record Macro (Запись макроса…)

Дайте своему макросу имя (без пробелов) и нажмите ОК.

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

Excel сигнализирует о том, что включен режим записи макроса в двух местах. Во-первых, в меню Macros (Макросы) – вместо строки Record Macro (Запись макроса…) появилась строка Stop Recording (Остановить запись).

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

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

Далее, введите в ячейки формулы в соответствии с названиями заголовков (даны варианты формул для англоязычной и русифицированной версии Excel, адреса ячеек – всегда латинские буквы и цифры):

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

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

Далее, мы подведем итоги для всей таблицы, для этого делаем ещё несколько математических действий:

Теперь, когда с вычислениями закончили, займёмся форматированием. Для начала для всех ячеек зададим одинаковый формат отображения данных. Выделите все ячейки на листе, для этого воспользуйтесь комбинацией клавиш Ctrl+A, либо щелкните по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов. Затем нажмите Comma Style (Формат с разделителями) на вкладке Home (Главная).

Далее, изменим внешний вид заголовков столбцов и строк:

И, наконец, настроим формат итоговых значений.

Вот так это должно выглядеть в итоге:

Если Вас все устраивает, остановите запись макроса.

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

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

Чтобы очистить все ячейки от данных, щёлкните правой кнопкой мыши по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов, и из контекстного меню выберите пункт Delete (Удалить).

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

Важный момент! Если Вы сохраните файл с расширением XLTX, то макрос в нём работать не будет. Кстати, можно сохранить книгу как шаблон Excel 97-2003, который имеет формат XLT, он тоже поддерживает макросы.

Когда шаблон сохранён, можно спокойно закрыть Excel.

Выполнение макроса в Excel

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

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

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

Следующим шагом, мы импортируем последний обновлённый набор данных из файла CSV (на основе такого файла мы создавали наш макрос).

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

Когда импорт будет закончен, зайдите в меню Macros (Макросы) на вкладке View (Вид) и выберите команду View Macros (Макросы).

В открывшемся диалоговом окне мы увидим строку с именем нашего макроса FormatData. Выберите его и нажмите Run (Выполнить).

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

Заглянем под капот: Как работает макрос?

Как уже не раз упоминалось, макрос – это программный код на языке программирования Visual Basic for Applications (VBA). Когда Вы включаете режим записи макроса, Excel фактически записывает каждое сделанное Вами действие в виде инструкций на языке VBA. По-простому, Excel пишет программный код вместо Вас.

Чтобы увидеть этот программный код, нужно в меню Macros (Макросы) на вкладке View (Вид) кликнуть View Macros (Макросы) и в открывшемся диалоговом окне нажать Edit (Изменить).

Откроется окно Visual Basic for Applications, в котором мы увидим программный код записанного нами макроса. Да, Вы правильно поняли, здесь этот код можно изменить и даже создать новый макрос. Те действия, которые мы совершали с таблицей в этом уроке, вполне можно записать с помощью автоматической записи макроса в Excel. Но более сложные макросы, с тонко настроенной последовательностью и логикой действий требуют программирования вручную.

Добавим ещё один шаг к нашей задаче…

Представьте, что наш исходный файл с данными data.csv создаётся автоматически каким-то процессом и сохраняется на диске всегда в одном и том же месте. Например, C:\Data\data.csv – путь к файлу с обновляемыми данными. Процесс открытия этого файла и импорта данных из него тоже можно записать в макрос:

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

Если есть желание заняться программированием, Вы можете объединить действия этих двух макросов в один – просто скопировав код из LoadData в начало кода FormatData.

Источник

10 Примеры основных макросов в Excel

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

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

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

1) Отображение сообщения

Просто вставьте команду MsgBox в свой макрос. Посмотрите, как это делается в примере ниже:

MsgBox «текст сообщения»

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

2) Запустить макрос при открытии таблицы

Бывают случаи, когда макрос нужно открывать вместе с таблицей. В этом случае просто смонтируйте макрос с параметром Auto_Open (). Проверить пример

Sub Auto_Open ()
MsgBox «Чтобы узнать все об Excel, перейдите на сайт www.https: //luz.vc/»
End Sub

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

3) Рабочий лист с текущей датой и временем

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

Sub writeDataEHora ()
Диапазон («A1») = Сейчас
Конец подписки

4) Выполните одно и то же действие для каждой выделенной ячейки

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

Sub doAlgoACadaCellula ()
Для каждой ячейки в выделении.
Ячейка MsgBox
Далее
Конец подписки

5) То же самое во всех выделенных ячейках

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

Sub doSomethingAllCells ()
Selection.Cells.Value = «Привет»
Конец подписки

Вы можете сделать множество вставок в код, например, изменить цвет текста, размер шрифта, тип шрифта и многое другое.

6) Определение формул в каждой ячейке

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

Sub checkFormula ()
Если Range («A1»). HasFormula = True, то
MsgBox «Формула есть»
Еще
MsgBox «Это не формула»
Конец, если
Конец подписки

7) Изменение цвета ячейки при наведении

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

Private Sub Worksheet_SelectionChange (цель ByVal как диапазон)

Тусклая линия: начало как диапазон
Тусклая линия как диапазон
Dim Linha2 на всю длину

Установите LinhaInicio = Range («A» и Linha2, Target)

‘Закрашивает из выбранной ячейки в столбец 5
Установить Line = Range (Cells (Target.Row, 1), Cells (Target.Row, 5))

С линией
.Interior.ColorIndex = 12
Конец с

8) Изменение цвета интерьера и шрифтов

Этот макрос изменяет цвета внутри и у источника ячеек в соответствии с буквой ячеек.

9) Говорящий макрос

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

10) Защита электронных таблиц с логином и паролем

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

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

А вы уже используете какой-либо макрос в повседневной жизни? Поделитесь с нами своим опытом, оставив комментарий или связавшись с нами!

Вы сомневаетесь? Приходите Форум LUZ Excel и отправьте свой вопрос!

Источник