Изучаем мойофис: как использовать макросы в редакторах документов и электронных таблиц

Введение

Зачастую нам приходится выполнять одни и те же действия в Excel большое количество раз. C такой «рутинной работой» рано или поздно сталкивается почти каждый пользователь Excel — формирование ежедневных и еженедельных отчетов, обработка однообразных данных и т.д.

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

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

Примеры возможностей макросов с описанием кодов

Макрос для объединения одинаковых ячеек в таблице Excel.Исходный код макроса для одновременного поиска и объединения ячеек с одинаковыми значениями в таблице. Работа со свойством Merge.

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

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

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

Как объединить столбцы в Excel используя макрос.Автоматическое горизонтальное объединение всех выделенных ячеек по столбцам таблицы с помощью VBA макроса. Как одновременно объединить ячейки в нескольких выделенных диапазонах?

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

Как запустить макрос в Excel разными способами.Как выполнить макрос с помощью кнопки, панели быстрого запуска и стандартными средствами на панели инструментов РАЗРАБОТЧИК?

Личная книга макросов в Excel и как ее использовать.Работа с личной книгой макросов и краткий обзор ее преимуществ. Где находиться личная книга макросов и как записывать в нее свои макросы?

Как создать и добавить надстройку в Excel с кодом VBA.Практическое пошаговое руководство по созданию надстроек с VBA кодом программ макросов. Управление, редактирование и удаление надстроек из программы.

Как написать макрос в Excel на языке программирования VBA.Как создать макрос с помощью редактора кода языка программирования Visual Basic for Application? Краткий обзор основных возможностей макросов.

Вариант 1: Автоматическая запись макросов

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

Подробнее: Включение и отключение макросов в Microsoft Excel

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

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

Открывается окно настройки записи макроса. Тут можно указать любое имя для него, если установленное по умолчанию вас не устраивает. Главное, чтобы имя это начиналось с буквы, а не с цифры, а также в названии не должно быть пробелов. Мы оставили название по умолчанию – «Макрос1».
Тут же при желании можно установить сочетание клавиш, при нажатии на которые макрос будет запускаться. Первой клавишей обязательно должна быть Ctrl, а вторую пользователь устанавливает самостоятельно. Мы в качестве примера установили клавишу М.
Далее следует определить, где будет храниться макрос. По умолчанию он расположен в этой же книге (файле), но при желании можно установить хранение в новой книге или в отдельной книге макросов. Мы оставим значение по умолчанию.
В самом нижнем поле можно оставить любое подходящее по контексту описание макроса, но это делать не обязательно. Когда все настройки выполнены, жмем на кнопку «OK».

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

Когда алгоритм был выполнен, щелкаем на кнопку «Остановить запись». Эта кнопка преобразовалась из кнопки «Запись макроса» после включения записи.

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

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

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

После этого открывается окно со списком записанных макросов. Ищем макрос, который мы записали, выделяем его и кликаем на кнопку «Выполнить».

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

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

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

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

Открывается «Microsoft Visual Basic» (VBE) – среда, где происходит их редактирование.

Запись каждого макроса начинается с команды , а заканчивается командой . Сразу же после указывается имя макроса. Оператор указывает выбор ячейки. Например, при команде «Range(«C4»).Select» выбирается ячейка «C4». Оператор используется для записи действий в формулах и других расчетов.
Попытаемся немного изменить макрос, дописав выражение:

Выражение заменим на .

Закрываем редактор и запускаем макрос. Как видим, вследствие введенных нами изменений была добавлена дополнительная ячейка с данными. Она также была включена в расчет общей суммы.
В случае если макрос слишком большой, его выполнение может занять значительное время, но внесением ручного изменения в код мы можем ускорить процесс. Добавляем команду . Она позволит сохранить вычислительные мощности, а значит, ускорить работу. Это достигается путем отказа от обновления экрана во время выполнения вычислительных действий. Чтобы возобновить обновление после выполнения макроса, в его конце пишем команду .
Добавим также команду в начало кода, а в его конец дописываем . Этим мы сначала отключаем автоматический пересчет результата после каждого изменения ячеек, а в конце макроса – включаем. Таким образом, Excel подсчитает результат только один раз, а не будет его постоянно пересчитывать, чем сэкономит время.

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

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

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

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

Личная книга макросов

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

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

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

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

Подпрограммы, размещенные в Личной книге макросов, можно просмотреть в окне «Список макросов», открыв его из любой рабочей книги Excel. Они будут перечислены с приставкой «PERSONAL.XLSB!». Функции в этом окне не отображаются.

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

Список пользовательских функций в Личной книге макросов

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

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

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

Что такое макрос и для чего он используется?

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

Читать еще:   Штрих код в word 2020

Макрос в программе Ворд может использоваться для следующих целей:

1. Для ускорения процесса обработки текста. В частности, с помощью этого элемента можно быстро заполнить требуемую область либо отформатировать содержимое документа.
2. Для того чтобы объединить несколько команд. Согласитесь, создавать табличную форму в Ворд и потом еще наполнять ее содержимым довольно проблематично. Такого рода инструмент позволит быстро создать нужный параметр и в дальнейшем просто вставить его в требуемое место.
3. Для того чтобы максимально упростить доступ к всевозможным параметрам, которые разбросаны в разных диалоговых окнах. Пользователи инструмента Ворд наверняка знают, как сложно порой отыскать требуемый элемент из-за обилия различных многоуровневых меню. Подобный компонент поможет заметно упростить эту задачу.
4. Для того чтобы автоматизировать обработку тех действий, которые являются весьма сложными и рутинными. Макросы в Ворд умеют доводить до автоматизма те параметры, на которые, казалось бы, уходит очень много драгоценного времени.

Примеры макросов для автоматизации работы

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

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

Макрос для слияния данных при серийной печати рассылок в Excel.Альтернатива инструменту слияния в Word для печати рассылок прямо из Excel. Исходный код макроса, который выполняет слияние данных для серийной печати рассылок.

Макросы для фильтра сводной таблицы в Excel.Как автоматизировать фильтр в сводных таблицах с помощью макроса? Исходные коды макросов для фильтрации и скрытия столбцов в сводной таблице.

Макрос позволяет рассчитать процент в сводной таблице Excel.Как быстро преобразовать значения сводной таблицы на доли в процентном соотношении к общим итогам по строкам и по столбцам? Исходные коды макросов для работы с процентами.

Макрос для настройки полей и форматирования сводной таблицы.Управление полями сводной таблицы с помощью макроса. Исходный код VBA-макроса для настройки и изменения структуры в сводных таблицах.

Макрос для создания сводной таблицы в Excel.Как автоматически сгенерировать сводную таблицу с помощью макроса? Исходный код VBA для создания и настройки сводных таблиц на основе исходных данных.

Макросы для изменения формата ячеек в таблице Excel.Как форматировать ячейки таблицы макросом? Изменение цвета шрифта, заливки и линий границ, выравнивание. Автоматическая настройка ширины столбцов и высоты строк по содержимому с помощью VBA-макроса.

VBA макрос для поиска скрытых строк и столбцов на листе Excel.Поиск всех скрытых строк и столбцов на рабочем листе с помощью VBA-макроса. Исходные коды макросов для получения сводной информации о скрытых строках и столбцах рабочего листа.

Макрос для копирования и переименования листов Excel.Как одновременно копировать и переименовывать большое количество листов одним кликом мышкой? Исходный код макроса, который умеет одновременно скопировать и переименовать любое количество листов.

Запись макросов макрорекордером

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

• Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу – запись останавливается.
• Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
• Если во время записи макроса макрорекордером вы ошиблись – ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) – во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Чтобы включить запись необходимо:

• в Excel 2003 и старше – выбрать в меню Сервис – Макрос – Начать запись (Tools – Macro – Record New Macro)
• в Excel 2007 и новее – нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

• Имя макроса – подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
• Сочетание клавиш – будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис – Макрос – Макросы – Выполнить (Tools – Macro – Macros – Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
• Сохранить в… – здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
  • Эта книга – макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
  • Новая книга – макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
  • Личная книга макросов – это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.

После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording).

Макрос Excel: пример 6

На примере этой процедуры показано использование операторов On Error и Resume для обработки ошибок. В данном коде также показан пример открытия и чтения данных из файла.

'Процедура Sub присваивает аргументам Val1 и Val2 значения ячеек A1 и B1
'из рабочей книги Data.xlsx, находящейся в папке C:\Documents and Settings

Sub Set_Values(Val1 As Double, Val2 As Double)

   Dim DataWorkbook As Workbook

   On Error GoTo ErrorHandling

      'Открываем рабочую книгу с данными

      Set DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data")

      'Присваиваем переменным Val1 и Val2 значения из заданной рабочей книги

      Val1 = Sheets("Лист1").Cells(1, 1)
      Val2 = Sheets("Лист1").Cells(1, 2)

      DataWorkbook.Close

   Exit Sub

ErrorHandling:

   'Если файл не найден, пользователю будет предложено поместить искомый файл
   'в нужную папку и после этого продолжить выполнение макроса

   MsgBox "Файл Data.xlsx не найден! " & _
      "Пожалуйста добавьте рабочую книгу в папку C:\Documents and Settings и нажмите OK"
   Resume

End Sub

Цикл FOR

В следующем примере Вы увидите, как использовать цикл FOR. Цикл FOR позволяет нам выполнить повторение цикла с разными значениями. Давайте посмотрим, как можно заполнить числами от 1 до 5 ячейки A1:A5.

Для этого на вкладке Developer (Разработчик) нажмите Visual Basic. Дважды кликните по объекту из списка Microsoft Excel Objects, в котором должен быть сохранён макрос. Введите вот такой код:

Sub Macro1 () For n = 1 To 5 Cells(n, 1) = n Next n End Sub

Сохраните файл. Чтобы выполнить макрос, перейдите View > Macros > View Macros (Вид > Макросы > Макросы), выберите из списка название нужного макроса и нажмите Run (Выполнить).

Следующий код отображает фразу “Hello World” в окне сообщений Windows.

Sub MacroName() MsgBox ("Hello World!") End Sub

В следующем примере мы создаём сообщение с выбором Yes (Да) или No (Нет). Если выбрать вариант Yes (Да), то значение ячейки будет удалено.

Sub MacroName() Dim Answer As String Answer = MsgBox("Are you sure you want to delete the cell values ?", vbQuestion + vbYesNo, "Delete cell") If Answer = vbYes Then ActiveCell.ClearContents End If End Sub

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

Если Вы нажмёте Yes (Да), значение в выделенной ячейке будет удалено. А если No (Нет) – значение сохранится.

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

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

Sub Colorir_interior_letra () Для N = 1 до диапазона («O65536»). End (xlUp) .Row

Выберите диапазон шкалы («O» и N) Случай “А” Диапазон («O» и N) .Interior.ColorIndex = 3 Диапазон («O» и N) .Font.ColorIndex = 1

Случай “B” Диапазон («O» и N) .Interior.ColorIndex = 4 Диапазон («O» и N) .Font.ColorIndex = 2

Случай “C” Диапазон («O» и N) .Interior.ColorIndex = 5 Диапазон («O» и N) .Font.ColorIndex = 3

Случай “D” Диапазон («O» и N) .Interior.ColorIndex = 7 Диапазон («O» и N) .Font.ColorIndex = 12

Case Else Диапазон («O» и N) .Interior.ColorIndex = 6 Диапазон («O» и N) .Font.ColorIndex = 4 End Select

Вводная часть – зачем нужны макросы

С 1993 года в разработке приложений для компьютеров началась революция, когда был создан объединённый продукт Microsoft Office, где Exel стал играть одну из ключевых ролей. Именно в это время появляется мощное дополнение VBA, позволяющее автоматизировать задачи Exel. Табличный процессор получил возможность не только предоставлять удобный интерфейс для умного пересчёта ячеек, но и стал полноценным продуктом для решения прикладных задач.

Рассмотрим на простых примерах как создавать программы. Автоматизация или программирование работы машины любит точные определения:

• Работаем в приложении Office 365 для дома. При этом рассматриваются те возможности, которые не принципиальны при переходе на работу в расширенные версии. Например, для малого бизнеса, где наиболее полноценно раскрываются возможности табличного процессора и макросы excel 2010.
• При описании будет использоваться интерфейс Exel 2016.
• VBA, диалект предметно ориентированного языка Visual Basic применяемый при работе с приложениями Microsoft Office.
• Макрос или макрокоманда — программный алгоритм действий, определённый пользователем. Кроме того, это понятие применяется для символьного имени книги-шаблона, в которой хранится код действий.
• Используется только функциональность, предусмотренная базовым вариантом табличного процессора, использующего макросы для excel 2013.
• Предполагается, что читатель знаком с возможностями табличного процессора, но никогда не использовал макрос в excel 2007.

Рассмотрим, как создать макрос в excel 2007 на примере решения очень простой задачи:

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

Задача — используя информацию кладовщиков (см. рис.1) сделать сводную таблицу за неделю. Посчитать общий вес поступившего цемента и по каждому кладовщику отдельно.

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

Пример 4

Следующая процедура «Sub» читает содержимое ячеек из колонки А другого листа, имеющего название «Sheet2» и с этими значениями осуществляет арифметические операции. Результат вычислений пишется в колонке А текущего листа.

Этот пример показывает:

1. Как объявлять переменные.
2. Объекты Excel.
3. Цикл Do Until. 
4. Доступ к листам электронных таблиц и диапазонам ячеек с текущей книги.

‘ Подпрограмма, запускающая цикл через значения в колонке А текущего листа

‘ «Sheet2», perform arithmetic operations on each value, and write the

‘ result into Column A of the current Active Worksheet («Sheet1»)

Sub Transfer_ColA()

Dim i As Integer

Dim Col As Range

Dim dVal As Double

‘ Установить переменную Col в колонку А листа 2

Set Col = Sheets(«Sheet2»).Columns(«A»)

i = 1

‘ Прохождение цикла через каждую ячейку колонки ‘Col’ до тех пор, пока

‘ не будет обнаружена пустая ячейка

Do Until IsEmpty(Col.Cells(i))

‘ Применение арифметических операций к значению текущей ячейки

dVal = Col.Cells(i).Value * 3 — 1

‘ Команда ниже копирует результат в колонку А

‘ текущего активного листа — без уточнения названия активного листа

Cells(i, 1) = dVal

i = i + 1

Loop

End Sub

Код макроса на VBA Excel

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

Примечание! По умолчанию данной вкладке в Excel 2010 может и не быть, чтобы ее отобразить нажмите правой кнопкой по ленте пункт меню «Настройка ленты»

затем, в правой области поставьте галочку напротив пункта «Разработчик»

После вкладка разработчик станет отображаться на ленте.

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

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

Sub Карточка()
'Книга
NewBook = ""
' Путь, где будут храниться наши карточки
' Т.е. в той папке, откуда запустился файл с макросом
Path = ThisWorkbook.Path
' Выбираем лист с данными
Sheets("Данные").Select
' Запускаем цикл, скажем на 100000 итераций
' Начиная со второй строки, не учитывая заголовок
For i = 2 To 100000
' Выйдем из него, когда фамилии закончатся, т.е. строки
If Cells(i, 1).Value = "" Then
        i = 100000
    Exit For
End If
' Имя файла карточки, назовем по фамилии
Name_file = Path & "\" & Sheets("Данные").Cells(i, 1).Value & ".xls"
‘Выбираем лист с шаблоном
Sheets("Шаблон").Select
' Присваиваем значения нашим ячейкам, по именам которые мы задавали
    Range("fio").Value = Sheets("Данные").Cells(i, 1).Value & " " & _
        Sheets("Данные").Cells(i, 2).Value & " " & Sheets("Данные").Cells(i, 3).Value
    Range("number").Value = Sheets("Данные").Cells(i, 4).Value
    Range("addres").Value = Sheets("Данные").Cells(i, 5).Value
    Range("dolgn").Value = Sheets("Данные").Cells(i, 6).Value
    Range("phone").Value = Sheets("Данные").Cells(i, 7).Value
    Range("comment").Value = Sheets("Данные").Cells(i, 8).Value
    ' Копируем все
    Cells.Select
    Selection.Copy
    ' Создаем новую книгу или делаем ее активной 
    If NewBook = "" Then
        Workbooks.Add
        NewBook = ActiveWorkbook.Name
    Else
        Workbooks(NewBook).Activate
        Cells(1, 1).Select
    End If
    ' Вставляем данные в эту книгу
    Application.DisplayAlerts = False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ' Сохраняем с нашим новым названием
    ActiveWorkbook.SaveAs Filename:= _
    Name_file, FileFormat:=xlExcel8, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    NewBook = ActiveWorkbook.Name
    Application.DisplayAlerts = True
    ' Снова активируем файл с макросом и выбираем лист
    Workbooks("Макрос.xls").Activate
    Sheets("Данные").Select
' Переходим к следующей строке
Next i
' Закроем книгу
Workbooks(NewBook).Close
' Выведем сообщение об окончании
MsgBox ("Выполнено!")
End Sub

Теперь осталось выполнить этот макрос, для этого откройте вкладку разработчик->макросы->выполнить наш макрос:

и после выполнения у Вас в той же папке появится вот такие файлы

Вот с таким содержимым:

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

Нравится24Не нравится1

Макросы

Последнее обновление: 22.05.2017

Все идентификаторы, определяемые с помощью директив #define, которые предполагают замену на определенную последовательность символов, еще называют макросами.

Макросы позволяют определять замену не только для отдельных символов, но и для целых выражений:

#include <stdio.h>

#define HELLO printf("Hello World! \n")
#define FOR for(int i=0; i<4; i++)

int main(void)
{
	FOR HELLO;
	return 0;
}

Макрос HELLO определяет вывод на консоль строки «Hello World! \n». А макрос FOR определяет цикл, который отрабатывает 4 раза.
И итоге после обработки препроцессора функция main будет выглядеть следующим образом:

int main(void)
{
	for(int i=0; i<4; i++) printf("Hello World! \n");
	return 0;
}

То есть данный код 4 раза выведет на консоль строку «Hello World! \n».

Подобные определения директивы #define имеют один недостаток, последовательность символов, которая используется директивой фиксирована. Например, здесь везде, где встретится в исходном коде идентификатор
HELLO, выводится строка «Hello World!». Но что, если мы динамически хотим передавать строку, то ест строка может быть любой.
В этом случае мы можем задать макроопределение с параметрами в следующей форме:

#define имя_макроса(список_параметров) последовательность_символов

Список_параметров здесь это список идентификаторов, разделенных запятыми. Между именем макроса и открывающей скобкой не должно быть пробелов.

Для обращения к макросу применяется конструкция:

имя_макроса(список_аргументов)

Список_аргументов — это набор значений, которые передаются для каждого параметра макроса.

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

#define print(a) printf("%d \n", a)

Здесь — это имя макроса или идентификатор, после которого в скобках указан параметр . Этот параметр будет представлять любое целое число. И
любой вызов макроса print будет заменяться на строку . Посмотрим, это будет выглядеть на примере:

#include <stdio.h>
#define print(a) printf("%d \n", a)

int main(void)
{
	int x = 10;
	print(x);
	int y =20;
	print(y);
	print(22);
	return 0;
}

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

#include <stdio.h>

#define t int
#define swap(t, x, y) { t temp = x; x = y; y=temp;}

int main(void)
{
	t x = 4;
	t y = 10;
	swap(t, x, y)
	printf("x=%d \t y=%d", x, y);
	return 0;
}

Макрос swap применяет блок для обмена значениями

Причем данный макрос фактически универсален: нам неважно, какой тип у переменных x и y

Или еще один пример — нахождение минимального значения:

#include <stdio.h>
#define min(a,b) (a < b ? a : b)

int main(void)
{
	int x = 23;
	int y = 14;
	int z = min(x,y);
	printf("min = %d", z);	// min = 14
	return 0;
}

То есть в данном случае после работы препроцессора вместо строки мы получим строку:

int z = (x < y ? x : y);

Препроцессорные операции

При обработки исходного кода препроцессор может выполнять две операции: # и ##.

Операция # позволяет заключать текст параметра, который следует после операции, в кавычки:

#include <stdio.h>
#define print_int(n) printf(#n"=%d \n",n);

int main(void)
{
	int x = 23;
	print_int(x);		// x=23
	int y = 14;
	print_int(y);		// y=14
	int number = 203;
	print_int(number);	// number=203
	return 0;
}

Директива ## позволяет объединять две лексемы:

#include <stdio.h>
#define print(a,b,c) printf("%d", a##b##c);

int main(void)
{
	print(2, 81, 34);	// 28134
	return 0;
}

Здесь на склеиваются три числа, которые передаются в макрос print. Или аналогичный пример:

#include <stdio.h>
#define unite(a,b,c) a##b##c;

int main(void)
{
	int x = unite(2, 81, 34);	// 28134
	printf("%d \n", x);
	return 0;
}

НазадВперед

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

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

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

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

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

Запуск макроса и назначение горячих клавиш

После остановки макрорекодера, протестируйте работу нового макроса. Для этого кликнете по пиктограмме «Макросы» на ленте вкладки «Вид». Должно появиться следующее окно со списком все макросов открытых книг:

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

• Имя макроса – его поменять здесь нельзя. Это возможно сделать только в коде записанной процедуры.
• Сочетание клавиш – укажите один символ того языка, в раскладке которого чаще приходиться работать, так как, например, сочетания Ctrl + q и Ctrl + й являются разными, но находятся на одной клавише. Так же имеет значение регистр символа. Если указать символ «Й», то горячими клавишами будет служить сочетание Ctrl + Shift + й.
• Описание – служит памяткой тому, кто записал макрос, и пояснением остальным.

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

Корректируем макрос

Созданный макрос можно изменить. Самая распространенная причина, которая приводит к такой необходимости – сделанные при записи ошибки. Вот как можно отредактировать макрос:

Нажимаем кнопку “Макросы” (или комбинацию Ctrl+F8).
В появившемся окошке выбираем наш макрос и щелкаем “Изменить”.
На экране отобразится окно редактора “Microsoft Visual Basic”, в котором мы можем внести правки. Структура каждого макроса следующая:
открывается с команды “Sub”, закрывается – “End Sub”;

после “Sub” отображается имя макроса;
далее указано описание (если оно есть) и назначенная комбинация клавиш;
команда “Range(“…”).Select” возвращает номер ячейки. К примеру, “Range(“B2″).Select” отбирает ячейку B2.
В строке “ActiveCell.FormulaR1C1” указывается значение ячейки или действие в формуле.

Давайте попробуем скорректировать макрос, а именно, добавить в него ячейку B4 со значением 3

В код макроса нужно добавить следующие строки: Range(“B4”).Select ActiveCell.FormulaR1C1 = “3”
Для результирующей ячейки D2, соответственно, тоже нужно изменить начальное выражение на следующее: ActiveCell.FormulaR1C1 = “=RC*RC*RC” .Примечание: Обратите внимание, что адреса ячеек в данной строке (ActiveCell.FormulaR1C1) пишутся в стиле R1C1.
Когда все готово, редактор можно закрывать (просто щелкаем на крестик в правом верхнем углу окна).
Запускаем выполнение измененного макроса, после чего можем заметить, что в таблице появилась новая заполненная ячейка (B4 со значением “3”), а также, пересчитан результат с учетом измененной формулы.
Если мы имеем дело с большим макросом, на выполнение которого может потребоваться немало времени, ручное редактирование изменений поможет быстрее справиться с задачей.
Добавив в конце команду Application.ScreenUpdating = False мы можем ускорить работу, так как во время выполнения макроса, изменения на экране отображаться не будут.
Если потребуется снова вернуть отображение на экране, пишем команду: Application.ScreenUpdating = True .

Чтобы не нагружать программу пересчетом после каждого внесенного изменения, в самом начале пишем команду Application.Calculation = xlCalculationManual , а в конце – Application.Calculation = xlCalculationAutomatic. Теперь вычисление будет выполняться только один раз.