Mini-ats102.ru

ООО “Мультилайн”
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Создание макросов в Microsoft Excel

Макрос записывается двумя способами: автоматически и вручную. Воспользовавшись первым вариантом, вы просто записываете определенные действия в Microsoft Excel, которые выполняете в данный момент времени. Потом можно будет воспроизвести эту запись. Такой метод очень легкий и не требует знания кода, но применение его на практике довольно ограничено. Ручная запись, наоборот, требует знаний программирования, так как код набирается вручную с клавиатуры. Однако грамотно написанный таким образом код может значительно ускорить выполнение процессов.

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

Когда все готово, приступаем к записи.

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

Включение записи макроса в Microsoft Excel

Настройки записи макроса в Microsoft Excel

Формула в Microsoft Excel

Запуск макроса

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

    Кликаем в том же блоке инструментов «Код» по кнопке «Макросы» или жмем сочетание клавиш Alt + F8.

Переход к запуску макроса в Microsoft Excel

Выбор макроса в Microsoft Excel

Редактирование макроса

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

    Снова щелкаем на кнопку «Макросы». В открывшемся окне выбираем нужный и кликаем по кнопке «Изменить».

Переход к изменению макроса в Microsoft Excel

Microsoft Visual Basic в Microsoft Excel

Изменение макроса в Microsoft Excel

Изменение кода в Microsoft Visual Basic в Microsoft Excel

Начало создания макроса

Макрос для отчетов формата Word

Для того, чтобы создать или просмотреть макрос для отчета формата Word необходимо:

Если макроса там не будет, его можно создать самостоятельно, введя в данном окне строки начала и конца макроса, а между ними — сам текст макроса:

Sub ПослеВыполненияОтчета(ob As Variant, app As Variant)

Внимание! В Word 2007 закладка «Разработчик» по умолчанию скрыта. Чтобы вывести ее на показ необходимо:

нажать кнопку Office в верхнем левом углу, нажать кнопку "Параметры Word";

Макрос для отчетов формата Excel

Для того, чтобы создать или просмотреть макрос для отчета формата Excel, необходимо:

Внимание! В Excel 2007 закладка «Разработчик» по умолчанию скрыта. Чтобы вывести ее на показ необходимо:

нажать кнопку Office в верхнем левом углу, нажать кнопку "Параметры Excel";

Записываем первый свой макрос

Шаблон таблицы - работаем в Эксель

На странице «Сводный» делаем шаблон исходной таблицы (смотреть рисунок).

Единственное, что надо знать для написания программы в excel — как включить макросы. По ряду существенных причин по умолчанию они отключены. На панели вкладок находим закладку «Вид» и активируем её. Если в базовой версии такой вкладки нет, то добавляем. На всплывающей панели активируем функцию «Макросы». Активируем кнопку «Запись Макроса». Теперь любое действие с таблицей будет фиксироваться и записываться в виде специальных кодов.

Выбираем имя, например «Склад».

Как производится активация макроса в Эксель

  • Назначаем сочетание клавиш, активирующих программу – «Ctrl +Я».
  • Выбираем сохранить в таблице – «Эта книга».
  • Описание – «Сортировка данных кладовщиков».
  • Подтверждаем клавишей OK (результат действий приведён на рис.).
Читайте так же:
Можно ли включать водонагреватель без воды

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

Используя стандартные операции и формулы заполняем шаблон (на рис. ниже приведена получившаяся форма).

Завершаем запись. Теперь при всех изменениях исходных данных, нажатием клавиш «Ctrl +Я», будут меняться значения в таблице-шаблоне. Даже на очень простом примере видно, как написать макрос в excel 2007.

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

Работа макроса в Exel - что и как

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

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

Предположим, вы добавили макрос VBA для книги, теперь вы хотите сохранить его как шаблон. При обычном методе сохранения в виде шаблона появится диалоговое окно с предупреждением, см. Снимок экрана ниже. Фактически, мы можем легко сохранить эту книгу как шаблон с поддержкой макросов.

  • Сохранить текущую книгу как шаблон с поддержкой макросов
  • Измените формат сохранения по умолчанию как шаблоны с поддержкой макросов
  • макрос шаблона документа включен 1

Сохранить текущую книгу как шаблон с поддержкой макросов

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

1. Нажмите Файл > Сохраните > Компьютер > Приложения.

2. В появившемся диалоговом окне «Сохранить как» введите имя этой книги в поле Имя файла и нажмите Сохранить как поле и выберите Шаблон Excel с поддержкой макросов (* .xltm) из раскрывающегося списка.

макрос шаблона документа включен 2

Примечание: Когда вы указываете тип книги как Шаблон Excel с поддержкой макросов (* .xltm), он автоматически откроет папку для сохранения шаблонов по умолчанию.

3. Нажмите Сохраните кнопку.

На данный момент вы уже сохранили книгу с макросами VBA как шаблон с поддержкой макросов.

Измените формат сохранения по умолчанию как шаблон с поддержкой макросов

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

1. Нажмите Файл > Параметры.

2. В открывшемся диалоговом окне «Параметры Excel» щелкните Сохраните в левой панели выберите Шаблон Excel с поддержкой макросов (* .xltm) из Сохраняйте файлы в этом формате выпадающий список в Сохранить книги раздел. Смотрите скриншот:

3. Нажмите OK кнопку.

С этого момента любая книга будет автоматически сохраняться как шаблон с поддержкой макросов при нажатии Файл > Сохраните.

Сохранить диапазон как мини-шаблон (запись автотекста, остальные форматы ячеек и формулы) для повторного использования в будущем

Обычно Microsoft Excel сохраняет всю книгу как персональный шаблон. Но иногда вам может просто потребоваться часто повторно использовать определенный выбор. По сравнению с сохранением всей книги в виде шаблона, Kutools for Excel предоставляет симпатичный обходной путь Авто Текст Утилита для сохранения выбранного диапазона как записи автотекста, в которой могут оставаться форматы ячеек и формулы в диапазоне. И тогда вы сможете повторно использовать этот диапазон одним щелчком мыши. Полнофункциональная бесплатная 30-дневная пробная версия!
совокупный годовой темп роста автотекста объявления

Читайте так же:
Можно ли герпес мазать солкосерилом

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

Всего можно задать макрос двумя способами:

  • Автоматически;
  • Вручную.

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

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

Включение автоматической записи макросов

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

Здесь же будет предоставлена только краткая инструкция по их включению:

  1. Откройте табличный редактор MS Excel и нажмите на кнопку «Файл», что расположена в верхней части окна.
  2. Здесь, в левом меню, выберите пункт «Параметры».

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

  1. Снова перейдите во вкладку «Разработчик», которая должна была появится после выполнения предыдущей инструкции.
  2. Здесь нажмите на кнопку «Запись макроса». Она расположена в блоке «Код» и в некоторых версиях может быть без подписи, поэтому смотрите её расположение на скриншоте ниже. Оно не меняется в зависимости от версии продукта.

После этого включится автоматическая запись действий для встроенного в программу макроса. Будут записываться все ваши действия в программе до тех пор, пока вы не остановите запись. Давайте для примера создадим макрос, который будет при запуске складывать содержимое нескольких ячеек. В нашем случае формула для макроса будет выглядеть так: =C4+C5+C6.

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

Давайте запишем наш первый макрос:

  1. Выделите ячейку, в которой будет записана формула.
  2. Пропишите формулу. В данном случае это формула вида a+b+c. Вместо букв потребуется вставить номера ячеек. Вид формулы можете посмотреть выше или перейти по ссылке с отдельной статьёй. Помните, что ячейки, которые вы указываете в формуле, не должны быть пустыми. В противном случае вместо решения вы получите ошибку.
  3. Макрос записал ваше действие с формулой. Нажмите Enter, чтобы она посчиталась.

Запускаем макрос

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

    Снова откройте вкладку «Разработчик» и там найдите большую кнопку «Макросы». Она расположена в блоке «Код». Также вы можете воспользоваться сочетанием клавиш Alt+F8.

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

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

Редактируем макрос

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

    Перейдите во вкладку «Разработчик», а затем откройте «Макросы» или воспользуйтесь комбинацией клавиш Alt+F8.

  • Sub – это команда отвечающая за начало записи макроса. Она обязательно стоит в самом начале, затем идёт наименование макроса, которое задал пользователь, а затем сам код макроса;
  • End Sub. Стоит в самом конце кода. Отвечает за остановку записи, следовательно, и выполнения макроса;
  • Range(«…»).Select. Это оператор, отвечающий за выбор ячейки. Например, команда Range(«A1»).Select выберет ячейку с идентификатором A1;
  • ActiveCell.FormulaR1C1. Отвечает за работу с формулами, которые используются при записи макроса. Также может быть использовано для других расчётов.

В итоге у вас должно получится, что в формулу добавится ещё одна ячейка, а в ней будет число 1158 или любое, которое вы зададите. При выполнении макроса вы получите указанные ячейки заполненными, а формулу посчитанной.

Во время работы с большими макросами вы можете столкнуться с тем, что его выполнение занимает много времени, а также потребляет значительную часть ресурсов машины. Обычно, данная проблема больше актуальна для старых компьютеров. Если вы хотите ускорить процесс выполнения, то в код придётся добавить команду Application.ScreenUpdating = False. Она отвечает за выполнение обновления экрана во время вычислительных действий. Если у неё после равно стоит значение «False», то обновление экрана после каждого действия макроса будет отключено, что позволит значительно сократить потребление вычислительных ресурсов компьютера. В конце вы только увидите результат вычисления.

Для возобновления обновления экрана после выполнения каждой задачи следует прописать команду Application.ScreenUpdating = True.

Также для оптимизации работы макросов в Excel можно воспользоваться командой Application.Calculation = xlCalculationManual, которая ставится в начальной позиции кода (примерное расположение отмечено на скриншоте). В конце кода вам нужно будет написать команду Application.Calculation = xlCalculationAutomatic. Первая команда отключит автоматический пересчёт результата после каждого изменения в ячейках, а вторая наоборот, включит его, но только уже после выполнения основной части макроса. Благодаря этому Excel будет считать результат только один раз, а не после каждого выполненного действия, что значительно сэкономит время и ресурсы. Правда, иногда подобного рода команды могут исказить результат подсчёта, поэтому в определённых условиях от использования этой команды приходится отказаться.

Создание макроса с чистого листа

Этот вариант подойдёт только для продвинутых пользователей, которые хорошо знакомы с языком Basic, так как на нём пишутся все макросы. Программа позволяет не только редактировать макрос в специальном редакторе, но и писать его с нуля в таком же редакторе.

  1. Перейдите во вкладку «Разработчик» и нажмите там на кнопку «Visual Basic».
  2. Откроется среда разработки, в которую вам и нужно будет прописывать код. Он пишется вручную, но некоторые плагины для офисного пакета Майкрософт могут значительно ускорить написание макросов.

Сами макросы позволяют значительно ускорить процесс работы в Excel и сопутствующих продуктах. В основном они завязана на автоматизации рутинных процессов, которые постоянно повторятся и никак (или почти никак) не меняются. Макросы можно записать без знаний кода и отредактировать/оптимизировать их, если имеются базовые представления о синтаксисе Basic. Если у вас имеются какие-либо вопросы по статье или предложения, то напишите их в комментариях ниже.

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

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

Как убрать отображение выполнения действий макроса?

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

Не забудьте включить функцию в конце макроса

Как убрать выделение копирования после выполнения макроса?

Если вы в своем макросе все такие выделяете диапазон для копирования и вставки, не забудьте в конце макроса поставить отключение выделения

Полезные макросы. Как найти последнюю строку или столбец диапазона

Эта конструкция поможет легко найти номер последней строки или столбца заполненного диапазона. Особенно удобно применять в циклах: вам не нужно задавать 1000 строк цикла с запасом. Excel сам найдет, где конец диапазона при помощи такой конструкции:

Причем, я сразу объявляю переменную как Long (длина 2 147 483 647), чтобы не попасть в ту ситуация, когда популярного Integer может не хватить (32 767) для больших таблиц.

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

Цикл For и проверка условия в цикле

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

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

Полезные макросы

Подсчет времени выполнения макроса

MsgBox выдает такой результат:
Полезные макросы. Подсчет времени выполнения макроса

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

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

Изменение макроса

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

Отобразите окно с макросами, выберите любой из имеющихся и нажмите кнопку «Изменить». Программа Вас перенаправит в редактор Visual Basic в модуль с кодом выбранного макроса. Если Вы точно следовали статье, то на экране должен быть приблизительно следующий скрипт (зеленый текст, расположенный после апострофа, является комментарием и не выполняется программой):

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

Дополните Ваш код в соответствии с нижеприведенным образцом:

Запустите макрос и убедитесь, что внизу страницы появилось наше сообщение:

Строка статуса, выведенная через макрос

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector