Excel макрос сделать ссылку
Если Вы уже записывали макросы обработки таблиц, то наверняка сталкивались с ситуацией, когда макросом в таблицу добавляется столбец с формулами, которые потом необходимо распространить на все строки. Но если количество строк в таблице изменяется, то макрос работает некорректно: если строк стало больше, то формулы проставляются не на все строки, а если строк стало меньше – то появляются строки с лишними формулами.
Если Вы еще не знаете что такое макрос и как его записывать и воспроизводить, то рекомендуется сначала ознакомиться со статьей: Что такое макрос и где его искать?
К примеру, возьмем таблицу такого вида:
В конце таблицы нам необходимо добавить столбец «Стоимость», прописав в нем нехитрую формулу перемножения количества на цену:
= F2 * G2
Перед записью макроса выделяем ячейку H1 . При обычной записи макроса наши шаги такие:
1. Выделили I1
2. Записали в неё заголовок «Стоимость»
3. Перешли в I2
4. Записали формулу: = F2 * G2
5. Распространили формулу до конца таблицы (через автозаполнение или путем копирования ячейки с формулой и вставки в остальные ячейки)
Макрос работает отлично. Пока количество строк не изменится. Если при записи макроса в таблице было 319 строк, а потом добавилось еще 20, то записанный макрос создаст формулу только в первых 319 строках. Все дело в том, что при обычной записи макрос использует абсолютную адресацию ячеек. Т.е. в нем каждый наш шаг обозначает выделение ячеек с конкретно указанным адресом (I1, I2, I319 и т.д.):
Как выйти из такой ситуации? Все не слишком сложно. В группе кнопок код на вкладке Разработчик есть кнопка «Относительные ссылки». Если нажать её до записи макроса(или во время), то ссылки на ячейки будут уже запоминаться не как конкретный адрес, а как смещение относительно последней выделенной ячейки.
Например, запишем два простых макроса, которые будут делать одно и то же действие – перемещение вниз таблицы и выделение ячеек от нижней до верхней. Только первый макрос будет записан обычным способом, а перед записью второго мы нажмем кнопку «Относительные ссылки». Наши действия будут следующими (одинаковыми для обоих макросов):
1. До записи макроса выделяем ячейку I2
2. Начинаем запись макроса
3. Выделяем ячейку H2
4. Комбинацией клавиш Ctrl + ↓ (стрелка вниз) перемещаемся вниз таблицы
5. Стрелка вправо (т.е. выделяем последнюю ячейку в столбце I)
6. Комбинацией клавиш Ctrl + Shift + ↑ (стрелка вверх) выделяем столбец I от последней ячейки до первой
7. Завершаем запись макроса
Теперь можно посмотреть на код обоих макросов:
Отличия очевидны: в первом используется обращение к ячейкам по их конкретным адресам. Во втором же все действия происходят относительно последней выделенной ячейки(на Range("A1") не обращаем внимания – это из другой оперы и если их удалить ничего не изменится). Из этого можно сделать вывод, что для создания гибких универсальных макросов с использованием относительных ссылок необходимо как можно меньше использовать мышку и максимально стараться применять горячие клавиши. Попробую пояснить почему: когда мы применяем то же автозаполнение (наведение курсора мыши на нижний правый угол ячейки и протягивание вниз или двойной щелчок левой кнопкой мыши) – оно применяется к конкретно определенному количеству ячеек. Т.е. даже относительные ссылки не помогут заполнить его формулами, как того требует наша изначальная задача. Но если использовать горячие клавиши перемещения и выделения ( Ctrl + стрелка и Ctrl + Shift + стрелка ), то мы можем создать макрос, которому уже будет не важно сколько строк в нашей таблице. Чтобы в этом убедиться, запишем макрос из начала статьи, но уже с использованием относительных ссылок и исключительно клавиш для перемещения. Наши действия:
1. Перед записью макроса выделяем ячейку H1
2. Начали запись макроса
3. Нажимаем кнопку Относительные ссылки(если она еще не нажата)
4. Выделяем I1
5. Записываем в неё заголовок «Стоимость»
6. Переходим в I2
7. Записываем в I2 формулу: = F2 * G2
8. Комбинацией клавиш Ctrl + C (или при помощи контекстного меню мыши) копируем ячейку с формулой
9. Стрелкой вправо перемещаемся в ячейку H2
10. Комбинацией клавиш Ctrl + ↓ (стрелка вниз) перемещаемся вниз таблицы
11. Стрелка вправо (т.е. выделяем последнюю ячейку в столбце I)
12. Комбинацией клавиш Ctrl + Shift + ↑ (стрелка вверх) выделяем столбец I от последней ячейки до первой
13. Комбинацией клавиш Ctrl + V вставляем скопированную формулу
14. Нажимаем Esc для сброса буфера обмена
15. Запись макроса можно завершить
Если теперь попробовать применить такой макрос к таблице, у которой строк больше или меньше, чем было при записи макроса – все пройдет идеально. Макрос создаст столбец и запишет в нем формулу только на нужное количество строк.
Более того. Если наша таблица находится уже в другом листе и даже начинается не с первой ячейки, а где-то в середине:
Нам достаточно будет выделить ячейку заголовка последнего столбца(K5) и запустить наш макрос. Он без проблем добавит столбец с формулой в нужном месте и на все строки. Макрос же без использования относительных ссылок в такой ситуации спасует по полной: он создаст формулы начиная с ячейки I2 и до заголовка, только испортив таблицу и не сделав ничего полезного.
Так же хочу дополнить, что Относительные ссылки играют роль исключительно во время записи макроса. Во время воспроизведения совершенно не важно включены они или нет. Плюс можно(а иногда и нужно) комбинировать во время записи макросов режим относительных ссылок с обычным режимом. Например, когда столбцов в таблице у нас всегда одинаковое количество и таблица всегда в одном месте, и столбец мы добавляем всегда в столбец I. Но формулы при этом надо протягивать на разное количество строк. Тогда можно начать запись макроса обычным режимом, а после того, как записали название столбца - включить режим относительных ссылок, чтобы определение последней ячейки таблицы не зависело от количества строк в этой таблице.
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Поделитесь своим мнением
Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Hyperlinks.Add — это метод, который добавляет новую гиперссылку к указанному объекту Range (диапазону, ячейке) или объекту Shape (фигуре).
Синтаксис
Expression — выражение (переменная), возвращающее объект Hyperlinks.
Параметры
Параметр | Описание |
---|---|
Anchor | Объект Range или объект Shape, к которому привязывается (добавляется) новая гиперссылка. Обязательный параметр. Тип данных — Object. |
Address | Адрес гиперссылки. Обязательный параметр. Тип данных — String. |
SubAddress | Субадрес гиперссылки. Необязательный параметр. Тип данных — Variant. |
ScreenTip | Всплывающая подсказка, отображаемая при наведении указателя мыши на текст гиперссылки. Необязательный параметр. Тип данных — Variant. |
TextToDisplay | Текст гиперссылки. Необязательный параметр. Тип данных — Variant. |
Если текст гиперссылки (TextToDisplay) не указан, будет отображен текст параметров Address и SubAddress.
Создание гиперссылок
Гиперссылка на другой лист
Создание гиперссылки на рабочий лист другой книги:
ActiveSheet . Hyperlinks . Add Anchor : = Range ( "A4" ) , Address : = "C:\Users\Evgeniy\Desktop\Книга2.xlsx" , _
SubAddress : = "Лист3!D5" , ScreenTip : = "Гиперссылка на Лист3!D5 в Книга2" , TextToDisplay : = "Книга2 - Лист3!D5"
Создание гиперссылки на другой лист текущей книги:
Создание гиперссылки на другую ячейку того же листа:
Гиперссылка на любой файл
Создание гиперссылки на документ Word:
ActiveSheet . Hyperlinks . Add Anchor : = Range ( "A1" ) , Address : = "C:\Users\Evgeniy\Documents\Документ 1.docx" , _
Создание гиперссылки на точечный рисунок:
ActiveSheet . Hyperlinks . Add Anchor : = Range ( "A1" ) , Address : = "C:\Users\Evgeniy\Documents\Рисунок 1.bmp" , _
Гиперссылка на интернет-сайт
Пример добавления гиперссылки на рубрику «VBA Excel» сайта «Время не ждёт»:
ActiveSheet . Hyperlinks . Add Anchor : = Range ( "A1" ) , Address : = "https://vremya-ne-zhdet.ru/category/vba-excel/" , _
Поиск гиперссылок в диапазоне
Поиск первой ячейки с гиперссылкой в заданном диапазоне:
Для поиска последней ячейки с гиперссылкой в заданном диапазоне следует заменить строку
For i = 1 To n
на
For i = n To 1 Step -1 .
9 комментариев для “VBA Excel. Метод Hyperlinks.Add (создание гиперссылки)”
Здравствуйте, Евгений.
Подскажите пожалуйста, как можно определить адрес ячейки, которая содержит гиперссылку? А если ячеек с гиперссылками несколько, то как определить адрес последней (нижней правой) из них?
Здравствуйте, Юрий!
Ответ добавил в конец статьи.
Добрый день!
Скажите, пожалуйста, как сделать, чтобы в переменную Address:= вставлять не конкретный адрес, а скопированную ссылку из буфера обмена?
Заранее спасибо!
Решил проблему вот так вот:
ActiveSheet . Hyperlinks . Add Anchor : = Selection , Address : = GetClipBoardText ( ) , TextToDisplay : = "ссылка"
Добрый день!
У меня в очередной раз «зачудила» книга Excell. Сама поменяла родные адреса гиперссылок в Инет во многих ячейках на один, которые понравившейся ей, меняя лишь концовку 1649, 1650, …. .
В отладчике VBA вышел на этот список подмены: Range("L10"). Hyperlinks.Items 1.Formyla[1…5] содержащий 5 вставляемых ею адресов. В VBA не удалось добраться до этих формул.
Скажите, пожалуйста, можно ли программно восстановить стоящие ранее адреса? И как это сделать? И можно ли ей запретить так своевольничать?
Здравствуйте, Павел!
Я не знаю, как восстановить предыдущую версию файла Excel. Есть что-то у разработчиков, но это не программно.
Код, который перезаписывает гиперссылки, поищите в модулях книги и листа. Процедуры, размещенные в этих модулях, могут запускаться самостоятельно при совершении определенных событий, к которым они привязаны.
Здравствуйте, Евгений!
Спасибо за ответ. Отрицательный ответ, когда книга шибко нужна, лишь стимулирует поиск решения. Буду делать ей “костыли”, гиперссылки превращу в обычный текст и, как лучший вариант, хотел бы запускать их программно без привязки к конкретной ячейке конкретного листа конкретной книги.
Скажите, пожалуйста, есть ли возможность программно запускать гиперссылку на открытие страницы в Инете? Без привязки к конкретной ячейке.
Здравствуйте, Павел!
Открыть страницу сайта можно методом FollowHyperlink. Этот метод может не работать в старых версиях Excel.
Здравствуйте, Евгений!
Спасибо за ответ. Предложенный Вами метод работает в моей версии.
В своих статьях я часто прикладываю примеры с кодами. Т.к. мой сайт ориентирован как на знающих программирование в Visual Basic for Application(VBA), так и на начинающих, я в примерах почти всегда на первом листе делаю кнопку, по нажатию которой можно сразу же перейти в редактор VBA на нужный код без каких-либо лишних телодвижений. Как-то меня уже просили разъяснить каким способом я это делаю. Вот сегодня на одном из форумов попросили в очередной раз, что и побудило меня сесть и написать эту статью. Итак, к сути.
На самом деле это не так уж и сложно. Для начала необходимо создать хоть какой-то код(макрос) внутри книги(предполагается, что это вы умеете уже. Если нет - статья в помощь). Предположим, что наш макрос расположен в модуле Module1 и называется он Макрос1. Теперь создадим кнопку для вызова этого макроса, если необходимо. А после этого создадим еще одну кнопку(я использую для этих целей автофигуры), по нажатию на которую мы и будем попадать сразу в тело нужного нам макроса.
Способ 1:
Жмем на созданной фигуре правой кнопкой мыши. Выбираем в появившемся меню Гиперссылка:
С прочими возможностями гиперссылок и методах из создания можно ознакомиться в статье: Что такое гиперссылка?
Способ 2:
Этот способ кажется мне более "замороченным" и не эстетичным. Я его практически не использую. Создаем еще одну процедуру(можно в отдельном модуле), в которой и прописываем переход в нужный модуль и в нужную процедуру:
Sub GoTo_Sub() Application.Goto "Module1.Макрос1" End Sub
А созданной кнопке назначаем выполнение именно этого макроса - GoTo_Sub. Главный недостаток этого метода в том, что придется для каждой процедуры либо создавать новую процедуру с переходом, либо действовать через всевозможные конструкции типа If. Then, Select Case , что не очень удобно. Но данный метод может быть использован и в других целях. Например, для перехода в конкретную процедуру при возникновении ошибки в другой процедуре.
Это простая, но весьма полезная функция, позволяющая создать живую ссылку на:
- любую ячейку на любом листе в книге
- именованный диапазон
- фрагмент умной таблицы
- внешний файл Excel на диске или на сервере в сети
- веб-страницу
Кроме того, эта функция умеет создавать заполненную форму письма email, что сильно помогает при рассылке типовых оповещений.
Синтаксис функции прост:
=ГИПЕРССЫЛКА( Адрес ; Текст )
- Текст - это видимый текст гиперссылки в ячейке (обычно выделенный синим цветом и подчеркиванием, как все гиперссылки), по которому щелкает пользователь. Может быть любым, это роли не играет.
- Адрес - место, куда мы хотим сослаться (и это самое интересное).
Давайте рассмотрим все варианты подробнее.
Ссылка на веб-страницу
Это самый простой и очевидный вариант применения нашей функции ГИПЕРССЫЛКА (HYPERLINK) . Первым аргументом здесь будет полная ссылка на веб-страницу (URL):
Обратите внимание, что адрес должен быть полным, т.е. обязательно начинаться с протокола (обычно "http"). При щелчке по созданной ссылке заданный сайт откроется в браузере по умолчанию.
Ссылка на ячейку внутри книги
Если нужна ссылка на ячейку или диапазон, находящийся внутри текущей книги, то адрес нужно будет указать следующим образом:
Если имя листа содержит пробелы, то его обязательно заключать в апострофы - иначе ссылка не сработает:
Ссылка на ячейку во внешней книге
Можно легко создать ссылку на ячейку во внешней книге. Тогда в качестве первого аргумента нужен будет полный путь к файлу (с расширением!), имя листа и адрес ячейки:
Аналогично внутренней ссылке, если имя листа содержит пробелы, то его необходимо дополнительно заключить в апострофы.
Ссылка на файл на сервере
Также поддерживаются сетевые ссылки, если файл сохранен, например, на корпоративном сервере в общей папке - в этом случае используется путь в формате UNC, начинающийся с двух обратных дробей:
Ссылка на именованный диапазон
Если вы создали именованный диапазон (например, через Формулы - Диспетчер имен - Создать), то можно спокойно использовать это имя в ссылке:
Если нужно сослаться на именованный диапазон во внешнем файле, то потребуется уточнить его имя и путь к нему, как в предыдущем пункте. Имя листа указывать уже не нужно:
Ссылка на умную таблицу или ее элементы
Если вы знакомы с умными таблицами, то знаете какая это полезная штука. При желании, можно легко создать с помощью функции ГИПЕРССЫЛКА (HYPERLINK) ссылку на любой нужный нам фрагмент умной таблицы или на нее целиком. Для этого в первом аргументе нужно указать имя таблицы (например Таблица1) и кодовое обозначение ее элемента:
Поддерживаются следующие обозначения:
Формирование заполненной формы письма email
Это относительно экзотический вариант применения функции гиперссылка, позволяющий создавать заполненный бланк электронного письма:
При щелчке по такой ссылке мы увидим вот такую красоту:
Можно указывать несколько адресатов, получателей обычной и скрытой копий, форматировать текст письма и т.д. Очень удобно для служебных уведомлений и рассылок. Единственное, что нельзя таким образом сделать - прикрепить вложения. Подробнее про все возможности и параметры такого способа рассказано в отдельной статье.
Использование нестандартных символов
Иногда можно сочетать функцию ГИПЕРССЫЛКА с функцией вывода нестандартных знаков СИМВОЛ (CHAR), которую мы уже разбирали. Это позволяет заменить простой текст ссылки на что-то более симпатичное:
Имеем две таблицы на разных листах одной книги: таблицу с заказами от клиентов (на листе Заказы) и таблицу с клиентской базой (лист Клиенты). Работая с таблицей заказов, хочется иметь возможность быстро переходить на таблицу с клиентами, чтобы просмотреть подробные данные по клиенту (адрес, ФИО директора и т.д.). То есть, другими словами, хочется в таблице заказов иметь гиперссылку в каждой строке, при щелчке мышью по которой будет происходить переход на лист Клиенты, причем именно на ту строчку где упоминается данный клиент:
Что-то типа типа функции ВПР (VLOOKUP), но не ради подстановки данных, а для быстрой ссылки из одной таблицы в другую.
Шаг 1. Создаем переменную с именем листа
Для создания гиперссылок в будущем нам понадобится каждый раз прописывать имя текущего файла и имя листа Клиенты, на который пойдут ссылки. Поэтому проще один раз создать переменную в памяти Excel с нужным значением и обращаться к ней потом по мере надобности.
В Excel 2007/2010 для этого можно воспользоваться вкладкой Формулы (Formulas) и кнопкой Диспетчер имен (Name Manager) . В более старых версиях выбрать в меню Вставка - Имя - Присвоить (Insert - Name - Define) . В открывшемся окне нажмите кнопку Создать (New) и введите туда имя переменной (я назвал ее для примера Мой_Лист) и формулу в строку Диапазон (Reference) :
=ПСТР(ЯЧЕЙКА("имяфайла";Клиенты!$A$1); ПОИСК("[";ЯЧЕЙКА("имяфайла";Клиенты!$A$1)) ;256) &"!"
Разберем эту конструкцию на составляющие для понятности:
- ЯЧЕЙКА("имяфайла";Клиенты!$A$1) - функция, которая по адресу заданной ячейки (А1 с листа Клиенты) выдает любые нужные данные по листу и файлу. В данном случае - полный путь к текущему файлу до листа в виде D:\Рабочие документы\Договоры[Бюджет.xls]Клиенты
- Из этой строки нам нужна только часть с именем файла и листа (без диска и папок), поэтому мы ищем первое вхождение квадратной открывающей скобки в строку с помощью функции ПОИСК (FIND) и затем вырезаем из строки все, начиная с этого символа и до конца (256 символов) с помощью функции ПСТР (MID) .
- В конце, к вырезанному фрагменту с именем файла и листа приклеиваем восклицательный знак - стандартный разделитель имен листов и адресов ячеек в формулах, т.к. дальше должны будут идти адреса ячеек.
Таким образом эта формула выдает на выходе имя текущего файла в квадратных скобках с расширением с приклееным к нему именем листа и восклицательным знаком. Работу формулы легко проверить - просто введите в любую пустую ячейку =Мой_Лист и нажмите клавишу Enter.
Шаг 2. Создаем гиперссылки
Выделите пустую ячейку в строке напротив первого заказа и введите туда вот такую формулу:
Разберем ее на составляющие аналогичным образом:
- Функция ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0) - ищет порядковый номер ячейки в диапазоне А1:А7 на листе Клиенты, где встречается название текущего клиента из B2 (последний аргумент =0 означает поиск точного совпадения, аналогично функции ВПР)
- Функция АДРЕС формирует адрес ячейки (в виде текстовой строки) по номеру строки и столбца, т.е. адрес ячейки с нужным клиентом, куда должна потом ссылаться гиперссылка
- Затем мы приклеиваем к адресу ссылку на файл и лист (переменную Мой_Лист) и используем это в качестве аргумента для функции ГИПЕРССЫЛКА (HYPERLINK) , которая, собственно, и создает нужную нам ссылку.
При желании, можно заменить внешнее представление гиперссылки с банальных символов ">>" на что-нибудь поинтереснее с помощью функции СИМВОЛ (CHAR) , которая умеет выводить нестандартные символы по их кодам:
=ГИПЕРССЫЛКА(Мой_Лист&АДРЕС(ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0);1);СИМВОЛ(117))
Так, например, если использовать шрифт Wingdings 3 и символ с кодом 117, то можно получить вот такие симпатичные значки гиперссылок:
Читайте также: