Convert macro output (formula) to VBA

[Dr. Demento]

I'm stuck in how to replace a term with the appropriate VBA code. The goal is to have a dynamic hyperlink that changes not only anchor but also the Display Text. I realize the code as it stands is circular, but I can figure out how to make it work if I could only get the syntax correct.

The original looks like this:

Function Hyperlink_dynamic(gnrAnchor As range, _
                           rtsDisplayText As String) As String
' ~~ Puts a dynamic hyperlink formula in the destination cell. _
     Formula automatically adjusts if its target is moved.
' [URL]http://www.vbaexpress.com/kb/getarticle.php?kb_id=237[/URL]
Dim strPath As String
  
   'This next statement avoids an error if the target workbook hasn't been saved
  If gnrAnchor.Parent.Parent.path <> vbNullString Then _
    strPath = gnrAnchor.Parent.Parent.path & Application.PathSeparator
    
  Hyperlink_dynamic = "=HYPERLINK(""[" & strPath & gnrAnchor.Parent.Parent.name & "]"" & " & _
                      "CELL(""address"",'" & gnrAnchor.Parent.name & "'!" & gnrAnchor.Address & "),""" & _
                      [B][COLOR=#ff0000]rtsDisplayText [/COLOR][/B]& """)"
End Function

However, I want replace rtsDisplayText term with the VBA equivalent of this code:

   Hyperlink_dynamic = "=HYPERLINK(""[Book1]"" & _
                          CELL(""address"",Sheet2!R11C11), _
                          [B][COLOR=#0000ff]CELL(""address"",Sheet2!R11C11))"[/COLOR][/B]

where Sheet2!R11C11 = gnrAnchor.Parent.name & "'!" & gnrAnchor.Address

Thanks y'all.

 

[Dr. Demento]

Bump

 

[Dr. Demento]

Any takers?? Is additional info needed? Let me know if it's unclear.