Макросы Excel - Краткое руководство

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

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

Макрос и VBA

Вы можете записывать и запускать макросы с помощью команд Excel или Excel VBA.

VBA означает Visual Basic для приложений и представляет собой простой язык программирования, доступный через редактор Excel Visual Basic (VBE), доступный на вкладке РАЗРАБОТЧИК на ленте. Когда вы записываете макрос, Excel генерирует код VBA. Если вы просто хотите записать макрос и запустить его, изучать Excel VBA не нужно. Однако, если вы хотите изменить макрос, вы можете сделать это, только изменив код VBA в редакторе Excel VBA.

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

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

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

Вы узнаете о личной книге макросов в главе - Сохранение всех ваших макросов в одной книге.

Макро безопасность

Макросы будут храниться в Excel как код VBA. Как и в случае любого другого кода, макрокод также уязвим для вредоносного кода, который может запускаться при открытии книги. Это угроза вашему компьютеру. Microsoft предоставила средство Macro Security, которое помогает защитить ваш компьютер от таких макровирусов.

Подробнее об этом вы узнаете в главе - Безопасность макросов.

Абсолютные ссылки и относительные ссылки

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

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

Макро-код в VBA

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

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

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

Назначение макросов объектам

Вы можете назначить макрос объекту, такому как фигура, рисунок или элемент управления. Затем вы можете запустить макрос, щелкнув этот объект. Вы узнаете об этом в главе «Назначение макросов объектам».

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

В Excel предусмотрено несколько способов запуска макроса. Вы можете выбрать способ запуска макроса. Вы узнаете об этих различных возможных способах запуска макроса в главе - Запуск макроса.

Создание макроса с помощью редактора VBA

Если вы решили написать код макроса, вы можете изучить его в главе «Создание макроса с помощью редактора VBA». Однако предварительным условием является знание Excel VBA.

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

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

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

Пользовательские формы

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

Вы узнаете, как создать пользовательскую форму и как использовать различные элементы управления в главе - Пользовательские формы.

Отладка макрокода

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

Настройка макроса для запуска при открытии книги

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

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

Предположим, вам нужно повторно собирать определенные результаты в следующем формате -

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

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

Чтобы записать макрос, сделайте следующее -

  • Щелкните вкладку ПРОСМОТР на ленте.
  • Щелкните Макросы в группе Макросы.
  • В раскрывающемся списке выберите «Запись макроса».

В Record Macro появится диалоговое окно.

  • Тип MyFirstMacro в поле Имя макроса.

  • Тип A Simple Macro в поле Описание и нажмите ОК.

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

  • Щелкните в ячейке B2.

  • Создайте таблицу.

  • Щелкните другую ячейку на листе.

  • Щелкните вкладку ПРОСМОТР на ленте.

  • Щелкните Макросы.

  • Выбрать Stop Recording из раскрывающегося списка.

Ваша запись макроса завершена.

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

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

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

  • Щелкните новый лист.

Обратите внимание на активную ячейку. В нашем случае это A1.

  • Щелкните значок VIEW вкладка на ленте.

  • Нажмите Macros.

  • Выбрать View Macros из раскрывающегося списка.

Откроется диалоговое окно «Макрос».

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

  • Щелкните имя макроса - MyFirstMacro в диалоговом окне «Макрос». Будет отображено описание, которое вы ввели при записи макроса. Описание макроса позволяет определить, для какой цели вы записали макрос.

  • Щелкните кнопку "Выполнить". Та же таблица, которую вы создали во время записи макроса, появится всего за долю секунды.

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

  • Хотя активной ячейкой перед запуском макроса была ячейка A1, таблица помещается в ячейку B2, как вы записали.

  • Кроме того, активной ячейкой стала E2, поскольку вы щелкнули по этой ячейке до того, как остановили запись.

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

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

Сохранение макроса

Вы можете задаться вопросом, как сохранить созданные макросы. В этом контексте вам нужно знать -

  • Сохранение макроса
  • Сохранение файла с поддержкой макросов

Когда вы создаете макрос, вы можете выбрать, где сохранить этот конкретный макрос. Вы можете сделать это вRecord Macro диалоговое окно.

Щелкните поле - Store macro in. Доступны следующие три варианта -

  • Эта рабочая тетрадь.
  • Новая рабочая тетрадь.
  • Личная книга макросов

Эта книга

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

Новая рабочая тетрадь

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

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

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

Сохранение файла с поддержкой макросов

Если бы вы выбрали This Workbook в качестве варианта для сохранения макроса вам необходимо сохранить книгу вместе с макросом.

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

Note- Если вы нажмете «Да», Excel сохранит вашу книгу как файл .xls без макросов, и ваш макрос, который вы сохранили с опцией «Эта книга», не будет сохранен. Чтобы избежать этого, Excel предоставляет вам возможность сохранить книгу как книгу с поддержкой макросов, которая будет иметь расширение .xlsm.

  • Щелкните Нет в окне предупреждения.
  • Выберите книгу Excel с поддержкой макросов (* .xlsm) в поле «Сохранить как».
  • Щелкните Сохранить.

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

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

Сохранение макросов в личной книге макросов

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

Выберите личную книгу макросов из раскрывающегося списка под категорией Store macro in.

  • Запишите свой второй макрос.
  • Укажите подробные сведения о макросе в диалоговом окне «Запись макроса», как показано ниже.
  • Щелкните ОК.

