Use this VBA function to extract the URL from a cell in Excel. This versatile VBA function works whether the hyperlink is entered in Excel using the “=HYPERLINK()” function or the “Insert > Hyperlink” menu. To top it off, the function can be called from another VBA macro or it can be entered as a formula directly into another cell in Excel. That’s what makes this function different from other solutions you may find online.
This helpful macro was submitted by a member of the wellsrPRO community, Mitch! Thank you, Mitch!
Macro to extract the URL from a cell
Created by Mitch
Function LinkLocation(rng As Range) 'DESCRIPTION: Get the formula url from hyperlink/formula or the insert/hyperlink method 'DEVELOPER: Mitch (wellsrPRO member) ' vars Dim sFormula As String, sAddress As String Dim L As Long Dim sHyperlink As Hyperlink, rngHyperlink As Hyperlinks ' cell formula sFormula = rng.Formula ' gets starting position of the file path. Also acts as a test if ' there is a formula L = InStr(1, sFormula, "HYPERLINK(""", vbBinaryCompare) ' tests for hyperlink formula and returns the address. If a link ' then returns the link location. If L > 0 Then sAddress = Mid(sFormula, L + 11) sAddress = Left(sAddress, InStr(sAddress, """") - 1) Else Set rngHyperlink = rng.Worksheet.Hyperlinks For Each sHyperlink In rngHyperlink If sHyperlink.Range = rng Then sAddress = sHyperlink.Address End If Next sHyperlink End If ' boom, got the hyperlink address LinkLocation = sAddress End Function
Make powerful macros with our free VBA Developer's Kit
There's a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA Developer's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier.
How to use the LinkLocation Macro
You can call the
These hyperlinks point to different URLs on the VBA Tutorials Blog and can be added using the “Insert > Hyperlink” menu, by right-clicking the cell and selecting “Hyperlink” or by using the native Excel =HYPERLINK() function, like this:
It doesn’t matter how your user inserts the hyperlink, the
It’s important to point out the
Calling the LinkLocation function using VBA
To call the
Sub ExtractURL() Dim strURL As String strURL = LinkLocation(Range("C3")) Debug.Print strURL End Sub
When you run this macro, the string
Notice how we only passed the
Calling the LinkLocation function from Excel
If all you wanted to do was to find out where a cell hyperlinks to, you could call the
In our example above, this would return the URL
Here’s what our extract URLs would look like if we placed our
That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.