Extrai a URL de uma célula com uma fórmula = Hyperlink () aplicada a ela

Question

Eu quero buscar o URL de uma célula que tenha essa fórmula aplicada a ele.

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

A fórmula tem uma referência estruturada a uma das colunas da minha planilha, 'Customer CID'.

Quando tento aplicar essa macro à minha planilha, ela fornece o default_value mesmo quando a fórmula está avaliando uma URL correta.

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

Mas quando eu não aplico a fórmula e adiciono um Hyperlink à célula clicando com o botão direito do mouse na célula, a macro funtion =GetUrl([@[Customer CID]], "") funciona e me fornece o URL.

Alguém sabe como posso executar essa tarefa para buscar Hyperlink de uma célula se essa célula está avaliando o hiperlink de uma fórmula ??

    

Answer 1

Não existe uma maneira direta de obter o URL de uma célula com um hiperlink gerado por uma fórmula. Você precisa extrair o primeiro argumento da função HYPERLINK() e avaliá-lo manualmente.

Esta é a versão modificada do seu código que faz isso:

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

Observe que quaisquer espaços estranhos ou quebras de linha adicionadas na fórmula são devidamente explicados.

Advertências:

• Isso só funcionará em fórmulas com uma função HYPERLINK() mais externa. (No entanto, todas as fórmulas podem ser refatoradas para que HYPERLINK() seja o mais externo, com apenas uma pequena desvantagem; alternativamente, todas as fórmulas podem ser refatoradas para um dos formulários =IF(…,…,HYPERLINK()) ou =HYPERLINK() , sem inconvenientes e exigindo apenas uma pequena modificação no código; finalmente, com bastante esforço, o código poderia ser escrito para analisar qualquer fórmula não importando onde a função HYPERLINK() estivesse situada.
• Se houver alguma vírgula após a vírgula delimitando o primeiro e o segundo argumentos da função HYPERLINK() , o código será interrompido (pode ser corrigido com relativa facilidade).