Ваша запись начинается. Создайте таблицу, как показано ниже.

  • Остановить запись.

  • Щелкните вкладку ПРОСМОТР на ленте.

  • Щелкните Макросы.

  • Выбрать View Macrosиз раскрывающегося списка. Откроется диалоговое окно «Макрос».

Имя макроса отображается с префиксом PERSONAL.XLSB! означает, что макрос находится в личной книге макросов.

Сохраните свою книгу. Он будет сохранен в виде файла .xls, так как макроса нет в вашей книге, и закройте Excel.

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

Щелкните кнопку Сохранить. Ваш макрос сохраняется вPersonal.xlsb файл на вашем компьютере.

Скрытие / отображение личной книги макросов

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

  • Щелкните значок VIEW вкладка на ленте.

  • Щелкните Показать в группе Окно.

Появится диалоговое окно «Показать».

PERSONAL.XLSB появится в поле Показать книгу и нажмите ОК.

Теперь вы можете просматривать макросы, сохраненные в личной книге макросов.

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

  • Щелкните личную книгу макросов.
  • Щелкните вкладку ПРОСМОТР на ленте.
  • Щелкните Скрыть на ленте.

Запуск макросов, сохраненных в личной книге макросов

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

  • Щелкните Просмотреть макросы.
  • Выберите имя макроса из списка макросов.
  • Щелкните кнопку "Выполнить". Макрос запустится.

Добавление / удаление макросов в личной книге макросов

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

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

  • Убедитесь, что личная книга макросов не скрыта.
  • Щелкните имя макроса в диалоговом окне «Просмотр макросов».
  • Щелкните кнопку Удалить.

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

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

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

Макросы, которые вы создаете в Excel, будут написаны на языке программирования VBA (Visual Basic для приложений). Вы узнаете о коде макросов Excel в следующих главах. Как вы знаете, когда есть исполняемый код, возникает угроза вирусов. Макросы также восприимчивы к вирусам.

Что такое макровирусы?

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

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

Книги Excel с поддержкой макросов

Самая важная функция безопасности макросов Excel - расширения файлов.

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

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

Способы доверия к книге с поддержкой макросов

Excel предоставляет три способа доверять книге с поддержкой макросов.

  • Размещение книг с поддержкой макросов в надежную папку

  • Проверка наличия цифровой подписи макроса

  • Включение предупреждений системы безопасности перед открытием книг с поддержкой макросов

Размещение книг с поддержкой макросов в надежную папку

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

Проверка наличия цифровой подписи макроса

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

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

Когда вы открываете книгу, Excel предупреждает вас о том, что книга содержит макросы, и спрашивает, хотите ли вы их включить. Вы можете нажать наEnable Content нажмите кнопку, если источник книги надежен.

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

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

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

Параметры безопасности макросов в центре управления безопасностью

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

  • Щелкните вкладку ФАЙЛ на ленте.

  • Щелкните Параметры. Откроется диалоговое окно "Параметры Excel".

  • Нажмите Trust Center на левой панели.

  • Щелкните значок Trust Center Settings в центре управления безопасностью Microsoft Excel.

В Trust Center появится диалоговое окно.

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

Настройки макроса

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

В разделе «Настройки макроса» доступны четыре параметра.

  • Disable all macros without notification - Если выбран этот параметр, макросы и предупреждения системы безопасности о макросах отключены.

  • Disable all macros with notification- Макросы отключены, но предупреждения безопасности появляются, если макросы присутствуют. Вы можете включать макросы в индивидуальном порядке.

  • Disable all macros except digitally signed macros- Макросы отключены, но предупреждения системы безопасности появляются, если макросы присутствуют. Однако, если макрос имеет цифровую подпись от доверенного издателя, макрос запускается, если вы доверяете издателю. Если вы не доверяете издателю, вы получите уведомление о том, что нужно включить подписанный макрос и доверять издателю.

  • Enable all macros (not recommended, susceptible to macro viruses)- Если выбрана эта опция, запускаются все макросы. Этот параметр делает ваш компьютер уязвимым для потенциально вредоносного кода.

У вас есть дополнительная опция безопасности в разделе «Параметры макроса разработчика» с флажком.

  • Trust access to the VBA project object model.

    • Этот параметр обеспечивает программный доступ к объектной модели Visual Basic для приложений (VBA) из клиента автоматизации.

    • Этот параметр безопасности предназначен для кода, написанного для автоматизации программы Office и управления средой VBA и объектной моделью.

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

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

Определение надежного расположения

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

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

Щелкните Надежные расположения в диалоговом окне Центр управления безопасностью. Надежные расположения, установленные Microsoft Office, отображаются справа.

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

Вы также можете найти варианты, которые офис не рекомендует, например места в Интернете.

Макросы с цифровой подписью из надежных источников

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

Вы найдете надежных издателей в Центре управления безопасностью.

  • Нажмите Trusted Publishersв диалоговом окне центра управления безопасностью. Справа появится список сертификатов с подробной информацией - Кому выдан, Кем выдан и Срок действия.

  • Выберите сертификат и нажмите «Просмотр».

Отображается информация о сертификате.

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

Использование предупреждающих сообщений

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

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

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

Включение / отключение предупреждений безопасности на панели сообщений

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

  • Щелкните вкладку ФАЙЛ на ленте.
  • Щелкните Параметры. Откроется диалоговое окно "Параметры Excel".
  • Щелкните Центр управления безопасностью.
  • Нажмите кнопку «Настройки центра управления безопасностью».
  • Щелкните Панель сообщений.

Появятся настройки панели сообщений для всех приложений Office.

Есть два варианта под - Showing the Message Bar.

Option 1 - Показывать панель сообщений во всех приложениях, когда активный контент, например макросы, заблокирован.

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

  • Если вы выбрали - Disable all macros without notification in the Macro Settings of the Trust Center, этот параметр не выбран, и панель сообщений не отображается.

Option 2 - Никогда не показывать информацию о заблокированном контенте.

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

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

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

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

Теперь отчет должен быть помещен в ячейку B2 и должен быть в заданном формате.

Образец заполненного отчета будет таким, как показано ниже -

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

  • C3 - Отчет за дату.
  • C13 - Количество выполненных сегодня задач.
  • C14 - Общее количество выполненных задач.
  • C15 - Работа выполнена на%.

Из них также в C3 (Отчет за дату) вы можете поместить функцию Excel = TODAY ()который помещает дату вашего отчета без вашего вмешательства. Кроме того, в ячейке C15 вы можете иметь формулу C14 / C12 и отформатировать ячейку C15 в процентах, чтобы Excel рассчитал для вас% завершения работы.

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

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

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

Обеспечение абсолютных ссылок

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

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

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

Note - Первые три шага, указанные выше, очень важны.

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

  • Если не щелкнуть другую ячейку в начале записи, даже если активной является ячейка B2, Excel поместит записанные шаги в активную ячейку. Когда вы запускаете макрос, он помещает записанный формат отчета в любую часть рабочего листа на основе активной ячейки. Явным щелчком по ячейке, отличной от B2, а затем по ячейке B2, вы сообщаете устройству записи всегда помещать шаги макроса в ячейку B2.

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

Вы можете начать запись макроса с Record Macroна Ленте на вкладке ВИД → Макросы. Вы также можете щелкнутьStart Recording Macro кнопка присутствует в левой части панели задач Excel.

  • Начните запись макроса. ВRecord Macro появится диалоговое окно.

  • Дайте значимое имя, чтобы идентифицировать макрос как отчет о конкретном проекте.

  • Выберите эту книгу в Store macro in, так как вы будете создавать отчеты только из этой конкретной книги.

  • Дайте описание вашему макросу и нажмите OK.

Ваш макрос начинает запись.

  • Создайте новый рабочий лист. Это гарантирует, что ваш новый отчет будет на новом листе.

  • Щелкните любую ячейку, кроме B2, на новом листе.

  • Щелкните в ячейке B2. Это гарантирует, что макрос всегда помещает ваши записанные шаги в B2.

  • Создайте формат для отчета.

  • Заполните статическую информацию для отчета по проекту.

  • Поместите = СЕГОДНЯ () в C3 и = C14 / C12 в ячейку C15.

  • Отформатируйте ячейки датами.

Остановите запись макроса.

Вы можете остановить запись макроса с помощью Stop Recording на ленте на вкладке ВИД → Макросы или нажав кнопку «Остановить запись макроса» в левой части панели задач Excel.

Макрос вашего отчета по проекту готов. Сохраните книгу как книгу с поддержкой макросов (с расширением .xlsm).

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

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

  • Нажмите кнопку ПРОСМОТР на ленте.
  • Щелкните Макросы.
  • В раскрывающемся списке выберите Просмотреть макросы. Откроется диалоговое окно «Макрос».
  • Щелкните макрос Report_ProjectXYZ.
  • Щелкните кнопку "Выполнить".

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

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

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

  • Название избирательного округа.
  • Общая численность населения округа.
  • Количество избирателей в округе.
  • Количество избирателей-мужчин, и
  • Число избирателей-женщин.

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

Анализировать данные в указанном выше формате невозможно. Поэтому расположите данные в таблице, как показано ниже.

Если вы попытаетесь расположить данные в указанном выше формате -

  • Сбор данных по 280 округам занимает много времени.

  • Это может быть подвержено ошибкам

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

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

Использование относительных ссылок

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

  • Щелкните значок VIEW вкладка на ленте.

  • Нажмите Macros.

  • Нажмите Use Relative References.

Подготовка формата данных

Первым шагом в организации приведенных выше данных является определение формата данных в таблице с заголовками.

Создайте ряд заголовков, как показано ниже.

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

Запишите макрос следующим образом -

  • Щелкните Записать макрос.

  • Дайте макросу осмысленное имя, скажем, DataArrange.

  • Тип = row ()- 3в ячейке B4. Это потому, что S. No. - это номер текущей строки - 3 строки над ней.

  • Вырежьте ячейки B5, B6, B7, B8 и B9 и вставьте их в ячейки C4 – C8 соответственно.

  • Теперь щелкните ячейку B5. Ваша таблица выглядит так, как показано ниже.

Первый набор данных располагается в первой строке таблицы. Удалите строки B6 - B11 и щелкните ячейку B5.

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

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

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

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

Активная ячейка - B5. Запустите макрос. Второй набор данных будет расположен во второй строке таблицы, а активной ячейкой будет B6.

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

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

Запускайте макрос, пока все 280 наборов данных не будут расположены в 280 строк в таблице. Этот процесс занимает несколько секунд, и, поскольку шаги автоматизированы, все упражнение проходит без ошибок.

Excel хранит макросы в виде кода Excel VBA (Visual Basic для приложений). После записи макроса вы можете просмотреть сгенерированный код, изменить его, скопировать его часть и т. Д. Если вам удобно программировать на VBA, вы даже можете написать код макроса самостоятельно.

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

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

Вы можете получить доступ к коду макроса в VBA на вкладке «Разработчик» на ленте.

Если вы не найдете вкладку Разработчик на ленте, вам необходимо добавить ее следующим образом:

  • Щелкните ленту правой кнопкой мыши.

  • Выбрать Customize the Ribbon из раскрывающегося списка.

В Excel Options появится диалоговое окно.

  • Выбрать Main Tabs из Customize the Ribbon раскрывающийся список.

  • Установите флажок - Разработчик в списке основных вкладок и нажмите ОК. Появится вкладка разработчика.

Команды разработчика для макросов

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

Щелкните вкладку РАЗРАБОТЧИК на ленте. Следующие команды доступны в группе Код -

  • Visual Basic
  • Macros
  • Запись макроса
  • Использовать относительные ссылки
  • Макро безопасность

Команда Visual Basic используется для открытия редактора VBA в Excel, а команда «Макросы» используется для просмотра, запуска и удаления макросов.

Вы уже изучили команды, отличные от редактора VBA, в предыдущих главах.

Редактор VBA

Редактор VBA или VBE - это платформа для разработки VBA в Excel.

Откройте книгу MyFirstMacro.xlsm, которую вы сохранили ранее в главе «Создание простого макроса» в этом руководстве.

Вы можете открыть VBE любым из двух способов -

Option 1 - Щелкните Visual Basic в группе «Код» на вкладке «Разработчик» на ленте.

Option 2 - Нажмите «Правка» в диалоговом окне «Макрос», которое появляется при переходе на вкладку ПРОСМОТР → Макросы → Просмотр макросов

VBE появится в новом окне.

Имя вашей книги с поддержкой макросов Excel отображается с префиксом - Microsoft Visual Basic для приложений.

Вы найдете следующее в VBE -

  • Обозреватель проектов.
  • Properties.
  • Окно модуля с кодом.

Обозреватель проектов

Project Explorer - это то место, где вы найдете имена проектов VBA. Под проектом вы найдете имена листов и имена модулей. Когда вы щелкаете имя модуля, соответствующий код появляется в правой части окна.

Окно свойств

Свойства - это параметры для объектов VBA. Если у вас есть такой объект, как командная кнопка, его свойства появятся в окне «Свойства».

Окно модуля с кодом

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

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

Однако вы все равно можете просмотреть код макроса в редакторе Excel VBA и сопоставить его с шагами, которые вы записали в макросе. Вы узнаете, как просмотреть и понять код для первого макроса, который вы создали в этом руководстве - MyFirstMacro.

Просмотр кода макроса в редакторе VBA

Чтобы просмотреть код макроса, сделайте следующее -

  • Откройте книгу, в которой вы сохранили макрос.
  • Щелкните вкладку ПРОСМОТР на ленте.
  • Щелкните Макросы.
  • В раскрывающемся списке выберите Просмотреть макросы.

Откроется диалоговое окно «Макрос».

  • Щелкните MyFirstMacro в списке макросов.
  • Щелкните кнопку Edit.

Откроется редактор VBA и появится код макроса MyFirstMacro.

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

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

  • Начни читать код.
  • Сопоставьте код с записанными шагами.

Прокрутите код, чтобы просмотреть больше кода. Как вариант, вы можете увеличить окно кода.

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

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

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

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

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

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

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

  • Щелкните значок INSERT вкладка на ленте.

  • Нажмите Shapes в группе Иллюстрации.

  • Выберите любую из готовых фигур, которые появятся в раскрывающемся списке. Например, фигура блок-схемы - подготовка, поскольку вы находитесь в процессе подготовки данных.

Нарисуйте фигуру и отформатируйте ее.

  • Щелкните правой кнопкой мыши фигуру и выберите Edit Text из раскрывающегося списка.

  • Введите текст внутри фигуры - «Запустить макрос».

  • Отформатируйте текст.

  • Щелкните правой кнопкой мыши по фигуре.
  • В раскрывающемся списке выберите «Назначить макрос».

Откроется диалоговое окно «Назначить макрос». Щелкните имя макроса, например RelativeMacro, и нажмите OK.

Макрос назначается фигуре.

  • Щелкните ячейку, в которой вы должны запустить макрос, скажем, B4.

  • Переместите курсор (указатель) на фигуру. Курсор (указатель) изменится на палец.

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

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

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

  • Щелкните вкладку ВСТАВИТЬ на ленте.
  • Щелкните изображения в группе иллюстрации.
  • Выберите файл, содержащий вашу графику.

Остальные шаги такие же, как и формы, указанные в предыдущем разделе.

Назначение макроса элементу управления

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

  • Щелкните значок DEVELOPER вкладка на ленте.

  • Нажмите Insert в группе Controls.

Выберите значок кнопки под Form Controls из раскрывающегося списка, как показано на скриншоте ниже -

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

  • Щелкните имя макроса и нажмите ОК.

Будет вставлена ​​кнопка управления с назначенным макросом.

  • Щелкните правой кнопкой мыши по кнопке.
  • Щелкните Редактировать текст.
  • Тип - Запустить макрос.
  • Форматировать текст и изменять размер кнопки.

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

Использование элементов управления формой - это простой и эффективный способ взаимодействия с пользователем. Подробнее об этом вы узнаете в главе - Взаимодействие с пользователем.

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

Вы можете запустить макрос следующими способами -

  • Запуск макроса из вкладки просмотра
  • Запуск макроса нажатием Ctrl плюс сочетание клавиш
  • Запуск макроса нажатием кнопки на панели быстрого доступа
  • Запуск макроса путем нажатия кнопки в настраиваемой группе на ленте
  • Запуск макроса путем нажатия на графический объект
  • Запуск макроса из вкладки разработчика
  • Запуск макроса из редактора VBA

