VBA for setting active URL

[jesper117]

Hi. I really want an VBA function that "works like" =HYPERLINK, e.g. when execute as a forumla the link in the cell is clickabl afterwards. The closest I've found is:

Option Explicit
Function myHyperLink()

With Application.Caller
.Parent.Hyperlinks.Add Anchor:=.Cells, _
Address:="http://www.microsoft.com"
End With

myHyperLink = "Click me!"
End Function

From: http://www.sqldrill.com/excel/programming-vba-vb-c-etc/1002172-udf-returns-hyperlink.html

But is doesn't work in "my hands" just gives a
"a value used in the formula is of the wrong datatype" when commenting out the "with"-section it works as normal (but doesn't give a hyperlink)..

How can I .. in VBA do the same thing as =HYPERLINK does, (I want to do more, thus I cannot use).

 

[jim may]

Try recording a Macro. Then review code.

 

[jesper117]

It suggests putting =Hyperlink into ActiveCell.formula.. which wouldn't work since I need it to work like a formula itselv .. =myhyperlink() is allready in formula.. Or I have missed some obvious logic here...