Копирование гиперссылки в excel vba
Обновлено: 04.07.2024
У меня есть электронная таблица, в которой много строк данных. Я хотел бы иметь возможность щелкнуть ячейку, которая будет запускать макрос, используя данные из этой строки. Поскольку количество строк всегда будет меняться, я думаю, что гиперссылка для каждой строки может быть лучшим способом.
Есть ли способ запустить макрос vba, щелкнув гиперссылку и узнав строку ячейки, которая щелкнула гиперссылку?
Это сработает для вас
Я думаю, что вместо того, чтобы создавать гиперссылку для каждой ячейки, лучше создать макрос, который ссылается на свойство Activecell . Затем создайте сочетание клавиш для запуска макроса. Для этого:
- Нажмите ALT + F8
- Выберите Options
- Выберите клавишу, которая будет сочетанием клавиш
Если у вас уже есть гиперссылки, лучше всего запустить макрос с помощью события Hyperlink_Follow . Если нет, то учтите мою рекомендацию.
На листе, на котором вы хотите выполнить двойной щелчок, используйте это:
А затем в Module1 есть процедура, которая будет обрабатывать активную ячейку:
Вы также должны отключить метод двойного щелчка в Excel в событиях книги:
Я считаю, что метод двойного щелчка действительно очень удобен и интуитивно понятен для многих процессов, например. создание UserForm с данными, заполненными из строки, или перенос этих данных на другой лист, возможно, с помощью вычислений или форматирования и т. д.
Более интересный способ запуска макроса с помощью гиперссылки, похоже, будет следующим подходом (формула гиперссылки). Не нужно никаких событий:
- Напишите формулу гиперссылки в ячейку. Это можно сделать и в VBA:
Более эффектным будет следующий подход, способный сохранить начальное значение ячеек обрабатываемого диапазона (например, "A1: A5"):
- Создайте функцию для вызова (в модуле):
- При нажатии на ячейку запускается функция MyFunctionkClick() .
Да, вы можете, для этого выполните следующие простые шаги:
- Выберите ячейку, в которой вы хотите сделать гиперссылку
- Щелкните правой кнопкой мыши -> Гиперссылка
- Введите адрес той же ячейки, в которой вы создаете гиперссылку, и дайте имя ссылке. См. Картинку ниже:
Назначить макрос гиперссылке
Примечание: щелчок по этой гиперссылке ничего не сделает, потому что она назначена тому же адресу ячейки.
- Теперь нажмите Alt + F11
- Скопируйте и вставьте приведенный ниже код
Запустите макрос Excel, щелкнув гиперссылку
В приведенном выше коде мы сравниваем адрес ячейки, а затем выполняем набор кода или функции. Есть и другой способ сделать это. Мы можем сравнить с целевым именем и выполнить код. В приведенном выше примере я дал имя цели гиперссылки как MyMacro.
Это простая, но весьма полезная функция, позволяющая создать живую ссылку на:
- любую ячейку на любом листе в книге
- именованный диапазон
- фрагмент умной таблицы
- внешний файл 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) - функция, которая по адресу заданной ячейки (А1 с листа Клиенты) выдает любые нужные данные по листу и файлу. В данном случае - полный путь к текущему файлу до листа в виде D:\Рабочие документы\Договоры[Бюджет.xls]Клиенты
- Из этой строки нам нужна только часть с именем файла и листа (без диска и папок), поэтому мы ищем первое вхождение квадратной открывающей скобки в строку с помощью функции ПОИСК (FIND) и затем вырезаем из строки все, начиная с этого символа и до конца (256 символов) с помощью функции ПСТР (MID) .
- В конце, к вырезанному фрагменту с именем файла и листа приклеиваем восклицательный знак - стандартный разделитель имен листов и адресов ячеек в формулах, т.к. дальше должны будут идти адреса ячеек.
Таким образом эта формула выдает на выходе имя текущего файла в квадратных скобках с расширением с приклееным к нему именем листа и восклицательным знаком. Работу формулы легко проверить - просто введите в любую пустую ячейку =Мой_Лист и нажмите клавишу Enter.
Шаг 2. Создаем гиперссылки
Выделите пустую ячейку в строке напротив первого заказа и введите туда вот такую формулу:
= ГИПЕРССЫЛКА(Мой_Лист& АДРЕС( ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0) ;1) ;">>")
Разберем ее на составляющие аналогичным образом:
- Функция ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0) - ищет порядковый номер ячейки в диапазоне А1:А7 на листе Клиенты, где встречается название текущего клиента из B2 (последний аргумент =0 означает поиск точного совпадения, аналогично функции ВПР)
- Функция АДРЕС формирует адрес ячейки (в виде текстовой строки) по номеру строки и столбца, т.е. адрес ячейки с нужным клиентом, куда должна потом ссылаться гиперссылка
- Затем мы приклеиваем к адресу ссылку на файл и лист (переменную Мой_Лист) и используем это в качестве аргумента для функции ГИПЕРССЫЛКА (HYPERLINK) , которая, собственно, и создает нужную нам ссылку.
При желании, можно заменить внешнее представление гиперссылки с банальных символов ">>" на что-нибудь поинтереснее с помощью функции СИМВОЛ (CHAR) , которая умеет выводить нестандартные символы по их кодам:
=ГИПЕРССЫЛКА(Мой_Лист&АДРЕС(ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0);1);СИМВОЛ(117))
Так, например, если использовать шрифт Wingdings 3 и символ с кодом 117, то можно получить вот такие симпатичные значки гиперссылок:
Подобно функции листа в Excel, мы также можем копировать данные вставки или набор значений в VBA. В обычных данных мы используем CTRL + C, чтобы скопировать выборку данных, а затем CTRL + V, чтобы вставить выбранные данные в целевую ячейку. Но то же самое в VBA делают определенные коды, которые мы узнаем в этой статье.
Как мы используем копирование и вставку в Excel VBA? Для этого сначала нам нужно активировать целевой лист или рабочую книгу, из которой мы хотим скопировать любой диапазон данных, и этот выбор копируется определенным кодом, когда этот выбор успешно скопирован, мы можем продолжить и вставить данные в целевой диапазон ячеек.
Синтаксис для копирования данных в VBA выглядит следующим образом:
Теперь синтаксис для вставки данных в другой лист или в любой другой диапазон ячеек выглядит следующим образом:
Мы используем оператор Dot (.) Для использования методов копирования и вставки в VBA.
Мы можем скопировать весь столбец и вставить его в другой столбец, а также мы также можем скопировать всю строку и вставить ее в другую строку. Мы узнаем все это в этой статье.
Примечание. Чтобы использовать VBA в Excel, убедитесь, что вкладка «Разработчик» включена из вкладки «Файл», а затем в раздел настроек параметров.
Как использовать Excel VBA Copy Paste?
Мы научимся использовать VBA Copy Paste с несколькими примерами в Excel.
Вы можете скачать этот шаблон VBA Copy Paste Excel здесь - Шаблон VBA Copy Paste Excel
VBA Copy Paste - Пример № 1
Для демонстрации у меня есть случайное значение в ячейке A1, которое я хочу скопировать и вставить в ячейку B1, используя код VBA. Посмотрите, какие данные находятся в ячейке A1, а ячейка B1 пуста.
Выполните следующие шаги, чтобы использовать Excel VBA Copy Paste:
Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic, чтобы открыть VB Editor.
Шаг 2: Как только редактор VB откроется, нажмите на кнопку вставки, а затем на кнопку Вставить модуль, чтобы вставить окно кода.
Шаг 3: Объявите подфункцию, чтобы начать писать код.
Код:
Шаг 4. Сначала активируйте лист, чтобы использовать свойства листа с помощью следующего кода.
Код:
Шаг 5: Скопируйте данные, которые находятся в ячейке A1, с помощью следующего кода.
Код:
Шаг 6: Теперь вставьте скопированные данные в целевую ячейку, которая является ячейкой B1, с помощью следующего кода.
Код:
Шаг 7: Запустите приведенный выше код с помощью кнопки запуска и посмотрите результат в ячейке B1.
VBA Copy Paste - Пример №2
У меня есть данные в столбце C, и я хочу скопировать все данные или значения и вставить их в столбец D, используя код VBA. Посмотрите ниже, что это за данные в столбце C, и что столбец D пуст.
Выполните следующие шаги, чтобы использовать Excel VBA Copy Paste:
Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic, чтобы открыть VB Editor.
Шаг 2: Нажмите на вставленный модуль, чтобы открыть окно кода,
Шаг 3: Объявите подфункцию, чтобы начать писать код.
Код:
Шаг 4. Сначала активируйте рабочий лист с помощью следующего кода.
Код:
Шаг 5: Скопируйте данные в столбце C с помощью следующего кода.
Код:
Шаг 6: Теперь, чтобы вставить данные в столбец D, используйте следующий код.
Код:
Шаг 7: Запустите следующий код с помощью кнопки запуска или нажмите F5.
Запустите код, чтобы увидеть следующий результат.
VBA Copy Paste - Пример № 3
Теперь для этого примера у меня есть целый диапазон данных в диапазоне ячеек G1: H3, и я хочу скопировать данные в диапазоне ячеек I1: J3. Посмотрите ниже на данные, которые у меня есть в диапазоне ячеек G1: H3, а диапазон ячеек I1: J3 пуст.
Выполните следующие шаги, чтобы использовать Excel VBA Copy Paste:
Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic, чтобы открыть VB Editor.
Шаг 2: Нажмите на вставленный модуль, чтобы открыть окно кода,
Шаг 3: Объявите подфункцию, чтобы начать писать код.
Код:
Шаг 4. Сначала активируйте лист, чтобы использовать его свойства с помощью следующего кода.
Код:
Шаг 5: Скопируйте данные в целевой диапазон ячеек с помощью следующего кода.
Код:
Шаг 6: Теперь, чтобы вставить данные в ячейку назначения, используйте следующий код.
Код:
Шаг 7: Запустите приведенный выше код с помощью кнопки запуска или нажмите F5, чтобы увидеть следующий результат.
VBA Copy Paste - пример № 4
Для этого примера у меня есть данные в строке 10, и я хочу вставить данные в строке 11. Посмотрите ниже, чтобы увидеть, какие данные находятся в строке 10, а строка 11 свободна.
Выполните следующие шаги, чтобы использовать Excel VBA Copy Paste:
Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic, чтобы открыть VB Editor.
Шаг 2: Нажмите на вставленный модуль, чтобы открыть окно кода,
Шаг 3: Объявите подфункцию, чтобы начать писать код.
Код:
Шаг 4. Активируйте рабочий лист, чтобы использовать свойства рабочего листа.
Код:
Шаг 5: Скопируйте строку 10 со следующим кодом.
Код:
Шаг 6: Вставьте данные строки 10 в строку 11 с помощью следующего кода.
Код:
Шаг 7: Запустите приведенный выше код, нажав F5, чтобы увидеть следующий результат.
То, что нужно запомнить
- Чтобы использовать данные из любого листа для их копирования, нам нужно сначала активировать лист.
- Точно так же, когда нам нужно вставить данные в любой другой лист в VBA, нам нужно сначала активировать целевой лист.
- Если мы копируем весь столбец или строку и вставляем данные в любой другой столбец, тогда данные в любом месте строки или столбца в целевых ячейках копируются и вставляются в целевые ячейки. Это может привести к определенным нежелательным данным.
- Лучший способ скопировать данные - скопировать определенный диапазон и вставить данные в целевые ячейки.
Рекомендуемые статьи
Это было руководство к VBA Copy Paste. Здесь мы обсудили, как использовать Excel VBA Copy paste, а также некоторые практические примеры и загружаемый шаблон Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Гиперссылка обычно используется с веб-сайтами для перехода с одной страницы на другую или с одного веб-сайта на другой в Интернете. Аналогичным образом, мы можем также контролировать движения в листе Excel. Различные операции, которые могут быть выполнены в Excel:
- Перемещение в определенное место в текущей рабочей книге.
- Открываем разные документы и выбираем указанную область внутри документа.
- Переход к веб-страницам с рабочего листа.
- Отправка электронной почты на указанный адрес.
Гиперссылка легко распознается из-за изменения цвета, в основном синего цвета. Существуют разные способы создания гиперссылки в Excel и использования VBA.
Как создать гиперссылку в Excel, используя код VBA?
Вы можете добавить гиперссылку на текст или один лист на другой лист в Excel, используя свойство добавления гиперссылки. Формат должен сопровождаться указанием, где гиперссылка должна быть создана и URL навигации и т. Д.
Формат для VBA Гиперссылка Добавить
Формат показывает параметры, которые необходимо указать для добавления гиперссылки на лист.
- Якорь: Определяет ячейку, которую вы хотите создать гиперссылку.
- Адрес: URL, по которому должна перейти навигация.
- (SubAddress): подадрес URL.
- (ScreenTip): значение указателя мыши, отображаемое при размещении указателя мыши.
- (Текст для отображения): текст должен отображаться в ячейке.
Используйте свойство Active cell, чтобы добавить гиперссылку.
Выберите метод добавления из списка
Примеры создания гиперссылок в Excel VBA
Ниже приведены различные примеры создания гиперссылок в Excel с использованием кода VBA.
Вы можете скачать этот шаблон гиперссылки VBA Excel здесь - Шаблон гиперссылки VBA Excel
Пример № 1 - Создание гиперссылки из рабочего листа на сайт
Мы хотим создать гиперссылку из рабочей таблицы с именем sub на веб-сайт, используя код VBA в Excel.
Ниже приведены шаги для создания гиперссылки в Excel VBA:
Шаг 1: Создайте функцию с именем hyper, чтобы добавить гиперссылку.
Код:
Шаг 2: Используйте активный объект ячейки, чтобы открыть метод добавления гиперссылки.
Код:
Шаг 3: Укажите значения параметров для метода добавления гиперссылки.
Код:
- Якорь: название листа
- Адрес: Гиперссылка, по которой следует перемещать элемент управления, учитывая адрес веб-сайта.
- ScreenTip: текст указателя мыши
- TextToDisplay: к какому тексту должна быть привязана гиперссылка
Шаг 4: Нажмите F5 или кнопку Run под VBE, чтобы запустить этот код и увидеть результат.
Пример №2 - Гиперссылка для соединения двух листов
У нас есть две таблицы с именами Home и sub . Давайте попробуем создать гиперссылку с юга на дом, используя код VBA.
Выполните следующие шаги, чтобы создать гиперссылку с одного листа на другой в той же книге, используя код VBA.
Шаг 1: Создайте функцию, где мы напишем все коды для выполнения действия. Напишите код для выбора «подчиненного» листа, используя метод выбора рабочего листа.
Код:
Поскольку элемент управления перемещается внутри листа, необходимо выбрать лист, в котором вы создаете гиперссылку.
Шаг 2: Выберите диапазон ячеек на листе, где будет создана гиперссылка.
Код:
Шаг 3: Теперь давайте добавим гиперссылку, используя свойство active cell.
Код:
Поскольку рабочая таблица уже выбрана, привязка задается как «Выбор». Гиперссылка указана как лист «Домой» и диапазон А1.
Шаг 4: Запустите код и на листе будет показана гиперссылка, как показано ниже.
Шаг 5: После нажатия на текст элемент управления будет перемещен на лист « Домой ». И ячейка А1 будет выделена.
Пример № 3 - Гиперссылка с несколькими листами
Если вы хотите создать гиперссылку на нескольких листах, это также возможно. В этом примере у нас есть несколько листов в одной книге. Существует другой тип функций Excel, так что из основного листа «Функции». Давайте попробуем создать гиперссылку на другой лист, названный разными функциями, используя код VBA:
Несколько рабочих листов названы ниже с различными именами функций Excel
Поскольку мы хотим создать гиперссылку на каждый лист, трудно повторить код. Для создания гиперссылки с использованием кода VBA в Excel выполните следующие действия:
Шаг 1: Создайте переменную для удобной работы с таблицей.
Код:
Шаг 2: Теперь мы хотим выбрать главную страницу, которая действует как индексную страницу, и выбрать диапазон ячеек A1.
Код:
Шаг 3: Для перемещения по нескольким рабочим листам и гиперссылкам мы создаем для каждого цикла. A1 уже выбран в качестве активной ячейки, поэтому создайте гиперссылку из этого.
Код:
Шаг 4. Укажите значения параметров, чтобы создать гиперссылку для каждого листа. Поскольку гиперссылка начинается с активной ячейки, якорь = активная ячейка, адрес задается как «» .
Код:
Шаг 5: Гиперссылка проходит через лист, поэтому мы должны указать подадрес в качестве имен листов. Чтобы получить имена листов, мы можем использовать переменную ws и диапазон ячеек как A1. Название листа будет указано с одинарной кавычкой. Имя листа и диапазон будут указаны, а также закрыты одинарной кавычкой.
Код:
Шаг 6: Чтобы получить гиперссылку с именем листа, TextToDisplay присваивается как ws.Name
Код:
Этот код будет хранить гиперссылки для каждого листа в той же ячейке A1.
Шаг 7: Чтобы изменить этот каждый лист на другую ячейку вниз на одну ячейку от активной ячейки.
Код:
Шаг 8: Запустите программу, и каждое имя листа будет отображено на листе «Функции», и при нажатии на него элемент управления переместится на соответствующий лист.
То, что нужно запомнить
- Свойство гиперссылки активной ячейки, используемой для создания гиперссылок в VBA.
- Гиперссылка поможет легко перемещаться по книге.
Рекомендуемые статьи
Это руководство по гиперссылкам VBA. Здесь мы узнаем, как создавать гиперссылки на рабочем листе, используя код VBA для быстрого перехода с одного листа на другой, а также некоторые практические примеры и загружаемый шаблон Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Читайте также: