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

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

ActiveCell.Offset(4, 2).Range("A1").Select

Она указывает приложению на необходимость выделить ячейку, смещенную на 4 строки и 2 столбца от активной на данный момент ячейки. Это значит, если при последующем выполнении макроса предварительно выделить ячейку G10, то смещенной на 4 строки и 2 столбца окажется ячейка I14.

Для активации описанного параметра перейдите на вкладку «Вид», найдите область «Макросы» и в раскрывающемся меню кликните по пункту «Относительные ссылки».

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

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

Чтобы записать макрос, нужно включить режим записи. Это можно сделать на вкладке Вид (View) в разделе Макросы (Macros) или в меню Сервис (Tools), если у Вас Excel 2003. Ниже на картинках показано, как выглядят эти меню.

Далее откроется диалоговое окно Запись макроса (Record Macro), как показано на картинке ниже:

Здесь, по желанию, можно ввести имя и описание для макроса. Рекомендуется давать макросу такое имя, чтобы, вернувшись к нему спустя некоторое время, можно было без труда понять, для чего этот макрос нужен. Так или иначе, если не ввести для макроса имя, то Excel автоматически назовёт его Макрос1, Макрос2 и так далее.

Здесь же можно назначить сочетание клавиш для запуска записанного макроса. Запускать макрос таким способом будет значительно проще. Однако будьте осторожны! Если случайно назначить для макроса одно из предустановленных клавиатурных сочетаний Excel (например, Ctrl+C), то в дальнейшем макрос может быть запущен случайно.

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

При включении режима записи макроса в строке состояния (внизу слева) появляется кнопка Стоп. В Excel 2003 эта кнопка находится на плавающей панели инструментов.

Нажмите Стоп, когда выполните все действия, которые должны быть записаны в макрос. Теперь код записанного макроса сохранён в модуле редактора Visual Basic.

Параметр «Относительные ссылки»

Если перед началом записи макроса включить параметр Относительные ссылки (Use Relative References), то все ссылки в записываемом макросе будут создаваться как относительные. Если же параметр выключен, то при записи макроса будут создаваться абсолютные ссылки (подробнее об этих двух типах ссылок можно узнать в статьях, посвящённых теме ссылок на ячейки в Excel).

Параметр Относительные ссылки (Use Relative References) находится в разделе Макросы (Macros) на вкладке Вид (View). В Excel 2003 этот параметр расположен на плавающей панели инструментов.

Просмотр кода VBA

Код VBA, записанный в макрос, размещается в модуле, который можно просмотреть в редакторе Visual Basic. Редактор можно запустить нажатием Alt+F11 (одновременное нажатие клавиш Alt и F11).

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

Запуск записанного макроса в Excel

Записывая макрос, Excel всегда создаёт процедуру Sub (не Function). Если при создании макроса к нему было прикреплено сочетание клавиш, то именно с его помощью запустить макрос будет проще всего. Существует и другой способ запустить макрос:

• Нажмите Alt+F8 (одновременно нажмите клавиши Alt и F8);
• В появившемся списке макросов выберите тот, который нужно запустить;
• Нажмите кнопку Выполнить (Run).

Ограничения

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

• Константы, переменные и массивы;
• Выражения IF;
• Циклы;
• Обращения к встроенным функциям или внешним процедурам.

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

Автоматическое создание кода VBA в Excel отлично работает для простых макросов, но если нужно построить более сложный макрос, то придётся научиться писать код VBA самостоятельно. Тем не менее, запись макроса в Excel – это отличны инструмент, при помощи которого можно создавать первоначальный код, а в дальнейшем исправлять или вставлять его в более сложные макросы.

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

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

Приветствую всех, дорогие читатели блога TutorExcel.Ru.

Как мы уже хорошо знаем всего в Excel выделяют 3 типа ссылок: относительные (А1), абсолютные ($А$1) и смешанные ($А1 и А$1).
Применение каждого из типов имеет свой смысл и определенные преимущества, поэтому зачастую бывает полезно в формулах заменить все относительные ссылки на абсолютные (или наоборот), к примеру, часто может пригодиться при копировании диапазона ячеек.

Поменять тип для конкретной ссылки в ячейке можно с помощью клавиши F4.
Для этого выделяем ссылку в формуле (либо на ячейку, либо на диапазон ячеек) и поочередно нажимаем F4, в результате ссылки будут меняться в порядке A1 -> $A$1 -> $A1 -> A$1. Затем останавливаемся на нужном шаге и задача смены типа решена.

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

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

Как поменять ссылки в формулах на абсолютные?

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

Переходим в редактор VBA (для быстрого перехода нажимаем Alt + F11), создаем новый модуль (щелкаем правой кнопкой мыши в панели проектов и выбираем Insert -> Module) и добавляем туда код макроса:

Sub ChangeCellStyleInFormulas()     Dim MyCell As Range     On Error Resume Next ‘Обработка ошибки, если рассматриваемый диапазон ячеек не содержит формул     For Each MyCell In Selection.SpecialCells(xlCellTypeFormulas) ‘Цикл для каждой ячейки диапазона содержащая формулу         MyCell.Formula = Application.ConvertFormula(MyCell.Formula, xlA1, xlA1, xlAbsolute) ‘Меняем тип ссылки     Next End Sub

Попробуем проверить работу макроса на простой таблице с формулами:

Выделяем диапазон ячеек с таблицей (в нашем случае это диапазон F6:J10) и запускаем макрос ChangeCellStyleInFormulas (в панели вкладок выбираем Разработчик -> Макросы (или нажимаем Alt + F8), далее находим нужный макрос и жмем Выполнить):

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

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

Как поменять ссылки в формулах на относительные?

За преобразование формулы в макросе отвечает функция ConvertFormula, в которой один из параметров задает тип ссылки:

• xlAbsolute. Абсолютная ($А$1);
• xlRelative. Относительная (А1);
• xlAbsRowRelColumn. Смешанная. Абсолютная строка и относительный столбец (А$1);
• xlRelRowAbsColumn. Смешанная. Относительная строка и абсолютный столбец ($А1).

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

Например, для той же таблицы применим макрос с параметром xlAbsRowRelColumn (абсолютная строка и относительный столбец, вид A$1):

И для параметра xlRelRowAbsColumn (относительная строка и абсолютный столбец, вид $A1):

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

Способ преобразования без использования макросов

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

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

Выделяем диапазон с данными, нажимаем Ctrl + H, в поле Найти указываем знак доллара, а поле Заменить на оставляем пустым, нажимаем Заменить все и получаем нужный результат:

Замечания

Будьте внимательны при работе с формулами массива, после работы макроса они превращаются в обычные и могут вернуть значение ошибки. Также из-за особенностей VBA макрос может некорректно обрабатывать большие по объему формулы.

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — пишите в комментариях.

Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!