Запуск макроса из вкладки просмотра

Вы уже научились запускать макрос на вкладке «Просмотр» на ленте. Краткое резюме -

  • Щелкните вкладку ПРОСМОТР на ленте.
  • Щелкните Макросы.
  • В раскрывающемся списке выберите Просмотреть макросы.

Откроется диалоговое окно «Макрос».

  • Щелкните имя макроса.
  • Щелкните кнопку "Выполнить".

Запуск макроса с помощью сочетания клавиш

Вы можете назначить комбинацию клавиш (Ctrl + клавиша) для макроса. Вы можете сделать это во время записи макроса вCreate Macroдиалоговое окно. В противном случае вы можете добавить это позже вMacro Options диалоговое окно.

Добавление сочетания клавиш при записи макроса

  • Щелкните вкладку ПРОСМОТР.
  • Щелкните Макросы.
  • В раскрывающемся списке выберите «Запись макроса».

Откроется диалоговое окно «Создать макрос».

  • Введите имя макроса
  • Введите букву, например q, в поле рядом с Ctrl + в разделе «Сочетание клавиш».

Добавление сочетания клавиш в параметры макроса

  • Щелкните вкладку ПРОСМОТР.
  • Щелкните Макросы.
  • В раскрывающемся списке выберите Просмотреть макросы.

Откроется диалоговое окно «Макрос».

  • Выберите имя макроса.
  • Щелкните кнопку Параметры.

Откроется диалоговое окно «Параметры макроса». Введите букву, например q, в поле рядом с Ctrl + в разделе «Сочетание клавиш». Щелкните ОК.

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

Note- Вы можете использовать любые строчные или прописные буквы для сочетания клавиш макроса. Если вы используете любую комбинацию Ctrl + буквы, которая является сочетанием клавиш Excel, вы переопределите ее. Примеры включают Ctrl + C, Ctrl + V, Ctrl + X и т.д. Следовательно, используйте вашу юрисдикцию при выборе букв.

Запуск макроса через панель быстрого доступа

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

Предположим, у вас есть макрос с именем MyMacro в вашей личной книге макросов.

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

  • Щелкните правой кнопкой мыши на панели быстрого доступа.

  • Выбрать Customize Quick Access Toolbar из раскрывающегося списка.

Откроется диалоговое окно "Параметры Excel". Выберите Макросы из раскрывающегося списка под категорией-Choose commands from.

Список макросов отображается в разделе «Макросы».

  • Щелкните PERSONAL.XLSB! MyMacro.
  • Щелкните кнопку Добавить.

Название макроса отображается с правой стороны с изображением кнопки макроса.

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

  • Щелкните имя макроса в правом поле.
  • Нажмите кнопку "Изменить".

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

Измените отображаемое имя, которое появляется, когда вы помещаете указатель на изображение кнопки на панели быстрого доступа, на значимое имя, например, для этого примера Run MyMacro. Щелкните ОК.

Имя макроса и значок значка изменятся на правой панели. Щелкните ОК.

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

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

Запуск макроса в пользовательской группе

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

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

В Excel Options появится диалоговое окно.

  • Выберите «Основные вкладки» в разделе «Настроить ленту».
  • Щелкните Новая вкладка.

Новая вкладка (пользовательская) появится в списке основных вкладок.

  • Щелкните Новая вкладка (Пользовательская).
  • Щелкните кнопку New Group.

Новая группа (настраиваемая) появится в разделе «Новая вкладка (настраиваемая)».

  • Щелкните Новая вкладка (Пользовательская).
  • Нажмите кнопку "Переименовать".

Откроется диалоговое окно "Переименовать". Введите имя настраиваемой вкладки, которая отображается на основных вкладках на ленте, например «Мои макросы», и нажмите «ОК».

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

Название новой вкладки изменится на Мои макросы (Пользовательские).

  • Щелкните Новая группа (Пользовательская).
  • Нажмите кнопку "Переименовать".

В Renameпоявится диалоговое окно. Введите имя группы в диалоговом окне «Отображаемое имя» и нажмите «ОК».

Новое имя группы изменится на Персональные макросы (настраиваемые).

Щелкните Макросы на левой панели под Choose commands from.

  • Выберите имя макроса, скажем - MyFirstMacro из списка макросов.
  • Щелкните кнопку Добавить.

Макрос будет добавлен в группу «Персональные макросы (пользовательские)».

  • Щелкните Мои макросы (Пользовательские) в списке.
  • Щелкайте стрелки, чтобы переместить вкладку вверх или вниз.

Положение вкладки в списке основных вкладок определяет, где она будет размещена на ленте. Щелкните ОК.

Ваша настраиваемая вкладка - Мои макросы появится на ленте.

Перейдите на вкладку - Мои макросы. На ленте появится группа «Персональные макросы». MyFirstMacro появится в группе «Персональные макросы». Чтобы запустить макрос, просто щелкните MyFirstMacro в группе «Персональные макросы».

Запуск макроса путем щелчка по объекту

Вы можете вставить на рабочий лист такой объект, как фигура, рисунок или элемент управления VBA, и назначить ему макрос. Чтобы запустить макрос, просто щелкните объект.

Подробнее о запуске макроса с использованием объектов см. В главе «Назначение макросов объектам».

Запуск макроса из вкладки разработчика

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

  • Щелкните вкладку Разработчик на ленте.
  • Щелкните Макросы.

Откроется диалоговое окно «Макрос». Щелкните имя макроса, а затем щелкните Выполнить.

Запуск макроса из редактора VBA

Вы можете запустить макрос из редактора VBA следующим образом:

  • Щелкните вкладку Выполнить на ленте.
  • В раскрывающемся списке выберите Run Sub / UserForm.

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

Объекты и модули VBA

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

  • Откройте книгу с поддержкой макросов с вашим первым макросом.
  • Щелкните вкладку РАЗРАБОТЧИК на ленте.
  • Щелкните Visual Basic в группе «Код».

Откроется окно редактора VBA.

В окне Projects Explorer вы увидите следующее:

  • Ваша книга с поддержкой макросов - MyFirstMacro.xlsm отображается как проект VBA.

  • Все листы и книга отображаются в проекте как объекты Microsoft Excel.

  • Module1 отображается в разделе "Модули". Ваш код макроса находится здесь.

  • Щелкните Module1.

  • Щелкните вкладку «Просмотр» на ленте.

  • В раскрывающемся списке выберите Код.

Появится код вашего макроса.

Создание макроса путем кодирования

Затем создайте второй макрос в той же книге - на этот раз написав код VBA.

Вы можете сделать это в два этапа -

  • Вставьте командную кнопку.

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

Вставка командной кнопки

  • Создайте новый рабочий лист.

  • Щелкните на новом листе.

  • Нажмите кнопку РАЗРАБОТЧИК на ленте.

  • Нажмите Вставить в группе Элементы управления.

  • Выберите значок кнопки из Form Controls.

  • Щелкните на листе, где вы хотите разместить кнопку команды.
  • Откроется диалоговое окно «Назначить макрос».

Появится редактор Visual Basic.

Вы увидите следующее -

  • Новый модуль - Module2 вставлен в Project Explorer.
  • Появится окно кода с заголовком Module2 (Код).
  • Подпроцедура Button1_Click () вставляется в код Module2.

Кодирование макроса

Ваше кодирование наполовину выполняется самим редактором VBA.

Например, введите MsgBox “Best Wishes to You!”в подпроцедуре Button1_Click (). При нажатии кнопки команды отображается окно сообщения с заданной строкой.

Это оно! Ваш код макроса готов к запуску. Как вы знаете, код VBA не требует компиляции, так как выполняется с интерпретатором.

Запуск макроса из редактора VBA

Вы можете протестировать свой код макроса из самого редактора VBA.

  • Щелкните вкладку Выполнить на ленте.

  • В раскрывающемся списке выберите Run Sub / UserForm. Окно сообщения с введенной вами строкой появится на вашем листе.

Вы можете видеть, что кнопка выбрана. Щелкните ОК в окне сообщения. Вы вернетесь в редактор VBA.

Запуск макроса из рабочего листа

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

  • Щелкните где-нибудь на листе.
  • Щелкните кнопку. На листе появится окно сообщения.

Вы создали макрос, написав код VBA. Как видите, кодирование на VBA очень простое.

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

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

Копирование макрокода

Вы создали два макроса - MyFirstMacro и Button1_Click в книге MyFirstMacro.xlsm с поддержкой макросов. Вы создали первый макрос, записав шаги, а второй макрос, написав код. Вы можете скопировать код из первого макроса во второй макрос.

  • Откройте книгу MyFirstMacro.xlsm.

  • Щелкните вкладку Разработчик на ленте.

  • Щелкните Visual Basic. Откроется редактор Visual Basic.

  • Откройте код для Module1 (код макроса MyFirstMacro) и Module2 (код макроса Button1_Click ()).

  • Щелкните вкладку «Окно» на ленте.

  • В раскрывающемся списке выберите «Плитка по горизонтали».

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

  • Скопируйте строку MsgBox в код Module2.

  • Вставьте его над этой линией.

  • Измените строку как -

    MsgBox «Hello World!»

  • Скопируйте следующий код из Module1.

Вставьте его в код Module2 между двумя строками кода MsgBox.

  • Щелкните значок Сохранить, чтобы сохранить код.

  • Нажмите кнопку на листе Excel. Появится окно сообщения с сообщением - Hello World! Щелкните ОК.

Появятся данные таблицы (в соответствии с кодом, который вы скопировали) и появится окно сообщения с сообщением - Best Wishes to You!

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

Переименование макроса

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

  • Щелкните вкладку ПРОСМОТР на ленте.
  • Щелкните Макросы.
  • В раскрывающемся списке выберите Просмотреть макросы.

Откроется диалоговое окно «Макрос».

  • Щелкните имя макроса - Button1_Click.
  • Щелкните кнопку Edit.

Код макроса появится в редакторе VBA.

Измените имя, которое отображается в строке Sub, с Button1_Click на RenamedMacro. Оставьте Sub и круглые скобки как есть.

Откройте диалоговое окно «Макрос». Имя макроса отображается после переименования.

  • Щелкните RenamedMacro.
  • Щелкните кнопку "Выполнить". Макрос запускается. Теперь нажимать кнопку не нужно.

Удаление макроса

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

  • Откройте диалоговое окно "Макросы".
  • Щелкните имя макроса.
  • Щелкните кнопку Удалить.

В Delete появляется подтверждающее сообщение.

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

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

Основное преимущество UserForm заключается в том, что вы можете сэкономить время, которое вы тратите на то, что и как заполнять информацию.

Создание пользовательской формы

Чтобы создать UserForm, действуйте следующим образом -

  • Щелкните вкладку РАЗРАБОТЧИК на ленте.
  • Щелкните Visual Basic. Откроется окно Visual Basic для книги.
  • Щелкните Вставить,
  • В раскрывающемся списке выберите UserForm.

Пользовательская форма появится в правой части окна.

Понимание пользовательской формы

Разверните окно UserForm.xlsx - UserForm1.

Вы находитесь в режиме дизайна. Вы можете вставить элементы управления в пользовательскую форму и написать код для соответствующих действий. Элементы управления доступны в ToolBox. Свойства UserForm находятся в окне «Свойства». UserForm1 (заголовок UserForm) находится в разделе Forms в Projects Explorer.

  • Измените заголовок UserForm на Project Report - Daily в окне свойств.
  • Измените имя UserForm на ProjectReport.

Изменения отражаются в UserForm, свойствах и проводнике проекта.

Элементы управления в ToolBox

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

Excel предоставляет два типа элементов управления - элементы управления формы и элементы управления ActiveX. Вы должны понимать разницу между этими двумя типами элементов управления.

Форма управления

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

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

Элементы управления ActiveX

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

У вас есть следующие элементы управления ActiveX в UserForm ToolBox -

  • Pointer
  • Label
  • TextBox
  • ComboBox
  • ListBox
  • CheckBox
  • OptionButton
  • Frame
  • ToggleButton
  • CommandButton
  • TabStrip
  • MultiPage
  • ScrollBar
  • SpinButton
  • Image

В дополнение к этим элементам управления Visual Basic предоставляет вам функцию MsgBox, которую можно использовать для отображения сообщений и / или запроса действия пользователя.

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

метка

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

Example

Текстовое окно

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

Example

Окно списка

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

  • Вставьте ListBox в UserForm.
  • Щелкните ListBox.
  • Введите ProjectCodes в поле Name в окне свойств ListBox.

Есть три типа списков -

  • Single-selection List box- Поле списка с одним выбором позволяет выбрать только один вариант. В этом случае поле со списком напоминает группу переключателей, за исключением того, что с помощью списка можно более эффективно обрабатывать большое количество элементов.

  • Multiple selection List Box - Поле со списком множественного выбора позволяет выбрать один вариант или смежные (смежные) варианты.

  • Extended-selection List Box - Поле списка расширенного выбора позволяет выбрать один вариант, непрерывный выбор и несмежный (или несвязанный) выбор.

Вы можете выбрать один из этих типов списков в окне «Свойства».

  • Щелкните правой кнопкой мыши пользовательскую форму.
  • В раскрывающемся списке выберите Просмотреть код. Откроется окно кода UserForm.
  • Нажмите Инициализировать в правом верхнем поле окна кода.
  • Введите следующее в Private Sub UserForm_Initialize ().
ProjectCodes.List = Array ("Proj2016-1", "Proj2016-2", "Proj2016-3", "Proj20164", "Proj2016-5")
  • Щелкните вкладку Выполнить на ленте.
  • В раскрывающемся списке выберите Run Sub / UserForm.

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

Поле со списком

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

  • Вставьте ComboBox в UserForm.
  • Щелкните ComboBox.
  • Введите ProjectCodes2 в качестве имени в окне свойств ComboBox.
  • Щелкните правой кнопкой мыши пользовательскую форму.
  • В раскрывающемся списке выберите Просмотреть код.
  • Откроется окно кода UserForm.

Введите следующее, как показано ниже.

ProjectCodes2.List = Array ("Proj2016-1", "Proj2016-2", "Proj2016-3", "Proj20164", "Proj2016-5")
  • Щелкните вкладку Выполнить на ленте.
  • В раскрывающемся списке выберите Run Sub / UserForm.

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

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

CheckBox

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

Флажок может иметь два состояния -

  • Выбрано (включено), отмечается галочкой в ​​окошке
  • Снят (выключен), обозначен прозрачным квадратом

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

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

  • Установите флажки в пользовательской форме, как показано ниже.

  • Щелкните вкладку Выполнить на ленте.
  • В раскрывающемся списке выберите Run Sub / UserForm.
  • Щелкните поля для выбранных вами параметров.

OptionButton

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

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

  • Выбрано (включено), обозначено точкой в ​​круге
  • Снят (выключен), обозначен пробелом

Рамка

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

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

  • Вставьте рамку с надписью «Выбор».

  • Вставьте две кнопки выбора с заголовками «Да» и «Нет» в элемент управления рамкой. Варианты Да и Нет являются взаимоисключающими.

  • Щелкните вкладку Выполнить на ленте.
  • В раскрывающемся списке выберите Run Sub / UserForm.
  • Нажмите на выбранный вами вариант.

Кнопка-переключатель

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

Вставьте кнопку переключения в UserForm, как показано ниже -

  • Щелкните вкладку Выполнить на ленте.

  • В раскрывающемся списке выберите Run Sub / UserForm. По умолчанию кнопка переключения будет во включенном состоянии.

Щелкните переключатель. Кнопка переключения будет отключена.

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

CommandButton

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

Командная кнопка также называется нажимной кнопкой. Вставьте командную кнопку в UserForm, как показано ниже -

  • Щелкните правой кнопкой мыши командную кнопку.
  • Введите следующий код во вложенной Commandbutton1_click ().
ProjectCodes2.DropDown
  • Щелкните вкладку Выполнить на ленте.
  • В раскрывающемся списке выберите Run Sub / UserForm.

Щелкните командную кнопку. Откроется раскрывающийся список поля со списком, поскольку это действие, которое вы написали в коде.

TabStrip

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

Полоса прокрутки

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

Вставьте полосу прокрутки в UserForm, нарисовав ее в нужном месте, и отрегулируйте длину полосы прокрутки.

  • Щелкните полосу прокрутки правой кнопкой мыши.
  • В раскрывающемся списке выберите Просмотреть код. Откроется окно кода.
  • Добавьте следующую строку в подпрограмму ScrollBar1_Scroll ().
TextBox2.Text = "Scrolling Values"
  • Щелкните вкладку Выполнить на ленте.
  • В раскрывающемся списке выберите Run Sub / UserForm.

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

MsgBox ()

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

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

Отображение значка окна сообщения

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

  • Введите следующий код в поле ScrollBar1_scroll.
MsgBox "Select Ok or Cancel", vbOKCancel, "OK  - Cancel Message" 
MsgBox "It's an Error!", vbCritical, "Run time result" 
MsgBox "Why this value", vbQuestion, "Run time result" 
MsgBox "Value Been for a Long Time", vbInformation, "Run time result" 
MsgBox "Oh Is it so", vbExclamation, "Run time result"
  • Щелкните вкладку Выполнить на ленте.
  • В раскрывающемся списке выберите Run Sub / UserForm.
  • Перетащите полосу прокрутки.

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

Разработка UserForm

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

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

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

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

Отладка VBA

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

  • Пошаговый код
  • Использование точек останова
  • Резервное копирование или движение вперед в коде
  • Не переходить через каждую строку кода
  • Запрос чего-либо при прохождении кода
  • Остановка казни

Это лишь некоторые из задач, которые вы можете выполнять в среде отладки VBA.

Пошаговый код

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

Вы можете войти в код либо из диалогового окна «Макрос» в своей книге, либо из самого редактора VBA.

Stepping into the code from the workbook

Чтобы войти в код из книги, сделайте следующее:

  • Щелкните вкладку ПРОСМОТР на ленте.
  • Щелкните Макросы.
  • В раскрывающемся списке выберите Просмотреть макросы.

Откроется диалоговое окно «Макрос».

  • Щелкните имя макроса.
  • Щелкните кнопку Step into.

Откроется редактор VBA, и код макроса появится в окне кода. Первая строка в коде макроса будет выделена желтым цветом.

Stepping into the code from the VBA editor

Чтобы войти в код из редактора VBA, сделайте следующее:

  • Щелкните вкладку РАЗРАБОТЧИК на ленте.
  • Щелкните Visual Basic. Откроется редактор VBA.
  • Щелкните модуль, содержащий код макроса.

Код макроса появится в окне кода.

  • Щелкните вкладку «Отладка» на ленте.

  • Выбрать Step into из раскрывающегося списка.

Будет выделена первая строка в коде макроса. Код находится в режиме отладки, и параметры в раскрывающемся списке «Отладка» станут активными.

Резервное копирование или движение вперед в коде

Вы можете двигаться вперед или назад в коде, выбрав Step Over или Step Out.

Не переходить через каждую строку кода

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

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

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

Использование часов

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

Остановка казни

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

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

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

Вы также можете написать код VBA для той же цели с событием Open книги. Событие Open запускает код в подпроцедуре Workbook_Open () каждый раз, когда вы открываете книгу.

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

Вы можете записать макрос Auto_Run следующим образом:

  • Щелкните вкладку ПРОСМОТР на ленте.
  • Щелкните Макросы.
  • Щелкните Записать макрос. Откроется диалоговое окно «Запись макроса».
  • Введите Auto_Run в качестве имени макроса.
  • Введите описание и нажмите ОК.
  • Начните запись макроса.
  • Остановить запись.
  • Сохраните книгу как книгу с поддержкой макросов.
  • Закройте книгу.
  • Откройте книгу. Макрос Auto_Run запустится автоматически.

Если вы хотите, чтобы Excel запускался без запуска макроса Auto_Open, удерживайте клавишу SHIFT при запуске Excel.

Ограничения макроса Auto_Open

Ниже приведены ограничения макроса Auto_Open:

  • Если книга, в которой вы сохранили макрос Auto_Open, содержит код для события Open книги, код для события Open переопределит действия в макросе Auto_Open.

  • Макрос Auto_Open игнорируется, когда книга открывается путем выполнения кода, использующего метод Open.

  • Макрос Auto_Open запускается перед открытием любых других книг. Следовательно, если вы записываете действия, которые вы хотите, чтобы Excel выполнял в книге по умолчанию Book1 или в книге, загруженной из папки XLStart, макрос Auto_Open завершится ошибкой при перезапуске Excel, поскольку макрос запускается до открытия книг по умолчанию и запускаемых книг. .

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

Код VBA для открытого события книги

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

Откройте книгу, в которой вы сохранили макрос, который вы написали для абсолютных ссылок - Report_ProjectXYZ. Когда этот макрос запускается, новый рабочий лист будет добавлен в книгу, и структура отчета проекта появится на новом листе.

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

Следуйте приведенной ниже процедуре в редакторе VBA:

  • Дважды щелкните ThisWorkbook в проводнике проектов.

  • В окне кода выберите Workbook в левом раскрывающемся списке и Open в правом раскрывающемся списке. Появится Sub Workbook_Open ().

  • Щелкните Модули в проводнике проектов.

  • Дважды щелкните имя модуля, содержащего код макроса.

  • Скопируйте код макроса из модуля и вставьте его в Sub WorkBook_Open ().

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