Если вы только начинаете работать с 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. В раскрывающихся меню «Объект» и «Метод» выберите «Рабочий лист» и «Активировать» . Это позволит запускать код каждый раз при открытии электронной таблицы.
Это создаст подфункцию Worksheet_Activate() . Вставьте приведенный выше код в эту функцию.
Эта команда устанавливает активный рабочий лист на Sheet1 , сканирует лист, подключается к файлу, используя путь к файлу, указанный в переменной strFile , а затем цикл With проходит по каждой строке файла и помещает данные на лист, начиная с ячейки 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.
Теперь, в зависимости от того, какую кнопку вы нажмете, столбец с налогом будет рассчитан соответствующим образом.
После завершения у вас на листе появятся обе кнопки. Каждая запустит свой расчет налогов и отобразит разные результаты в столбце «Результаты».
Чтобы проверить это, выберите меню «Разработчик» , а затем выберите «Режим проектирования» из группы элементов управления на ленте, чтобы отключить режим проектирования . Это активирует кнопки.
Попробуйте нажать каждую кнопку, чтобы увидеть, как изменится столбец результатов «Налоги».
Отправить результаты проверки учетной записи кому-либо по электронной почте
А что, если вы захотите отправить результаты из электронной таблицы кому-нибудь по электронной почте?
Вы можете создать еще одну кнопку под названием « Отправить лист в электронную почту начальнику», используя описанную выше процедуру. Код для этой кнопки будет использовать объект Excel CDO для настройки параметров 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 = "Налоги, уплаченные в этом квартале" 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
Замените email@website.com и password на данные своей учетной записи.
Наконец, чтобы начать отправку электронного письма, введите следующий код.
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.
Комментарии закрыты.