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

Обновлено: 20.11.2024

Вы можете использовать Microsoft Excel 2013 XLL Software Development Kit и C API для создания высокопроизводительных функций листа для Excel 2013. Обновления до Excel 2013 C API отражают постоянную поддержку пользователей, для которых производительность сторонних или внутренняя функциональность имеет решающее значение.

Интерфейсы программирования Excel

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

Макроязык XLM: первый доступный пользователю язык для расширения Excel и основа C API. Хотя XLM по-прежнему поддерживается в Excel 2010, он уже давно заменен Visual Basic для приложений (VBA).

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

VBA: объекты кода Visual Basic, связанные с объектами книги Excel. VBA позволяет перехватывать события, настраивать их и добавлять определяемые пользователем функции и команды. VBA является наиболее часто используемым и наиболее доступным из вариантов расширения.

COM: стандарт взаимодействия для приложений Windows, с помощью которого Excel предоставляет доступ к своим событиям и объектам. VBA использует COM для взаимодействия с Excel. Excel экспортирует библиотеки типов COM, которые могут помочь вам создавать ресурсы кода C++ COM и приложения, которые могут управлять Excel извне.

Когда использовать C API

Основной причиной написания XLL и использования C API является создание высокопроизводительных функций рабочего листа. Хотя функции XLL часто называют определяемыми пользователем функциями, затраты времени на получение понимания и навыков, необходимых для написания XLL, делают эту технологию непрактичной для большинства пользователей. Тем не менее применение высокопроизводительных функций — а в Excel 2013 и возможность писать многопоточные интерфейсы для мощных ресурсов сервера — делают это очень важной частью расширяемости Excel.

Пересмотр C API, представленный в Excel 2007, в основном касается аспектов, связанных с высокопроизводительными вычислениями, а не такими функциями, как пользовательский интерфейс.

Написание высокопроизводительных пользовательских функций рабочего листа

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

Доступ к многопоточным серверам с помощью функций рабочего листа XLL

Многопоточный пересчет (MTR), впервые представленный в Excel 2007, позволяет создавать потокобезопасные функции листа XLL. Вы можете использовать эти функции для доступа к многопоточным серверам. В следующих разделах более подробно описывается, как это может значительно повысить производительность, наблюдаемую пользователем. Для пользователей Excel, которым иногда требуется доступ к большой вычислительной мощности, сочетание XLL, использующего MTR, и мощного сервера вычислений обеспечивает высочайшую производительность.

Настройка пользовательского интерфейса Excel

Для многих версий Excel API C был не лучшим выбором для настройки пользовательского интерфейса. VBA имеет превосходный доступ к объектам и событиям Excel. Пользовательский интерфейс, представленный в Excel 2007, значительно отличается от более ранних версий как по внешнему виду, так и по базовой технологии. Этот интерфейс лучше всего настроить с помощью ресурсов управляемого кода.

Создание приложений, доступных в Интернете

Управление Excel из внешних приложений

Асинхронный вызов Excel

Excel позволяет XLL вызывать C API только в том случае, если Excel передал управление XLL. Функция рабочего листа, вызываемая Excel, может выполнять обратный вызов в Excel с помощью C API. Команда XLL, вызываемая Excel, может вызывать C API. Функции и команды DLL и XLL, которые вызываются VBA, когда сам VBA вызывается Excel, могут вызывать C API. Вы не можете, например, установить синхронизированный обратный вызов Windows в свой XLL и вызывать из него C API, и вы не можете вызывать C API из фонового потока, созданного вашим XLL. Асинхронный вызов Excel с помощью COM из библиотеки DLL или XLL не рекомендуется.

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

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

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

C API и его связь с XLM

Язык макросов Excel (XLM) был первой доступной пользователю средой программирования, реализованной в Excel. Это позволило пользователям создавать собственные команды и функции на специальных листах макросов, которые выглядят как обычные рабочие листы. Листы макросов XLM по-прежнему поддерживаются в Excel 2013. Вы можете использовать все обычные функции рабочего листа, такие как SUM и LOG, на листе макроса, в дополнение к следующим элементам, которые нельзя ввести на листе:

