Откройте Excel и VBE (редактор Visual Basic). Если это не было изменено, окно VBE содержит окно ProjectExplorer и окно Properties (к ним можно получить доступ из меню «Просмотр»).

Project Explorer: работает как файловый менеджер. Помогает перемещаться по коду в книге.

Окно свойств: показывает свойства текущего активного объекта (например, Sheet1) текущей книги (например ,Book1).

В этой статье мы узнаем, как легко записывать макрос в Excel.

Упражнение 1: Запись макроса.

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

  1. На пустом листе в новой книге выберите ячейку C10. 2. Запустите средство записи макросов с возможностью сохранения макроса в This Workbook. На этом этапе VBE создает новую папку Modules. Пойти и посмотреть на него вполне безопасно — ваши действия не будут записаны. Щелкните [+] рядом с папкой и убедитесь, что VBE поместил модуль в папку и назвал его Module1. Дважды щелкните значок модуля, чтобы открыть окно его кода. Вернитесь в Excel.

  2. Убедитесь, что кнопка «Относительная ссылка» на панели инструментов Остановить запись НЕ нажата.

  3. Выберите ячейку B5 и остановите регистратор.

  4. Переключитесь на VBE и посмотрите код:

Диапазон («B5»). Выберите 6. Теперь запишите другой макрос точно таким же образом, но на этот раз с нажатой кнопкой «Относительная ссылка».

  1. Переключитесь на VBE и посмотрите код:

ActiveCell.Offset (-5, -1) .Range («A1»). Выберите 8. Теперь запишите другой макрос, но вместо выбора ячейки B5 выберите блок ячеек 3×3, начиная с B5 (выберите ячейки B5: F7)

  1. Переключитесь на VBE и посмотрите на код:

ActiveCell.Offset (-5, -1) .Range («A1: B3»). Выберите 10. Воспроизведите макрос, предварительно выбрав ячейку, отличную от C10 (для Macro2 и Macro3 начальная ячейка должна быть в строке 6 или ниже — см. шаг 11 ниже)

Macro1 — всегда перемещает выделение на B5. Macro2 — перемещает выделение в ячейку на 5 строк вверх и на 1 столбец слева от выбранной ячейки.

Macro3 — всегда выбирает блок из шести ячеек, начиная с 5 строк вверх и 1 столбца слева от выбранной ячейки.

  1. Запустите Macro2, но вызовите ошибку, выбрав ячейку в строке 5 или выше. Макрос пытается выбрать несуществующую ячейку, потому что его код говорит ему выбрать ячейку на 5 строк выше начальной точки, и это за пределами верхней части листа. Нажмите Debug, чтобы перейти к той части макроса, которая вызвала проблему.

