Расширенное руководство по VBA для MS Excel

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

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

Расширенное руководство по VBA для Microsoft Excel

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

Ниже приведены наиболее распространенные варианты использования VBA в Excel.

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

С помощью этих трех примеров вы сможете написать собственный расширенный код VBA для Excel.

Импорт данных и выполнение расчетов

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

С VBA вы можете автоматизировать весь процесс. Например, если у вас есть новый CSV-файл, загружаемый в каталог на вашем компьютере каждый понедельник, вы можете настроить код VBA для запуска при первом открытии электронной таблицы во вторник утром.

Следующий код импорта будет запущен и импортирует файл CSV в вашу электронную таблицу Excel.

Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook. Листы ("Лист1") Ячейки. ClearContents strFile = «c: \ temp \ Purchases.csv» с ws. Таблицы запросов. Добавить (Соединение: = "ТЕКСТ;" & strFile, Назначение: = ws. Диапазон ("A1")) .TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh. Конец с

Откройте инструмент редактирования Excel VBA и выберите объект Sheet1. В раскрывающихся списках объекта и метода выберите Рабочий лист и Активировать. Это будет запускать код каждый раз, когда вы открываете электронную таблицу.

Это создаст Sub Worksheet_Activate () функция. Вставьте приведенный выше код в эту функцию.

Это устанавливает активный рабочий лист в Лист1, очищает лист, подключается к файлу, используя путь к файлу, который вы определили с strFile переменная, а затем С цикл перебирает каждую строку в файле и помещает данные в лист, начиная с ячейки A1.

Если вы запустите этот код, вы увидите, что данные файла CSV импортированы в вашу пустую электронную таблицу в Лист1.

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

Ваш код должен выполнять следующие действия:

1. Создайте новый столбец результатов под названием налоги.
2. Прокрутите проданных единиц столбец и рассчитайте налог с продаж.
3. Запишите результаты расчета в соответствующую строку листа.

Следующий код выполнит все эти шаги.

Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell as Range
Dim fltTax As Double
Установите StartCell = Range ("A1")
'Найти последнюю строку и столбец
LastRow = ws. Ячейки (ws. Рядов. Подсчитайте, StartCell. Столбец) .End (xlUp) .Row
Установите rng = ws. Диапазон (ws. Ячейки (2, 4), ws. Ячейки (LastRow, 4))
rowCounter = 2
Cells (1, 5) = "налоги"
Для каждой ячейки In rng
fltTax = ячейка. Значение * 0,05
Ячейки (rowCounter, 5) = fltTax
rowCounter = rowCounter + 1
Следующая ячейка

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

Вставьте приведенный выше код VBA под предыдущим кодом и запустите сценарий. Вы увидите результаты в столбце E.

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

Расчет результатов при нажатии кнопки

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

Кнопки управления полезны, если вы хотите контролировать, какие вычисления используются. Например, в том же случае, что и выше, что, если вы хотите использовать ставку налога 5% для одного региона и ставку налога 7% для другого?

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

Используя ту же таблицу, что и выше, выберите Разработчик вкладка и выберите Вставлять из Органы управления группа в ленте. Выберите нажать кнопку ActiveX Control из раскрывающегося меню.

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

Щелкните кнопку правой кнопкой мыши и выберите Характеристики. В окне «Свойства» измените заголовок на то, что вы хотите отображать для пользователя. В этом случае это может быть Рассчитайте налог 5%.

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

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

Dim ws как рабочий лист, strFile как строка
Установите ws = ActiveWorkbook. Листы ("Лист1")

Теперь повторите процесс еще раз, создав вторую кнопку. Сделайте подпись Рассчитайте налог 7%.

Дважды щелкните эту кнопку и вставьте тот же код, но сделайте множитель налога 0,07.

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

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

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

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

Отправить результаты расчета по электронной почте

Что, если вы хотите отправить результаты в таблице кому-нибудь по электронной почте?

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

Чтобы включить эту функцию, вам нужно выбрать Инструменты и справочные материалы. Прокрутите вниз до Microsoft CDO для библиотеки Windows 2000, включите и выберите хорошо.

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

Первый - это настройка переменных для хранения темы, адресов «Кому» и «От», а также тела письма.

Dim CDO_Mail как объект
Dim CDO_Config как объект
Dim SMTP_Config как вариант
Dim strSubject как строка
Dim strFrom As String
Dim strTo As String
Dim strCc как строка
Dim strBcc как строка
Dim strBody как строка
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell as Range
Dim fltTax As Double

Установите ws = ActiveWorkbook. Листы ("Лист1")
strSubject = "Налоги, уплаченные в этом квартале"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "Ниже приводится разбивка налогов, уплаченных с продаж в этом квартале."

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

Установите StartCell = Range ("A1") 'Найти последнюю строку и столбец. LastRow = ws. Ячейки (ws. Рядов. Подсчитайте, StartCell. Столбец) .End (xlUp) .Row. Установите rng = ws. Диапазон (ws. Ячейки (2, 4), ws. Ячейки (LastRow, 4)) rowCounter = 2. strBody = strBody & vbCrLf Для каждой ячейки в rng strBody = strBody & vbCrLf strBody = strBody & "Мы продали" & Cells (rowCounter, 3). Значение & "of" & Cells (rowCounter, 1) .Value _ & "for" & Cells (rowCounter, 4) .Value & "и уплаченные налоги" & Cells (rowCounter, 5) .Value & "." rowCounter = rowCounter + 1. Следующая ячейка

Следующий раздел включает в себя настройку параметров SMTP, чтобы вы могли отправлять электронную почту через свой SMTP-сервер. Если вы используете Gmail, это обычно ваш адрес электронной почты Gmail, пароль Gmail и SMTP-сервер Gmail (smtp.gmail.com).

Установите CDO_Mail = CreateObject ("CDO.Message") на ошибку Перейти к Error_Handling. Установите CDO_Config = CreateObject ("CDO.Configuration") CDO_Config. Нагрузка -1. Установите SMTP_Config = CDO_Config. Поля с SMTP_Config. .Элемент(" http://schemas.microsoft.com/cdo/configuration/sendusing") = 2. .Элемент(" http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" .Элемент(" http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1. .Элемент(" http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]" .Элемент(" http://schemas.microsoft.com/cdo/configuration/sendpassword") = "пароль" .Элемент(" http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465. .Элемент(" http://schemas.microsoft.com/cdo/configuration/smtpusessl") = Верно. .Обновлять. Завершить с помощью набора CDO_Mail .Configuration = CDO_Config. Конец с

Заменять [email protected] и пароль с данными вашей учетной записи.

Наконец, чтобы начать отправку электронной почты, вставьте следующий код.

CDO_Mail. Тема = strSubject
CDO_Mail. From = strFrom
CDO_Mail. To = strTo
CDO_Mail. TextBody = strBody
CDO_Mail. CC = strCc
CDO_Mail. BCC = strBcc
CDO_Mail. послать
Обработка ошибок:
Если Err. Описание <> "" Тогда MsgBox Err. Описание

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

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

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