Информационные функции рабочей области, такие как GET.CELL и GET.WORKBOOK.

Функции, эквивалентные командам, которые позволяют автоматизировать обычные пользовательские операции, такие как DEFINE.NAME и PASTE.

Функции, относящиеся к надстройкам, например REGISTER.

Ловушки событий, эквивалентные командам, такие как ON.ENRTY и ON.TIME.

Операции макрофункций, такие как ARGUMENT и VOLATILE.

Операции управления потоком, такие как GOTO и RETURN.

Ограниченная версия C API существовала в Excel версии 3. Однако в Excel версии 4 язык XLM был сопоставлен с C API. С тех пор библиотеки DLL могут вызывать все функции рабочего листа, информационные функции листа макросов и команды, а также устанавливать ловушки событий. Библиотеки DLL не могут вызывать функции управления потоком XLM из C API. Эти функции и команды листа макросов задокументированы в файле справки XLMacr8.hlp (ранее называвшемся Macrofun.hlp). Чтобы получить этот файл справки, перейдите в Центр загрузки Майкрософт и выполните поиск «XLMacr8.hlp».

Windows Vista и Windows 7 не поддерживают файлы .hlp напрямую, но вы можете загрузить программу справки Windows (WinHlp32.exe) для Windows Vista или программу справки Windows (WinHlp32.exe) для Windows 7, которая позволяет их использовать. открыть.

DLL вызывают эквиваленты C API этих функций и команд с помощью функций обратного вызова Excel4, Excel4v, Excel12 и Excel12v (последние две были введены в Excel 2007). Перечисленные константы, соответствующие каждой функции и команде, определяются в заголовочном файле и передаются в качестве одного из аргументов этих обратных вызовов. Например, GET.CELL представлен xlfGetCell, REGISTER — xlfRegister, а DEFINE.NAME — xlcDefineName.

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

С момента появления листов Visual Basic для приложений (VBA) в Excel версии 5 и редактора Visual Basic (VBE) в версии 8 (Excel 97) пользователям проще всего настроить Excel с помощью VBA вместо XLM. Следовательно, большая часть новых функций, представленных в более поздних версиях Excel, доступна через VBA, но не через XLM или C API. Например, некоторые команды, ловушки событий и расширенные возможности диалоговых окон доступны через VBA, но не через XLM или C API.

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

В этом руководстве по VBA мы рассмотрим следующие темы.

Что такое Visual Basic для приложений (VBA)?

Visual Basic для приложений (VBA) – это язык программирования, управляемый событиями, реализованный корпорацией Майкрософт для разработки приложений Office. VBA помогает разрабатывать процессы автоматизации, Windows API и пользовательские функции. Он также позволяет управлять функциями пользовательского интерфейса хост-приложений.


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

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

VBA на самом деле является подмножеством Visual Basic 6.0 BASIC, расшифровывается как универсальный код символических инструкций для начинающих.

Почему VBA?

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

Личные и деловые приложения VBA в Excel

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

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

Введение в Visual Basic для приложений

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

    Переменная – в старших классах мы изучали алгебру. Найдите (x + 2y), где x = 1 и y = 3. В этом выражении x и y — переменные. Им можно присвоить любые номера, т.е. 1 и 3 соответственно, как в этом примере. Их также можно изменить, скажем, на 4 и 2 соответственно. Короче говоря, переменные — это ячейки памяти. При работе с VBA Excel вам также потребуется объявлять переменные, как на уроках алгебры
  • Не используйте зарезервированные слова. Если вы работаете студентом, вы не можете использовать титул «лектор» или «директор». Эти титулы зарезервированы за преподавателями и администрацией школы. Зарезервированные слова — это слова, которые имеют особое значение в Excel VBA, поэтому их нельзя использовать в качестве имен переменных.
  • Имена переменных не могут содержать пробелы — вы не можете определить переменную с именем первого числа. Вы можете использовать firstNumber или first_number.
  • Используйте описательные имена. Очень заманчиво назвать переменную своим именем, но избегайте этого. Используйте описательные названия, например количество, цена, промежуточный итог и т. д. – это облегчит чтение кода Excel VBA.

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

  • + для добавления
  • – для вычитания
  • * для умножения
  • / для деления.

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

Как включить вкладку "Разработчик"

Ниже приведен пошаговый процесс включения вкладки разработчика в Excel:

  • Создать новую книгу
  • Нажмите кнопку запуска ленты.
  • Выбрать параметры
  • Нажмите "Настроить ленту".
  • Установите флажок разработчика, как показано на изображении ниже.
  • Нажмите "ОК".

VBA Привет, мир!

Теперь мы покажем, как программировать на языке программирования VBA. Вся программа в VBA должна начинаться с «Sub» и заканчиваться «End sub». Здесь имя — это имя, которое вы хотите присвоить своей программе. В то время как sub означает подпрограмму, которую мы изучим в более поздней части руководства.

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

В этом руководстве предполагается, что вы прошли обучение по макросам в Excel и включили вкладку РАЗРАБОТЧИК в Excel.

  • Создать новую рабочую книгу
  • Сохраните его в формате рабочего листа Excel с поддержкой макросов *.xlsm.
  • Перейдите на вкладку "РАЗРАБОТЧИК".
  • Нажмите раскрывающийся список ВСТАВИТЬ под панелью элементов управления.
  • Выберите командную кнопку, как показано на изображении ниже.

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

Появится следующее диалоговое окно

  • Переименуйте имя макроса в btnHelloWorld_Click
  • Нажмите новую кнопку
  • Появится следующее окно кода VBA

Введите следующие коды инструкций

  • «Затемнить имя как строку» создает переменную с именем name. Переменная будет принимать текст, числовые и другие символы, поскольку мы определили ее как строку
  • "name = InputBox("Введите ваше имя")" вызывает встроенную функцию InputBox, которая отображает окно с заголовком Введите ваше имя. Затем введенное имя сохраняется в переменной name.
  • «MsgBox «Hello » + name» вызывает встроенную функцию MsgBox, которая отображает Hello и введенное имя.

  • Закройте окно кода
  • Нажмите правой кнопкой мыши на кнопку 1 и выберите "Редактировать текст".
  • Введите поздороваться

  • Нажмите "Поздороваться".
  • Появится следующее поле ввода

  • Введите свое имя, например Джордан.
  • Появится следующее окно сообщения

Поздравляем, вы только что создали свою первую программу VBA в Excel

Пошаговый пример создания простого калькулятора EMI в Excel

В этом учебном упражнении мы создадим простую программу, вычисляющую EMI. EMI является аббревиатурой от Equated Monthly Instalment. Это ежемесячная сумма, которую вы выплачиваете при получении кредита. На следующем изображении показана формула для расчета EMI.

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

Функция PMT работает следующим образом

ЗДЕСЬ

  • «ставка» — это месячная ставка. Это процентная ставка, деленная на количество платежей в год.
  • «nper» — это общее количество платежей. Это срок кредита, умноженный на количество платежей в год.
  • текущая стоимость «pv». Это фактическая сумма кредита.

Создайте графический интерфейс, используя ячейки Excel, как показано ниже

Добавить командную кнопку между строками 7 и 8

Дайте макросу кнопки имя btnCalculateEMI_Click

Нажмите кнопку редактирования

Введите следующий код

ЗДЕСЬ

  • "Dim month_rate As Single,..." Dim – это ключевое слово, используемое для определения переменных в VBA, month_rate – это имя переменной, Single – тип данных, означающий, что переменная может принимать числа.
  • “monthly_rate = Range(“B6”).Value / Range(“B5″).Value” Range – это функция, используемая для доступа к ячейкам Excel из VBA, Range(“B6”). Значение ссылается на значение в B6
  • «WorksheetFunction.Pmt(…)» WorksheetFunction — это функция, используемая для доступа ко всем функциям в Excel

