Как в excel добавить макрос: Как вставить готовый макрос в рабочую книгу?

Как создать оглавление в Excel используя VBA макрос

Главная » Макросы (VBA)

Автор Дмитрий Якушев На чтение 4 мин. Просмотров 3.1k.

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

Содержание

  1. Как макрос работает
  2. Код макроса
  3. Как этот код работает
  4. Как использовать

Как макрос работает

На первый взгляд данный макрос выглядит сложным. Тем не менее, рассмотрим несколько простых действий, как это происходит

  1. Удаляет любую предыдущую таблицу Содержание листа
  2. Создает новую таблицу Содержание листа
  3. Захватывает имя каждого листа и вставляет его в оглавлении
  4. Добавляет гиперссылку к каждой записи в оглавлении

Код макроса

Sub SozdatOglavlenie()
'Шаг 1: Объявить переменные
Dim i As Long
'Шаг 2: Удалить предыдущий лист с названием Оглавление и перейти к 'следующему
On Error Resume Next
Application. DisplayAlerts = False
Sheets("Table Of Contents").Delete
Application.DisplayAlerts = True
On Error GoTo 0
'Шаг 3: Добавить новый лист в качестве первого
ThisWorkbook.Sheets.Add _
Before:=ThisWorkbook.Worksheets(1)
ActiveSheet.Name = "Оглавление"
'Шаг 4: Запустить счетчик
For i = 1 To Sheets.Count
'Шаг 5: Выбрать следующую доступную строку
ActiveSheet.Cells(i, 1).Select
'Шаг 6: Добавить имя листа и гиперссылку
ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveSheet.Cells(i, 1), _
Address:="", _
SubAddress:="'" & Sheets(i).Name & "'!A1", _
TextToDisplay:=Sheets(i).Name
'Шаг 7: Цикл назад приращение I
Next i
End Sub

Как этот код работает

  1. Шаг 1 объявляет целую переменную с именем I, чтобы служить в качестве счетчика, который перебирает листы.
  2. В предыдущих макросах переменная выбирала каждый рабочий лист. В этой процедуре мы используем счетчик (наша переменная I). Основная причина в том, что мы не только должны следить за листами, но мы также должны суметь ввести имя каждого листа на новую строку в таблицу оглавления. Идея заключается в том, что, как счетчик проходит через листы, он также служит для перемещения курсора вниз в таблице содержания, каждая новая запись идет на новую строку.
  3. Шаг 2, по существу пытается удалить любой предыдущий лист под названием «Оглавление». Шаг 2 должен начаться с On Error Resume Next — обработчика ошибок. Если возникает ошибка — происходит удаление оглавления листа с использованием метода DisplayAlerts. Наконец, мы сбрасываем обработчик ошибок, снова введя On Error GoTo 0.
  4. На шаге 3 добавляем новый лист в книгу, используя аргумент, чтобы поместить новый лист в качестве первого листа. Затем именуется лист как «Оглавление». Как мы уже упоминали ранее в этой части, при добавлении нового рабочего листа, то он автоматически становится активным. Поскольку этот новый лист имеет фокус на протяжении всей процедуры, любые ссылки на ActiveSheet в этом коде, обращаются к таблице содержания листа.
  5. Шаг 4 запускает счетчик I и происходит подсчет всех листов в книге. Когда будет достигнуто максимальное количество листов, конец макрокоманды.
  6. Шаг 5 выбирает соответствующую строку в таблице содержания листа. То есть, если счетчик на 1, он выбирает первую строку в таблице содержания листа. Если счетчик I находится в 2, она выбирает вторую строку, и так далее.
  7. Мы можем сделать это с помощью пункта меню Cells. Элемент
    Cells обеспечивает чрезвычайно удобный способ выбора диапазонов с помощью кода. Для этого требуется только относительные строки и столбца позиции в качестве параметров. Таким образом, клетки (1,1) приводит к строке 1, столбец 1 (или ячейку A1). Ячейка (5, 3) приводит к 5-й строке, столбце 3 (или ячейка C5). Числовые параметры в пункте ячейки особенно удобны, когда вы хотите, чтобы цикл шел через серию строк или столбцов, используя увеличивающееся номер индекса.
  8. Шаг 6 использует метод Hyperlinks.Add, чтобы добавить имя листа и гиперссылки к выбранной ячейке. Параметры методом Hyperlinks. Add необходимы для построения гиперссылок.
  9. Последний шаг в макросе возвращает к началу цикла приращения счетчика I к следующему. Когда счетчик достигает числа I конец макрокоманд.

Как использовать

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

  1. Активируйте редактор Visual Basic, нажав ALT + F11.
  2. Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
  3. Выберите Insert➜Module.
  4. Введите или вставьте код во вновь созданном модуле.

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

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

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

Содержание: “Как включить/отключить макросы в Excel”

  • Активация и выключение макросов во вкладке Разработчика
  • Настройка макросов в Параметрах программы
  • Настройка макросов в ранних версиях Эксель
  • Заключение

Активация и выключение макросов во вкладке Разработчика

Смотрите также: “Как закрепить заголовок таблицы в Эксель”

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

  1. Щелкаем левой кнопкой мыши по меню «Файл».
  2. Затем, внизу раскрывшегося списка выбираем пункт «Параметры».
  3. В параметрах программы нас интересует пункт «Настройка ленты». Далее ставим галочку напротив вкладки “Разработчик”. Теперь подтверждаем действие нажатием кнопки «OK».

По завершении этих действий вкладка «Разработчик» будет активирована. Теперь можно приступить к включением макросов.

  1. Щелкаем по вкладке “Разработчик”. В левом углу будет находиться требуемый раздел, где нажимаем кнопку «Безопасность макросов» в виде восклицательного знака.
  2. В появившемся окне с настройками можно активировать сразу все макросы. Для этого необходимо выбрать из всех предложенных параметров вариант “Включить все макросы”. Нажатием кнопки “OK” подтверждаем внесенные изменения и выходим из параметров.Однако, следует обратить внимание на то, что разработчики Microsoft не рекомендуют выбирать этот вариант, так как есть вероятность запуска опасной программы, что может навредить компьютеру. Поэтому, выполняя эту операцию, помните, что вы действуете на свой страх и риск.

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

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

Настройка макросов в Параметрах программы

  1. Заходим в меню «Файл», и выбираем в нем пункт «Параметры» – аналогично первому пункту в рассмотренном ранее примере.
  2. Но теперь вместо настроек ленты, выбираем раздел “Центр управления безопасностью”. В правой части окна щелкаем на кнопку “Параметры центра управления безопасностью…”
  3. В итоге система нас направит в окно с настройками макросов, которое открывалось и при выполнении операции во вкладке Разработчика. Далее выбираем нужную нам опцию и кликаем “OK”.

Настройка макросов в ранних версиях Эксель

В ранних версиях программы активация и деактивация макросов происходила иначе.

Например, алгоритм действий в программах 2010 года и моложе аналогичный, но есть определенные различия в интерфейсе программ.

А для активации или выключения макросов в версии 2007 года необходимо щелкнуть мышью по значку Microsoft Office в сверху в левом углу. После этого требуется найти раздел «Параметры» внизу открывшейся страницы. Кликнув по разделу «Параметры», мы попадем в Центр управления безопасностью. Далее нам нужны Параметры центра управления безопасностью и, в итоге, непосредственно, сами параметры макросов.

Заключение

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

Смотрите также: “Как посчитать процент от числа и долю в Эксель”

Как создать и использовать надстройку Excel (пошаговое руководство)

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

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

Примерно так, как показано ниже (макрос добавлен на панель быстрого доступа для запуска одним щелчком мыши):

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

Если вы заинтересованы в простом изучении VBA, ознакомьтесь с моим курсом Online Excel VBA Training .

В этом руководстве рассматриваются:

В этом руководстве вы узнаете, как создать надстройку Excel. Чтобы создать надстройку и сделать ее доступной в QAT, нужно выполнить три шага.

  • Запись/запись кода в модуль.
  • Сохранить как надстройку Excel.
  • Добавьте макрос на панель быстрого доступа.

Запись/запись кода в модуль

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

 Sub HighlightErrors()
 Selection.SpecialCells(xlCellTypeFormulas, xlErrors).Выбрать
 Выбор.Интерьер.Цвет = vbRed
End Sub

Если вы пишете код (или копируете его откуда-то), вот шаги:

  • Откройте книгу Excel.
  • Нажмите Alt + F11, чтобы открыть окно редактора VB.
  • В редакторе VB вы увидите объекты книги, перечисленные в проводнике проекта. Если вы этого не видите, перейдите в View -> Project Explorer.
  • Щелкните правой кнопкой мыши любой объект в рабочей книге. Перейдите к опции «Вставить» и нажмите «Модуль». Это вставит объект модуля.
  • Дважды щелкните модуль и введите указанный выше код (скопируйте и вставьте).
  • Нажмите Alt+F11, чтобы вернуться к рабочему листу Excel.

Примечание: Если вы записываете макрос, Excel автоматически из позаботится о вставке модуля и размещении в нем кода.

Теперь давайте создадим надстройку из этого кода.

Сохраните и установите надстройку

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

  • Перейдите на вкладку «Файл».
  • Нажмите «Сохранить как».
  • В диалоговом окне «Сохранить как» измените тип «Сохранить как» на .xlam. Имя, которое вы присвоите файлу, будет именем вашей надстройки. В этом примере файл сохраняется с именем Highlight Errors.
    • Вы заметите, что путь к файлу, в котором он сохраняется, автоматически меняется. Вы можете использовать значение по умолчанию или изменить его, если хотите.
  • Откройте книгу Excel и выберите «Разработчик» -> «Надстройки» -> «Надстройки Excel».
  • В диалоговом окне «Надстройки» найдите сохраненный файл и нажмите «ОК».

Надстройка активирована.

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

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

Примечание . Если вы создаете надстройку с настраиваемой функцией, вам не нужно переходить к шагу 3. К концу шага 2 эта функция будет доступна во всех книгах. . Шаг 3 предназначен для таких кодов, когда вы хотите, чтобы что-то происходило при запуске кода (например, выделение ячеек с ошибками).

Сохранение и установка надстройки

Для этого:

  • Щелкните правой кнопкой мыши любую из вкладок ленты и выберите «Настроить панель быстрого доступа».
  • В диалоговом окне «Параметры Excel» выберите «Макросы» в раскрывающемся списке «Выбрать команды». Вы заметите, что там указан макрос «HighlightErrors».
  • Щелкните макрос HighlightErrors и щелкните Добавить. Это добавит макрос в список справа.
  • Нажмите OK. Это добавит макрос на панель быстрого доступа.

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

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

Предупреждение . Изменения, сделанные макросом, нельзя отменить с помощью Control + Z.

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

Вам также могут понравиться следующие учебники по Excel:

  • Работа с ячейками и диапазонами в Excel VBA.
  • Работа с рабочими листами в VBA.
  • Работа с книгами в VBA.
  • Использование циклов в Excel VBA.
  • Использование оператора IF Then Else в Excel VBA.
  • Как создать и использовать личную книгу макросов в Excel.
  • Полезные примеры кода макросов Excel.
  • Использование цикла For Next в Excel VBA.
  • События Excel VBA — простое (и полное) руководство.
  • Обработка ошибок Excel VBA

Создание и добавление ярлыков в Excel

Кристи Перри

Категории: Макросы

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

Самый простой способ — добавить кнопку макроса в электронную таблицу, для чего Excel предлагает три простых метода:

  • Добавить кнопку на панель быстрого доступа;
  • Добавить кнопку управления формой; или
  • Добавьте кнопку управления ActiveX.

В этом примере мы добавим кнопку для макроса под названием «Say_Hello». Однако, прежде чем вы сможете использовать какой-либо из этих методов, на ленте должна быть доступна вкладка Разработчик .

Показать вкладку «Разработчик»

Посмотрите в крайний правый угол ленты Excel. Если нет вкладки « Developer », то добавляем ее:

  1. В меню « Файл » выберите « Опции ».
  2. Выберите « Настроить Ленту ».
  3. Установите флажок « Разработчик ».

1. Добавьте кнопку на панель быстрого доступа

Самый простой способ добавить кнопку «Say_Hello» — добавить ее на панель быстрого доступа, которая отображается в строке заголовка окна Excel.

  1. Щелкните правой кнопкой мыши пустую область ленты.
  2. Выберите « Настройка панели быстрого доступа ».
  3. В появившемся диалоговом окне выберите « Макросы » вместо « Выбрать команды из: » и выберите свой макрос из списка.
  4. Нажмите « Добавить >> » и « Сохранить ».

Ваша кнопка «Say_Hello» теперь отображается на панели быстрого доступа.

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

2. Добавьте кнопку управления формой

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

  1. Выберите ленту « Проявитель ».
  2. Выберите « Вставить | Кнопка (Контроль формы) ».
  3. Щелкните и перетащите прямоугольник для кнопки «Say_Hello».
  4. Когда вы отпустите кнопку мыши, появится диалоговое окно макроса.
  5. Щелкните имя макроса, который вы хотите запустить, когда пользователь нажимает кнопку «Say_Hello», затем выберите «ОК». Если вы еще не создали макрос, вы можете создать его сейчас с помощью кнопки «Создать» или записать его с помощью кнопки « Запись… ».

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

3. Добавьте кнопку управления ActiveX

Третий вариант, кнопка управления ActiveX, более эффективен, чем кнопка управления формой, поскольку использует весь спектр языка Visual Basic. Чтобы создать кнопку управления ActiveX:

  1. Выберите ленту « Проявитель ».
  2. Выберите « Вставить | Кнопка (Контроль формы)».
  3. Щелкните и перетащите прямоугольник для кнопки «Say_Hello».
  4. Щелкните правой кнопкой мыши кнопку «Say_Hello» и выберите « View Code ».
  5. Откроется редактор Visual Basic.
  6. Убедитесь, что в списке в верхнем правом углу отображается « Нажмите ».