Remkomplekty.ru

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

Как запустить vba в excel

Как в Excel 2010 или 2013 вставить и запустить код VBA – руководство для начинающих

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

Большинство пользователей не являются гуру Microsoft Office. Они могут не знать всех тонкостей работы той или иной функции, и не смогут ответить на вопрос, как отличается скорость выполнения макроса VBA в Excel 2010 и 2013. Многие просто используют Excel, как инструмент для обработки данных.

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

Вставляем код VBA в книгу Excel

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

    1. Открываем рабочую книгу в Excel.
    2. Нажатием Alt+F11 вызываем окно редактора Visual Basic
    3. На панели Project-VBAProject кликаем правой кнопкой мыши по имени рабочей книги (в левой верхней части окна редактора) и в контекстном меню выбираем Insert >Module.
    4. Копируем код VBA (с веб-страницы или из другого источника) и вставляем его в правую область редактора VBA (окно Module1).Подсказка: Как увеличить скорость выполнения макроса?

    В самом начале кода Вашего макроса VBA должны содержаться строки:

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

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

      В самое начало кода после всех строк, начинающихся с Dim (если строк, начинающихся с Dim нет, то вставляем сразу после строки Sub):

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    В самый конец кода, перед End Sub:

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    Эти строки, как можно понять из их содержания, отключают обновление экрана и пересчёт формул рабочей книги перед выполнением макроса. После выполнения кода эти параметры снова включаются. Данный приём приводит к ускорению выполнения макроса от 10% до 500% (да, макрос может работать в 5 раз быстрее, если манипуляции над ячейками происходят без лишних остановок).

  1. Сохраняем рабочую книгу, как книгу Excel с поддержкой макросов. Для этого нажмите Ctrl+S и в окне с предупреждением Следующие компоненты невозможно сохранить в книге без поддержки макросов (The following features cannot be saved in macro-free workbook) нажмите Нет (No).Откроется диалоговое окно Сохранение документа (Save as). В выпадающем списке Тип файла (Save as type) выбираем Книга Excel с поддержкой макросов (Excel macro-enabled workbook) и нажимаем кнопку Сохранить (Save).

  1. Нажимаем Alt+Q, чтобы закрыть окно редактора VBA и вернуться к книге Excel.

Запускаем макрос VBA в Excel

Чтобы запустить только что добавленный макрос, нажмите Alt+F8. Откроется диалоговое окно Макрос (Macro). В списке Имя макроса (Macro name) выберите нужный макрос и нажмите кнопку Выполнить (Run).

VBA Excel. Начинаем программировать с нуля

Первое знакомство с редактором VBA Excel, создание процедур (подпрограмм) и написание простейшего кода, работающего с переменными и ячейками рабочего листа.

Знакомство с редактором VBA

  1. Создайте новую книгу Excel и сохраните ее как книгу с поддержкой макросов с расширением .xlsm. В старых версиях Excel по 2003 год – как обычную книгу с расширением .xls.
  2. Нажмите сочетание клавиш «левая_клавиша_Alt+F11», которое откроет редактор VBA. С правой клавишей Alt такой фокус не пройдет. Также, в редактор VBA можно перейти по ссылке «Visual Basic» из панели инструментов «Разработчик» на ленте быстрого доступа. Если вкладки «Разработчик» на ленте нет, ее следует добавить в настройках параметров Excel.

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

  1. Нажмите кнопку «Module» во вкладке «Insert» главного меню. То же подменю откроется при нажатии на вторую кнопку после значка Excel на панели инструментов.

После нажатия кнопки «Module» вы увидите ссылку на него, появившуюся в проводнике слева.

Первая программа на VBA Excel

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

  1. Откройте стандартный модуль двойным кликом по его ссылке в проводнике. Поместите в него курсор и нажмите кнопку «Procedure…» во вкладке «Insert» главного меню. Та же ссылка будет доступна при нажатии на вторую кнопку после значка Excel на панели инструментов.

В результате откроется окно добавления шаблона процедуры (Sub).

  1. Наберите в поле «Name» имя процедуры: «Primer1», или скопируйте его отсюда и вставьте в поле «Name». Нажмите кнопку «OK», чтобы добавить в модуль первую и последнюю строки процедуры.

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

  1. Вставьте внутрь шаблона процедуры следующую строку: MsgBox «Привет» .

