VBA: Zieladresse für HYPERLINK()-Funktion auslesen

Forumthread: VBA: Zieladresse für HYPERLINK()-Funktion auslesen

17.05.2016 16:06:45

NoNet

Hallo Leute,
ich habe folgendes Problemchen (trotz langem Testen, Googlen und in der Beitragsarchiv-Suche habe ich keine Lösung gefunden und versuche es daher hier im Forum) :
In einer Tabelle habe ich einige Werte und Funktionen in Verbindung mit der HYPERLINK()-Funktion. Der angezeigte Zellwert entspricht dabei NICHT dem erzeugten Hyperlink. Ein vereinfachtes Beispiel findet ihr hier :
XLSM Datei auf OneDrive
Der VBA-Code müsste nun also z.B. für die Zelle C7 die Hyperlink-Adresse http://www.mein-toller-link.de/04711 ermitteln.
Die Problematik besteht darin, dass Excel per VBA keine Hyperlinks erkennt, die per =HYPERLINK(...) in einer Zelle erstellt wurden, die folgende VBA-Anweisungen ergeben also immer das Resultat 0 :

MsgBox ActiveSheet.Hyperlinks.Count
MsgBox Range("C7").Hyperlinks.Count

Ich habe Ansätze gefunden und auch selbst ermittelt, um zu überprüfen, OB eine Zelle (evtl.) einen Hyperlink beinhaltet (genauer : ob der STRING "HYPERLINK(" in der Zellfunktion enthalten ist), aber das ist NICHT das, was ich benötige !
Für ganz "Schlaue" : Bitte keine Lösungsvorschläge wie "Prüfe doch einfach, welcher Wert in Spalte B steht" - Das wäre das Pferd von hinten aufgezäumt - das möchte ich nicht, denn es geht mir hier um eine grundsätzliche Möglichkeit, die (wie auch immer zusammengestzten) Hyperlink-Adressen auszulesen...
Vielleicht hat einer von euch einen Lösungsansatz ?
Viel Spaß und Erfolg beim Tüfteln und DANKE schonmal vorab :-)
Salut, NoNet

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Tja, die Xl-Fkt erzeugt keine echten HLs, ...

17.05.2016 16:45:46

Luc:-?

…NoNet;
damit hatte ich mich wohl vor 1-2 Jahren mal befasst und wohl auch in diesem Zusammenhang Lösungen gefunden. Jetzt müsste ich aber erstmal die damalige Datei finden… ;-)
Also bis dahin,
Gruß, Luc :-?

Habe jetzt nicht gesucht, sondern mal eben ...

20.05.2016 04:34:17

Luc:-?

…neu pgmmiert, NoNet;
falls du dich überhpt noch dafür interessierst, denn bisher hast du dich ja, wie leider von dir gewohnt, nicht noch mal gemeldet!
Grundlage ist bei meiner Lösung, dass die XlFkt als unechter HL natürlich weder die entsprd ZellEigenschaft begründet noch das von dir verwendete Ereignis auslöst. Was aber immer zuerst bei Klick auf einen Zell-HL, egal welcher Art, ausgelöst wird, ist das SelectionChange-Ereignis. Folglich kannst du nur das verwenden und kommst dann um das Parsen der Fml nicht herum:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const txHL$ = "HYPERLINK(", txRelSym$ = "(,)"
Dim i As Long, k(1) As Long, p(2) As Long, fmHL As String, erg As Variant
On Error Resume Next
If Target.HasFormula Then
If Target.Text  "" And CBool(InStr(Target.Formula, txHL)) Then
fmHL = Split(Target.Formula, txHL)(1)
k(0) = Len(fmHL) - Len(Replace(fmHL, Left(txRelSym, 1), ""))
k(1) = Len(fmHL) - Len(Replace(fmHL, Right(txRelSym, 1), ""))
For i = 1 To k(1) - k(0)
fmHL = Left(fmHL, InStrRev(fmHL, Right(txRelSym, 1)) - 1)
Next i
erg = Me.Evaluate(fmHL)
Do While IsError(erg)
For i = 0 To 2
p(i) = InStrRev(fmHL, Mid(txRelSym, i + 1, 1))
Next i
If CBool(p(1)) Then
If p(0)  p(2) Then
fmHL = Left(fmHL, p(1) - 1)
ElseIf Len(fmHL) > 1 Then
fmHL = Left(fmHL, Len(fmHL) - 1)
Else: Exit Do
End If
ElseIf CBool(p(2)) Then
fmHL = Left(fmHL, p(2))
ElseIf Len(fmHL) > 1 Then
fmHL = Left(fmHL, Len(fmHL) - 1)
Else: Exit Do
End If
erg = Me.Evaluate(fmHL)
Loop
MsgBox IIf(IsError(erg), "Irregulärer HL", erg)
End If
End If
End Sub

Ich will mal hoffen, dass diese PgmVersion genügend HL-Varianten abdeckt. Evtl DezimalKommata müssen ggf getauscht wdn und ) in Texten (ohne ihr Ggstück) führen zu Fehlern.
Feedback nicht unerwünscht! Gruß, Luc :-?
Besser informiert mit …

Danke @alle - Gewohnheiten überprüfen ;-)

23.05.2016 16:24:48

NoNet

Hi @alle,
Danke erstmal für eure Beiträge. Ich dachte mir schon, dass es keine 100%-Lösung geben wird.
Die programmatische Ermittlung der "HYPERLINK("-Funktion bleibt wohl die einzige Lösung.
Luc's Code funktioniert auch recht gut - ich denke vorerst wird mir dieser Workaround (ergänzt um die Möglichkeit "echte" Hyperlinks zu überprüfen) genügen - Danke dafür.
@Luc : Den Zusatz "wie leider von dir gewohnt" solltest du nochmal überprüfen : Ich gebe zu meinen (wenigen) Fragen (fast ?) immer ein Feedback. Da ich aber (wie vielleicht dem ein oder anderen aufgefallen ist) in den letzten 2 Jahren nur noch sehr sporadisch hier ins Forum hinein schaue (aus Zeitgründen - nicht aus mangelndem Interesse !), dauert es hin und wieder einige Tage bis zum abschließenden Feedback - so wie auch in DIESEM Thread ;-).
Salut, NoNet

Hyperlinkadresse extrahieren...

18.05.2016 08:21:42

Case

Hallo NoNet, :-)
eine generelle Lösung habe ich nicht, aber einen Vorschlag bezogen auf Deine Beispieldatei: ;-)
https://www.herber.de/bbs/user/105613.xlsm
Servus
Case

Infobox / Tutorial

Zieladresse der HYPERLINK()-Funktion mit VBA auslesen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um die Zieladresse der HYPERLINK()-Funktion in Excel mit VBA auszulesen, kannst Du den folgenden VBA-Code verwenden. Dieser Code überprüft, ob die Zelle eine Formel enthält, die die HYPERLINK()-Funktion verwendet, und extrahiert die Adresse:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const txHL$ = "HYPERLINK("
    Dim fmHL As String, erg As Variant

    On Error Resume Next
    If Target.HasFormula Then
        If InStr(Target.Formula, txHL) > 0 Then
            fmHL = Mid(Target.Formula, InStr(Target.Formula, """") + 1)
            fmHL = Left(fmHL, InStr(fmHL, """") - 1)
            erg = Evaluate(fmHL)
            MsgBox IIf(IsError(erg), "Irregulärer Hyperlink", erg)
        End If
    End If
End Sub

Füge diesen Code in das Codefenster des Arbeitsblattes ein, um die Hyperlink-Adresse zu extrahieren, wenn Du auf die Zelle klickst.

Häufige Fehler und Lösungen

Fehler: "Irregulärer Hyperlink"
Dieser Fehler tritt auf, wenn die HYPERLINK()-Formel nicht korrekt ist. Überprüfe die Formel in der Zelle auf Syntaxfehler.
Fehler: Hyperlink wird nicht erkannt
Stelle sicher, dass Du den richtigen Zellbereich ansprichst und dass die Zelle tatsächlich eine HYPERLINK()-Formel enthält.
Problem: VBA erkennt keine Hyperlinks
Beachte, dass Excel die Hyperlinks, die mit der HYPERLINK()-Funktion erstellt wurden, nicht als echte Hyperlinks speichert. Der oben angegebene Code ermöglicht es dennoch, die Adresse aus der Formel zu extrahieren.

Alternative Methoden

Wenn Du die Hyperlink-Adressen aus einer Zelle extrahieren möchtest, kannst Du auch die Funktion HYPERLINK() direkt in einer anderen Zelle verwenden, um diese auszulesen. Alternativ dazu gibt es verschiedene Add-Ins oder Makros in der Excel-Community, die speziell dafür entwickelt wurden, um VBA Hyperlinks auszulesen.

Praktische Beispiele

Angenommen, Du hast in Zelle C7 die folgende Formel:

=HYPERLINK("http://www.dein-link.de", "Besuche meine Seite")

Wenn Du auf Zelle C7 klickst, wird der VBA-Code die Adresse http://www.dein-link.de in einer MessageBox anzeigen.

Tipps für Profis

Nutze die Evaluate-Funktion, um komplexere Formeln auszuführen und die Hyperlink-Adresse dynamisch abzurufen.
Teste den Code mit verschiedenen Hyperlink-Varianten, um sicherzustellen, dass er in allen Fällen funktioniert.
Achte darauf, dass Du den Code in der richtigen Arbeitsmappe und im richtigen Arbeitsblatt platzierst, um die gewünschten Ergebnisse zu erzielen.

FAQ: Häufige Fragen

1. Wie kann ich die Hyperlink-Adresse direkt in eine Zelle schreiben?
Du kannst die Adresse direkt in eine Zelle schreiben, indem Du den VBA-Code anpasst, um Range("ZielZelle").Value = erg hinzuzufügen.

2. Funktioniert dieser Code in allen Excel-Versionen?
Der Code sollte in den meisten modernen Excel-Versionen funktionieren, die VBA unterstützen, wie Excel 2010 und neuer.

3. Kann ich den Code auch für andere Hyperlink-Anwendungen verwenden?
Ja, Du kannst den Code anpassen, um auch andere Hyperlink-Anwendungen auszulesen, solange diese in einer Formel in der Zelle vorhanden sind.

Beliebteste Forumthreads (12 Monate)

VBA: Zieladresse für HYPERLINK()-Funktion auslesen

Forumthread: VBA: Zieladresse für HYPERLINK()-Funktion auslesen

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Zieladresse der HYPERLINK()-Funktion mit VBA auslesen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

VBA: Zieladresse für HYPERLINK()-Funktion auslesen

Forumthread: VBA: Zieladresse für HYPERLINK()-Funktion auslesen

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Zieladresse der HYPERLINK()-Funktion mit VBA auslesen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread