Если вы только начинаете использовать 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.Sheets("Sheet1") Cells.ClearContents strFile = "c:temppurchases.csv" With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")).TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Откройте редактор VBA в Excel и выберите объект Sheet1. В раскрывающихся списках «Объект» и «Метод» выберите Рабочий лист и активироватьЭто приведет к тому, что код будет запускаться каждый раз при открытии электронной таблицы.
Это создаст функцию. Sub Worksheet_Activate()Вставьте приведенный выше код в эту функцию.
Это устанавливает активный рабочий лист в... Sheet1Отсканируйте лист и подключитесь к файлу, используя путь к файлу, указанный в переменной. стрФайлЗатем цикл Благодаря более чем Пройдясь по каждой строке файла и разместив данные на листе, начиная с ячейки A1.
Если вы запустите этот код, вы увидите, что данные из CSV-файла импортируются в вашу пустую электронную таблицу. Sheet1.
Импорт — это только первый шаг. Далее вам нужно будет создать новый заголовок столбца, который будет содержать результаты ваших вычислений. В этом примере предположим, что вы хотите рассчитать 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") 'Найти последнюю строку и столбец 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Включите его и выберите OK.
Для отправки электронного письма и включения результатов из электронной таблицы необходимо создать три основных раздела кода.
Первый шаг — это настройка переменных для сохранения темы письма, адресов «кому» и «от кого», а также текста электронного письма.
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 = "Налоги, уплаченные в этом квартале" strFrom = "MyEmail@gmail.com" strTo = "BossEmail@gmail.com" strCc = "" strBcc = "" strBody = "Ниже приведена разбивка налогов, уплаченных с продаж в этом квартале."
Конечно, текст должен быть динамическим и зависеть от результатов, представленных в статье, поэтому здесь вам потребуется добавить цикл, который будет проходить по диапазону значений, извлекать данные и каждый раз выводить строку в текст.
Set StartCell = Range("A1"). vbCrLf For Each cell In rng strBody = strBody & vbCrLf strBody = strBody & "Мы продали " & Cells(rowCounter, 3).Value & " из " & Cells(rowCounter, 1).Value _ & " за " & Cells(rowCounter, 4).Value & " и заплатили налоги в размере " & 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@website.com" .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 Set .Configuration = CDO_Config End With
заменять электронная почта@веб-сайт.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 блокирует запуск «менее безопасных приложений». Вам нужно будет перейти по ссылке [link/reference]. Страница настроек менее безопасных приложений Включите эту опцию.
После включения этой функции ваше электронное письмо будет отправлено. Вот как это выглядит для человека, получающего автоматически сгенерированное письмо.
Как видите, с помощью Excel VBA можно автоматизировать очень многое. Попробуйте поэкспериментировать с фрагментами кода, изученными в этой статье, и создайте свои собственные уникальные сценарии автоматизации на VBA.
Комментарии закрыты.