Если вы только начинаете работать с VBA, вам стоит начать с изучения нашего Руководство по VBA для начинающих. Но если вы опытный эксперт по VBA и ищете более сложные возможности, которые можно реализовать с помощью VBA в Excel, продолжайте читать.
Возможность использовать кодирование VBA в Excel открывает целый мир автоматизации. Вы можете автоматизировать расчеты в Excel, кнопки и даже отправлять электронную почту. Существует больше возможностей для автоматизации вашей повседневной работы с VBA, чем вы можете себе представить.
Расширенное руководство по VBA для Microsoft Excel
Основная цель написания кода VBA в Excel состоит в том, чтобы вы могли извлекать информацию из электронной таблицы, выполнять с ней различные вычисления, а затем записывать результаты обратно в электронную таблицу
Ниже приведены наиболее распространенные варианты использования VBA в Excel.
С помощью этих трех примеров вы сможете написать собственный расширенный код Excel VBA.
Импорт данных и выполнение вычислений
Одна из наиболее распространенных целей, для которых люди используют Excel, — это выполнение вычислений над данными, существующими вне Excel. Если вы не используете VBA, это означает, что вам придется вручную импортировать данные, запускать вычисления и выводить эти значения в другой лист или отчет.
С помощью VBA вы можете автоматизировать весь процесс. Например, если каждый понедельник в каталог на вашем компьютере загружается новый файл CSV, вы можете настроить код VBA на запуск при первом открытии электронной таблицы во вторник утром.
Следующий код импорта запустит и импортирует CSV-файл в вашу электронную таблицу Excel.
Dim ws As Worksheet, strFile As StringSet ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = “c:\temp\purchases.csv” With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")) .TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Откройте инструмент редактирования Excel VBA и выберите объект Лист1. В раскрывающихся списках объектов и методов выберите Рабочий лист и Активировать . Код будет запускаться каждый раз, когда вы открываете таблицу.
Это создаст функцию Sub Worksheet_Activate() . Вставьте приведенный выше код в эту функцию.
При этом для активного рабочего листа устанавливается значение Лист1 , лист очищается, подключается к файлу, используя путь к файлу, который вы определили с помощью переменной strFile , а затем Цикл With проходит по каждой строке файла и помещает данные на лист, начиная с ячейки A1..
Если вы запустите этот код, вы увидите, что данные файла CSV импортированы в вашу пустую электронную таблицу, в Лист1 .
Импорт — это только первый шаг. Затем вы хотите создать новый заголовок для столбца, который будет содержать результаты ваших вычислений. В этом примере предположим, что вы хотите рассчитать 5 % налогов, уплачиваемых при продаже каждого товара.
Порядок действий, которые должен выполнять ваш код:
Следующий код выполнит все эти шаги.
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double
Set StartCell = Range("A1")
'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))
rowCounter = 2
Cells(1, 5) = "taxes"
For Each cell In rng
fltTax = cell.Value * 0.05
Cells(rowCounter, 5) = fltTax
rowCounter = rowCounter + 1
Next cell
Этот код находит последнюю строку в вашем листе данных, а затем устанавливает диапазон ячеек (столбец с ценами продажи) в соответствии с первой и последней строкой данных. Затем код проходит по каждой из этих ячеек, выполняет расчет налога и записывает результаты в новый столбец (столбец 5).
Вставьте приведенный выше код VBA под предыдущий код и запустите сценарий. Результаты появятся в столбце E.
Теперь каждый раз, когда вы открываете лист Excel, он автоматически выходит и получает самую свежую копию данных из файла CSV. Затем он выполнит вычисления и запишет результаты на лист. Вам больше не придется ничего делать вручную!
Рассчитать результаты по нажатию кнопки
Если вы предпочитаете иметь более прямой контроль над выполнением вычислений, а не выполнять их автоматически при открытии листа, вместо этого вы можете использовать кнопку управления.
Кнопки управления полезны, если вы хотите контролировать, какие вычисления используются. Например, в том же случае, что и выше, что если вы хотите использовать ставку налога в размере 5 % для одного региона и ставку налога в размере 7 % для другого?
Вы можете разрешить автоматический запуск того же кода импорта CSV, но оставить код расчета налогов запускаться при нажатии соответствующей кнопки.
Используя ту же таблицу, что и выше, выберите вкладку Разработчик и выберите Вставить в группе Элементы управления на ленте. В раскрывающемся меню выберите кнопку Элемент управления ActiveX..
Нарисуйте кнопку в любой части листа, подальше от данных.
Нажмите кнопку правой кнопкой мыши и выберите Свойства . В окне «Свойства» измените заголовок на то, что вы хотите отображать пользователю. В данном случае это может быть Рассчитать налог в размере 5% .
Вы увидите этот текст на самой кнопке. Закройте окно свойств и дважды щелкните саму кнопку. Откроется окно редактора кода, и ваш курсор окажется внутри функции, которая запускается, когда пользователь нажимает кнопку.
Вставьте в эту функцию код расчета налога из приведенного выше раздела, сохранив множитель налоговой ставки равным 0,05. Не забудьте включить следующие две строки для определения активного листа.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
Теперь повторите процесс еще раз, создав вторую кнопку. Напишите заголовок Рассчитать налог в размере 7% .
Дважды нажмите эту кнопку и вставьте тот же код, но установите налоговый мультипликатор 0,07.
Теперь в зависимости от того, какую кнопку вы нажмете, столбец налогов будет рассчитываться соответствующим образом.
Как только вы закончите, на вашем листе появятся обе кнопки. Каждый из них инициирует отдельный расчет налога и записывает разные результаты в столбец результатов.
Чтобы отправить это сообщение, выберите меню Разработчик и выберите Режим разработки в группе элементов управления на ленте, чтобы отключить Режим разработки . Это активирует кнопки.
Попробуйте нажать каждую кнопку, чтобы увидеть, как изменится столбец результатов «Налоги».
Отправить результаты вычислений по электронной почте
Что, если вы хотите отправить результаты таблицы кому-нибудь по электронной почте?
Вы можете создать еще одну кнопку под названием Отправить лист по электронной почте начальнику , используя ту же процедуру, что и выше. Код этой кнопки будет включать использование объекта CDO Excel для настройки параметров электронной почты SMTP и отправку результатов по электронной почте в формате, удобном для чтения..
Чтобы включить эту функцию, вам нужно выбрать Инструменты и ссылки . Прокрутите вниз до пункта Библиотека Microsoft CDO для Windows 2000 , включите его и нажмите ОК .
В коде, который необходимо создать для отправки электронного письма и встраивания результатов электронной таблицы, необходимо создать три основных раздела.
Первый – это настройка переменных для хранения темы, адресов получателя и отправителя, а также тела электронного письма.
Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double
Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."
Конечно, тело должно быть динамическим в зависимости от того, какие результаты находятся на листе, поэтому здесь вам нужно будет добавить цикл, который проходит через диапазон, извлекает данные и записывает построчно в тело.
Set StartCell = Range("A1")
'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))
rowCounter = 2
strBody = strBody & vbCrLf
For Each cell In rng
strBody = strBody & vbCrLf
strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _
& " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "."
rowCounter = rowCounter + 1
Next cell
Следующий раздел посвящен настройке параметров SMTP, чтобы вы могли отправлять электронную почту через SMTP-сервер. Если вы используете Gmail, это обычно ваш адрес электронной почты Gmail, пароль Gmail и SMTP-сервер Gmail (smtp.gmail.com).
Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Update
End With
With CDO_Mail
Set .Configuration = CDO_Config
End With
Замените электронная почта@website.com и пароль на данные своей учетной записи.
Наконец, чтобы инициировать отправку электронного письма, вставьте следующий код.
CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send
Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description
Примечание . Если при попытке запуска этого кода вы видите ошибку транспорта, скорее всего, это связано с тем, что ваша учетная запись Google блокирует запуск «менее безопасных приложений». Вам нужно будет посетить страница настроек менее безопасных приложений и включить эту функцию.
После этого ваше электронное письмо будет отправлено. Вот как это выглядит для человека, который получает электронное письмо с автоматически сгенерированными результатами.
Как видите, с помощью Excel VBA можно автоматизировать многое. Попробуйте поэкспериментировать с фрагментами кода, о которых вы узнали из этой статьи, и создайте свои собственные уникальные средства автоматизации VBA.
.