Edcomp.ru

Советы по настройке и оптимизации компьютера
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Работа с макросами в Excel

Как создавать, изменять и выполнять макросы в Excel

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

Создание макросов

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

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

Метод 1: Записываем макрос в автоматическом режиме

Чтобы иметь возможность записать макросы автоматически, для начала нужно их активировать в параметрах Эксель.

Узнать, как это можно сделать, можно в нашей статье – “Как включать и отключать макросы в Excel”.

После того, как макросы включены, можно перейти к нашей основной задаче.

Запись макроса в Эксель

  1. Переключаемся во вкладку “Разработчик”. В группе инструментов “Код” щелкаем по значку “Записать макрос”.
  2. На экране появится окошко, в котором мы можем выполнить настройки записи. Здесь указывается:
    • имя макроса – любое, но должно начинаться с буквы (не с цифры), не допускаются пробелы.
    • комбинация клавиш, которая будет использоваться для запуска макроса. Обязательной является Ctrl, а вторую мы можем назначить в специальном поле. Допустим, пусть это будет клавиша “r”. Если нужно использовать заглавную букву, нужно зажать клавишу Shift, которая будет добавлена в сочетание клавиш.

Запускаем выполнение макроса

Чтобы проверить работу записанного макроса, нужно сделать следующее:

  1. В той же вкладке (“Разработчик”) и группе “Код” нажимаем кнопку “Макросы” (также можно воспользоваться горячими клавишами Alt+F8).Переход к макросам в Excel
  2. В отобразившемся окошке выбираем наш макрос и жмем по команде “Выполнить”.Запуск выполнения макроса в ЭксельПримечание: Есть более простой вариант запустить выполнение макроса – воспользоваться сочетанием клавиш, которое мы задали при создании макроса.

Корректируем макрос

Созданный макрос можно изменить. Самая распространенная причина, которая приводит к такой необходимости – сделанные при записи ошибки. Вот как можно отредактировать макрос:

  1. Нажимаем кнопку “Макросы” (или комбинацию Ctrl+F8).
  2. В появившемся окошке выбираем наш макрос и щелкаем “Изменить”.Переход к редактированию макроса в Excel
  3. На экране отобразится окно редактора “Microsoft Visual Basic”, в котором мы можем внести правки. Структура каждого макроса следующая:
    • открывается с команды “Sub”, закрывается – “End Sub”;
    • после “Sub” отображается имя макроса;
    • далее указано описание (если оно есть) и назначенная комбинация клавиш;
    • команда “Range(“…”).Select” возвращает номер ячейки. К примеру, “Range(“B2″).Select” отбирает ячейку B2.
    • В строке “ActiveCell.FormulaR1C1” указывается значение ячейки или действие в формуле.Код макроса в Эксель
  4. Давайте попробуем скорректировать макрос, а именно, добавить в него ячейку B4 со значением 3. В код макроса нужно добавить следующие строки:
    Range(«B4»).Select
    ActiveCell.FormulaR1C1 = «3» Редактирование макроса в Эксель
  5. Для результирующей ячейки D2, соответственно, тоже нужно изменить начальное выражение на следующее:
    ActiveCell.FormulaR1C1 = «=RC[-2]*R[1]C[-2]*R[2]C[-2]» .Редактирование макроса в ExcelПримечание: Обратите внимание, что адреса ячеек в данной строке (ActiveCell.FormulaR1C1) пишутся в стиле R1C1.
  6. Когда все готово, редактор можно закрывать (просто щелкаем на крестик в правом верхнем углу окна).
  7. Запускаем выполнение измененного макроса, после чего можем заметить, что в таблице появилась новая заполненная ячейка (B4 со значением “3”), а также, пересчитан результат с учетом измененной формулы.Результат выполнения измененного макроса в Эксель
  8. Если мы имеем дело с большим макросом, на выполнение которого может потребоваться немало времени, ручное редактирование изменений поможет быстрее справиться с задачей.
    • Добавив в конце команду Application.ScreenUpdating = False мы можем ускорить работу, так как во время выполнения макроса, изменения на экране отображаться не будут.Отключение и включение отображения действий макроса на экране в Эксель
    • Если потребуется снова вернуть отображение на экране, пишем команду: Application.ScreenUpdating = True .
  9. Чтобы не нагружать программу пересчетом после каждого внесенного изменения, в самом начале пишем команду Application.Calculation = xlCalculationManual , а в конце – Application.Calculation = xlCalculationAutomatic . Теперь вычисление будет выполняться только один раз.Отключение и включение автоматических расчетов по макросу в Эксель

