Учебное пособие по Excel VBA - Как написать код в электронной таблице с помощью Visual Basic

Введение

Это руководство по написанию кода в электронных таблицах Excel с использованием Visual Basic для приложений (VBA).

Excel - один из самых популярных продуктов Microsoft. В 2016 году генеральный директор Microsoft сказал: «Подумайте о мире без Excel. Для меня это просто невозможно». Что ж, возможно, мир не может мыслить без Excel.

  • В 1996 году Microsoft Excel было более 30 миллионов пользователей (источник).
  • Сегодня около 750 миллионов пользователей Microsoft Excel. Это немного больше, чем население Европы, и в 25 раз больше пользователей, чем было в 1996 году.

Мы одна большая счастливая семья!

В этом руководстве вы узнаете о VBA и о том, как писать код в электронной таблице Excel с помощью Visual Basic.

Предпосылки

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

  • Знакомство с Microsoft Excel от начального до среднего
  • Если вы хотите следовать примерам VBA в этой статье, вам понадобится доступ к Microsoft Excel, желательно к последней версии (2019), но Excel 2016 и Excel 2013 будут работать нормально.
  • Готовность пробовать новое

Цели обучения

Из этой статьи вы узнаете:

  1. Что такое VBA
  2. Почему вы должны использовать VBA
  3. Как настроить в Excel для написания VBA
  4. Как решить некоторые реальные проблемы с помощью VBA

Важные концепции

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

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

Если у вас нет опыта в объектно-ориентированном программировании, и это совершенно новая концепция, найдите секунду, чтобы понять это!

Процедуры : процедура - это фрагмент кода VBA, написанный в редакторе Visual Basic, который выполняет задачу. Иногда это также называют макросом (подробнее о макросах ниже). Есть два типа процедур:

  • Подпрограммы: группа операторов VBA, которые выполняют одно или несколько действий.
  • Функции: группа операторов VBA, которая выполняет одно или несколько действий и возвращает одно или несколько значений.

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

Макросы . Если вы потратили какое-то время на изучение более сложных функций Excel, вы, вероятно, сталкивались с концепцией «макроса». Пользователи Excel могут записывать макросы, состоящие из пользовательских команд / нажатий клавиш / щелчков, и воспроизводить их с молниеносной скоростью для выполнения повторяющихся задач. Записанные макросы генерируют код VBA, который затем можно изучить. На самом деле довольно интересно записать простой макрос, а затем посмотреть код VBA.

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

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

Что такое VBA?

Visual Basic для приложений - это язык программирования, разработанный Microsoft. Каждая программа в пакете Microsoft Office поставляется с языком VBA без дополнительной оплаты. VBA позволяет пользователям Microsoft Office создавать небольшие программы, которые работают с программами Microsoft Office.

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

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

Ням.

Зачем использовать VBA в Excel?

Ведь пицца в дровах самая лучшая!

Но серьезно.

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

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

Вам следует подумать об использовании VBA, если вам нужно:

  • Автоматизировать повторяющиеся задачи
  • Создавайте простые способы взаимодействия пользователей с вашими таблицами
  • Управляйте большими объемами данных

Подготовка к записи VBA в Excel

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

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

Чтобы добавить вкладку Разработчик на ленту:

  1. На вкладке «Файл» выберите «Параметры»> «Настроить ленту».
  2. В разделе «Настроить ленту» и в разделе «Основные вкладки» установите флажок «Разработчик».

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

Редактор VBA

Перейдите на вкладку разработчика и нажмите кнопку Visual Basic. Появится новое окно - это редактор Visual Basic. Для использования в этом руководстве вам просто нужно быть знакомым с панелями Project Explorer и Property Properties.

Примеры Excel VBA

Во-первых, давайте создадим файл, в котором мы будем играть.

  1. Откройте новый файл Excel
  2. Сохраните его как книгу с поддержкой макросов (. Xlsm)
  3. Выберите вкладку Разработчик
  4. Откройте редактор VBA

Давайте посмотрим на несколько простых примеров, которые помогут вам писать код в электронной таблице с помощью Visual Basic.

Пример # 1: отображение сообщения, когда пользователи открывают книгу Excel

В редакторе VBA выберите Insert -> New Module

Напишите этот код в окне модуля (не вставляйте!):

Sub Auto_Open ()

MsgBox («Добро пожаловать в рабочую книгу XYZ.»)

Конец подписки

Сохраните, закройте книгу и снова откройте книгу. Этот диалог должен отобразиться.

Да да!

Как это происходит?

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

  • Подпрограмма (сокращение от «Подпрограмма»): запомните с самого начала «группу операторов VBA, которые выполняют одно или несколько действий».
  • Auto_Open: это конкретная подпрограмма. Он автоматически запускает ваш код при открытии файла Excel - это событие, запускающее процедуру. Auto_Open будет запускаться только при открытии книги вручную; он не будет запускаться, если книга открывается с помощью кода из другой книги (Workbook_Open сделает это, узнайте больше о различиях между ними).
  • По умолчанию доступ к подпрограмме является публичным. Это означает, что любой другой модуль может использовать эту подпрограмму. Все примеры в этом руководстве будут общедоступными подпрограммами. При необходимости вы можете объявить подпрограммы закрытыми. Это может понадобиться в некоторых ситуациях. Узнайте больше о модификаторах доступа к подпрограммам.
  • msgBox: это функция - группа операторов VBA, которая выполняет одно или несколько действий и возвращает значение. Возвращаемое значение - сообщение «Добро пожаловать в книгу XYZ».

Короче говоря, это простая подпрограмма, содержащая функцию.

Когда я смогу это использовать?

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

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