ПРИМЕЧАНИЕ. Когда VBE находится в режиме отладки, строка кода, вызвавшая проблему, выделяется желтым цветом. Вы должны «сбросить» макрос перед тем, как продолжить. Нажмите кнопку «Сброс» на панели инструментов VBE или выберите «Выполнить»> «Сброс». Желтое выделение исчезнет, ​​и VBE выйдет из режима отладки.

  1. Важно попытаться предвидеть подобную ошибку пользователя. Самый простой способ — изменить код, чтобы просто игнорировать ошибки и перейти к следующей задаче. Сделайте это, добавив строку… On Error Resume Next… непосредственно над первой строкой макроса (под строкой Sub Macro1 ()

  2. Запустите Macro2, как и раньше, начав слишком высоко на листе. Введенная вами строка указывает Excel игнорировать строку кода, которую он не может выполнить. Сообщение об ошибке отсутствует, и макрос завершает работу, сделав все возможное. Используйте этот метод обработки ошибок с осторожностью. Это очень простой макрос. более сложный макрос, вероятно, не работал бы должным образом, если бы ошибки просто игнорировались.Кроме того, пользователь не подозревает, что что-то пошло не так.

  3. Измените код Macro2, чтобы включить более сложный обработчик ошибок следующим образом:

Sub Macro2 ()

При ошибке Перейти к ErrorHandler ActiveCell.Offset (-5, -1) .Range («A1»). Выберите Exit Sub ErrorHandler:

MsgBox «Вы должны начать ниже строки 5 «

End Sub 15. На этот раз пользователю будет показано диалоговое окно, если что-то пойдет не так. Если ошибки нет строка ExitSub заставляет макрос завершиться после того, как он выполнил свою работу — в противном случае пользователь увидел бы сообщение, даже если бы ошибки не было.

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

Упражнение 2: Улучшение записанных макросов Это упражнение показывает, что при записи макросов часто создается больше кода, чем необходимо. Он демонстрирует использование оператора With для уточнения кода .

  1. Выберите любую ячейку или блок ячеек.

  2. Запустите средство записи макросов и вызовите макрос FormatCells. Параметр «Относительные ссылки» не имеет значения.

  3. Перейдите в Формат> Ячейки> Шрифт и выберите Times New Roman и Red.

Перейдите в Узоры и выберите Желтый.

Перейдите к выравниванию и выберите Horizontal, Center. Перейдите к Number и выберите Currency.

  1. Щелкните OK и остановите диктофон.

  2. Нажмите кнопку «Отменить» (или Ctrl + Z), чтобы отменить изменения в рабочем листе.

  3. Выберите блок ячеек и запустите макрос FormatCells. Учтите, что его нельзя отменить! Введите ячейки, чтобы проверить результат форматирования.

  4. Посмотрите на код:

Sub FormatSelection ()

Selection.NumberFormat = «$ , # 0.00″

При выборе .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .ShrinkToFit = False .MergeCells = False End With Selection.Font .Name = «Times New Roman»

Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 3 Завершить с помощью Selection.Interior .ColorIndex = 6 .Pattern. End With End Sub 8. Отметьте все дополнительные инструкции, которые были записаны. Удалите строки кода, чтобы осталось только следующее:

Sub FormatSelection ()

Selection.NumberFormat = «$ , # 0.00″

С выделением .HorizontalAlignment = xlCenter Конец с выделением.Font .Name = «Times New Roman»

ColorIndex = 3 Завершить с помощью Selection.Interior .ColorIndex = 6 Завершить с помощью End Sub 9. Запустите макрос, чтобы проверить отредактированный код. Он по-прежнему работает как раньше.

  1. Теперь измените код еще больше:

Sub FormatSelection ()

С выделением .NumberFormat = «$ , # 0.00″

Font.ColorIndex = 3 .Interior.ColorIndex = 6 Конец с помощью End Sub 11. Протестируйте макрос. Все по-прежнему работает, и код будет работать намного быстрее.

  1. Попробуйте записать тот же макрос с помощью кнопок на панели инструментов вместо перехода в диалоговое окно:

Измените шрифт на Times New Roman Измените цвет шрифта на Red Измените цвет заливки на Yellow Нажмите кнопку Center Нажмите кнопку Currency 13. Посмотрите на код. Вы по-прежнему получаете много вещей, которые вам не нужны. Excel записывает все настройки default. Большинство из них можно безопасно удалить.

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

Упражнение 3: Наблюдайте за записываемым макросом Это упражнение показывает, что вы можете учиться, наблюдая за построением макроса во время его записи. Это также пример того, когда иногда оператор With не подходит .

  1. Откройте файл VBA01.xls.

Хотя этот рабочий лист визуально нормален и понятен пользователю, наличие пустых ячеек может вызвать проблемы. Попробуйте отфильтровать данные и посмотреть, что произойдет. Выберите Данные> Фильтр> Автофильтр и отфильтруйте по региону или месяцу. Ясно, что Excel не делает тех же предположений, что и пользователь. Пустые ячейки необходимо заполнить.

  1. Разместите окна Excel и VBE мозаикой (вертикально), чтобы они располагались рядом.

  2. Выберите любую ячейку в данных. Если это пустая ячейка, она должна быть рядом с ячейкой, содержащей данные.

  3. Запустите средство записи макросов и вызовите макрос FillEmptyCells.

Установите для записи относительных ссылок.

  1. В окне VBE найдите и дважды щелкните модуль (Module1)

для текущей книги, чтобы открыть панель редактирования, затем выключите окна Project Explorer и Properties (только для освобождения места).

  1. Запишите новый макрос следующим образом:

Шаг 1. Ctrl + * (для выбора текущего региона)

Шаг 2. Edit> Go To> Special> Blanks> OK (чтобы выделить все пустые ячейки в текущей области)

Шаг 3. Введите = [UpArrow], затем нажмите Ctrl + Enter (чтобы ввести текст во все выбранные ячейки)

Шаг 4. Ctrl + * (чтобы снова выбрать текущий регион)

Шаг 5. Ctrl + C (чтобы скопировать выделение — подойдет любой метод)

Шаг 6. Правка> Специальная вставка> Значения> ОК (чтобы вставить данные обратно в то же место, но без формул)

Шаг 7. Esc (выход из режима копирования)

Шаг 8. Остановите запись.

  1. Посмотрите на код:

Sub FillEmptyCells ()

Selection.CurrentRegion.Select Selection.SpecialCells (xlCellTypeBlanks) .Select Selection.FormulaR1C1 = «= R [-1] C»

Selection.CurrentRegion.Select Selection.Copy Selection.PasteSpecial Paste: = xlValues, Operation: = xlNone, SkipBlanks: = _ False, Transpose: = False Application.CutCopyMode = False End Sub 8. Обратите внимание на использование пробела и подчеркивания «_» Означает разделение одной строки кода на новую строку. Без этого Excel обрабатывал бы код как два отдельных оператора.

  1. Поскольку этот макрос был записан с помощью хорошо продуманных команд, в нем мало ненужного кода. В Paste Special все после слова «xlValues» можно удалить.

  2. Попробуйте макрос. Затем используйте инструмент Автофильтр и обратите внимание на разницу.