Как создавать макросы в excel 2007 для чайников

Работа с макросами Excel для чайников

Многие люди боятся использовать макросы в Excel, потому что считают их довольно сложными для понимания. Все потому, что они связаны с программированием. Но на практике все оказывается значительно проще, чем может показаться на первый взгляд. В программировании важно научиться строить алгоритмы. И если человек умеет кодить хотя бы на одном языке, ему значительно проще усвоить все остальные.

Мы приведем некоторые примеры макросов Excel, которые станут верными помощниками в выполнении наиболее частых задач.

Понятие макроса

Термин «Макрос» слышало множество людей. Нередко при запуске таблицы появляется предупреждение: “Этот документ использует макросы, способные навредить данному компьютеру, поэтому они отключены с целью защиты от вредоносных действий”.

Макрос – действенный способ автоматизировать самые частые действия, которые нужно выполнять в электронных таблицах. Макросы – это разновидность программирования. Разработка этих подпрограмм осуществляется с помощью языка VBA. Впрочем, некоторые виды макросов не требуют навыков программирования. Ведь существует еще такое понятие, как макрорекордер. Достаточно его включить и совершить некоторые действия, как далее они будут повторяться по нажатию одной кнопки.

Макросы могут быть реально опасными. Поскольку при их написании используется язык программирования, с его помощью можно создать настоящий вирус, который способен повредить информацию, а также собирать данные для злоумышленников (особенно опасно, если в таблице есть банковские данные, пароли и так далее).

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

Значительно проще объяснить, зачем нужны макросы, на реальном примере. Например, необходимо каждый день удалять из электронной таблицы несколько столбцов, а потом добавлять новые строки. Это невероятно утомительное занятие, отнимающее много времени. Если же воспользоваться макросами, есть реальная возможность значительно его сэкономить.

Макросы можно запускать по нажатию определенной комбинации клавиш. Например, если нажать Ctrl+J, можно запустить подпрограмму.

Интересный факт: известная программа бухгалтерского учета 1C изначально очень напоминала Excel, но потом ее функционал расширился до текущего.

Если же нужно давать компьютеру сложные инструкции, можно воспользоваться редактором Visual Basic, примеры кода в котором мы и рассмотрим немного позже.

Когда какой тип записи макросов использовать?

Если необходимо автоматизировать простейшие действия, достаточно использовать встроенный инструмент для записи макросов. То есть, если не приходится прописывать никаких условий, переменных и других подобных вещей. Просто обычная последовательность действий.

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

Пример использования макросов №1

Сперва этот пример кода использовался для демонстрации комментариев кода, написанного на VBA. Но поскольку он включает и иные возможности языка, он может применяться для демонстрации следующих функций:

  1. Объявление переменных.
  2. Указание ссылок на ячейки Excel.
  3. Применение цикла типа For.
  4. Применение условного оператора.
  5. Отображение оповещения.

‘ Подпрограмма для поиска ячеек с адресами 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

‘ Всплывающее сообщение, информирующее пользователя о найденной строке и ее номере

If iRowNumber = 0 Then

MsgBox “String ” & sFindText & ” not found”

MsgBox “String ” & sFindText & ” found in cell A” & iRowNumber

Пример 2

Эта процедура перечисляет все значения числовой последовательности Фибоначчи, вплоть до 1000. В этом примере приводятся следующие возможности макросов Excel:

  1. Объявление переменных.
  2. Цикл Do While.
  3. Ссылки на ячейки текущего листа Excel.
  4. Условный оператор.

‘ Подпрограмма для перечисления всех значений последовательности Fibonacci для всех значений ниже тысячи

Sub Fibonacci()

Dim i As Integer ‘ счетчик для позиции в серии значений

Dim iFib As Integer ‘ сохраняет текущее значение в серии

Dim iFib_Next As Integer ‘ сохраняет следующее значение в серии

Dim iStep As Integer ‘ хранит размер следующего шага

‘ Инициализация переменных variables i и iFib_Next

iFib_Next = 0

‘ Цикл Do While, который исполняется до тех пор, пока номер

‘ числа в последовательности Фибоначчи меньше 1000.

Do While iFib_Next Следующий пример подпрограммы читает значения с ячейки в колонке A активного листа, пока не найдет пустую ячейку. Вся полученная информация сохраняется в массиве. Это простой пример макросов в электронных таблицах, который показывает:

  1. Как объявлять переменные.
  2. Работу динамического массива.
  3. Цикл Do Until.
  4. Ссылки на ячейки в текущем листе Excel.
  5. Встроенную функцию Ubound, которая предназначена для определения размера массива.

‘ Подпрограмма, которая хранит значения колонки А текущего листа

Sub GetCellValues()

Dim iRow As Integer ‘ сохраняется текущий номер строки

Dim dCellValues() As Double ‘ массив, в котором хранятся значения ячеек

ReDim dCellValues(1 To 10)

‘ Цикл Do Until, который извлекает значение каждой ячейки в столбце А

‘ активного листа до тех пор, пока ячейка не окажется пустой

Do Until IsEmpty(Cells(iRow, 1))

‘ Проверка, достаточно ли большой массив dCellValues

‘ Если нет, используется ReDim, чтобы увеличить размер массива на 10 элементов.

If UBound(dCellValues) Следующая процедура «Sub» читает содержимое ячеек из колонки А другого листа, имеющего название «Sheet2» и с этими значениями осуществляет арифметические операции. Результат вычислений пишется в колонке А текущего листа.

Этот пример показывает:

  1. Как объявлять переменные.
  2. Объекты Excel.
  3. Цикл Do Until.
  4. Доступ к листам электронных таблиц и диапазонам ячеек с текущей книги.

‘ Подпрограмма, запускающая цикл через значения в колонке А текущего листа

‘ “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”)

‘ Прохождение цикла через каждую ячейку колонки ‘Col’ до тех пор, пока

‘ не будет обнаружена пустая ячейка

Do Until IsEmpty(Col.Cells(i))

‘ Применение арифметических операций к значению текущей ячейки

dVal = Col.Cells(i).Value * 3 – 1

‘ Команда ниже копирует результат в колонку А

‘ текущего активного листа – без уточнения названия активного листа

Cells(i, 1) = dVal

Пример 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”

Пример 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

ErrorHandling:

‘ Если файл не найден, предложить пользователю найти правильную директорию

‘ после чего продолжить выполнение подпрограммы

MsgBox “Data Workbook not found;” & _

“Please add the workbook to C:\Documents and Settings and click OK”

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

Рекомендации по использованию макросов

Есть несколько рекомендаций, позволяющих значительно увеличить эффективность использования макросов в электронных таблицах:

  1. Перед тем, как записывать макрос с помощью рекордера, следует заранее продумать все свои действия, поскольку автоматизироваться будут все действия (в том числе, и ошибочные).
  2. Не стоит торопиться, поскольку паузы при записи макросов не учитываются. Вполне можно начать продумывать по ходу какие-то действия. А все записанные операции будут обработаны в один момент.
  3. Обязательно необходимо научиться использовать режим отладки макроса. Если возникают какие-то ошибки, он поможет обнаружить, в чем причина неполадки. На первых порах без ошибок не обойтись, потому что в реальной программе будет все не так идеально, как может показаться на первый взгляд.
  4. Перед использованием макросов, сделанных другими людьми, нужно настроить антивирусную программу на их обнаружение. Как правило, эта опция установлена по умолчанию.
  5. Если загружаются документы из сомнительных источников, следует выбрать опцию «Отключить макросы» при их открытии. И не рекомендуется менять настройки, которые выставлены по умолчанию в настройках безопасности Excel.

Выводы

Таким образом, макросы – это эффективный инструмент автоматизации рабочих процессов в Excel. Он позволяет автоматизировать даже самые сложные последовательности действий. Если необходимо сделать простую программку, то достаточно воспользоваться встроенной функцией для записи макросов. Для более сложных необходимо освоить язык VBA, который является простым для изучения и очень гибким.

Если используются сторонние макросы, обязательно нужно побеспокоиться о безопасности их использования.

Как записать макрос в Excel? Пошаговая инструкция.

Что такое макрос?

Для начала немного о терминологии.

Макрос — это код, написанный на встроенном в Excel языке VBA (Visual Basic for Application). Макросы могут создаваться как вручную, так и записываться автоматически с помощью так называемого макрорекодера.

Макрорекодер — это инструмент в Excel, который пошагово записывает все что вы выполняете в Excel и преобразует это в код на языке VBA. Макрорекодер создает очень подробный код (как мы увидим позже), который вы сможете при необходимости отредактировать в дальнейшем.

Записанный макрос можно будет запускать неограниченное количество раз и Excel повторит все записанные шаги. Это означает, что даже если вы ничего не знаете о VBA, вы можете автоматизировать некоторые задачи, просто записав свои шаги и затем повторно использовать их позже.

Теперь давайте погрузимся и посмотрим, как записать макрос в Excel.

Отображение вкладки "Разработчик" в ленте меню

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

  1. Щелкните правой кнопкой мыши по любой из существующих вкладок на ленте и нажмите «Настроить ленту». Он откроет диалоговое окно «Параметры Excel».Настроить ленту в Excel
  2. В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». Справа на панели «Основные вкладки» установите флажок «Разработчик».Записать макрос в Excel - Включить вкладку «Разработчик»
  3. Нажмите «ОК».

В результате на ленте меню появится вкладка "Разработчик"

Запись макроса в Excel

Теперь давайте запишем очень простой макрос, который выбирает ячейку и вводит в нее текст, например "Excel".

Вот шаги для записи такого макроса:

  1. Перейдите на вкладку "Разработчик".
  2. В группе "Код" нажмите кнопку "Запись макроса". Откроется одноименное диалоговое окно.
  3. В диалоговом окне "Запись макроса" введите имя для своего макроса, например "ВводТекста". Есть несколько условий именования, которые необходимо соблюдать при назначении макроса. Например, вы не можете использовать пробелы между ними. Обычно я предпочитаю сохранять имена макросов как одно слово, с разными частями с заглавным первым алфавитом. Вы также можете использовать подчеркивание для разделения двух слов — например, "Ввод_текста".
    Имя макроса в диалоговом окне
  4. Если вы хотите, то можете задать сочетание клавиш. В этом случае мы будем использовать ярлык Ctrl + Shift + N. Помните, что сочетание, которое вы указываете, будет отменять любые существующие горячие клавиши в вашей книге. Например, если вы назначили сочетание Ctrl + S, вы не сможете использовать это для сохранения рабочей книги (вместо этого, каждый раз, когда вы его используете, он выполняет макрос).
    Запись макроса в Excel - назначение ярлыка в диалоговом окне
  5. В поле "Сохранить в" убедитесь, что выбрана опция "Эта книга". Этот шаг гарантирует, что макрос является частью рабочей книги. Он будет там, когда вы сохраните его и снова откроете, или даже если вы поделитесь файлом с кем-то.
    Записать макрос в Excel - сохранить макрос в этой книге
  6. Введите описание при необходимости. Обычно я этого не делаю, но если у вас много макросов, лучше указать, чтобы в будущем не забыть что делает макрос.
  7. Нажмите "ОК". Как только вы нажмете OK, Excel начнет записывать ваши действия. Вы можете увидеть кнопку "Остановить запись" на вкладке "Разработчик", которая указывает, что выполняется запить макроса.
  8. Выберите ячейку A2.
  9. Введите текст "Excel" (или вы можете использовать свое имя).
  10. Нажмите клавишу Enter. Вы попадете на ячейку A3.
  11. Нажмите кнопку "Остановить запись" на вкладке "Разработчик".
    Записать макрос в Excel - Остановить запись

Поздравляем! Вы только что записали свой первый макрос в Excel. Хотя макрос не делает ничего полезного, но он поможет нам понять как работает макрорекордер в Excel.

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

  1. Удалите текст в ячейке A2. Это нужно, чтобы проверить будет ли макрос вставлять текст в ячейку A2 или нет.
  2. Выберите любую ячейку — кроме A2. Это нужно проверить, выбирает ли макрос ячейку A2 или нет.
  3. Перейдите на вкладку "Разработчик".
  4. В группе "Код" нажмите кнопку "Макросы".
  5. В диалоговом окне "Макрос" щелкните макрос "ВводТекста".
  6. Нажмите кнопку "Выполнить".
    Записать макрос в Excel

Вы увидите, что как только вы нажмете кнопку "Выполнить", текст "Excel" будет вставлен в ячейку A2 и выбрана ячейка A3. Это происходит за миллисекунды. Но на самом деле макрос последовательно выполнил записанные действия.

Примечание. Вы также можете запустить макрос с помощью сочетания клавиш Ctrl + Shift + N (удерживайте клавиши Ctrl и Shift, а затем нажмите клавишу N). Это тот же самый ярлык, который мы назначили макросу при его записи.

Что записывает макрос?

Теперь перейдем к редактору кода и посмотрим что у нас получилось.

Вот шаги по открытию редактора VB в Excel:

  1. Перейдите на вкладку "Разработчик".
  2. В группе "Код" нажмите кнопку "Visual Basic".

Вы также можете использовать комбинацию клавиш Alt + F11 и перейти в редактор кода VBA.

Рассмотрим сам редактор кода. Далее коротко опишем интерфейс редактора.

Окно редактора кода

  • Панель меню: содержит команды, которые можно использовать во время работы с редактором VB.
  • Панель инструментов — похожа на панель быстрого доступа в Excel. Вы можете добавить к ней дополнительные инструменты, которыми часто пользуетесь.
  • Окно проектов (Project Explorer) — здесь Excel перечисляет все книги и все объекты в каждой книге. Например, если у нас есть книга с 3 рабочими листами, она появится в Project Explorer. Здесь есть несколько дополнительных объектов, таких как модули, пользовательские формы и модули классов.
  • Окно кода — собственно сам код VBA размещается в этом окне. Для каждого объекта, указанного в проводнике проекта, есть окно кода, например, рабочие листы, книги, модули и т. д. В этом уроке мы увидим, что записанный макрос находится в окне кода модуля.
  • Окно свойств — вы можете увидеть свойства каждого объекта в этом окне. Я часто использую это окно для обозначения объектов или изменения их свойств.
  • Immediate Window (окно предпросмотра) — На начальном этапе оно вам не пригодится. Оно полезно, когда вы хотите протестировать шаги или во время отладки. Он по умолчанию не отображается, и вы можете его отобразить, щелкнув вкладку «View» и выбрав опцию «Immediate Window».

Когда мы записали макрос "ВводТекста", в редакторе VB произошли следующие вещи:

  • Был добавлен новый модуль.
  • Макрос был записан с именем, которое мы указали — "ВводТекста"
  • В окне кода добавлена новая процедура.

Поэтому, если вы дважды щелкните по модулю (в нашем случае модуль 1), появится окно кода, как показано ниже.

Вот код, который записан макрорекодером:

В VBA, любая строка , которая следует за ‘ (знак апострофа) не выполняется. Это комментарий, который предназначен только для информационных целей. Если вы удалите первые пять строк этого кода, макрос по-прежнему будет работать.

Теперь давайте пробежим по каждой строке кода и опишем что и зачем.

Код начинается с Sub, за которым следует имя макроса и пустые круглые скобки. Sub — сокращение для подпрограммы. Каждая подпрограмма (также называемая процедурой) в VBA начинается с Sub и заканчивается End Sub.

  • Range("A2").Select — эта строка выбирает ячейку A2.
  • ActiveCell.FormulaR1C1 = «Excel» — эта строка вводит текст "Excel" в активной ячейке. Поскольку мы выбрали ячейку A2 в качестве первого шага, она становится нашей активной ячейкой.
  • Range("A3").Select — выбор ячейки A3. Это происходит, когда мы нажимаем клавишу Enter после ввода текста, результатом которого является выбор ячейки A3.

Надеюсь, что у вас есть некоторое базовое понимание того, как записывать макрос в Excel.

Обращаем внимание, что код, записанный через макрорекордер, как правило, не является эффективным и оптимизированным кодом. Макрорекордер часто добавляет дополнительные ненужные действия. Но это не значит, что не нужно пользоваться макрорекодером. Для тех, кто только изучает VBA , макрорекордер может быть отличным способом проанализировать и понять как все работает в VBA.

Абсолютная и относительная запись макроса

Вы уже знаете про абсолютные и относительные ссылки в Excel? Если вы используете абсолютную ссылку для записи макроса, код VBA всегда будет ссылаться на те же ячейки, которые вы использовали. Например, если вы выберете ячейку A2 и введете текст "Excel", то каждый раз — независимо от того, где вы находитесь на листе и независимо от того, какая ячейка выбрана, ваш код будет вводить текст "Excel" в ячейку A2.

Если вы используете параметр относительной ссылки для записи макроса, VBA не будет привязываться к конкретному адресу ячейки. В этом случае программа будет "двигаться" относительно активной ячейки. Например, предположим, что вы уже выбрали ячейку A1, и вы начинаете запись макроса в режиме относительной ссылки. Теперь вы выбираете ячейку A2, вводите текст Excel и нажмите клавишу Enter. Теперь, если вы запустите этот макрос, он не вернется в ячейку A2, вместо этого он будет перемещаться относительно активной ячейки. Например, если выбрана ячейка B3, она переместится на B4, запишет текст "Excel" и затем перейдет к ячейке K5.

Теперь давайте запишем макрос в режиме относительных ссылок:

  1. Выберите ячейку A1.
  2. Перейдите на вкладку "Разработчик".
  3. В группе "Код" нажмите кнопку "Относительные ссылки". Он будет подсвечиваться, указывая, что он включен.
    Запишите макрос в Excel - используя «Относительные ссылки»
  4. Нажмите кнопку "Запись макроса".
    Нажмите кнопку «Запись макроса» на вкладке «Разработчик».
  5. В диалоговом окне "Запись макроса" введите имя для своего макроса. Например, имя "ОтносительныеСсылки".
    Записать макрос в Excel - имя макроса
  6. В опции "Сохранить в" выберите "Эта книга".
  7. Нажмите "ОК".
  8. Выберите ячейку A2.
  9. Введите текст "Excel" (или другой как вам нравится).
  10. Нажмите клавишу Enter. Курсор переместиться в ячейку A3.
  11. Нажмите кнопку "Остановить запись" на вкладке "Разработчик".

Макрос в режиме относительных ссылок будет сохранен.

Теперь сделайте следующее.

  1. Выберите любую ячейку (кроме A1).
  2. Перейдите на вкладку "Разработчик".
  3. В группе "Код" нажмите кнопку "Макросы".
  4. В диалоговом окне "Макрос" кликните на сохраненный макрос "ОтносительныеСсылки".
  5. Нажмите кнопку "Выполнить".

Как вы заметите, макрос записал текст "Excel" не в ячейки A2. Это произошло, потому что вы записали макрос в режиме относительной ссылки. Таким образом, курсор перемещается относительно активной ячейки. Например, если вы сделаете это, когда выбрана ячейка B3, она войдет в текст Excel — ячейка B4 и в конечном итоге выберет ячейку B5.

Вот код, который записал макрорекодер:

Код макроса VBA для относительной ссылки

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

Не обращайте внимание на часть кода Range(«A1»). Это один из тех случаев, когда макрорекодер добавляет ненужный код, который не имеет никакой цели и может быть удален. Без него код будет работать отлично.

Что нельзя сделать с помощью макрорекодера?

Макро-рекордер отлично подходит для вас в Excel и записывает ваши точные шаги, но может вам не подойти, когда вам нужно сделать что-то большее.

  • Вы не можете выполнить код без выбора объекта. Например, если вы хотите, чтобы макрос перешел на следующий рабочий лист и выделил все заполненные ячейки в столбце A, не выходя из текущей рабочей таблицы, макрорекодер не сможет этого сделать. В таких случаях вам нужно вручную редактировать код.
  • Вы не можете создать пользовательскую функцию с помощью макрорекордера. С помощью VBA вы можете создавать пользовательские функции, которые можно использовать на рабочем листе в качестве обычных функций.
  • Вы не можете создавать циклы с помощью макрорекордера. Но можете записать одно действие, а цикл добавить вручную в редакторе кода.
  • Вы не можете анализировать условия: вы можете проверить условия в коде с помощью макрорекордера. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и запуска кода, если true (или другой код, если false).

Расширение файлов Excel, которые содержат макросы

Когда вы записываете макрос или вручную записываете код VBA в Excel, вам необходимо сохранить файл с расширением файла с поддержкой макросов (.xlsm).

До Excel 2007 был достаточен один формат файла — .xls. Но с 2007 года .xlsx был представлен как стандартное расширение файла. Файлы, сохраненные как .xlsx, не могут содержать в себе макрос. Поэтому, если у вас есть файл с расширением .xlsx, и вы записываете / записываете макрос и сохраняете его, он будет предупреждать вас о сохранении его в формате с поддержкой макросов и покажет вам следующее диалоговое окно:

Если вы выберете "Нет", Excel сохранить файл в формате с поддержкой макросов. Но если вы нажмете "Да", Excel автоматически удалит весь код из вашей книги и сохранит файл как книгу в формате .xlsx. Поэтому, если в вашей книге есть макрос, вам нужно сохранить его в формате .xlsm, чтобы сохранить этот макрос.

Ссылка на основную публикацию