Экономьте часы, автоматизируя свою работу!

Вы когда-нибудь использовали Microsoft Excel для выполнения утомительной и трудоемкой задачи, только чтобы почувствовать, что ваше время можно было бы лучше потратить на более целенаправленную работу? Ты не одинок. Нет ничего ужаснее, чем тратить свое драгоценное время и энергию на однообразную ручную работу над таблицами (#FirstWorldProblems). К счастью, есть решение, позволяющее избавиться от ручных задач и страха, связанного с этим, - это решение VBA.

VBA (Visual Basics for Applications) - это язык программирования, используемый в приложениях Microsoft Office. Возможности VBA огромны. С помощью VBA вы можете программировать макросы для автоматизации таких задач, как очистка и форматирование данных или отправка электронных писем. Макрос (также называемый подпроцедурой) - это группа кода, которая выполняет серию задач или команд в Excel. Прочитав эту статью, вы будете обладать знаниями, необходимыми для использования VBA, чтобы он мог сделать за вас тяжелую работу.

Ниже приведены 10 вещей, которые вам нужно знать о VBA, чтобы начать автоматизировать свою работу!

1. Настройте

Прежде чем вы сможете начать кодирование в Excel, вы должны отобразить новую вкладку в верхней части экрана: Разработчик. Открыть вкладку «Разработчик» очень просто (и вам нужно сделать это только один раз). Просто выполните следующие действия:

Файл - ›Параметры -› Настроить ленту - ›Поставьте галочку рядом с« Разработчик »

Теперь мы можем открыть редактор VBA, чтобы писать наши программы. На вкладке «Разработчик» нажмите «Visual Basic», чтобы открыть редактор. Вы также можете открыть редактор с помощью сочетания клавиш Alt + F11.

В редакторе слева вы увидите файловый менеджер. Дважды щелкните «ThisWorkbook» под текущим каталогом файлов. Теперь вы можете использовать редактор для ввода и выполнения кода VBA в своей книге!

2. Привет, мир

Давайте пройдемся по быстрой программе hello world, чтобы научиться основам. Прежде чем мы начнем, вам необходимо сохранить файл Excel как книгу с поддержкой макросов (с расширением .xlsm). Ваш макрос будет запущен только в том случае, если файл сохранен с расширением .xlsm.

Файл- ›Сохранить как« helloworld.xlsm »

Ниже приведено видео, показывающее, как создать свой первый макрос hello world. Вот три способа запустить макрос после завершения программы:

  1. Нажмите F5 на клавиатуре в редакторе
  2. Нажмите макросы и "запустите" свою программу на вкладке "Разработчик"
  3. Вставьте кнопку на вкладке "Разработчик"

Макрос Helloworld:

Sub helloworld()
    MsgBox "Hello World!" 'Outputs Hello World to screen
End Sub

Каждый макрос VBA начинается с «Sub» и заканчивается «End Sub». Sub Обозначает дополнительную процедуру. Думайте о подпроцедурах как о последовательности команд, которые вы хотите, чтобы ваш компьютер выполнял. В этой программе функция MsgBox отображает сообщение «Hello World!». В этом случае имя макроса указывается после оператора Sub с помощью «helloworld ()». Вы можете оставлять комментарии в своей программе, ставя перед текстом апостроф.

3. Рекордер

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

  1. Вводит ваше имя в ячейку
  2. Вводит текущую дату и время в ячейку ниже с помощью функции «= Сейчас ()»
  3. Форматирует обе ячейки полужирным шрифтом и размером шрифта 22 пункта
  4. Изменяет обе ячейки для отображения выравнивания по центру

Макрос, который вы собираетесь записать, может выполнить все эти действия одним действием. Чтобы начать запись макроса, выполните следующие действия:

  1. Выберите любую ячейку
  2. Выберите вкладку «Разработчик» - ›Дополнительная вкладка« Код »-› Запись макроса
  3. В диалоговом окне «Запись макроса» введите «имя макроса» и нажмите «ОК».

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

Посмотрите 30-секундный ролик ниже, чтобы узнать, как записать этот макрос:

Записав макрос, вы можете просмотреть сгенерированный код: Выберите вкладку «Разработчик» - ›вложенная вкладка« Код »-› Макросы - ›Выберите имя макроса -› Шаг с заходом

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

Вот код, сгенерированный для изменения размера шрифта до пункта 22:

With Selection.Font
    .Name = "Calibri"
    .Size = 22
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontMinor
End With

А вот как выглядит весь макрос после удаления несвязанного кода:

Sub recorder_example()
    Range("A1").Select
    ActiveCell.Formula = "John Doe"
    Range("A2").Select
    ActiveCell.Formula = "=NOW()"
    Range("A1:A2").Select
    Selection.Font.Bold = True
    Selection.Font.Size = 22
    Selection.HorizontalAlignment = xlCenter
End Sub

4. Создание переменных

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

Чтобы заставить себя объявить все используемые вами переменные, добавьте следующий оператор в верхнюю часть модуля VBA (вне всех подпрограмм):

Option Explicit

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

Теперь, когда вы знаете о преимуществах объявления переменных, давайте применим это на практике. Самый распространенный способ объявления переменных - использовать оператор Dim. Вот несколько примеров того, как вы объявляете переменные:

Dim YourName as String
Dim x as Integer
Dim NetRevenue as Double
Dim RowNumber as Long
Dim Today as Date
Dim SampleValue

Dim - это старый программный термин, обозначающий измерение, что означает, что вы назначаете ячейку памяти для определенной переменной.

Вы также можете объявить несколько переменных с помощью одного оператора Dim:

Dim x as Integer, y as Integer, z as Integer

После объявления переменных вы можете присвоить этим переменным значения:

Sub Variable_Example()
    x = 6
    y = 3
    z = x + 2 * y
    MsgBox z 'Outputs 12
End Sub

5. Выбор данных

Для ссылки на данные в электронной таблице Excel вы можете использовать объект Range, который представляет диапазон данных. Объект Range может быть крошечным, как одна ячейка, или иметь размер 10 293 289 228 ячеек на листе. После определения диапазона вы можете использовать метод Select для выбора диапазона ячеек.

Ссылка на отдельную ячейку:

Range("Z4")

Ссылка на диапазон, состоящий из нескольких ячеек:

Range("A1:D5")

Ссылка на диапазон за пределами активного листа:

Worksheets("Sheet4").Range("A1:D5")

Обратитесь к диапазону в другой книге:

Workbooks("Sales.xlsx").Worksheets("Sheet1").Range("A1:D5")

Вы также можете ссылаться на диапазон с помощью объекта Cells, который принимает два аргумента (номер строки и номер столбца). Следующий пример относится к ячейке D3 на листе Sheet3:

Worksheets("Sheet3").Cells(3,4)

Используйте свойство Cells для ссылки на диапазон ячеек от A1 до F20:

Range(Cells(1,1),Cells(20,6))

Теперь, когда вы знаете объект Range, вот пример того, как можно использовать метод Select объекта Range для копирования и вставки данных:

Sub CopyRange()
    Range("A1:B10").Select
    Selection.Copy
    Range("Z1").PasteSpecial xlValues
End Sub

Другие популярные методы, помимо Select, включают Clear и Delete. Очистить удаляет содержимое в заданном диапазоне, в то время как Удалить перемещает ячейки, чтобы заменить удаленные ячейки. Бывший. Range("A1:B10").Delete or Range("A1:B10").Clear

6. Важные свойства

Вот пять полезных свойств Excel, которые вам следует знать:

1. Значение - представляет значение, содержащееся в ячейке. Вы можете выполнять операции чтения-записи:

MsgBox Range("A1").Value

Range("A1").Value = 150

2. Текст - возвращает строку, представляющую текст, отображаемый в ячейке:

MsgBox Range("A1").Text

3. Счетчик - возвращает количество ячеек в диапазоне:

MsgBox Range("A1:B10").Count

4. Формула - представляет формулу в ячейке:

Range("A20").Formula = "=Sum(A1:A19)"

5. Строка и столбец - возвращает номер строки или столбца диапазона, состоящего из одной ячейки:

MsgBox Range("R5").Row

MsgBox Range("R5").Column

7. Если-то

Используйте оператор If-Then, если вы хотите выполнить одну или несколько команд условно. Если вы используете необязательное предложение Else, вы можете выполнить одно или несколько операторов, если условие, которое вы проверяете, не выполняется.

Если-То Пример:

Sub Reminder()
    If Time > .5 Then 
        MsgBox "Don't Forget!"
    End IF
End Sub

Пример "если-то-еще":

Sub Reminder()
    If Time > .5 Then 
        MsgBox "Don't Forget!"
    Else
        MsgBox "You still have time!"
    End IF
End Sub

В этих примерах If-Then мы используем функцию VBA Time для получения системного времени. Если текущее время больше 0,5 (полдень), подпрограмма отобразит напоминание. В противном случае, если есть инструкция Else, будет отображаться другое сообщение.

8. Петли

Наиболее популярные типы циклов в VBA:

Цикл For-Next

Самый простой вид цикла - цикл For-Next. В цикле For-Next цикл контролируется счетчиком, который увеличивается с помощью оператора Next. Операторы между операторами For и Next повторяются в цикле. Вот пример подпрограммы, использующей цикл For-Next для вычисления суммы первых 100 положительных чисел:

Sub GetSum()
    Dim sum as Double
    Dim count As Long
    Sum = 0
    For count = 1 to 100
        sum = sum + count
    Next count
    MsgBox sum
End Sub

Цикл Do-While

Цикл Do-While будет выполнять цикл, пока выполняется условие в начале цикла. Следующая подпрограмма перебирает строки первого столбца, пока текущее значение ячейки не пусто. Он считает, сколько значений больше 1000 в первом столбце. Цикл продолжается до тех пор, пока не встретит пустую ячейку.

Sub DoWhileExample()
    i = 1
    count = 0
    Do While Cells(i, 1).Value <> ""
        If Cells(i,1).Value > 1000 Then
            count = count + 1
        End If
        i = i +1
    Loop
    MsgBox count
End Sub

Для каждого следующего цикла

Цикл For Each-Next используется для перебора коллекции объектов (в данном случае листов). Приведенный ниже макрос устанавливает значение ячейки A1 равным 1 на каждом листе.

Sub HideSheets()
   Dim sht As Worksheet
   For Each sht In ActiveWorkbook.Worksheets
       sht.Cells(1, 1).Value = 1
   Next sht
End Sub

9. Отправка электронных писем

С VBA у вас есть возможность автоматизировать создание и доставку электронных писем. Приведенный ниже код создает электронное письмо с помощью Microsoft Outlook. Перед тем, как вы увидите код, кратко рассмотрим, что делает макрос:

Код Send_newemail:

Sub Send_newemail()
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
            
    With OutMail
        .to = Range("A1").Value
        .CC = ""
        .Subject = "Meeting Reminder"
        .body = "Hi," & vbNewLine & vbNewLine _
         & "This is a friendly reminder that the meeting is today."
        '.Attachments.Add ("") 'You can add files here
        .display
        '.Send
   End With
            
   Set OutMail = Nothing
   Set OutApp = Nothing
    
End Sub

В этом макросе вы можете заполнить содержимое свойств электронной почты (.to, .CC, .Subject, .body и т. Д.) С помощью объекта OutMail. Обратите внимание, что свойство «Отправить» закомментировано, поэтому электронное письмо не отправляется, а создается только черновик. Если вы хотите, чтобы письмо было отправлено, раскомментируйте свойство «Отправить».

10. Повышение производительности

Вот три способа повысить скорость ваших программ VBA:

Обновление экрана

Одним из существенных препятствий для производительности программ VBA является обновление экрана. Когда VBA обновляет данные на листе, обновляется изображение экрана. Чтобы повысить производительность, вы можете отключить обновление экрана, установив для свойства приложения Excel значение False. В конце макроса вы можете снова включить обновление экрана, установив для него значение True. См. Пример ниже:

Sub ScreenUpdating_Example()
    ‘Disable Screen Update
    Application.ScreenUpdating = False
    'Do Something
    Range("A1").Copy
    Range("B4").Paste
    'Enable Screen Update
    Application.ScreenUpdating = True
End Sub

Расчет

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

Sub Calculation_Example()
    ‘Disable Automatic Calculation
    Application.Calculation = xlCalculationManual
    'Do Something
    Range("A1").Copy
    Range("B4").Paste
    'Enable Automatic Calculation
    Application.Calculation = xlCalculationAutomatic
End Sub

Обратите внимание, что если вы отключите автоматические вычисления и ваш макрос полагается на значения обновляемых ячеек, вы можете вручную обновить ячейки с помощью метода Calculate. Бывший. ActiveWorksheet.Calculate

Массивы

Вы можете столкнуться с ситуацией, когда вам нужно изменить данные в более чем 100 000 строк! Если вы решите использовать цикл For-Next для прохождения каждой ячейки в диапазоне, это будет медленным, потому что вы постоянно обращаетесь к электронной таблице Excel (независимо от того, читаете ли вы / записываете значения ячеек или форматируете ячейки). Вместо того, чтобы перебирать строки, вы можете оптимизировать производительность, выгружая значения диапазона в массив, а затем просматривая массив в цикле для изменения данных.

В приведенном ниже примере цикл For-Loop используется для перебора каждой ячейки в диапазоне от A2 до A1000, что не является оптимальным решением. В диапазоне от A2 до A1000 мы меняем все экземпляры значения «ожидает» на «завершено». Это решение заняло 0,5 секунды на моем компьютере:

Sub forloop_example()
    For i = 2 To 1000:
        If Cells(i, 1).Value = "pending" Then
            Cells(i, 1).Value = "complete"
        End If
    Next
       
End Sub

В более оптимальном решении, приведенном ниже, мы выгружаем значения диапазона от A2 до A1000 в массив под названием «myarray» и перебираем этот массив, чтобы изменить все экземпляры слова «ожидающий» на «завершено». Затем мы устанавливаем диапазон значений равным массиву. Это решение заняло всего 0,0078 секунды на моем компьютере:

Sub array_example()
    Dim myarray As Variant
    myarray = Range("A2:A1000").Value
    
    For i = 1 To UBound(myarray, 1)
      myarray(i, 1) = Replace(myarray(i, 1), "pending", "complete")
    Next i
    
    Range("A2:A1000").Value = myarray
   
End Sub

В приведенном выше коде myarray задан как вариант, поэтому массив может хранить различные типы данных, такие как строки и целые числа. Мы используем функцию UBound для определения длины массива, поэтому мы знаем, сколько раз выполнить цикл. В цикле мы используем функцию Replace, чтобы изменить значение элемента массива на «завершено». В этом примере мы сэкономили только долю секунды при изменении 1000 ячеек. Однако вы сэкономите значительно больше времени, используя массивы, если начнете изменять данные в более чем 100 000 ячеек.

Поздравляю!

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