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.
' http://www.vbaexpress.com/kb/getarticle.php?kb_id=237
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 & "),""" & _
                      rtsDisplayText & """)"
End Function

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

  Hyperlink_dynamic = "=HYPERLINK(""[Book1]"" & _
                          CELL(""address"",Sheet2!R11C11), _
                          CELL(""address"",Sheet2!R11C11))"

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

Thanks y'all.

BTW, I'm cross-posting from here.

 

[Hui]

It will be something like

Hyperlink_dynamic = "=HYPERLINK(""[Book1]"" & _
                          CELL(""address""," & Sheet2.range("K11").text & "), _
                          CELL(""address""," & Sheet2.range("K11").text & "))"

Each part must be text so that the whole string is equivalent to the previous macro recorded string

 

[Dr. Demento]

Thank you. I will give that a try.

Apologies for the belated thank you.

 

[Dr. Demento]

That's not really working. When I paste it directly, the text is red (presumably missing a " somewhere?

My apologies; I should have paid better attention to the source (VBAExpress.com), as it was there the whole time.

Final code:

  Hyperlink_dynamic = "=HYPERLINK(""[" & strPath & gnrAnchor.Parent.Parent.name & "]"" & " & _
                                  "CELL(""address"",'" & gnrAnchor.Parent.name & "'!" & gnrAnchor.Address & ")," & _
                                  "CELL(""address"",'" & gnrAnchor.Parent.name & "'!" & gnrAnchor.Address & "))"

Thanks again.