Как научиться писать макросы в excel с нуля


Самоучитель по работе с макросами в Excel

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

Например, самостоятельно написанный макрос можно привязать к иконке и вывести на Ленту меню. Либо Вы можете создать пользовательскую функцию (UDF) и использовать ее точно так же, как и остальные встроенные функции Excel.

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

Настройка разрешения для использования макросов в Excel

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

Кликните эту ссылку, чтобы узнать больше о параметрах безопасности макросов в Excel

Редактор Visual Basic

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

Кликните эту ссылку, чтобы узнать больше о редакторе Visual Basic в Excel

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

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

Кликните эту ссылку, чтобы узнать больше о записи макросов в Excel

Учебник Excel VBA

Для тех, кто только начинает осваивать язык программирования Excel VBA, предлагаем небольшой вводный курс по Visual Basic for Applications.

Кликните эту ссылку, чтобы перейти к учебнику Excel VBA

Оцените качество статьи. Нам важно ваше мнение:

Step by Step Учебное пособие для начинающих

  • Home
  • Testing

      • Back
      • Agile Testing
      • BugZilla
      • Cucumber
      • Database Testing
      • 9A Тестирование базы данных
      • JUnit
      • LoadRunner
      • Ручное тестирование
      • Мобильное тестирование
      • Mantis
      • Почтальон
      • QTP
      • Назад
      • Центр контроля качества (ALM)
      • RPA 9000 Test4 Управление
      • TestLink
  • SAP

      • Назад
      • ABAP
      • 9 0003 APO
      • Начинающий
      • Basis
      • BODS
      • BI
      • BPC
      • CO
      • Назад
      • CRM
      • Crystal Reports
      • FICO
      • Hroll
      • Hroll
      • Назад
      • PI / PO
      • PP
      • SD
      • SAPUI5
      • Безопасность
      • Менеджер решений
      • Successfactors
      • SAP Tutorials

  • Web
  • AngularJS
  • ASP.Net
  • C
  • C #
  • C ++
  • CodeIgniter
  • СУБД
  • JavaScript
  • Назад
  • Java
  • JSP
  • Kotlin
  • Linux
  • Linux
  • Linux js
  • Perl
  • Назад
  • PHP
  • PL / SQL
  • PostgreSQL
  • Python
  • ReactJS
  • Ruby & Rails
  • Scala
  • SQL
  • 000
  • SQL
  • 000
  • SQL
  • 000 0003 SQL 000
  • UML
  • VB.Net
  • VBScript
  • Веб-службы
  • WPF
  • Обязательно учите!

      • Назад
      • Бухгалтерский учет
      • Алгоритмы
      • Android
      • Блокчейн
      • Business Analyst
      • Создание веб-сайта
      • CCNA
      • Облачные вычисления
      • 00030003 COBOL
          9000 Compiler
            9000 Встроенный
              9000 Compiler
            • Ethical Hacking
            • Учебные пособия по Excel
            • Программирование на Go
            • IoT
            • ITIL
            • Jenkins
            • MIS
            • Сети
            • Операционная система
            • Назад
            • Управление проектами Обзоры
            • Salesforce
            • SEO
            • Разработка программного обеспечения
            • VB A
        • Большие данные

          .

          Как создать макрос с нуля в Excel

          «Секрет успеха в том, чтобы начать» - Марк Твен.

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

          Давайте начнем с быстрого взгляда, что такое макросы?


          Что такое макросы Excel?

          Термин «макросы Excel» вызывает большую путаницу. Вкратце, макросы Excel относятся к языку программирования VBA, который встроен в Excel.

          Когда используются термины Excel Macros или VBA , они относятся к одному и тому же. Вообще говоря, кто-то, кто плохо знаком с VBA, будет использовать термин Macros , а как только они станут знакомыми, они будут использовать термин VBA .


          Что такое VBA?

          VBA - это язык программирования Visual Basic для приложений. Этот язык предустановлен в Excel. Это очень мощный инструмент, который позволяет расширить функциональные возможности Excel.

          VBA также входит в комплект всех других приложений Microsoft Office, таких как Microsoft Word, Access и Outlook.

          В следующих разделах мы собираемся создать макрос с нуля. Под макросом мы подразумеваем Sub .

          Sub (сокращение от Subroutine) - это просто набор строк кода VBA, который выполняет определенные задачи. Любое приложение VBA, которое вы создаете, по сути представляет собой набор Subs.

          Итак, теперь мы понимаем фон, давайте создадим макрос.


          Шаг 1. Сделайте вкладку разработчика видимой

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



          1. Щелкните ленту правой кнопкой мыши. В появившемся списке выберите «Настроить ленту»

          .





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




          Шаг 2 Проверьте настройки безопасности

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





          Ваши настройки безопасности должны совпадать с настройками на скриншоте ниже




          Шаг 3 Сохранить как правильный тип файла

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



          Выберите File-> Save As и выберите «Macro Enabled Workbook» в типе файла. Затем нажмите «Сохранить».




          Шаг 4 Войдите в редактор Visual Basic

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




          Шаг 5 Создайте модуль

          Макросы хранятся в модулях, поэтому создайте модуль.

          Посмотрите в окне проекта VBA. Если окно не отображается, выберите в меню «Просмотр» -> «Проводник проекта» (сочетание клавиш Ctrl и R).



          Выберите книгу, в которую вы хотите поместить код. Рабочие книги будут заключены в скобки рядом с «VBAProject». Каждая открытая книга будет иметь такую ​​запись в окне проекта VBA

          .





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





          появится новый модуль:



          Когда вы дважды щелкаете модуль в окне проекта VBA, код этого модуля отображается в главном окне кода. Когда создается новый модуль, он обычно не содержит никакого кода (обратите внимание, что он может содержать "Option Explicit", если у вас включена эта опция).




          Шаг 6 Создайте макрос

          Введите «Sub MyFirstMacro» в главном окне кода и нажмите клавишу возврата.У вас должны быть следующие




          Шаг 7 Добавьте код в свой макрос

          Используйте MsgBox «Hello World» . Это отобразит диалоговое окно с «Hello World» для пользователя

          .




          Шаг 8 Запустите макрос

          Для запуска макроса:

          1. Щелкните в любом месте макроса
          2. Выберите Run-> Run Sub / UserForm

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




          Вы должны увидеть это диалоговое окно




          Заключение

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

          Теперь вы готовы начать работу с VBA / макросами. Возможно, вы захотите ознакомиться с The Ultimate VBA Tutorial, предназначенным для начинающих.


          Что дальше?

          Бесплатное руководство по VBA Если вы новичок в VBA или хотите отточить свои существующие навыки VBA, то почему бы не попробовать The Ultimate VBA Tutorial.

          Связанное обучение: Получите полный доступ к обучающим веб-семинарам по Excel VBA и всем учебным материалам.

          ( ПРИМЕЧАНИЕ: Планируете создание приложения VBA или управление им? Узнайте, как создать 10 приложений Excel VBA с нуля.)


          Получите бесплатную электронную книгу

          Не стесняйтесь подписаться на мой информационный бюллетень и получать эксклюзивный контент VBA, который вы не можете найти здесь в блоге, а также бесплатный доступ к моей электронной книге How to Ace the 21 Most Common Question in VBA , которая полна примеров, которые вы можно использовать в собственном коде.




          .

          Создать макрос в Excel

          Вкладка разработчика | Командная кнопка | Назначить макрос | Редактор Visual Basic

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

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

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

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

          2. В разделе «Настройка ленты» в правой части диалогового окна выберите «Основные вкладки» (при необходимости).

          3. Установите флажок Разработчик.

          4. Щелкните OK.

          5. Вы можете найти вкладку «Разработчик» рядом с вкладкой «Просмотр».

          Командная кнопка

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

          1.На вкладке "Разработчик" нажмите "Вставить".

          2. В группе ActiveX Controls нажмите кнопку Command.

          3. Перетащите командную кнопку на рабочий лист.

          Назначить макрос

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

          1. Щелкните правой кнопкой мыши CommandButton1 (убедитесь, что выбран режим «Дизайн»).

          2. Щелкните Просмотреть код.

          Откроется редактор Visual Basic.

          3.Поместите курсор между Private Sub CommandButton1_Click () и End Sub.

          4. Добавьте строку кода, показанную ниже.

          Примечание: окно слева с именами Sheet1 (Sheet1) и ThisWorkbook называется Project Explorer. Если проводник проекта не отображается, щелкните «Просмотр», «Обозреватель проекта». Если окно кода для Sheet1 не отображается, щелкните Sheet1 (Sheet1). Вы можете пока игнорировать оператор Option Explicit.

          5. Закройте редактор Visual Basic.

          6. Щелкните командную кнопку на листе (убедитесь, что режим «Дизайн» не выбран).

          Результат:

          Поздравления. Вы только что создали макрос в Excel!

          Редактор Visual Basic

          Чтобы открыть редактор Visual Basic, на вкладке Разработчик щелкните Visual Basic.

          Откроется редактор Visual Basic.

          .

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

          Как написать макрос в Excel? Вот несколько быстрых советов:
          Excel Macro - Как записать
          Excel Macro - Как редактировать
          Excel Macro - Повышение скорости кода
          Excel Macro - Примеры

          Запись макроса в Excel

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

          В Excel 2007 (и Excel 2010) пункт меню «Записать Марко» находится под лентой «Просмотр». Нажмите кнопку «Макросы» в правом конце ленты, а затем нажмите «Записать макрос».Во всплывающем окне вас попросят ввести данные макроса. Если вы только начинаете записывать свой собственный макрос, вы можете просто нажать кнопку «ОК». После этого вы можете выполнять необходимые действия, такие как ввод чисел, формул, форматирование ячеек, вставка диаграмм и т.д.

          Кроме того, вы можете начать и прекратить запись макроса в Excel 2010 (и 2007), щелкнув значок, расположенный в нижнем левом углу окна приложения, справа от того места, где он помечен как «Готово».

          Теперь вы можете просмотреть макрос, который вы только что записали, щелкнув опцию «Просмотр макросов» под кнопкой «Макросы». Или вы можете нажать Alt + F8 в качестве сочетания клавиш. Вы можете дать макросу описательное имя или назначить комбинацию клавиш, чтобы при нажатии этой комбинации клавиш макрос начинал выполнение.

          Как записать макрос в Excel 2003 (и ранее)

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

          Вы можете просмотреть только что записанный макрос, щелкнув опцию «Инструменты» в строке меню, затем щелкнув «Макрос» и выбрав опцию «Макросы». Или вы можете нажать Alt + F8 в качестве сочетания клавиш. Вы можете записать полный набор макросов, а затем запускать их один за другим, чтобы получить шаги, связанные с задачей, выполненной за один раз.

          Как редактировать макрос

          Поздравляем! Вы написали свой первый макрос. Следующим логическим шагом будет проверка кода, сгенерированного макросом. Код, сгенерированный приложением, написан на языке VBA (Visual Basic для приложений). Чтобы просмотреть код, нам нужно будет открыть редактор VBA (также называемый VBA IDE или интегрированной средой разработки VBA). Вы можете использовать сочетание клавиш Alt + F11, чтобы открыть IDE, или просто используйте следующие параметры меню.

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

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

          Сделав это, теперь вы можете использовать вкладку «Разработчик» для доступа к редактору VBA, нажав кнопку «Visual Basic».

          Редактирование макроса в Excel 2003 и более ранних версиях
          Доступ к редактору Visual Basic можно получить с помощью сочетания клавиш Alt + F11, с помощью кнопки редактора Visual Basic на панели инструментов Visual Basic или путем выбора параметра редактора Visual Basic в меню, как показано ниже.

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

          Вот как это будет выглядеть после того, как вы откроете VBA IDE.

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

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

          Это момент, когда вы можете начать редактировать код, созданный макросом, когда вы записывали его в Excel. Например, вам необходимо автоматизировать задачу, в которой определенный столбец заполняется числами от 1 до 10.Вы записали первые три шага, так что теперь у вас есть код, который может вводить числа 1, 2 и 3 в первые три ячейки этого столбца. Теперь вы хотите написать следующие семь шагов.

          Если вы посмотрели на приведенный выше код, вы бы догадались, что в коде есть структура. Приложение сначала перемещает курсор в ячейку с помощью инструкции типа Range («A1»). Выберите , а затем отредактируйте его содержимое, используя что-то вроде ActiveCell.FormulaR1C1 = «1» .Таким образом, для оставшихся шагов мы можем просто повторить эти шаги, указав адрес новой ячейки, а затем на следующем шаге указав значение, которое мы хотели бы ввести. Например, если вы хотите ввести 4 в ячейку A4, вы должны написать:

          1
          2

          Диапазон ("A4"). Выберите
          ActiveCell.FormulaR1C1 = "4"

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

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

          Уделите несколько минут, чтобы внимательно прочитать код, созданный макросом. Если вы новичок, несколько минут, потраченных на чтение кода, принесут потрясающие результаты и помогут вам познакомиться с часто используемыми объектами в VBA. (Имейте в виду, что этот пример предназначен только для иллюстрации. Существуют лучшие и более быстрые способы достижения тех же результатов, некоторые из которых мы рассмотрим в следующих разделах.)

          Повышение скорости выполнения макроса Excel

          Пока все хорошо. Давайте перейдем к изучению некоторых приемов, которые помогут вам повысить производительность кода VBA вашего макроса. Рассмотрим пример фрагмента кода, показанного выше. На высокопроизводительном компьютере приведенный выше код, вероятно, будет работать быстрее, чем вы можете щелкнуть веком, но предположим, что нам пришлось запускать один и тот же код снова и снова для 50 000 ячеек (или итераций).На это потребуется время. Если написанный вами макрос состоит из сотен строк кода, вы можете повысить скорость выполнения, сократив все другие процессы, которые не требуются при выполнении кода.

          Использование команды Application.ScreenUpdating

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

          1
          2
          3
          4
          5
          6
          7
          8
          9
          10

          Sub Macro1 ()
          Application.ScreenUpdating = False
          Range ("A1"). Выберите
          ActiveCell. .FormulaR1C1 = "1" Диапазон
          ("A2"). Выберите
          ActiveCell.FormulaR1C1 = "2" Диапазон
          ("A3"). Выберите
          ActiveCell.FormulaR1C1 = "3"
          Application.ScreenUpdating = True
          End Sub

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

          Использование команды Application.Calculation

          Вторая уловка - отключить автоматические вычисления. Позволь мне объяснить. Всякий раз, когда пользователь или процесс обновляет ячейку, excel имеет тенденцию пересчитывать все ячейки, которые зависят от этой ячейки.Так, если, скажем, 10 000 ячеек зависят от ячейки, которую будет обновлять ваш код, Excel пересчитает их все до того, как код завершит выполнение. Теперь, если таких ячеек несколько, пересчет может сильно замедлить выполнение кода. Чтобы этого не произошло, вы можете установить для параметра Application.Calculation значение manual в начале кода, а затем обратно в конце кода.

          1
          2
          3
          4
          5
          6
          7
          8
          9
          10
          11
          12

          Sub Macro1 ()
          Application.ScreenUpdating = False
          Application.Calculation = xlCalculationManual
          Range ("A1"). Выберите
          ActiveCell.FormulaR1C1 = "1"
          Range ("A2"). Выберите
          ActiveCell.FormulaR1C1 = "2"
          Range ("A3") .Select
          ActiveCell.FormulaR1C1 = "3"
          Application.Calculation = xlCalculationAutomatic
          Application.ScreenUpdating = True
          End Sub

          Будьте осторожны, чтобы снова включить эту опцию в конце, иначе она останется выключенной даже после того, как код будет полностью выполнен.Если вы позволите ему выключиться с помощью кода, вы можете изменить его на автоматический, используя вкладку «Инструменты» -> «Параметры» -> «Расчет» в меню, как показано ниже:

          Избегайте выбора ячейки и диапазона в VBA

          Когда вы записываете макрос, вы обычно видите строки кода, например Range («A1»). Выберите . В нашем примере выше мы использовали такие операторы несколько раз, чтобы выбрать конкретную ячейку и затем изменить ее значение. Вы можете избежать этого, просто присвоив ячейке значение, не выбирая его.(Макрос записал перемещение курсоров из одной ячейки в другую и, следовательно, вставил эти шаги, однако они не нужны при работе с кодом VBA). Итак, в этом случае гораздо более эффективный код будет:

          1
          2
          3
          4
          5
          6
          7
          8
          9
          10
          11

          Sub Macro1 ()
          Application.ScreenUpdating = False
          Application.Calculation = x «A1»). Значение = 1
          Диапазон («A2»).Значение = 2
          Диапазон ("A3"). Значение = 3
          Диапазон ("A4"). Значение = 4
          Диапазон ("A5"). Значение = 5
          Application.Calculation = xlCalculationAutomatic
          Application.ScreenUpdating = True
          End Sub

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

          Как писать циклы в VBA

          Вы можете обратиться к некоторым из предыдущих статей, посвященных этому вопросу.Вы можете узнать больше о VBA For Loop здесь. Для получения информации о цикле Do While Loop обратитесь к этой статье.

          Как исправить ошибки времени выполнения в VBA

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

          Макрос Excel - Примеры

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

          Макрос Excel для вставки строки

          1
          2
          3

          Подмакрос1 ()
          Строки (2) .EntireRow.Insert 'Вставить новую строку над второй строкой
          End Sub

          Макрос Excel для вставки столбца

          1
          2
          3

          Подмакрос1 ()
          Столбцы (3) .EntireColumn.Insert 'Вставить новый столбец слева от 3-го столбца
          End Sub

          Макрос Excel для вставки столбца

          1
          2
          3
          4
          5
          6

          Подмакрос1 ()
          'Измените форматирование ячейки D2 на полужирный, подчеркнутый и курсивный
          Ячейки (2, 4).Font.Bold = True
          Cells (2, 4) .Font.Underline = xlUnderlineStyleSingle
          Cells (2, 4) .Font.Italic = True
          End Sub

          Макрос Excel для перехода по диапазону

          1
          2
          3
          4
          5
          6

          Субмакрос1 ()
          Для каждой ячейки в диапазоне (ячейки (1, 1), ячейки (10, 5))
          counter = counter + 1
          cel.Value = counter
          Next cel
          End Sub

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

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

          .

          Смотрите также