Макросы. Автоматизация задач с помощью средства записи макросов — Excel

Таблицы Excel являются одним из самых популярных способов хранения и систематизации информации. Если внедрить в Excel макросы, то возможности программы можно расширить практически вдвое. Например, макросы помогают автоматизировать рутинные процессы и ускорить выполнение монотонной работы. Часто пользуются ими специалисты, которые в Excel переводят отчеты из 1С. При помощи внедренного кода их легко привести к единому стандартному шаблону.

Что представляют собой Excel-макросы?

Макросы используются не только в таблицах, но и во всем Microsoft Office. Они представляют собой созданный при помощи Visual Basic for Applications код. Если вы частично знакомы с web-программированием, то можно провести аналогию с Javascript. Excel-макросы делают примерно то же самое, что и Javascript с данными формата HTML.

Небольшая часть того, что может сделать макрос в офисных приложениях:

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

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

Чем могут быть опасны макросы в Excel?

Повредить файлы на ПК;
. похитить конфиденциальные данные.

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

Еще один способ работы вредоносного макроса - посредничество для внедрения угроз в систему. В этом случае он является своеобразными воротами для внедрения и генерирования троянского ПО. Им уже будет управлять не видоизменённый макрос, а сама операционная система, а следовательно, угроза становится более масштабной. Не всегда получается вылечить такие вирусы, иногда приходится полностью менять Windows OC или жесткий диск ПК (в зависимости от задач, которые ставил хакер при написании кода).

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

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

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

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

Создание начинается с меню «Вид». В нем нужно выбрать позицию «Макросы» и команду «Записать Макрос». Далее нужно задать имя без пробелов, например, «Format» (если код будет отвечать за изменение формата текста или ячеек).

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

Как включить макросы в Excel

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

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

Как увидеть программный код макроса?

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

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

Развернуть вкладку «Вид»;
. открыть «Макросы»;
. выбрать имя необходимого макроса;
. вызывать окно с его кодом при помощи кнопки «Изменить».

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

Как начать работу макроса по клику на ячейку

Допустим, вы хотите, чтобы при выделении ячейки А1 запускалась работа макроса. Чтобы выбранная в Excel ячейка макрос запускала, необходимо в первый модуль добавить программный код:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then: Call module1
End Sub

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

Где можно научиться работать с макросами

Есть несколько способов научиться созданию более сложных макросов. Они должны отличаться логикой и четко настроенной последовательностью действий. Создаются такие макросы только путем написания кода VBA вручную. Для создания сложных макросов вам потребуется затратить некоторое время на изучения тонкостей языка программирования Visual Basik.

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

Примеры

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

Sub Макрос1()

Работа с книгами

Макрос 1. Создание новой рабочей книги с нуля
Макроc 2. Сохранение книги при изменении определенной ячейки/диапазона
Макрос 3. Сохранение рабочей книги перед закрытием
Макрос 4. Защита рабочего листа в книге перед закрытием
Макрос 5. Снятие защиты с листа при открытии Excel-файла
Макрос 6. Открыть книгу на нужном листе
Макрос 7. Открытие конкретной книги, определенной пользователем
Макрос 8. Определяем, открыта ли книга
Макрос 9. Определяем, существует ли книга в папке
Макрос 10. Обновляем все связи в открытых книгах
Макрос 11. Закрываем сразу все книги
Макрос 12. Открыть все рабочие книги в папке
Макрос 13. Распечатать все книги в папке
Макрос 14. Не даем закрыть книгу до тех пор, пока не заполнит ячейку
Макрос 15. Создаем резервную копию текущей книги с сегодняшней датой

Работа с листами

Макрос 16. Добавляем новый рабочий лист и присваиваем имя
Макрос 17. Удалить все листы, кроме активного
Макрос 18. Скрыть все, кроме активного рабочего листа
Макрос 19. Отобразить все листы книги
Макрос 20. Перемещение рабочих листов
Макрос 21. Сортировка листов по названию
Макрос 22. Группа листы по Цвету ярлычка
Макрос 23. Скопировать лист в новую книгу
Макрос 24. Создать новую рабочую книгу для каждого листа
Макрос 25. Печать листов
Макрос 26. Защитить все листы
Макрос 27. Снять защиту со всех листов
Макрос 28. Создание оглавления
Макрос 29. Изменение масштаба изображения рабочего листа с помощью двойного щелчка
Макрос 30. Выделить столбец активной строки

Выделение и изменение диапазонов

Макрос 31. Выбор и форматирование диапазона
Макрос 32. Создание и выбор именованных диапазонов
Макрос 33. Перебор с помощью ряда клеток
Макрос 34. Выбор и форматирование диапазонов
Макрос 35. Вставка пустых строк в диапазоне
Макрос 36. Отобразить все скрытые строки и столбцы
Макрос 37. Удаление пустых строк
Макрос 38. Удаление пустых столбцов
Макрос 39. Выбор и форматирование всех формул в рабочем журнале
Макрос 40. Найдите и выберите первую пустую строку или столбец
Макрос 41. Применить дополнительный цвет заливки
Макрос 42. Отсортировать диапазоны по двойному щелчку
Макрос 43. Ограничение диапазона прокрутки в той или иной области
Макрос 44. Автоматически задать область печати листа

Работа с данными

Макрос 45. Копирование и вставка диапазона
Макрос 46. Преобразование всех формул в диапазоне в значения
Макрос 47. Преобразуем текстовых значений в числовые
Макрос 48. Преобразование тире в минус
Макрос 49. Удаляем лишние пробелы из всех ячеек в диапазоне
Макрос 50. Отсекаем слева 5 знаков в каждой ячейки диапазона
Макрос 51. Добавляем в ячейку недостающие нули
Макрос 52. Заменить пустые ячейку нулём
Макрос 53. Добавление текста в начало или конец ячейки
Макрос 54. Создание макроса преобразования данных
Макрос 55. Очистка данных (непечатаемые символы)
Макрос 56. Выделим дубликаты в диапазоне данных
Макрос 57. Скрываем повторяющиеся строки
Макрос 58. Выборочно скрываем стрелки автофильтра
Макрос 59. Копируем отфильтрованные строки в новую книгу
Макрос 60. Создание нового листа для каждого элемента в Автофильтре
Макрос 61. Показать отфильтрованные столбцы в строке состояния

Работа со сводными таблицами

Макрос 62: Создание обратной совместимости сводной таблицы
Макрос 63. Обновление всех сводных таблиц книги
Макрос 64. Создание «описи» всех сводной таблицы книги
Макрос 65. Создаем все сводные таблицы, используя тот же кэш данных
Макрос 66. Скрываем все промежуточные итоги в сводной таблице
Макрос 67. Изменяем названия данных всех полей сводной
Макрос 68. Принудительное суммирование для всех данных сводной
Макрос 69. Применить числовой формат для всех элементов данных
Макрос 70. Сортировка полей сводной в алфавитном порядке
Макрос 71. Применить пользовательскую сортировку к элементам данных
Макрос 72: Ставим защиту на сводную таблицу
Макрос 73. Применять ограничения сводного поля
Макрос 74. Автоматическое удаление листов с детализацией сводной
Макрос 75. Печать сводной таблицы для каждого элемента фильтра
Макрос 76. Создание нового файла для каждого элемента фильтра
Макрос 77. Готовим диапазон данных для сводной таблицы

Работа с диаграммами и графиками

Макрос 78. Изменение размера диаграмм на рабочем листе
Макрос 79. Привязываем график к определенному диапазону
Макрос 80. Создание набора бессвязных диаграмм
Макрос 81. Печать всех диаграмм на рабочем листе
Макрос 82. Отмечаем лучшее и худшее значение на
Макрос 83. Одинаковые цвета для значений на разных диаграммах
Макрос 84. Соответствие цвета диаграмм цвету диапазонов

Отправка писем из Excel

Макрос 85. Отправка активной книги почтой (вложение)
Макрос 86. Отправка диапазон значений в качестве вложения
Макрос 87. Отправка одного листа в качестве вложения
Макрос 88. Отправить письмо с ссылкой на наши файлы
Макрос 89: Рассылка писем с добавлением адресов в наш список контактов
Макрос 90. Сохранение всех вложений в отдельной папке
Макрос 91. Сохранение определенных вложений в папку

Взаимодействие с другими приложениями Office

Макрос 92. Запуск запроса доступа из Excel



Макрос 96. Сжатие базы данных Access из Excel
Макрос 97. Отправка данных Excel в документ в формате Word
Макрос 98. Делаем Слияние с документом в формате Word
Макрос 99. Отправка данных Excel в презентации PowerPoint
Макрос 100. Отправка Excel диаграмм в PowerPoint презентации
Макрос 101. Преобразование рабочей книги в презентации PowerPoint

Взаимодействие с другими приложениями Office
научимся работать с Word, Access и PowerPoint
Макрос 92. Запуск запроса доступа из Excel
Макрос 93. Запуск макроса Access из Excel
Макрос 94. Открытие отчета Access из Excel
Макрос 95. Открытие формы доступа из Excel

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

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

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

Макрос в Microsoft Office (да, этот функционал работает одинаково во многих приложениях пакета Microsoft Office) – это программный код на языке программирования (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. Иконка Стоп (маленький квадратик) указывает на то, что включен режим записи макроса. Нажатие на неё остановит запись. И наоборот, когда режим записи не включен, в этом месте находится иконка для включения записи макроса. Нажатие на неё даст тот же результат, что и включение записи через меню.

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

• =SUM(B2:K2) или =СУММ(B2:K2)
• =AVERAGE(B2:K2) или =СРЗНАЧ(B2:K2)
• =MIN(B2:K2) или =МИН(B2:K2)
• =MAX(B2:K2) или =МАКС(B2:K2)
• =MEDIAN(B2:K2) или =МЕДИАНА(B2:K2)

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

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

Соответственно:

• =SUM(L2:L21) или =СУММ(L2:L21)
• =AVERAGE(B2:K21) или =СРЗНАЧ(B2:K21) – для расчёта этого значения необходимо взять именно исходные данные таблицы. Если взять среднее значение из средних по отдельным строкам, то результат будет другим.
• =MIN(N2:N21) или =МИН(N2:N21)
• =MAX(O2:O21) или =МАКС(O2:O21)
• =MEDIAN(B2:K21) или =МЕДИАНА(B2:K21) – считаем, используя исходные данные таблицы, по причине указанной выше.

Теперь, когда с вычислениями закончили, займёмся форматированием. Для начала для всех ячеек зададим одинаковый формат отображения данных. Выделите все ячейки на листе, для этого воспользуйтесь комбинацией клавиш 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 – путь к файлу с обновляемыми данными. Процесс открытия этого файла и импорта данных из него тоже можно записать в макрос:

1. Откройте файл шаблона, в котором мы сохранили макрос – FormatData .
2. Создайте новый макрос с именем LoadData .
3. В процессе записи макроса LoadData сделайте импорт данных из файла data.csv – как мы это делали в предыдущей части урока.
4. Когда импорт будет завершён, остановите запись макроса.
5. Удалите все данные из ячеек.
6. Сохраните файл, как шаблон Excel с поддержкой макросов (расширение XLTM).

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

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

Грамотно организовать процесс работы в эксэле помогут такие объекты, как макросы в Excel.

Рассмотрим более подробно все особенности работы с данными объектами в пакете программ MS Office.

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

Что такое макросы и зачем они нужны

С макросами можно работать в любой из программ пакета MS Office. Прежде всего они нужны для того, чтобы грамотно организовать работу пользователя в программе.

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

Их создание и использование поможет существенно сэкономить время и максимально автоматизировать роботу в программе.

Важно! Благодаря созданному макросу можно просто мгновенно выполнять работу, в то время, как выполнение вручную занимало бы много времени.

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

С понятием макроса можно также столкнуться в программе Ворд, но в Экселе он имеет несколько преимуществ:

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

Отмечаем еще две статьи, которые могут вам пригодиться:

Создание собственных макросов в Excel 2010, 2007, 2013

Рассмотрим более подробно примеры создания в Excel 2007, 2013:

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

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

• Теперь можно перейти непосредственно к созданию самого макроса пользователя.
  После его создания, каждая ячейка будет автоматизирована – это означает, что любая ячейка пользовательского документа будет выполнять однотипное действие, которое укажет пользователь;
• Найдите во вкладке разработчика специальную клавишу для создания. Ее расположение указано на рисунке ниже;

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

• Далее нажмите ОК. Окно закроется и начнется процесс записи. Для остановки записи, нажмите соответствующую клавишу на панели управления;

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

Как включать и работать с макросами в Excel

Для этого следуйте нижеприведенной инструкции:

• На вкладке разработчика найдите кнопку под названием «Макросы». Нажмите на нее;

• Выберите нужный вам макрос из списка и нажмите кнопку «Выполнить»;

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

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

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

Рассмотрим два примера:

1. Ищем и используем команду

Например, нам необходима команда, которая бы вставляла в выделенные ячейки цифру "1". Запускаем поисковик, набираем поисковую фразу, получаем результаты, начинаем просматривать, находим код примерно в таком виде:

Sub Вставка1() Dim q As Object On Error Resume Next Set q = Cells For Each q In Selection q = 1 Next q End Sub

Выделяем данный код (без нумерации строк, начиная со слова Sub ) и нажимаем Ctrl+C . Переходим в свою рабочую книгу MS Excel и нажимаем сочетание клавиш Alt+F11 , у вас откроется окно редактора VBA:

В левом окне "Project - VBA Project" выбираем (щелкаем мышкой) нашу рабочую книгу, в которую необходимо вставить макрос, например, "VBAProject (Книга2)":

В пункте меню "Insert" выбираем "Module":

В левом окне "Project - VBA Project" у вас должна появиться новая папка "Modules" и в ней новый объект "Module1":

Переходим курсором в правое "Большое" поле для ввода и нажимаем Ctrl+V , скопированный макрос вставиться в модуль:

Макросы в MS Excel можно вставлять в следующие места:

• В Модуль, обычно вставляют код макросов, запуск которых будет производится по нажатию кнопки пользователем (как, например, в нашем случае) или код функций (формул);
• В Рабочий лист, обычно вставляют код макросов, запуск которых должен происходить автоматически в зависимости от действий пользователя или изменения данных в листе (поменялись данный, макрос выполнился);
• В Рабочую книгу, обычно вставляют код макросов, запуск которых должен происходить автоматически в зависимости от действий, производимых над книгой (файлом). Например, макрос, который запускается при открытии или закрытии книги, или при ее сохранении;
• Так же макросы могут быть частью пользовательской формы.

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

Чтобы вставить код в Рабочий лист, в левом окне редактора VBA выберите соответствующий лист, щелкните по нему два раза левой кнопкой мышки, переместите курсор в правое поле ввода и вставьте код.

Для вставки кода в Книгу, выберите "ЭтаКнига":

Давайте потренируемся. Вставьте код опубликованный ниже в "Лист1".

Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo a If Target = 2 Then VBA.MsgBox ("Ячейка " & Target.Address & " = 2") End If a: Exit Sub End Sub

Данный макрос выводит информационное сообщение если вы введете в любую ячейку листа "2".

Вернитесь в рабочую книгу, перейдите в "Лист1" и введите в ячейку "А1" цифру "2" и нажмите Enter , после чего у вас должно появиться следующее сообщение:

Если вы видите это сообщение, то вы все сделали правильно. Если нет, то вы вставили код куда-то не туда, повторите попытку.

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

2. Ищем и используем функцию

Находим в интернете VBA код функции, которая подсчитывает, например, количество слов в ячейке:

Public Function КолСловВЯчейке(Ячейка As Range) Dim q As Variant Application.Volatile q = VBA.Split(Application.WorksheetFunction.Trim(Ячейка.Value), " ") КолСловВЯчейке = UBound(q) + 1 End Function

Копируем код, нажимаем сочетание клавиш Alt+F11 , откроется редактор VBA:

Добавляем новый модуль в свою книгу и в этот модуль вставляем скопированный код:

Закрываем редактор VBA и переходим в свою рабочую книгу. Создаем новый лист (необязательно) в ячейку A1 вводим текст "мама мыла раму". Встаем в ячейку, в которой хотим получить результат (количество слов), в меню "Формулы" нажимаем кнопку "Вставить функцию":

В открывшемся окне "Вставка функции" в поле "Категория" выбираем "Определенные пользователем"

В списке доступных функций выбираем "КолСловВЯчейке", нажимаем "ОК":

Вводим необходимые аргументы и нажимаем "ОК":

Получаем результат:

Важно:

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

Если при вставке макроса в модуль у вас вместо некоторого текста стоят красные знаки "???????? "

Это значит, что у вас проблема с кодировкой, которая наблюдается с копированием Кириллического текста из некоторых браузеров. Чтобы победить эту проблему, попробуйте вставить скопированный код в пустой лист MS Excel, как "Текст в кодировке Unicode". Для этого перейдите в книгу MS Excel, выберите или создайте пустой лист, встаньте в ячейку "A1" и нажмите сочетания клавишCtrl+Alt+V . Должно будет появиться меню "Специальной вставки", выберите пункт "Текст в кодировке Unicode" и нажмите "OK".

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

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

Если вы не видите в редакторе VBA окна "Project - VBA Project", перейдите во вкладку меню "View" и в списке выберите пункт "Project Explorer" или нажмите сочетание клавиш Ctrl+R :