Многие люди боятся использовать макросы в Excel, потому что считают их довольно сложными для понимания. Все потому, что они связаны с программированием. Но на практике все оказывается значительно проще, чем может показаться на первый взгляд. В программировании важно научиться строить алгоритмы. И если человек умеет кодить хотя бы на одном языке, ему значительно проще усвоить все остальные.
Мы приведем некоторые примеры макросов Excel, которые станут верными помощниками в выполнении наиболее частых задач.
Понятие макроса
Термин «Макрос» слышало множество людей. Нередко при запуске таблицы появляется предупреждение: “Этот документ использует макросы, способные навредить данному компьютеру, поэтому они отключены с целью защиты от вредоносных действий”.
Макрос – действенный способ автоматизировать самые частые действия, которые нужно выполнять в электронных таблицах. Макросы – это разновидность программирования. Разработка этих подпрограмм осуществляется с помощью языка VBA. Впрочем, некоторые виды макросов не требуют навыков программирования. Ведь существует еще такое понятие, как макрорекордер. Достаточно его включить и совершить некоторые действия, как далее они будут повторяться по нажатию одной кнопки.
Макросы могут быть реально опасными. Поскольку при их написании используется язык программирования, с его помощью можно создать настоящий вирус, который способен повредить информацию, а также собирать данные для злоумышленников (особенно опасно, если в таблице есть банковские данные, пароли и так далее).
Также макрос может запустить реальную троянскую программу на компьютере. Поэтому, чтобы не допустить вредоносных действий со стороны стороннего макроса, не стоит запускать макросы из сторонних источников, которым не доверяют.
Значительно проще объяснить, зачем нужны макросы, на реальном примере. Например, необходимо каждый день удалять из электронной таблицы несколько столбцов, а потом добавлять новые строки. Это невероятно утомительное занятие, отнимающее много времени. Если же воспользоваться макросами, есть реальная возможность значительно его сэкономить.
Макросы можно запускать по нажатию определенной комбинации клавиш. Например, если нажать Ctrl+J, можно запустить подпрограмму.
Интересный факт: известная программа бухгалтерского учета 1C изначально очень напоминала Excel, но потом ее функционал расширился до текущего.
Если же нужно давать компьютеру сложные инструкции, можно воспользоваться редактором Visual Basic, примеры кода в котором мы и рассмотрим немного позже.
Когда какой тип записи макросов использовать?
Если необходимо автоматизировать простейшие действия, достаточно использовать встроенный инструмент для записи макросов. То есть, если не приходится прописывать никаких условий, переменных и других подобных вещей. Просто обычная последовательность действий.
Если же необходимо программировать сложные действия, то тогда придется пользоваться встроенной средой VBA. Например, если необходимо записать в массив все элементы какого-то диапазона значений, определить его длительность, и при условии, что количество элементов массива не превышает определенного числа, выдавать какое-то сообщение. Здесь стандартного инструмента для записи макросов окажется недостаточно, необходимо изучать язык программирования и записывать команды в специальной среде. А интерпретатор в дальнейшем будет выполнять написанный код.
Пример использования макросов №1
Сперва этот пример кода использовался для демонстрации комментариев кода, написанного на VBA. Но поскольку он включает и иные возможности языка, он может применяться для демонстрации следующих функций:
- Объявление переменных.
- Указание ссылок на ячейки Excel.
- Применение цикла типа For.
- Применение условного оператора.
- Отображение оповещения.
‘ Подпрограмма для поиска ячеек с адресами A1-A100 текущего активного листа
‘ и поиска ячеек, в которых содержится требуемая строка
Sub Find_String(sFindText As String)
Dim i As Integer ‘ Целочисленная переменная, которая используется в цикле типа «For»
Dim iRowNumber As Integer ‘ Целочисленная переменная, предназначенная для сохранения результата
iRowNumber = 0
‘ Цикл через ячейки A1-A100 до тех пор, пока не будет найдена строка ‘sFindText’
For i = 1 To 100
If Cells(i, 1).Value = sFindText Then
‘ Совпадение обнаружено для заданной строки
‘ Сохранение текущего номера строки и выход из цикла
iRowNumber = i
Exit For
End If
Next i
‘ Всплывающее сообщение, информирующее пользователя о найденной строке и ее номере
If iRowNumber = 0 Then
MsgBox «String » & sFindText & » not found»
Else
MsgBox «String » & sFindText & » found in cell A» & iRowNumber
End If
End Sub
Пример 2
Эта процедура перечисляет все значения числовой последовательности Фибоначчи, вплоть до 1000. В этом примере приводятся следующие возможности макросов Excel:
- Объявление переменных.
- Цикл Do While.
- Ссылки на ячейки текущего листа Excel.
- Условный оператор.
‘ Подпрограмма для перечисления всех значений последовательности Fibonacci для всех значений ниже тысячи
Sub Fibonacci()
Dim i As Integer ‘ счетчик для позиции в серии значений
Dim iFib As Integer ‘ сохраняет текущее значение в серии
Dim iFib_Next As Integer ‘ сохраняет следующее значение в серии
Dim iStep As Integer ‘ хранит размер следующего шага
‘ Инициализация переменных variables i и iFib_Next
i = 1
iFib_Next = 0
‘ Цикл Do While, который исполняется до тех пор, пока номер
‘ числа в последовательности Фибоначчи меньше 1000.
Do While iFib_Next < 1000
If i = 1 Then
‘ Специальный случай для первой записи в серии
iStep = 1
iFib = 0
Else
‘ Сохраняется следующий размер шага, перед перезаписью
‘ текущей записи в серии
iStep = iFib
iFib = iFib_Next
End If
‘ Печать текущего значения последовательности Фибоначчи для столбца А
‘ текущего листа
Cells(i, 1).Value = iFib
‘ Вычисление следующего значения последовательности и увеличение
‘ маркера позиции на 1
iFib_Next = iFib + iStep
i = i + 1
Loop
End Sub
Пример 3
Следующий пример подпрограммы читает значения с ячейки в колонке A активного листа, пока не найдет пустую ячейку. Вся полученная информация сохраняется в массиве. Это простой пример макросов в электронных таблицах, который показывает:
- Как объявлять переменные.
- Работу динамического массива.
- Цикл Do Until.
- Ссылки на ячейки в текущем листе Excel.
- Встроенную функцию Ubound, которая предназначена для определения размера массива.
‘ Подпрограмма, которая хранит значения колонки А текущего листа
‘ в массиве
Sub GetCellValues()
Dim iRow As Integer ‘ сохраняется текущий номер строки
Dim dCellValues() As Double ‘ массив, в котором хранятся значения ячеек
iRow = 1
ReDim dCellValues(1 To 10)
‘ Цикл Do Until, который извлекает значение каждой ячейки в столбце А
‘ активного листа до тех пор, пока ячейка не окажется пустой
Do Until IsEmpty(Cells(iRow, 1))
‘ Проверка, достаточно ли большой массив dCellValues
‘ Если нет, используется ReDim, чтобы увеличить размер массива на 10 элементов.
If UBound(dCellValues) < iRow Then
ReDim Preserve dCellValues(1 To iRow + 9)
End If
‘ Сохраняется текущая ячейка в массиве CellValues
dCellValues(iRow) = Cells(iRow, 1).Value
iRow = iRow + 1
Loop
End Sub
Пример 4
Следующая процедура «Sub» читает содержимое ячеек из колонки А другого листа, имеющего название «Sheet2» и с этими значениями осуществляет арифметические операции. Результат вычислений пишется в колонке А текущего листа.
Этот пример показывает:
- Как объявлять переменные.
- Объекты Excel.
- Цикл Do Until.
- Доступ к листам электронных таблиц и диапазонам ячеек с текущей книги.
‘ Подпрограмма, запускающая цикл через значения в колонке А текущего листа
‘ «Sheet2», perform arithmetic operations on each value, and write the
‘ result into Column A of the current Active Worksheet («Sheet1»)
Sub Transfer_ColA()
Dim i As Integer
Dim Col As Range
Dim dVal As Double
‘ Установить переменную Col в колонку А листа 2
Set Col = Sheets(«Sheet2»).Columns(«A»)
i = 1
‘ Прохождение цикла через каждую ячейку колонки ‘Col’ до тех пор, пока
‘ не будет обнаружена пустая ячейка
Do Until IsEmpty(Col.Cells(i))
‘ Применение арифметических операций к значению текущей ячейки
dVal = Col.Cells(i).Value * 3 — 1
‘ Команда ниже копирует результат в колонку А
‘ текущего активного листа — без уточнения названия активного листа
Cells(i, 1) = dVal
i = i + 1
Loop
End Sub
Пример 5
Этот пример макроса приводит пример кода VBA, связанного с событием. Каждый раз, когда человек выделяет ячейку или диапазон значений, связанное с макросом событие активируется.
‘ Код для отображения диалогового окна ячейки B1 текущего листа в случае, если она выбрана
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘ Проверка, выбрана ли ячейка B1
If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then
‘ При выборе ячейки B1 показать диалоговое окно
MsgBox «You have selected cell B1»
End If
End Sub
Пример 6
Следующая подпрограмма демонстрирует, как обрабатывать ошибки с помощью операторов OnError и Resume. Также в этом коде описывается, как открывать и читать данные с файла.
‘ Подпрограмма, для для установки определенных значений
‘ в ячейках A1 и B1 документа «Data.xls» на диске C:\
Sub Set_Values(Val1 As Double, Val2 As Double)
Dim DataWorkbook As Workbook
On Error GoTo ErrorHandling
‘ Открытие документа с данными
Set DataWorkbook = Workbooks.Open(«C:\Documents and Settings\Data»)
‘ Выбрать переменные Val1 and Val2 с данных в книге Excel
Val1 = Sheets(«Sheet1»).Cells(1, 1)
Val2 = Sheets(«Sheet1»).Cells(1, 2)
DataWorkbook.Close
Exit Sub
ErrorHandling:
‘ Если файл не найден, предложить пользователю найти правильную директорию
‘ после чего продолжить выполнение подпрограммы
MsgBox «Data Workbook not found;» & _
«Please add the workbook to C:\Documents and Settings and click OK»
Resume
End Sub
После детального ознакомления с этими примерами будет значительно легче применять свои навыки на практике.
Рекомендации по использованию макросов
Есть несколько рекомендаций, позволяющих значительно увеличить эффективность использования макросов в электронных таблицах:
- Перед тем, как записывать макрос с помощью рекордера, следует заранее продумать все свои действия, поскольку автоматизироваться будут все действия (в том числе, и ошибочные).
- Не стоит торопиться, поскольку паузы при записи макросов не учитываются. Вполне можно начать продумывать по ходу какие-то действия. А все записанные операции будут обработаны в один момент.
- Обязательно необходимо научиться использовать режим отладки макроса. Если возникают какие-то ошибки, он поможет обнаружить, в чем причина неполадки. На первых порах без ошибок не обойтись, потому что в реальной программе будет все не так идеально, как может показаться на первый взгляд.
- Перед использованием макросов, сделанных другими людьми, нужно настроить антивирусную программу на их обнаружение. Как правило, эта опция установлена по умолчанию.
- Если загружаются документы из сомнительных источников, следует выбрать опцию «Отключить макросы» при их открытии. И не рекомендуется менять настройки, которые выставлены по умолчанию в настройках безопасности Excel.
Выводы
Таким образом, макросы – это эффективный инструмент автоматизации рабочих процессов в Excel. Он позволяет автоматизировать даже самые сложные последовательности действий. Если необходимо сделать простую программку, то достаточно воспользоваться встроенной функцией для записи макросов. Для более сложных необходимо освоить язык VBA, который является простым для изучения и очень гибким.
Если используются сторонние макросы, обязательно нужно побеспокоиться о безопасности их использования.
