Как сделать относительную ссылку в excel для макроса

Как сделать относительную ссылку в excel для макроса?

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

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

Она указывает приложению на необходимость выделить ячейку, смещенную на 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) и добавляем туда код макроса:

Dim MyCell As Range

On Error Resume Next ‘Обработка ошибки, если рассматриваемый диапазон ячеек не содержит формул

Читайте также  Excel календарь в ячейке

For Each MyCell In Selection.SpecialCells(xlCellTypeFormulas) ‘Цикл для каждой ячейки диапазона содержащая формулу

MyCell.Formula = Application.ConvertFormula(MyCell.Formula, xlA1, xlA1, xlAbsolute) ‘Меняем тип ссылки

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


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


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

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

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

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

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

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

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


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


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

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

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

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

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

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

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

Источник:
http://word-office.ru/kak-sdelat-otnositel-nuyu-ssylku-v-excel-dlya-makrosa.html

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

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

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

  • Запись макроса в современных версиях Excel (2007 и новее):
  • Запись макроса в Excel 2003:

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

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

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

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

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

  • Кнопка Стоп в строке состояния в Excel 2007 и более новых версиях:
  • Панель инструментов записи макроса в Excel 2003:

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

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

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

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

  • Параметр Относительные ссылки (Use Relative References) в современных версиях Excel:
  • Параметр Относительные ссылки (Use Relative References) на плавающей панели инструментов в 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 – это отличны инструмент, при помощи которого можно создавать первоначальный код, а в дальнейшем исправлять или вставлять его в более сложные макросы.

Источник:
http://office-guru.ru/excel/zapis-makrosa-v-excel-452.html

Макрос с относительными ссылками

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

В MS Excel такая возможность предусмотрена. Для того чтобы ссылки на ячейки или диапа­зоны интерпретировались как относительные, в группе Кодпри записи макроса следует предварительно щелкнуть на кнопке . В частности, если эта кнопка отображается нажатой, то все ссылки в процессе за­писи интерпретируются как относительные. Если щелкнуть на ней еще раз ссылки будут интерпретироваться как абсо­лютные. В процессе записи макроса можно неоднократно щелкать на этой кнопке, помещая в макрос как абсолютные, так и относительные ссылки.

Читайте также  Слияние документов Word и Excel

Ниже эта особенность манипулирования ссылками будет использована для того, чтобы создать макрос, с помощью которого в диапазон ячеек, начиная с выбранной при запуске макроса, будут заноситься натуральные числа от 1 до 10, затем активи­зируется ячейка В1.

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

ПРИМЕЧАНИЕ. Удобно создавать данный макрос в той же книге, где был создан предыдущий макрос. Для его записи можно, например, перейти к новому рабочему листу.

1. Выберите вкладку Разработчик – группа КодЗапись макроса. В открывшемся диало­говом окне Запись макроса укажите имя макроса (можно что-то вроде Му_Масrо_2). Комментарий большого значения не имеет, а вот в качестве «горячей» комбинации клавиш целесообразно указать — для этого достаточно в поле Сочетание клавиш: ввести курсор и нажать клавишу на клавиатуре. При этом в соответствующем поле отображается строчная литера. Таким образом, для за­пуска макроса, заполняющего числами ячейки диапазона А1:А10,можно на­жать комбинацию .

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

3. После этого в ячейку А1 вводится число 1, в ячейку А2 — число 2, диапазон А1 :А2 выделяется и расширяется до А1 :А10.

4. Все, что осталось, — это щелкнуть мышью в ячейке B1, сделав ее тем самым активной.

5. В завершение записи макроса щелкните на кнопке останова записи, на панели Остановка записи.Макрос готов к использованию.

ПРИМЕЧАНИЕ. Все это лучше делать на чистом рабочем листе. Если трех рабочих листов книги, предлагаемых по умолчанию для работы, недостаточно, можно добавить еще один рабочий лист. Это делается посредством нажатия на кнопку (слева внизу окна Excel) или комбинацией клавиш .

Проверить работу макроса можно, выделив какую-то ячейку в рабочем листе, отличную от A1, и нажав . В результате чего диапазон из десяти ячеек, начиная с активной на мо­мент запуска макроса, будет заполнен последо­вательными натуральными числами от 1 до 10, активной станет ячейка B1.

Источник:
http://studopedia.ru/9_114954_makros-s-otnositelnimi-ssilkami.html

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

Многие знают, как изменить стиль ссылок в формуле с абсолютной на относительную и наоборот (за это отвечают знаки доллара внутри ссылки): выделяем ссылку внутри формулы и жмем F4. Ссылка последовательно меняется (на примере полностью относительной — C3:C8 ):

  • полностью абсолютная $C$3:$C$8 =>
  • абсолютные строки и относительные столбцы C$3:C$8 =>
  • абсолютные столбцы и относительные строки $C3:$C8 =>
  • полностью относительная C3:C8