Примеры из реального мира

  • Используйте функцию MsgBox для отображения сообщения при возникновении какого-либо события: пользователь закрывает книгу Excel, пользователь печатает, новый лист добавляется в книгу и т. Д.
  • Используйте функцию MsgBox для отображения сообщения, когда пользователю необходимо выполнить условие перед закрытием книги Excel.
  • Используйте функцию InputBox для получения информации от пользователя

Пример # 2: Разрешить пользователю выполнить другую процедуру

В редакторе VBA выберите Insert -> New Module

Напишите этот код в окне модуля (не вставляйте!):

Sub UserReportQuery ()

Dim UserInput As Long

Тусклый ответ как целое число

UserInput = vbYesNo

Answer = MsgBox («Обработать отчет XYZ?», UserInput)

Если Answer = vbYes, то ProcessReport

Конец подписки

Sub ProcessReport ()

MsgBox («Спасибо за обработку отчета XYZ.»)

Конец подписки

Сохраните и вернитесь на вкладку «Разработчик» в Excel и выберите «Кнопка». Щелкните ячейку и назначьте ей макрос UserReportQuery.

Теперь нажмите кнопку. Это сообщение должно отображаться:

Нажмите «Да» или нажмите Enter.

Еще раз, тада!

Обратите внимание, что вторичная подпрограмма ProcessReport может быть чем угодно . Я продемонстрирую больше возможностей в примере №3. Но сначала...

Как это происходит?

Этот пример основан на предыдущем примере и содержит довольно много новых элементов. Давайте пройдемся по новому:

  • Dim UserInput As Long: Dim - это сокращение от «измерение» и позволяет объявлять имена переменных. В этом случае UserInput - это имя переменной, а Long - тип данных. На простом английском языке эта строка означает «Вот переменная с именем« UserInput », и это переменная типа Long».
  • Dim Answer As Integer: объявляет другую переменную под названием «Ответ» с типом данных Integer. Узнайте больше о типах данных здесь.
  • UserInput = vbYesNo: присваивает значение переменной. В этом случае vbYesNo, который отображает кнопки Да и Нет. Есть много типов кнопок, подробнее читайте здесь.
  • Answer = MsgBox («Обработать отчет XYZ?», UserInput): присваивает значение переменной Answer как функции MsgBox, так и переменной UserInput. Да, переменная внутри переменной.
  • Если Answer = vbYes Then ProcessReport: это «оператор If», условный оператор, который позволяет нам сказать, истинно ли x, а затем выполнить y. В этом случае, если пользователь выбрал «Да», выполните подпрограмму ProcessReport.

Когда я смогу это использовать?

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

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

  • Проблема: каждый раз, когда необходимо создать один из этих отчетов, пользователь открывает файл и меняет форматирование и диаграммы; так далее и так далее. Этот файл интенсивно редактируется не менее 3 раз в неделю, и на каждое его редактирование уходит не менее 30 минут.
  • Решение: создайте по одной кнопке для каждого типа отчета, которая автоматически переформатирует необходимые компоненты отчетов и генерирует необходимые диаграммы.

Примеры из реального мира

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

Пример # 3: Добавление чисел в диапазон с помощью цикла For-Next

Циклы For очень полезны, если вам нужно выполнять повторяющиеся задачи для определенного диапазона значений - массивов или диапазонов ячеек. Говоря простым языком, цикл гласит: «Для каждого x делайте y».

В редакторе VBA выберите Insert -> New Module

Напишите этот код в окне модуля (не вставляйте!):

Sub LoopExample ()

Dim X как целое число

Для X = от 1 до 100

Диапазон ("A" и X). Значение = X

Следующий X

Конец подписки

Сохраните и вернитесь на вкладку «Разработчик» в Excel и нажмите кнопку «Макросы». Запустите макрос LoopExample.

Это должно произойти:

И т.д., до 100-го ряда.

Как это происходит?

  • Dim X As Integer: объявляет переменную X как целочисленный тип данных.
  • Для X = от 1 до 100: это начало цикла For. Проще говоря, он указывает циклу повторяться до тех пор, пока X = 100. X - счетчик . Цикл будет выполняться до тех пор, пока X = 100, выполнится последний раз, а затем остановится.
  • Range ("A" & X) .Value = X: объявляет диапазон цикла и то, что помещать в этот диапазон. Поскольку изначально X = 1, первой ячейкой будет A1, после чего цикл поместит X в эту ячейку.
  • Следующий X: это говорит, что цикл снова запустится

Когда я смогу это использовать?

Цикл For-Next - одна из самых мощных функций VBA; существует множество возможных вариантов использования. Это более сложный пример, который потребует нескольких уровней логики, но он передает мир возможностей в циклах For-Next.

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

Вам необходимо выяснить, какой должна быть розничная цена каждого продукта. Вы думаете, что это должна быть стоимость ингредиентов плюс 20%, но также на 1,2% ниже средней по рынку, если это возможно. Цикл For-Next позволит вам выполнить такой расчет.

Примеры из реального мира

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

Вывод

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

  • Что такое VBA?
  • Как мне начать использовать VBA в Excel?
  • Почему и когда вы бы использовали VBA?
  • Какие проблемы я мог бы решить с помощью VBA?

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

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

Удачного кодирования!

Образовательные ресурсы

  • Программирование Excel VBA для чайников, Джон Уокенбах
  • Начало работы с VBA, документация Microsoft
  • Изучение VBA в Excel, Линда

Немного обо мне

Я Хлоя Такер, художник и разработчик из Портленда, штат Орегон. Как бывший преподаватель, я постоянно ищу точки пересечения обучения и преподавания, технологий и искусства. Свяжитесь со мной в Twitter @_chloetucker и посетите мой сайт chloe.dev.