Extract URL from Excel using VBA not extracting after hash

Question

I am using the VBA method to extract the URL from a cell:

Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub

This is pulling everything in the URL before the # example: If the whole URL is https://www.w3.org/TR/html4/sgml/entities.html#h-24.4.1 my results will only show: https://www.w3.org/TR/html4/sgml/entities.html missing the #h-24.4.1 Does anyone have a VBA solution to extract the whole URL including the hash and everything after?

Answer 1

I found an answer and explanation on another site. The part of the link after the # is regarded as a subaddress. The issue is resolved by including the .SubAddress.

Function GetURL(cell As range, _
Optional default_value As Variant)
'Lists the Hyperlink Address for a Given Cell
'If cell does not contain a hyperlink, return default_value
If (cell.range("A1").Hyperlinks.Count <> 1) Then
GetURL = default_value
Else
GetURL = cell.range("A1").Hyperlinks(1).Address & "#" & cell.range("A1").Hyperlinks(1).SubAddress
End If
End Function