А теперь представим ситуацию: когда-то были созданы кучи формул в разных местах листа и вдруг понадобилось сменить ссылки на ячейки в формулах с относительных на абсолютные или наоборот, или закрепить только столбцы, а строки оставить «плавающими» или т.п.. Вручную это делать довольно долго и совсем не интересно. Вот в таких случаях и понадобится код, который поможет сделать данный процесс более приятным и более быстрым. Я бы сказал — два клика мышью и часы работы в пятницу сэкономлены 🙂

Sub Change_Style_In_Formulas() Dim rR As Range, rFormulasRng As Range, rA As Range Dim lType As String Dim aF_Source, aF_Res Dim lr As Long, lc As Long lType = InputBox(«Изменить тип ссылок у формул?» & Chr(10) & Chr(10) _ & «1 — Все абсолютные» & Chr(10) _ & «2 — Абсолютная строка/Относительный столбец» & Chr(10) _ & «3 — Относительная строка/Абсолютный столбец» & Chr(10) _ & «4 — Все относительные», «www.excel-vba.ru») If StrPtr(lType) = 0 Then Exit Sub If Val(lType) 4 Then MsgBox «Неверно указан тип преобразования!», vbCritical Exit Sub End If On Error Resume Next Set rR = Application.InputBox(«Выделите диапазон с формулами», «www.excel-vba.ru», , , , , , Type:=8) If rR Is Nothing Then Exit Sub On Error Resume Next Set rFormulasRng = rR.SpecialCells(xlFormulas) If rFormulasRng Is Nothing Then MsgBox «Выбранный диапазон не содержит формул», 64, «www.excel-vba.ru» Exit Sub End If On Error GoTo 0 For Each rA In rFormulasRng.Areas aF_Source = rA.Formula aF_Res = Application.ConvertFormula(aF_Source, xlA1, xlA1, Val(lType)) If IsArray(aF_Res) Then For lr = LBound(aF_Res, 1) To UBound(aF_Res, 1) For lc = LBound(aF_Res, 2) To UBound(aF_Res, 2) If IsError(aF_Res(lr, lc)) Then aF_Res(lr, lc) = aF_Source(lr, lc) End If Next Next Else If IsError(aF_Res) Then aF_Res = aF_Source End If End If rA.Formula = aF_Res Next Set rFormulasRng = Nothing MsgBox «Конвертация стилей ссылок завершена!», 64, «www.excel-vba.ru» End Sub

  • копируем приведенный выше код, переходим в редактор VBA (Alt+F11)
  • создаем стандартный модуль (InsertModule)
  • переходим на лист, формулы в котором надо преобразовать, жмем Alt+F9, выбираем макрос Change_Style_In_Formulas и жмем Выполнить

Сразу после запуска сначала будет предложено выбрать тип ссылок:

  1. Все абсолютные;
  2. Абсолютная строка/Относительный столбец;
  3. Относительная строка/Абсолютный столбец;
  4. Все относительные

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

Примечание: данный код не учитывает формулы массива и после работы кода такие формулы станут обычными.
Так же код может некорректно работать с длинными и сложными формулами, и формулами внутри умных таблиц — особенность VBA. Для таких формул, если их преобразование завершилось ошибкой, изменения не будут применены и формула останется такой же как была

Статья помогла? Поделись ссылкой с друзьями!

Источник:
http://www.excel-vba.ru/chto-umeet-excel/kak-pomenyat-v-formulax-otnositelnye-ssylki-na-asolyutnye-i-naoborot/

Вставка гиперссылок на листы в Excel макрос

Вставка гиперссылок на листы в Excel макрос

Добрый день уважаемые читатели блога! После небольшого перерыва возобновляю работу по Excel. Вопросов накопилось довольно много — буду отвечать на них по очереди.

Сегодня речь пойдёт о гиперссылках в Excel, точнее об их автоматической вставке на лист. Представим ситуацию, когда в нашей книге листов огромное количество, бухгалтерские расчёты, к примеру.

Вставить вручную гиперссылки можно, пусть для этого и понадобится чуть больше времени. Обычно мы вставляем гиперссылки через вкладку «Вставка» и кнопку «Гиперссылка», а затем переходим на пункт «Место в документе».

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

Вставим в наш документ новый модуль:

  1. Вкладка «Разработчик», блок кнопок «Код», кнопка «Visual Basic»;
  2. Далее «Insert» — > «Module».

Поместим в поле кода следующий текст:

Sub SheetNamesAsHyperLinks()
Dim sheet As Worksheet
Dim cell As Range
With ActiveWorkbook
‘ Для каждого листа (имени) создадим свою ссылку
For Each sheet In ActiveWorkbook.Worksheets
Set cell = Worksheets(1).Cells(sheet.Index, 1)
.Worksheets(1).Hyperlinks.Add Anchor:=cell, Address:=»», _
SubAddress:=» » & sheet.Name & «!A1»
cell.Formula = sheet.Name
Next
End With
End Sub

  • у нас есть две переменные «sheet» и «cell», лист и ячейка;
  • «With activeWorkbook» — показывает, что макрос будет оперировать пространством всей книги;
  • » For Each sheet In ActiveWorkbook.Worksheets
    Set cell = Worksheets(1).Cells(sheet.Index, 1)
    .Worksheets(1).Hyperlinks.Add Anchor:=cell, Address:=»», _
    SubAddress:=» » & sheet.Name & «!A1″ » » — для каждого листа в рабочей книге мы будем использовать первую ячейку (А1);
  • » cell.Formula = sheet.Name» — формула для макроса будет помещение в ячейку A1 названия первого листа книги, далее в A2 второго и т.д.

Таким образом, мы экономим себе время на переходах, так как не отвлекаемся на нижнюю часть экрана с именами листов, а имеем список листов в виде гиперссылок. Понаблюдаем за результатами работы. Нажимаем сочетание клавиш ALT+F8 или переходим на вкладку «Разработчик», нажимаем кнопку «Макросы», у нас единственная строка «SheetNamesAsHyperLinks», нажимаем «Выполнить».

На листе «Оглавление» появится список всех существующих листов в нашей рабочей книге.

Теперь мы можем просто щёлкать ячейку с именем листа для перехода.

Читайте также  Как сделать ячейку активной в excel vba?

Если что-то было непонятно, посмотрите наше видео!

Вас может заинтересовать урок — Поменять строки местами.

Источник:
http://pcandlife.ru/vstavka-giperssilok-na-listi-v-excel-makros/

Стиль ссылок в формулах в среде VBA

ПРАКТИЧЕСКАЯ РАБОТА

Практикум по программированию

наязыке Visual Basic for Application

Методические указания

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

Термином «макрос» обычно называют файл, хранящий последовательность действий, заданных пользователем системы. Каждый макрос должен иметь собственное имя. Макрос– это именованная последовательность заданных пользователем команд и действий, хранящаяся в форме программы на языке VBA(VisualBasicforApplication). Язык программирования (VisualBasic) поддерживает идеологию объектно–ориентированного программирования, является языком визуального проектирования приложений и языком управления событиями. Событие –распознаваемое объектом действие, для которого можно запрограммировать отклик.[1]Файл с макросами помогаетавтоматизировать типовые технологические этапы при работе с системой. Если макрос создан, то после его запуска хранящаяся в нем последовательность действий (команд) будет автоматически исполнена.

Макрос представляет собой программу и может быть создан автоматически в специальном режиме работы программной среды (в том числе и Excel) или как результат программирования (VBA), отредактирован, могут быть устранены ошибки и добавлены новые возможности.Средство VBA в MSExcelявляетсяуниверсальным инструментом для быстрого и точного решения любых пользовательских задач в MSExcel. Готовый файл с макросами нужно сохранить. Для этого необходимо войти по вкладке «Файл», команда «Сохранить как…», в выпадающем списке выбрать «Книга Excel с поддержкой макросов» (рисунок 1).

Рисунок 1. Сохранение файла
с макросом

При первоначальном запуске системы вкладка «Разработчик», в группе «Код» которой расположены «VisualBasic», «Макросы», «Безопасность макросов», «Запись макроса» и «Относительные ссылки» может отсутствовать, поэтому необходимо изменить основные параметры работы с Excel. Для этого выберем вкладку«Файл», команду «Параметры» / «Настройка ленты» и поставить галочку для вкладки «Разработчик» (рисунок 2):

Рисунок 2. Добавление вкладки «Разработчик» на ленту

В начале работы диалоговое окно Макрос, вызываемое пунктом Макросы, может показать пустой список. Вкладка «Разработчик»/«Код» «Безопасность макросов» открывает дополнительное окно, позволяющее задавать уровень безопасности при его использовании (рисунок 3).

Рисунок 3. Параметры макросов

При работе с Excel, как, и с другими программами пакета MicrosoftOffice, для создания макроса легче всего использовать автоматический режим, вызываемый по вкладке «Разработчик», группа «Код»команда «Запись макроса». В этом случае применяется стандартное средство записи – MacroRecorder, посредством которого можно записывать последовательность действий пользователя, которые автоматически преобразуются в программный код на языкеVBA.

При выборе команды «Запись макроса» открывается диалоговое окно, позволяющее задать имя макроса и, комбинацию клавиш, с помощью которой он может быть вызван, выбираем пунктНачать запись, по умолчанию система предлагает стандартное имя Макрос#,напишитесобственное имя макроса, отличное от стандартного. С этого момента все Ваши действия записываются в файл макроса. Остановить запись макроса можно кнопкой«Остановить запись» дополнительно открывшейся панели инструментов или по вкладке «Разработчик», группа «Код», команда «Остановить запись». Записанный макрос может быть сохранен в текущей рабочей книге, в личной книге макросов. В последнем случае он может быть доступен в любой открытой книге.

Удалить макрос, созданный в текущей рабочей книге, можно кнопкой «Удалить» диалогового окна по вкладке «Разработчик», группа «Код», команда«Макросы».

После записи макроса его можно редактировать, для этого необходимо выделить макрос и в диалоговом окне выбрать кнопку «Изменить», откроется окно редактораVisualBasicforApplication.

Рисунок 4. Редактирование макроса

РедакторVisualBasicвызывается по вкладке «Разработчик», группа «Код», команда«VisualBasic».

Рисунок 5. Вызов редактора VBA

Для быстрого запуска макроса можно создать кнопку и присвоить ей имя макроса. Для этого выбираем вкладку «Разработчик», группу «Элементы управления» и кнопку «Вставить», появляется выпадающее меню «Элементы управления формы». Выбрав элемент управления «Кнопка» размещаем его на экране (рисунок 6). Появляется окно «Назначить макрос объекту» (рисунок 7), в котором вводим название макроса или выбираем из списка уже имеющийся. Осталось только переименовать вновь созданную кнопку (выделить старое название «Кнопка1» и написать новое).

Рисунок 6. Добавление элементов управления

Рисунок 7. Назначение макроса объекту

Стиль ссылок в формулах в среде VBA

В среде VBA существует два типа записи ссылок на ячейки в ExcelA1и R1C1. По умолчанию, при программировании формул, используется стильA1, для которого адрес каждой ячейки представляет собой строку символов, содержащую имя столбца и номер строки. Использование этого стиля позволяет организовать относительную и абсолютную адресацию (вводится символ $) к ячейкам таблицы. Тем не менее,иногда при записи макросовExcel использует тип ссылки R1C1. В обозначении этого типа присутствуют первые буквы английских слов Row(строка) и Column (колонка). Обратите внимание на то, что, в отличие от типа A1, при использовании типа ссылок R1C1сначала записывается строка, а потом столбец. При использовании абсолютной адресации после символов R и C указывается собственно номер строки и столбца. При использовании относительной адресации в стиле R1C1, после обозначения строки или колонки в квадратных скобках указывается смещение по отношению к текущей ячейке.

  • RC — относительная ссылка на текущую ячейку
  • R3C2 — то же самое, что $B$6 (абсолютная ссылка)
  • RC5 — ссылка на ячейку из пятого столбца в текущей строке
  • RC[-1] — ссылка на ячейку из предыдущего столбца в текущей строке
  • RC[2] — ссылка на ячейку, отстоящую на два столбца правее в той же строке
  • R[2]C[-3] — ссылка на ячейку, отстоящую на две строки ниже и на три столбца левее от текущей ячейки

· R5C[-2] — ссылка на ячейку из пятой строки, отстоящую на два столбца левее текущей ячейки

Для включения стиля R1C1 необходимо перейти по вкладке «Файл» команда «Параметры» / «Формулы» и поставить галочку в разделе «Работа с формулами» стиль ссылок R1C1.

Основные понятия языка VBА[2]

Объекты – рабочая книга, рабочий лист и его составляющие

Sheet, Worksheets – лист Excel

Cell – ячейка

Range – диапазон ячеек

Application – приложение

UserForm – пользовательская форма

Доступ к объекту возможен через его методы и свойства.

Cells(1, 1) – ячейка А1

Cells(1, 2) — ячейка В1

Range(Cells(1, 1) , Cells(5, 3)) – диапазон А1:С5

Методы

Над объектом можно совершать различные действия, которые называются методами.

Например, ячейку можно очистить (Clear), приложение закрыть (Quit), пользовательскую форму показать (Show) или скрыть (Hide).

Selection– выбирает объект или группу объектов.
Activate– активизирует выбранный объект.

Название метода отделяется от названия объекта точкой: объект.метод.

Примеры использования методов:

Range(“B2:E2”).Select – выбрать диапазон ячеек B2:E2;

Range(“C1:C5”).Clear – очистить диапазон ячеек C1:C5;

Worksheets(“Лист1”).Activate – активизирует Лист1;

UserForm2.Hide – скрыть форму № 2;

UserForm5.Show – показать форму № 5;

Application.Quit – выйти из приложения.

Свойства

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

Range(“D1”).Value = 2005 – поместить в ячейку D1 значение 2005

Range(“A11”).Value = “информатика” – поместить в ячейку A11 текст «информатика»

Источник:
http://megaobuchalka.ru/11/53052.html