Функция MsgBox выводит информационное сообщение с указанным текстом. В нашем примере – это «Привет».

  1. Проверьте, что курсор находится внутри процедуры, и запустите ее, нажав клавишу «F5». А также, запустить процедуру на выполнение можно, нажав на треугольник (на изображении под пунктом меню «Debug») или на кнопку «Run Sub/UserForm» во вкладке «Run» главного меню редактора VBA Excel.


Если вы увидели такое сообщение, как на изображении, то, поздравляю – вы написали свою первую программу!

Работа с переменными

Чтобы использовать в процедуре переменные, их необходимо объявить с помощью ключевого слова «Dim». Если при объявлении переменных не указать типы данных, они смогут принимать любые доступные в VBA Excel значения. Комментарии в тексте процедур начинаются со знака «’» (апостроф).

Пример 2
Присвоение переменным числовых значений:

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

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

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

Перед запуском макроса

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

В Windowsперейдите в раздел Параметры _Гт_ файла _гт_ настроить ленту.

Для Macперейдите на вкладку _Гт_ параметры Excel. _гт_ ленты _амп_ Toolbar.

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

Откройте книгу, содержащую нужный макрос.

На вкладке Разработчик в группе Код нажмите кнопку Макросы.

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

Кроме того, у вас есть и другие варианты:

Options (параметры ) — добавить сочетание клавиш или описание макроса.

Пошаговые действия . редактор Visual Basic откроется в первой строке макроса. Нажимая клавишу F8 , вы сможете пошагово прокручивать код макроса по одной строке за раз.

Изменить — откроется редактор Visual Basic, и вы сможете изменить код макроса по мере необходимости. После внесения изменений вы можете нажать клавишу F5 , чтобы запустить макрос из редактора.

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

На вкладке Разработчик в группе Код нажмите кнопку Макросы.

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

Нажмите кнопку Параметры.

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

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

Для Windowsсочетание клавиш для прописных букв — CTRL + Letter. Для прописных букв — сочетание клавиш CTRL + SHIFT + Letter.

Для Macсочетание клавиш для строчных букв — Option + Command + Letter, но сочетание клавиш CTRL + буква также будет работать. Для прописных букв — сочетание клавиш CTRL + SHIFT + Letter.

Будьте внимательны при назначении сочетаний клавиш, так как они будут переопределять любые эквивалентные клавиши Excel по умолчанию, пока открыта книга, содержащая макрос. Например, если назначить макросу сочетание клавиш Ctrl + z, вы потеряете возможность его отмены. Поэтому рекомендуется использовать сочетание клавиш CTRL + SHIFT + прописной буквы , например сочетание клавиш Ctrl + Shift + Z, которое не имеет эквивалентного сочетания клавиш в Excel.

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

Введите описание макроса в поле Описание.

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

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

Вы можете создать настраиваемую группу, которая будет отображаться на вкладке ленты, а затем назначить ей кнопку в этой группе. Например, вы можете добавить настраиваемую группу с именем «Мои макросы» на вкладку «Разработчик», а затем добавить в нее макрос (выводится как кнопка). Для этого выполните действия, описанные в разделе Назначение макроса кнопке.

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

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

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

Чтобы узнать, как вставить графический объект, ознакомьтесь со статьей Добавление, изменение и удаление фигур.

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

Выполните одно из следующих действий.

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

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

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

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

Нажмите кнопку ОК.

На вкладке разработчик нажмите кнопку Visual Basic, чтобы запустить Редактор Visual Basic (VBE). Найдите в окне проекта модуль, содержащий макрос, который нужно запустить, и откройте его. Все макросы в этом модуле будут перечислены на правой панели. Выберите макрос, который вы хотите запустить, разместите курсор в любом месте макроса, нажмите клавишу F5или в меню выберите команду запустить _гт_ запустить макрос.

Создание события Воркбук_опен.

В следующем примере событие Open используется для запуска макроса при открытии книги.

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

На вкладке Разработчик в группе Код нажмите кнопку Visual Basic.

В окне » Обозреватель проектов » щелкните правой кнопкой мыши объект ThisWorkbook и выберите пункт Просмотреть код.

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

В списке объектов , расположенном над окном кода, выберите пункт Книга.

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

Private Sub воркбук_опен ()

Добавьте в процедуру следующие строки кода

Private Sub воркбук_опен ()
MsgBox Date
(«Лист1»). Range («a1»). Value = Дата
окончания подзадачи

Переключитесь на приложение Excel и сохраните данную книгу как книгу с поддержкой макросов (.XLSM).

Закройте и снова откройте книгу. После повторного открытия книги Excel запускает процедуру воркбук_опен , которая отображает текущую дату в окне сообщения.

В окне сообщения нажмите кнопку ОК.

Примечание: Ячейка a1 на листе Sheet1 также включает в себя дату, полученную в результате выполнения процедуры Воркбук_опен.

Дополнительные сведения

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

Знакомство с программированием в Excel

Добавление на ленту вкладки разработчика

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

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

Для Excel 2010 и 2013. В параметрах Excel нужно выбрать пункт «Настроить ленту» и в категории основные вкладки установить галочку для вкладки «Разработчик».

Знакомство с редактором Visual Basic в Excel

Чтобы попасть в редактор кода, кликните на вкладке разработчика в области «Код» по кнопке «Visual Basic».

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

№1 на изображении. Кнопка сохранить. Сохраняет текущую книгу Excel. Необходимо быть внимательнее при работе с несколькими книгами, так как текущей считается та, которая выделена в окне проектов (№3 на изображении).

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

№3 на изображении. Окно управления проектами VBA открытых книг (Project Explorer) и кнопка, отображающая это окно.

№4 на изображении. Кнопка, отображающая окно свойств объектов (Properties Window), выбранных в окне №3, и элементов пользовательских форм (работа с объектами, их свойствами и методами будет рассмотрена в отдельной статье этой категории).

№5 на изображении. Кнопка вызова окна объектов (Object Browser). В нем описаны все доступные для работы объекты, их свойства и методы.

Кнопки №3, №4 и №5 так же доступны на вкладке «View» главного меню редактора. Там же можно вызвать другие полезные для работы окна, которые не будут рассмотрены в этом материале.

Написание простой процедуры на Visual Basic for Application

Код любой процедуры (подпрограммы) располагается в модуле, поэтому необходимо его добавить, чтобы приступить к программированию. Выберите пункт «Module» на вкладке «Insert» основного меню редактора VBA. В основной области редактора (на изображении имеет серый фон) должно появиться новое окно кода – Имя_книги – имя_модуля (Code), а в окне управления проектами (№3) к дереву выбранного проекта добавится вновь созданный модуль.

Добавьте в модуль следующий код:

Данная процедура выводит на экран диалоговое окно с сообщением «Привет пользователь!». Протестируйте ее работу, нажав кнопку Rub Sub (№2 на изображении) или вызвав как обычный макрос.

Теперь более подробно разберем приведенный код.

Любая процедура начинается со строки объявления, состоящей из оператора Sub (сокращение от Subprogram), после которого следует имя подпрограммы. За именем следует перечень аргументов, заключенный в скобки.

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

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

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

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

Учимся пользоваться Object Browser

Выше была упомянута функция MsgBox, но ее возможности полностью не рассмотрены. Это хороший случай, чтобы научиться использовать браузер объектов (№5 на изображении окна редактора VBA).

Нажмите кнопку на панели или клавишу F2, чтобы отобразить Object Browser:

Выпадающий список №1 содержит перечень всех подключенных библиотек. Используйте его только в том случае, если точно знаете, к какой библиотеке относится изучаемый класс, функция и т.д. (подключить или отключить любую из доступных библиотек можно, выбрав пункт «References» на вкладке «Tools» главного меню редактора VBA).

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

Поле №2 предназначено для поиска свойств, методов, классов подключенных библиотек.

Результаты поиска отображаются в поле №3 и представляют из себя таблицу с тремя столбцами: библиотека, класс и член класса.

Поле №4 содержит перечень всех классов, выбранных в поле №1 библиотек.

В области №5 содержится список всех членов класса, выбранного в поле №4.

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

Примечание: библиотеки, классы, функции и процедуры (методы), свойства и т.п. в Object Browser имеют различные обозначения, представленные набором пиктограмм. Краткая справка (поле №6) всегда указывает какой тип элемента выделен. Обращайте на это внимание, чтобы в дальнейшем лучше ориентироваться в работе с объектами.

Примечание: Понятие объекта не относится к теме данной статьи и будет рассмотрено в следующих материалах.

Теперь найдем функцию MsgBox с помощью браузера объектов. В поле поиска (№2) впишите ее название и кликните по кнопке с изображением бинокля. В поле результатов поиска выделите строку со значением MsgBox в столбце «Member». В поле №6 появилась краткая информация, сообщающая нам, что выбранный член является функцией, имеет перечень аргументов, возвращает результат типа VbMsgBoxResult, относиться в классу Interaction библиотеки VBA.

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

Изучите самостоятельно возможности функции MsgBox.

Ссылка на процедуру VBA

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

Для лучшего понимая рассмотрим пример (в примере используется псевдокод):

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

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

Создание кнопки в excel для макросов

Рассмотрим несколько вариантов создания кнопок в Excel для запуска какой-нибудь процедуры. Для примера напишем простую процедуру, которая будет присваивать текстовой значение ячейки равным «Привет, мир!»

Переходим на вкладку Разработчик (о том как добавить ее вы можете узнать из статьи о вкладках в Excel ) и нажимаем иконку Visual Basic (либо можно воспользоваться комбинацией клавиш Alt+F11).

Откроет окно Microsoft Visual Basic for Applications. Создадим модуль.

В левой области окна Project – VBAProject нажимаем правую кнопку мыши на Microsoft Excel Objects – Insert – Module

В открывшемся окне пишем процедуру:

Далее переходим на лист Excel.

Создание кнопки через картинку или изображение

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

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

Снимаем выделение с прямоугольника. Теперь при нажатии мышкой на него будет выполняться макрос «Привет». Для выделения элементов с назначенными макросам для последующего редактирования используйте правую кнопку мыши.

Создание кнопки элемент управления формой

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

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

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

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

Создание кнопки ActiveX на листе и форме.

Как и в предыдущем примере нажимаем на вкладке Разработчик иконку вставить, но теперь выбираем кнопку элемент ActiveX:

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

Далее на панели инструментов нажимаем свойства:

Сейчас нас интересует свойство Caption – именно оно отвечает за надпись на кнопке. Атрибут Name отвечает за обращение к кнопке в коде процедуры, давайте тоже изменим, напишем просто Btn1 (использовать только латинские буквы).

Далее на панели инструментов в блоке элементы управления нажимаем Просмотр кода или просто двойной щелчок левой кнопкой мыши на кнопке, и переходим в код процедуры:

Пишем код для запуска процедуры с названием Привет, которую мы создали в самом начале:

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

Кнопка на форме создается аналогично, только сперва создадим форму. Переходим в окно VBA (ALT+F11). Нажимаем правой кнопкой мыши на Microsoft Excel Objects – Insert – UserForm

По умолчанию вызвался инструмент Toolbox из которого мы должны добавить кнопку CommandButton. Вставляем ее на форму и сразу изменим имя. Это делается точно также, как и со случаем кнопки ActiveX на листе, только сейчас для того, чтобы выбрать свойства нажимает правой клавишей на кнопке и выбираем Properties:

Также изменяем свойство Caption на Кнопка, а свойство Name на Btn1. Далее заходим в код кнопки – двойной щелчок на кнопке. Пишем в коде процедуры Call Привет:

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

Свойство Name созданной формы имеет значение UserForm1 (это легко проверить, зайдя в свойства (Properties) самой формы). Для того, чтобы выводить форму, теперь заходим в код кнопки на листе (режим конструктора должен быть включен). И меняем Call Привет на UserForm1.Show вот так:

Все проверяем. Нажимаем кнопку на листе (перед этим отключаем режим конструктора):

Читать еще:  Data mining excel
Ссылка на основную публикацию
Adblock
detector