Извлечь URL из ячейки, к которой применена формула = Hyperlink()

Question

Я хочу получить URL из ячейки, к которой применена эта формула.

=HYPERLINK(CONCATENATE("https://loremipsum.com/#/Advertiser/",[@[Customer CID]],"/.html"), "View")

Формула имеет структурированную ссылку на один из столбцов на моем листе, «Customer CID».

Когда я пытаюсь применить этот макрос к моему листу, он выдает значение по умолчанию, даже когда формула оценивает правильный URL.

Function GetURL(cell As Range, Optional default_value As Variant)
      If (cell.Range("A1").Hyperlinks.Count <> 1) Then
          GetURL = default_value
      Else
          GetURL = cell.Range("A1").Hyperlinks(1).Address
      End If
End Function

Но когда я не применяю формулу и не добавляю гиперссылку к ячейке, щелкая правой кнопкой мыши по ячейке, макрос funtion =GetUrl([@[Customer CID]], "") работает и дает мне URL.

Кто-нибудь знает, как я могу выполнить эту задачу для получения гиперссылки из ячейки, если эта ячейка оценивает гиперссылку из формулы?

Answer 1

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

Это модифицированная версия вашего кода, которая делает это:

Function GetURL(cell As Range, Optional default_value As Variant)
  With cell.Range("A1")
    If .Hyperlinks.Count = 1 Then
      GetURL = .Hyperlinks(1).Address
    Else
      If Left$(Replace(Replace(Replace(.Formula, " ", ""), vbCr, ""), vbLf, ""), 11) = "=HYPERLINK(" Then
        Dim idxFirstArgument As Long: idxFirstArgument = InStr(.Formula, "(") + 1
        GetURL = Evaluate(Mid$(.Formula, idxFirstArgument, InStrRev(.Formula, ",") - idxFirstArgument))
      Else
        GetURL = default_value
      End If
    End If
  End With
End Function

Обратите внимание, что любые посторонние пробелы или добавленные разрывы строк в формуле учитываются должным образом.

Предостережения:

• Это будет работать только с формулами с самой внешней функцией HYPERLINK() . (Тем не менее, каждая формула может быть реорганизована так, что HYPERLINK() является внешним, с незначительным недостатком; поочередно все формулы могут быть реорганизованы в одну из форм =IF(…,…,HYPERLINK()) или =HYPERLINK() , без недостатков, требующих лишь незначительной модификации кода, и наконец, если приложить немало усилий, можно написать код для анализа любой формулы независимо от того, где находится функция HYPERLINK() .);
• Если после запятой есть какие-либо запятые, разделяющие первый и второй аргументы функции HYPERLINK() , код будет разбит (можно относительно легко исправить).