Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

Что такое макрос и где его искать?


Скачать файл, используемый в видеоуроке:

  Tips_Macro_Basic_Video.xls (63,5 KiB, 1 279 скачиваний)


Наверное, многие слышали это слово "макрос", но не все имеют точное представление что это, если заглянули на эту страничку.
Макрос - это макрокоманда(так звучит в правильном переводе с англ.языка), содержащая последовательность действий, записанных пользователем. Запись производится с помощью встроенного в пакет Microsoft Office языка программирования - Visual Basic for Application(VBA).

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


 
ПОДГОТОВКА К ЗАПИСИ МАКРОСА
Прежде чем начать работать с макросами необходимо разрешить их выполнение, т.к. по умолчанию они отключены. Для этого необходимо сделать следующее:

  • Excel 2003:
    Сервис-Безопасность-Уровень макросов "Низкий"
  • Excel 2007:
    Кнопка Офис-Параметры Excel (Excel Options)-Центр управления безопасностью (Trust Centr)-Параметры центра управления безопасностью (Trust Centr Settings)-Параметры макросов (Macro Settings)-Разрешить все макросы (Enable All Macros)
  • Excel 2010:
    Файл (File)-Параметры (Options)-Центр управления безопасностью (Trust Centr)-Параметры центра управления безопасностью (Trust Centr Settings)-Параметры макросов (Macro Settings)-Разрешить все макросы (Enable All Macros)
  • После изменения параметров безопасности макросов необходимо перезапустить приложение Excel: закрыть его полностью и открыть заново. Только после этого изменения вступят в силу.

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

  • Макрорекордер записывает АБСОЛЮТНО ВСЕ ваши действия – ошибки, прокручивание экрана, переключение между листами, выделение ячеек и т.д. Поэтому перед записью своих действий необходимо сначала тщательно продумать все свои действия, чтобы в дальнейшем записать только то, что действительно необходимо записать и в дальнейшем воспроизвести. Это сократит как сам код, так и время его выполнения
  • Макрорекордер записывает действия выполненные только в пределах Microsoft Excel. Если вы переключитесь в другое приложение – действия в этой программе записаны не будут. Если закроете Excel – запись прекратится
  • Макрорекордер может записать только те действия, которые можно проделать вручную(те, которые доступны для выполнения из меню и с панелей)
  • Если во время кода вы совершили ошибочное действие и нажали кнопку отмены(Ctrl+Z) – отмененное действие не будет записано в макрос, как будто вы его просто не делали


 
ЗАПИСЬ МАКРОСА
Для пользователей Excel 2003 запись макроса возможна через меню:
Сервис -Макрос -Начать Запись
Начать запись

Для пользователей Excel 2007-2010 и старше:

  • С вкладки Разработчик (Developer): Группа Код (Code) -Запись макроса (Record Macro)
    Отобразить вкладку Разработчик (Developer) (если еще не отображена)
    Excel 2007: Параметры Excel(Excel Options)- Основные(Popular)- галочка напротив Показывать вкладку Разработчик на ленте(Show Developer Tab on Ribbon)
    Вкладка Разработчик

    Excel 2010: Файл (File)- Параметры (Options)- Настройка ленты (Customize Ribbon)- галочка напротив Разработчик (Developer)
  • Из строки состояния: Нажать кнопку "Запись макроса"
    Для записи макроса из строки состояния нужно убедиться в том, что в настройках Строки состояния стоит галочка напротив Запись макроса (Macro Recording)
    Строка состояния
    Посмотреть настройки строки состояния можно щелкнув правой кнопкой мыши на нижней границе приложения:
    Строка состояния

    Если данная галочка включена, то в строке состояния будет значек, отображающий текущее состояние записи
    Нет записи
    Нет записи
    Идет запись
    Идет запись


 
ПАРАМЕТРЫ ЗАПИСИ МАКРОСА
Перед записью можно задать имя записываемому макросу и назначить сочетание клавиш, при нажатии которых этот макрос будет запускаться. После нажатия кнопки для начала записи макроса появится окно:
Диспетчер записи макроса
Имя макроса – не должно содержать пробелов, запятых, двоеточий и т.п. Допустимы символы кириллицы, латиницы, цифры и нижнее подчеркивание (я рекомендую давать макросам имена, содержащие только буквы латиницы). Имя макроса всегда должно начинаться с буквы и не должно совпадать со встроенным именем Excel или именем другого объекта в книге (например, не должно носить имя Workbook, Cells или ЭтаКнига). Лучше давать макросу сразу понятное имя, отражающее примерную суть того, что он делает.
Сочетание клавиш – можно назначить вызов макроса сочетанием клавиш. Доступно назначение любой цифры или буквы в сочетании с Ctrl.
Сохранить в – выбираете место хранения записываемого макроса. Доступны варианты:

  • Эта книга (This Workbook) – макрос будет записан в той же книге, из которой была запущена запись. Записанный макрос будет доступен из приложения только если книга открыта
  • Новая книга (New Workbook) – будет создана новая книга, в которой записан макроса. Записанный макрос будет доступен из приложения только если книга открыта
  • Личная книга макросов (Personal Macro Workbook) – самый интересный вариант. Если его выбрать, то макрос будет сохранен в отдельной книге PERSONAL.XLS (для Excel 2007 и выше - PERSONAL.XLSB). Макросы, записанные в эту книгу доступны из всех открытых книг Excel и эта книга подключается автоматически при запуске самого Excel. Т.е. однажды записав где-то макрос в эту книгу – он теперь будет доступен из любой книги независимо от того, открыта ли книга, из которой был записан макрос или нет. Изначально эта книга отсутствует и создается в момент первого обращения к ней (т.е. как только вы первый раз выбрали Сохранить в личную книгу макросов)


 
ВОСПРОИЗВЕДЕНИЕ МАКРОСА
После записи макроса запустить его можно разными способами:

  1. ПРИ ПОМОЩИ СОЧЕТАНИЯ КЛАВИШ: Alt+F8
    Этот способ универсальный и его можно использовать из любой версии Excel. Достаточно нажать сочетание клавиш Alt+F8 и выбрать нужный макрос из списка:
    Вызов макроса
  2. ВЫЗОВ ИЗ МЕНЮ:
    Excel 2007-2010 и старше: вкладка Разработчик (Developer)Макросы (Macros)
    Excel 2003: СервисМакросМакросы
  3. НАЖАТИЕМ СОБСТВЕННОЙ КНОПКИ НА ЛИСТЕ:
    Можно разместить на листе собственную кнопку, по нажатии которой будет запускаться записанный макрос. Подробно этот процесс я описал в статье: Как создать кнопку для вызова макроса на листе
  4. ПРИ ПОМОЩИ НАЗНАЧЕННЫХ МАКРОСУ ГОРЯЧИХ КЛАВИШ:
    Если вы перед записью назначили макросу сочетание клавиш, то вы также можете вызвать этот макрос и нажатием данного сочетания. Горячие клавиши макросу можно назначить и после записи: Alt+F8→ Выделяем нужный макрос→ Параметры (Options).
  5. ПРИ НАСТУПЛЕНИИ ОПРЕДЕЛЕННОГО СОБЫТИЯ В ЛИСТЕ ИЛИ КНИГЕ:

    Прежде чем пробовать использовать этот метод рекомендуется изучить статью Что такое модуль? Какие бывают модули?Для этого сначала необходимо вставить код макроса в тело событийной процедуры (подробнее про событийные процедуры). Например, если выбрать процедуру листа Change, то в лист автоматически будет вставлена пустая процедура:

    Если ранее вами был записан код выделения и удаления двух столбцов – Макрос1:

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

    или вставить сам код (без Sub и End Sub) внутрь процедуры Worksheet_Change:


 
В каких файлах можно хранить макросы?
Как ни странно, но начиная с версии 2007 Excel не все файлы могут хранить макросы. Это значит, что если макрос был записан в файле и потом файл был сохранен в формате, который хранение макросов не поддерживает - макросы пропадут. Как правило Excel в таком случае выдаст предупреждение о том, что проект VBA в файле этого формата не может быть сохранен и будет удален, если нажать Да:
Сохранение без макросов
Чтобы сохранить файл с поддержкой макросов необходимо будет нажать Нет и из списка форматов выбрать тот, который поддерживает хранение макросов.
Ниже приведены форматы файлов, применяемые в Excel с описанием того, можно ли в них хранить макросы(VBA коды):

  • *.xls – формат книги Excel 97–2003 (поддержка VBA, макросов)
  • *.xla – надстройка Excel 97–2003 (поддержка VBA, макросов)
  • *.xlsx – формат книги Excel 2007 по умолчанию без поддержки макросов
  • *.xlsm – книга Excel 2007 с поддержкой макросов
  • *.xlsb – двоичный формат для больших объемов данных (с поддержкой VBA, макросов)
  • *.xltx – шаблон книги Excel 2007 без поддержки макросов
  • *.xltm – шаблон книги Excel 2007 с поддержкой макросов
  • *.xlam – надстройка Excel 2007

Для смены формата файла его необходимо сохранить в другом формате:

  • Excel 2007: Кнопка Офис -Сохранить как(SaveAs) -Выбрать необходимый формат файла
  • Excel 2010: Файл(File) -Сохранить как(SaveAs) -Выбрать необходимый формат файла

Теперь несложно понять, что хранить макросы в версиях Excel 2007 и выше можно в форматах: xls, xla, xlsm, xlsb, xltm, xlsm.

Также см.:
Почему не работает макрос?
Как создать кнопку для вызова макроса на листе?
Select и Activate - зачем нужны и нужны ли?
Как ускорить и оптимизировать код VBA


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Поиск по меткам

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 15 коммент.
  1. Лана:

    Спасибо, Ваша статья оказалась очень нужной и полезной в работе!

  2. Серый:

    Информация хорошо разжевана.
    Но вот кто бы рассказал, на каком языке говорит Эксель - как на его языке объяснить ему что мне надо сложить ячейки в которых формула СУММ, или ПРОЗВЕД ???

  3. Александр:

    Спасибо, пригодится.

  4. Rice:

    Если бы excel действительно записывал "АБСОЛЮТНО ВСЕ Ваши действия", я был бы практически счастлив. Но, к большому сожалению, excel по каким-то своим внутренним критериям выбирает, что ему записывать, а что нет.

  5. Rice, это действительно так. Притом в каждой версии Excel пропадают разные действия. Например, в 2007 не записываются действия по работе с объектами. Хотя в 2003 и 2010 это работает. И я не соглашусь, что по каким-то внутренним критерям - есть действия, которые может записать, а есть действия, которые записать не в состоянии по вполне объективным причинам. И в этом всегда есть закономерность, а никак не "по каким-то критериям".

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

  6. Юлия:

    Дмитрий, помогите, пожалуйста, случайно записала макрос в Личной книге макросов, теперь не знаю как эту книгу можно удалить, потому как теперь все экселевские файлы стали из-за ее появления чуть дольше открываться. макрос я удалила, а книга-то осталась. Help!!!

  7. Виктория:

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

  8. Виктория - однозначно что-то делаете не так. Макросы исключительно запоминают Ваши действия и воспроизводят их.

  9. Виктор:

    Подскажите, при помощи макроса можно выполнить такую задачу: есть 3 колонки в excell, в них вперемешку текст и пустые ячейки. Нужно сделать так чтобы столбец А равнялся на столбец С - если там пустая (или закрашенная) ячейка, то значение в столбце A переносилось ниже до тех пор пока в ячейка С (на одной линии) не будет содержать текст. Столбец B при этом не должен никак учитываться.

  10. Ирина:

    Не могу быстро сообразить, что придумать )), подскажите, плиззз, как можно прописать проще суммирование по формуле с разным количеством строк, кол-во строк зависит от внешнего документа. Например, в столбце 3 в ячейке С1 надо суммировать ячейки С2:С5, далее в ячейке С6 суммировать диапозон С7:С9, в ячейке С10 другой диапозон. И кол-во строк каждый раз зависит от критерия в колонке А. В данном случае заполнена Ячейки А1,А6,А10, соответственно с них и зависят диапазоны суммирования. Буду очень признательна!

    • Ирина, убедительная просьба - задайте свой вопрос на форуме. К этой статье он вообще никакого отношения не имеет. Или как минимум пройдитесь по разделу Хитрости и поищите там статью про СУММЕСЛИ.

      • Ирина:

        Дмитрий, спасибо! Задам на форуме. Прошу прощения, если сильно помешало сообщение здесь, можете смело удалить, а СУММЕСЛИ и СУММПРОИЗВ и макросы по сумме с критерием по цвету не работает, т.к. диапазон в одном столбике, но ограничен все время разным количеством строк.

Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти