Excel - Function returning Hyperlink 2

[Andrzejek]

I have an Excel where I have a column with some text.
I can easily manipulate this text to create a URL

I would like to create a Function (UDF) that I can use as a Formula that would return a Hyperlink, so if I have something like this:

I would like to have this working:

Option Explicit

Function TextToURL(ByRef strText As String) As Hyperlink   [green]'<<< is that right?
'Manipulate strText to create a URL[/green]

TextToURL = [red]???[/red]

End Function

So I can have this:

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mintjulep]

There is a native hyperlink formula function that already does that.

https://support.microsoft.com/en-us/office/hyperlink-function-333c7ce6-c5ae-4164-9c47-7de9b76f577f

 

[mikrom]

Hi mintjulep,
It would never occur to me that something like this already exists. Thank you for the information.

 

[Andrzejek]

According to this place Using Excel worksheet functions in Visual Basic I should be able to use the Hyperlink formula in my VBA this way:

Option Explicit

Function TextToURL(ByRef strText As String) As Hyperlink
Dim s As String

s = Replace(strText, "--", "")

TextToURL = Application.WorksheetFunction.[red]Hyperlink(s, Right(s, 10))[/red]

End Function

but, unfortunately, that does not work. I get an error #VALUE! :-(
I don't even see the [tt]Hyperlink[/tt] when I use [tt]Application.WorksheetFunction.[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mikrom]

@Andrzejek,
Because the function HYPERLINK() is not in the List of worksheet functions available to Visual Basic

https://docs.microsoft.com/en-us/of...worksheet-functions-available-to-visual-basic

 

[Andrzejek]

Thank you mikrom,
It looks to me that I cannot create my own Formula in VBA and create Hyperlinks in one step

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mintjulep]

You should be able to use your UDF inside the HYPERLINK() formula in a cell.

=HYPERLINK(TextToURL(A1),"my Link")

But if your text manipulation is as simple as your last post, then why use a UDF at all?

=HYPERLINK(SUBSTITUTE(A1, "--", ""), RIGHT(A1, 10))

 

[Andrzejek]

Great! Using UDF inside the HYPERLINK() formula works nice
And no, my text manipulation is not as simple as in my post. That was just an example.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson