Ref-sgain.ru

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

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

Создание макросов в excel (на примере)

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

Вкладка “Разработчик”

Для начала нужно в экселе отобразить вкладку “Разработчик”:

  • Выполнить команды: Файл ➜ Параметры ➜ Настроить ленту ➜ вкладка «Настроить ленту».
  • Справа на панели установить флажок «Разработчик» ➜ Нажать «ОК».

Переход в окно макросов
  • Перейти на вкладку “Разработчик” -> В группе “Код” нажать кнопку “Visual Basic”.
Интерфейс окна макросов Excel

Окно кода макросов

Чтобы понять, как работает макрос, давайте обратимся к коду.

Cells(4, 2) = “Расчет значений” ‘ текст в ячейке
Cells(6, 5) = 5 ‘ число в ячейке
Cells(8, 3) = Cells(6, 3) * Cells(7, 3) ‘ формула в ячейке

Cells(4, 2).Font.Size = 14 ‘ размер шрифта
Cells(4, 2).Font.Bold = True ‘ жирный
Cells(4, 2).Font.Italic = True ‘ курсив
Cells(4, 2).Font.Underline = xlUnderlineStyleSingle ‘ подчеркивание
Cells(4, 2).Interior.Color = 65535 ‘заливка ячейки

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

Начало создания макроса
  • Любой макрос должен начинаться с оператора Sub, за которым идет имя макроса и список аргументов (если аргументов нет, то скобки надо оставить пустыми).
  • Любой макрос должен заканчиваться оператором End Sub.
  • Все, что находится между Sub и End Sub – тело макроса, т.е. команды, которые будут выполняться при запуске макроса.
  • Комментарий пишется после знака апострофа. Он нужен для того, чтобы делать пометки для себя, так как открыв макрос через n-ное количество времени можно не вспомнить о чем он. Также они могут понадобится при написании другого макроса в качестве шпаргалки по командам.
Читайте так же:
Как сделать результаты формулы постоянными в Excel?
Ячейки в макросе
  • Ячейка в макросе обозначается Cells(4, 1), где 4-номер строки, 1-номер столбца
  • Каждой ячейке можно присвоить разные значения, например:
    • Cells(4, 1) = “Расчет значений” ➤ текст (пишется в кавычках)
    • Cells(4, 1) = 5 ➤ значение
    • Cells(4, 1) = Cells(2, 1) + Cells(3, 1) ➤ сумма других ячеек
    • Cells(4, 1) = Cells(2, 1) * 20 + Cells(3, 1) * 80 ➤ значение, полученное при вычислении формулы
    Формат ячеек
    • Формат ячейки можно задать с помощью ее свойств. В нашем примере это размер шрифта, формат шрифта и отступы ячеек.
    • Пример макрофайла содержит заливку ячеек, границы ячеек и диапазоны ячеек для всей таблицы, отдельные границы разной толщины (скачать по ссылке).
    • Другие команды для форматирования ячеек можно найти в Интернете.
    Кнопка для запуска макроса
    • Вкладка Разработчик ➜ группа Элементы управления ➜ кнопка Вставить
    • В группе Элементы управления формы нажать “Кнопка” (изображение кнопки) .
    • Щелкнуть на листе место, где должен быть расположен левый верхний угол кнопки. Появится всплывающее окно Назначение макроса.
      Назначьте кнопке макрос и нажмите кнопку ОК.
    • Чтобы задать свойства кнопки, щелкните ее правой кнопкой мыши и выберите форматирование.

    кнопка макрос эксель

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

    Как выполняются макросы в Excel

    Все доступные макросы вы найдете в диалоговом окне Макрос. Для того чтобы макрос был доступен, книга, содержащая его, должна быть открыта. Просмотр *Макросы *Макросы (или нажмите Alt+F8) открывает диалоговое окно Макрос. Имена макросов появляются в списке Макросы. Просто выберите нужный макрос и нажмите кнопку Выполнить.

    Использование окна Visual Basic Editor

    Вы можете запускать макросы непосредственно из VBE, хотя это не самый удобный способ. Нажмите Alt+F11, чтобы активировать VBE. Затем перейдите к проекту и модулю кода, содержащему макрос. Установите курсор в любом месте кода макроса и выберите Run ► Run Sub/UserForm или нажмите F5.

    Использование сочетаний клавиш

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

    1. Нажмите Alt+F8, чтобы открыть диалоговое окно Макрос.
    2. В окне Макрос выберите имя макроса из списка.
    3. Нажмите кнопку Параметры. Появится диалоговое окно Параметры макроса.
    4. Укажите сочетание клавиш и нажмите OK, чтобы вернуться в окно макросов.
    5. Нажмите кнопку Отмена, чтобы закрыть окно макроса.

    Назначение макроса кнопке

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

    Чтобы добавить кнопку на лист, должна присутствовать вкладка Разработчик. Вы можете добавить эту вкладку, выбрав Файл ► Параметры. В диалоговом окне Параметры Excel перейдите в раздел Параметры ленты и установите флажок Разработчик в списке справа.

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

    1. Выберите Developer ► Controls ► Insert и щелкните элемент Button в разделе Form Controls.
    2. Нарисуйте на листе кнопку. Появится окно Назначить макрос объекту.
    3. Выберите макрос из списка.
    4. Нажмите OK, чтобы закрыть диалоговое окно Назначить макрос объекту.
    5. Если вы хотите изменить текст, отображаемый на кнопке, щелкните правой кнопкой мыши добавленную кнопку, в контекстном меню выберите Редактировать текст, а затем внесите необходимые изменения.

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

    Назначение макроса фигурам

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

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

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

    1. Щелкните правой кнопкой мыши панель инструментов быстрого доступа и выберите Настроить панель инструментов быстрого доступа, чтобы открыть диалоговое окно Параметры Excel.
    2. Выберите Макросы в раскрывающемся списке Выбор команд. Появится список доступных макросов.
    3. Выберите макрос из списка и нажмите Добавить.
    4. При желании нажмите кнопку Изменить, выберите другой значок и укажите другое имя для кнопки на панели инструментов быстрого доступа.

    Добавление кнопки на ленту

    Excel позволяет пользователям изменять ленту, в том числе добавлять кнопки, запускающие макросы. Единственное, что можно сделать, — это добавить кнопку в новую группу, поскольку кнопки нельзя добавлять во встроенные группы. Ниже описаны шаги для назначения макроса кнопке ленты:.

    Работа с макросами в Excel

    Макрос — это специальная программа, написанная на встроенном в Excel языке программирования Visual Basic for Application (VBA). Если вам часто приходится выполнять одни и те же операции в Excel, гораздо проще написать макрос или найти в Интернете готовый код макроса для выполнения определенных действий.

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

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

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

    Работа с макросами в Excel

    В окне «Параметры Excel» перейдите на вкладку «Настройка ленты». Затем установите флажок «Разработчик» в правой части окна и нажмите «ОК».

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

    Теперь разрешим использование всех макросов. Снова откройте «Файл» — «Параметры». Выберите вкладку «Центр управления безопасностью». и в правой части окна нажмите на кнопку «Параметры центра управления безопасностью».

    Перейдите на вкладку «Параметры макросов». На вкладке «Параметры макросов» выберите «Разрешить все макросы» и нажмите «ОК». После закрытия программы снова запустите Excel.

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

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

    Вставьте макрокод в модуль.

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

    Откройте редактор VBA: перейдите на вкладку «Разработчик» и нажмите на кнопку «Visual Basic». Вы также можете использовать «Alt+F11».

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

    Щелкните правой кнопкой мыши по выбранному элементу и выберите в меню «Вставить» — «Модуль».

    В окне «Проект» есть новая папка с именем «Модули». В ней находится наш первый модуль, Module1.

    Справа появится окно, в которое можно ввести код VBA. С помощью «Ctrl+V» введите код, который объединит несколько ячеек без потери данных. Имя макроса — «MergeCell» .

    Будьте внимательны, куда вы вставляете код, он будет написан в заголовке редактора или в заголовке окна вставки кода. Мы вставляем код в модуль, поэтому он записывается «Modul1(Code)».

    Сохраните изменения, нажав «Ctrl+S» . Если вы используете Excel 2007 или более поздние версии, появится окно Сохранить документ. В окне Сохранить документ выберите тип файла из выпадающего списка. Выберите «Рабочая книга Excel с поддержкой макросов» из выпадающего списка и нажмите Сохранить.

    Поместите макрокод в электронную таблицу.

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

    В редакторе VBA нажмите кнопку «Проект» и выберите нужный лист, если в рабочей книге их несколько, на котором должен выполняться макрос: «Sheet1» (Лист1). Дважды щелкните по нему мышью.

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

    Чтобы вставить код макроса в рабочую книгу, дважды щелкните на «Эта рабочая книга» и вставьте код в открывшуюся область.

    Создание настраиваемых функций

    Например, создайте функцию, которая вычисляет НДС. Щелкните правой кнопкой мыши на листе в окне «Проект» и выберите «Вставить». — «Модуль» .

    Второй модуль с именем «Module2» появляется в каталоге «Modules». Введите код VBA в соответствующее окно и сохраните его, нажав «Ctrl+S». Функция будет называться «NDS».

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

    Вот что происходит, когда в документе Excel запускаются все ранее созданные макросы.

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

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

    Появится диалоговое окно «Макрос». В списке выберите имя нужного макроса: «MergeCell» и нажмите кнопку «Выполнить».

    Макрос был выполнен: нужные ячейки объединены, а текст в них сохранен.

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

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

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

    Если вы создаете пользовательскую функцию, вы также должны вставить ее в документ как функцию. Моя функция вычисляет НДС.

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

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

    Затем я выделяю ячейку, для которой нужно рассчитать НДС, и нажимаю OK.

    Добавить макросы в документ Excel 2010 несложно. Вы также должны знать, как вставить макрос в Excel и запустить его.

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