Программирование на vba в excel 2016 самоучитель комолова скачать бесплатно

Это учебник по написанию кода в электронных таблицах Excel с использованием Visual Basic для приложений (VBA).
Excel-один из самых популярных продуктов Microsoft. В 2016 году генеральный директор Microsoft сказал: Это просто невозможно для меня”. Ну, может быть, мир не может думать без Excel.

  • В 1996 году насчитывалось более 30 миллионов пользователей Microsoft Excel (источник).
  • Сегодня, по оценкам, насчитывается 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.

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

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

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

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

Ням.

Потому что пицца на дровах-самая лучшая!

Но серьезно.

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

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

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

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

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

Чтобы написать VBA, вам нужно добавить вкладку Developer на ленту, так что вы увидите ленту вот так.

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

  1. В разделе Настройка ленты и на основных вкладках установите флажок Разработчик.

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

Редактор VBA

Перейдите на вкладку Разработчик и нажмите кнопку Visual Basic. Появится новое окно — это редактор Visual Basic. Для целей этого руководства вам просто нужно быть знакомым с областью Обозревателя проектов и областью свойств свойств.

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

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

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

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

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

Sub Auto_Open()
MsgBox (
End Sub

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

Та-да!

Как он это делает?

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

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

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

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

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

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

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

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

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

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

Sub UserReportQuery()
Dim userInput As Long
Dim Answer As Integer
userInput = vbYesNo
Answer = MsgBox(
If Answer = vbYes Then ProcessReport
End Sub

Sub ProcessReport()
MsgBox (
End Sub

Сохраните и перейдите обратно на вкладку “Разработчик “Excel и выберите опцию” КнопкаНажмите на ячейку и назначьте макрос UserReportQuery кнопке.

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

Нажмите кнопку “да” или нажмите клавишу Enter.

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

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

Как он это делает?

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

  • Dim userInput As Long: Dim является сокращением от “dimension” и позволяет объявлять имена переменных. В этом случае userInput-это имя переменной, а Long-тип данных. На простом английском языке эта строка означает “Вот переменная под названием “userInput”, и это тип переменной Long.”
  • Dim Answer As Integer: объявляет другую переменную под названием “Answer” с типом данных Integer. Подробнее о типах данных читайте здесь.
  • userInput = vbYesNo: присваивает переменной значение. В данном случае vbYesNo, который отображает кнопки Yes и No. Существует много типов кнопок, узнайте больше здесь.
  • Answer = MsgBox(“Process the XYZ Report?”. UserInput): присваивает значение переменной Answer функции MsgBox и переменной userInput. Да, переменная внутри переменной.
  • If Answer = vbYes Then ProcessReport: это “оператор If”, условный оператор, который позволяет нам сказать. Если x истинно. То сделать y. В этом случае. Если пользователь выбрал “Да”, то выполните подпрограмму ProcessReport.

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

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

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

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

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

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

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

Циклы For очень полезны. Если вам нужно выполнять повторяющиеся задачи на определенном диапазоне значений — массивах или диапазонах ячеек. На простом английском языке цикл говорит: “для каждого x сделайте y.”

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

Sub LoopExample()
Dim X Как Целое
Число Для Диапазона X = От 1 До 100
(
Next X
End Sub

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

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

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

Как он это делает?

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

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

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

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

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

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

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

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

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

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

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

Счастливого кодирования!

Учебные ресурсы

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

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

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