Метод 2: создание макроса вручную

Опытные пользователи Эксель в некоторых случаях предпочитают создавать макросы от начала до конца полностью вручную . План действий следующий:

  1. Во вкладке “Разработчик” нажимаем на значком “Visual Basic” (группа инструментов “Код”).Переход к редактору Visual Basic в Эксель
  2. В результате, на экране появится уже знакомое окно, которое мы рассмотрели выше.Редактор Visual Basic в Excel
  3. Здесь создается макрос и пишется ее код – полностью вручную.

Заключение

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

Работа с макросами 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 < 1000

If i = 1 Then

‘ Специальный случай для первой записи в серии

‘ Сохраняется следующий размер шага, перед перезаписью

‘ текущей записи в серии

iStep = iFib

iFib = iFib_Next

‘ Печать текущего значения последовательности Фибоначчи для столбца А

‘ текущего листа

Cells(i, 1).Value = iFib

‘ Вычисление следующего значения последовательности и увеличение

‘ маркера позиции на 1

iFib_Next = iFib + iStep

Пример 3

Следующий пример подпрограммы читает значения с ячейки в колонке 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) < iRow Then

ReDim Preserve dCellValues(1 To iRow + 9)

‘ Сохраняется текущая ячейка в массиве CellValues

dCellValues(iRow) = Cells(iRow, 1).Value

iRow = iRow + 1

Пример 4

Следующая процедура «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

‘ Прохождение цикла через каждую ячейку колонки ‘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 SettingsData»)

‘ Выбрать переменные 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 2010 без программирования кода

Макросы позволяют существенно расширить возможности в программе Excel. Они автоматизируют рабочие процессы и берут большую часть рутинной работы пользователя на себя. Просто нужно научится пользоваться макросами и производительность труда возрастет в десятки раз!

Вам даже не нужно быть программистом и знать язык программирования «VBA» чтобы создавать свои макро-программы с помощью инструмента записи макросов.

Сначала надо включить панель разработчика. Для этого в меню «Файл» открываем группу опций «Параметры». В появившемся окне «Параметры Excel» открываем группу «Настройка ленты». Обратите внимание на правую колонку настроек под аналогичным названием «Настройка ленты». В ней следует отметить галочкой опцию «Разработчик» как показано ниже на рисунке:

Панель разработчика.

Теперь нам доступна на ленте новая закладка «Разработчик» со всеми своими инструментами для автоматизации работы в Excel и создания макросов.

Применение VBA и макросов в Microsoft Excel

Макросы – это внутренние приложения, которые берут на себя всю рутинную работу, облегчая жизнь пользователю. Каждый пользователь может создать макрос без знания языков программирования. Для этого существует макрорекодер, который запускается с помощью кнопки «Запись макроса».

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

Как записать готовый макрос в Excel? Очень просто:

  1. На вкладке «Разработчик» нажимаем кнопку «Запись макроса». Запись макроса.
  2. В появившимся диалоговом окне заполняем параметры макроса. И нажимаем «ОК». Параметры.
  3. После завершения нажимаем на кнопку «Остановить запись», после чего макрос будет автоматически сохранен. Остановить запись.
  4. Для выполнения или редактирования записанного макроса нажимаем на кнопку «Макросы» (или комбинацию клавиш ALT+F8). Появится окно со списком записанных макросов и кнопками для управления ими. Список макросов.

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

Как работать с макросами в Excel

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

1 Правильные имена в макросах.

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

Имя макроса обязательно должно начинаться с букв и не может содержать пробелы, символы или знаки препинания. После первого символа, вы можете использовать больше букв, цифр или нижнее подчеркивание, но максимальная длина имени составляет 80 символов.

2 Используйте относительные (не абсолютные) адреса ячеек

Абсолютный адрес ячейки – это точное местонахождение курсора, когда информация о его размещении записывается в макро-адреса с жесткой привязкой к конкретной ячейке в момент записи. Абсолютные адреса ограничивают возможности макроса, если будут добавляться / удаляться данные на листе Excel или список данных будет становиться больше. Относительные средства не привязывают курсор к конкретному адресу ячейки.

По умолчанию в Excel включен режим «Абсолют», но вы можете изменить его, включив кнопку «Относительные ссылки» расположенную ниже под кнопкой «Запись макроса» на панели инструментов вкладки «Разработчик»:

Относительные ссылки.

3 Всегда начинайте запись с курсором в A1

Абсолютный отсчет ячеек, всегда ведется с исходного положения (адрес ячейки А1) – до адреса курсора с вашими данными. Если вы сохранили ваш макрос в книге личных макросов (рекомендуется так и делать), то вы можете использовать свою программу на других листах с аналогичными данными. Независимо от того, где ваш курсор позиционируется, когда вы начинаете запись макроса! Даже если он уже находится в ячейке A1, ваш первый макрос лучше записывать после нажатия клавиш должны быть Ctrl + Home.

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

4 Всегда перемещаться с клавиш направления в момент записи макроса

Используйте кнопки со стрелками для управления курсором (Ctrl + Up, и т.п.). Позиционируйте курсор, так чтобы вы могли добавить, изменить или удалить данные внутри таблицы по мере необходимости.

Использование мыши для навигации является более сложным и не так надежным в момент записи. Когда дело доходит до макросов, использовать мышь лучше только для вызова меню.

5 Создавайте макросы для конкретных небольших задач

Держите ваши макросы для небольших специфичных задач. Чем больше программный код в макросе, тем медленнее он работает, особенно если это требуется для выполнения многих функций или рассчитать много формул в большой электронной таблице.

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

Если нельзя разбить длинный макрос на короткие приложения, а требуется проверить его функциональность пошагово («отладкой»). Нажмите клавишу F8 каждый раз, когда вы хотите перейти к следующему шагу выполнения задачи. Процесс выполнения программы останавливается, когда он видит ошибку. Вы можете исправить ошибку, которую легко найти с помощью «отладки» или записать по-новому.

Создание макросов и пользовательских функций на VBA

Всем нам приходится — кому реже, кому чаще — повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" — одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.

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

В принципе, существует великое множество языков программирования (Pascal, Fortran, C++, C#, Java, ASP, PHP. ), но для всех программ пакета Microsoft Office стандартом является именно встроенный язык VBA. Команды этого языка понимает любое офисное приложение, будь то Excel, Word, Outlook или Access.

Способ 1. Создание макросов в редакторе Visual Basic

Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно — редактор программ на VBA, встроенный в Microsoft Excel.

macro1.png

  • В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис — Макрос — Редактор Visual Basic(Toos — Macro — Visual Basic Editor).
  • В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)

    :

К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:

macro2.png

Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:

    Обычные модули — используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert — Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:

macro3.png

macro4.png

macro5.png

Обычный макрос, введенный в стандартный модуль выглядит примерно так:

macro6.png

Давайте разберем приведенный выше в качестве примера макрос Zamena:

  • Любой макрос должен начинаться с оператора Sub, за которым идет имя макроса и список аргументов (входных значений) в скобках. Если аргументов нет, то скобки надо оставить пустыми.
  • Любой макрос должен заканчиваться оператором End Sub.
  • Все, что находится между Sub и End Sub — тело макроса, т.е. команды, которые будут выполняться при запуске макроса. В данном случае макрос выделяет ячейку заливает выделенных диапазон (Selection) желтым цветом (код = 6) и затем проходит в цикле по всем ячейкам, заменяя формулы на значения. В конце выводится окно сообщения (MsgBox).

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

Способ 2. Запись макросов макрорекордером

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

  • Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу — запись останавливается.
  • Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
  • Если во время записи макроса макрорекордером вы ошиблись — ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) — во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Чтобы включить запись необходимо:

  • в Excel 2003 и старше — выбрать в меню Сервис — Макрос — Начать запись(Tools — Macro — Record New Macro)
  • в Excel 2007 и новее — нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

macro7.png

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