На следующем изображении показан полный исходный код

  • Нажмите "Сохранить" и закройте окно кода.
  • Протестируйте свою программу, как показано на анимированном изображении ниже

Как использовать VBA в примере Excel

Следующие шаги объяснят, как использовать VBA в Excel.

Шаг 1. Откройте редактор VBA

На вкладке "Разработчик" в главном меню щелкните значок "Visual Basic", чтобы открыть редактор VBA.

Шаг 2) Выберите лист Excel и дважды щелкните лист

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

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

Шаг 3) Напишите все, что вы хотите отобразить в MsgBox

На этом этапе мы увидим нашу первую программу VBA. Для чтения и отображения нашей программы нам нужен объект. В VBA этот объект или носитель в MsgBox.

  • Сначала напишите «Sub», а затем «имя вашей программы» (Guru99)
  • Напишите все, что хотите отобразить в MsgBox (обучение guru99 — это весело)
  • Завершить программу, нажав End Sub

Шаг 4) Нажмите зеленую кнопку запуска в верхней части редактора

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

Шаг 5) Выберите лист и нажмите кнопку «Выполнить»

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

Шаг 6) Отобразите сообщение в MsgBox

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

Обзор

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

Хлоя Такер

Это руководство по написанию кода в электронных таблицах 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

Редактор VBA

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

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

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

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

В редакторе VBA выберите «Вставить» -> «Новый модуль».

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

Sub Auto_Open()
MsgBox ("Добро пожаловать в книгу XYZ")
End Sub

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

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

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

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

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

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

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

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

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

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

В редакторе VBA выберите «Вставить» -> «Новый модуль».

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

Sub UserReportQuery()
Тусклый пользовательский ввод как длинный
Тусклый ответ как целое число
UserInput = vbYesNo
Answer = MsgBox("Обработать отчет XYZ?", UserInput)
Если Ответ = vbYes, то ProcessReport
End Sub

Sub ProcessReport()
MsgBox ("Спасибо за обработку XYZ-отчета")
End Sub

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В редакторе VBA выберите «Вставить» -> «Новый модуль».

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

Sub LoopExample()
Размер X как целое
Для X = от 1 до 100
Range("A" & X).Value = 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.

Возможно, это самая старая программа, которая до сих пор широко используется. 34 года назад, всего через три года после того, как Apple представила свои первые компьютеры Mac, Microsoft выпустила первую версию своего знакомого приложения для работы с электронными таблицами Excel, изначально являвшуюся черновой копией VisiCalc Дэна Бриклина. Перенесемся в будущее. В 2017 году газета Irish Times отметила, что генеральный директор Microsoft Сатья Наделла назвал Excel самым важным потребительским продуктом Microsoft, отметив, что у него более 750 миллионов пользователей.

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

Исследователи Microsoft считают, что теперь они наконец превратили Excel в полноценный язык программирования благодаря внедрению новой функции под названием LAMBDA. «Благодаря LAMBDA Excel стал полным по Тьюрингу. В принципе, теперь вы можете писать любые вычисления на языке формул Excel», — говорится в блоге Microsoft.

«Полнота по Тьюрингу — это лакмусовая бумажка полноценного языка программирования», — говорится в новой статье в журнале Visual Studio. И добавляется, что «первоначальная реакция сообщества была обнадеживающей», отмечая, что исследователи Microsoft с энтузиазмом представляют себе, как опытные пользователи Excel создают функции, «которые кажутся неотъемлемой частью Excel их коллегам, которые просто звонят им».

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

Программирование в Excel

Пользователи Excel большую часть своей работы выполняют с помощью формул, когда ввод в ячейку начинается со знака равенства, за которым следует какое-то вычисление ("=A2 + B2"). Блог Microsoft называет формулы самым широко используемым языком программирования в мире, но он всегда был ограничен предопределенным набором параметров. Для развертывания собственных пользовательских функций пользователям приходилось использовать другой язык программирования Microsoft, основанный на макросах, Visual Basic for Applications. (Или, начиная с 2018 года, JavaScript — и, конечно же, надмножество JavaScript от Microsoft — TypeScript.)

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

Имена предоставляются в разделе «Диспетчер имен», спрятанном на панели инструментов «Формулы» Excel. Как и в случае с git, пользователи могут вводить комментарий при создании своей функции.

Как описал это старший научный сотрудник Microsoft Джек Уильямс на конференции POPL 2021: «В Excel теперь можно создавать реальные, полноценные возможности программирования… Теперь мы можем начать создавать вещи, которые выглядят как настоящие программы».

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

Значение, отображаемое в конечном итоге в этой ячейке, является выходом указанной функции, а аргумент этой функции – это… адрес другой ячейки.

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

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

А сообщение в блоге Excel обещает, что в будущем появится больше функций для работы с массивами. Блог Microsoft Research даже обещает, что компания работает над «эффективными реализациями комбинаторов обработки массивов, таких как MAP и REDUCE», которые будут использоваться на выходе именованных функций.

А руководитель программы Excel Крис Гросс намекнул, что надежды на будущее еще больше. «Я бы хотел, чтобы мы добавили столь необходимые инструменты для отладки и создания формул», — написал он в комментарии. «Похоже на то, что вы получаете с отличными IDE».

Мечта 2004 года

Для Саймона Пейтона Джонса это воплощение давней мечты. Выступая на POPL 2021, давний исследователь Microsoft и сторонник функционального программирования рассказал историю посещения команды Microsoft Excel еще в 2004 году. «Я узнал, что Excel похож на супертанкер. Он имеет очень высокую ценность. Имеет очень большую массу. И управляется на удивление небольшой и сильно перегруженной командой. Так что сменить курс непросто!»

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

В блоге Microsoft Research эта новая функция LAMBDA названа «качественным сдвигом, а не просто постепенным изменением». Именованные лямбда-функции предлагают программистам высококачественные языковые атрибуты "составляемости" и "повторного использования", соблюдая один из давних принципов хорошего кодирования, а именно отсутствие повторения работы.

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

Интересно, что концепция листовых функций впервые была описана в исследовательской статье 2003 года, в соавторстве с Джонсом. «Наш кейс необычен тем, что показывает, как знания о языке программирования можно применить к продукту, который обычно не считается языком программирования», — написал Джонс.

Выступая на POPL 2021, Адвайт Саркар, старший научный сотрудник Microsoft Research Cambridge, также предположил, что дополнительные ячейки будут использоваться для комментирования кода и добавления элементов форматирования. «Мы рассматриваем проектирование языков программирования как исследовательскую дисциплину, целью которой является создание более удобных человеко-компьютерных интерфейсов», — сказал однажды Саркар.

В прошлом году Саркар стал соавтором статьи, в которой описывается новая концепция Excel под названием Gridlets, в которой можно копировать и вставлять выбранные ячейки, что позволяет повторно использовать группу формул, в конечном итоге предлагая своего рода объектно-ориентированное ориентированный аналог функций, определяемых листом. Гридлеты можно создавать снова и снова, каждый со своими уникальными параметрами или «свойствами», в то время как изменения в исходном родительском гридлете будут распространяться на его дочерние элементы.

Это работа команды Calc Intelligence из Microsoft Research Cambridge, которая поставила перед собой цель улучшить Excel как язык программирования. Сейчас он доступен только участникам бета-канала программы «Office Insider». Хотя даже в рамках этой программы нетерпеливые пользователи жаловались, что они недостаточно быстро ее развертывают, вспоминает глава отдела продуктов Excel Брайан Джонс в своем выступлении на POPL 2021. Уильямс сказал, что эта функция вызвала большой интерес. «Даже в течение первых нескольких недель после выпуска LAMBDA на YouTube были видео с сотнями тысяч просмотров. Существует действительно большое пространство для изучения того, как электронные таблицы и функциональное программирование более высокого порядка могут сосуществовать и создавать уникальные возможности».

Запись в блоге Microsoft Excel заканчивается обещанием, что названные функции LAMBDA — это только начало, и добавлением «присоединяйтесь к нам в путешествии».

Читайте также: