UDF to Generate Hyperlinks in Excel

Question

Background

I am trying to recreate the functionality of HYPERLINK() on my own terms, as a custom UDF called Hyper_Link(). My ultimate goal is to have Hyper_Link() embed a URL that is longer than 255 characters, which is currently impossible with HYPERLINK().

I currently have a working prototype, in the Code below. However, there remain several Concerns I wish to resolve.

Code

Note how I use "sudo mode" to let Hyper_Link() edit the .Hyperlinks on the sheet. Here I activate sudo mode recursively on Hyper_Link(), to restrict the "nomenclatural footprint" to the set of names already exported, and to thus protect Evaluate() from clashes.

' ###############
' ## Sudo Mode ##
' ###############

' Sudo mode.
Private Mod_isSudo As Boolean

' Parametric cache for sudo mode.
Private Sudo_caller As Range
Private Sudo_address As String
Private Sudo_subAddress As Variant
Private Sudo_screenTip As Variant

' Activate sudo mode.
Private Sub Sudo_Activate()
    ' Record the activation.
    Mod_isSudo = True
End Sub

' Deactivate sudo mode.
Private Sub Sudo_Deactivate()
    ' Clear the cache.
    Set Sudo_caller = Nothing
    Sudo_address = Empty
    Sudo_subAddress = Empty
    Sudo_screenTip = Empty
    
    ' Record the deactivation.
    Mod_isSudo = False
End Sub



' ##############
' ## User API ##
' ##############

' Generate a hyperlink within the calling cell.
Public Function Hyper_Link( _
    ByRef Address As String, _
    Optional ByRef TextToDisplay As Variant, _
    Optional ByRef ScreenTip As Variant, _
    Optional ByRef SubAddress As Variant _
) As String
    ' Handle any errors.
    On Error GoTo Fail
    
    ' Embed hyperlink in sudo mode...
    If Mod_isSudo Then
        ' ' Diagnostics.
        ' Debug.Print "Sudo_caller = " & Sudo_caller.Address
        ' Debug.Print "Sudo_address = '" & Sudo_address & "'"
        
        ' Update the calling cell.
        Sudo_caller.Hyperlinks.Add _
            Anchor := Sudo_caller, _
            Address := Sudo_address, _
            SubAddress := Sudo_subAddress, _
            ScreenTip := Sudo_screenTip
        
        ' ' Diagnostics.
        ' Debug.Print "Done updating"
        
        
        ' Reset sudo mode.
        Sudo_Deactivate
        
        
        ' Return nothing of interest.
        Hyper_Link = Empty
        
    ' ...or activate sudo mode to do so.
    Else
        ' Default to display the address.
        If VBA.IsMissing(TextToDisplay) Then
            TextToDisplay = Address
        Else
            TextToDisplay = VBA.CStr(TextToDisplay)
        End If
        
        
        ' Cache the arguments for sudo mode.
        Set Sudo_caller = Application.Caller.Cells(1, 1)
        Sudo_address = Address
        Sudo_subAddress = SubAddress
        Sudo_screenTip = ScreenTip
        
        ' Within sudo mode...
        Sudo_Activate
        ' ...call Hyper_Link() recursively...
        Sudo_caller.Worksheet.Evaluate "0 + Hyper_Link("""")"
        '                               ^^^^
        ' ...with hack to stop Evaluate() from calling twice.
        
        
        ' Return the display text.
        Hyper_Link = TextToDisplay
    End If
    
    
    ' Successfully return the result.
    Exit Function
    
    
' Handle housekeeping for errors:
Fail:
    ' Reset sudo mode...
    Sudo_Deactivate
    
    
    ' ' Diagnostics.
    ' Debug.Print Err.Number & ": " & Err.Description
    
    ' ...before promoting the error.
    Err_Raise VBA.Err
End Function



' #################
' ## Diagnostics ##
' #################

' Raise an objective error in VBA.
Private Sub Err_Raise( _
    Optional ByRef e As VBA.ErrObject = Nothing _
)
    ' Default to last error.
    If e Is Nothing Then
        Set e = VBA.Err
    End If
    
    
    ' Raise the error.
    VBA.Err.Raise _
        Number := e.Number, _
        Source := e.Source, _
        Description := e.Description, _
        HelpFile := e.HelpFile, _
        HelpContext := e.HelpContext
End Sub

Concerns

Residual Hyperlinks

When called in Excel formulae like so

= Hyper_Link("https://codereview.stackexchange.com")
= Hyper_Link("https://codereview.stackexchange.com", "Code Review")
= Hyper_Link("https://codereview.stackexchange.com", "Code Review", "Click Here!")
= Hyper_Link("https://codereview.stackexchange.com", "Code Review", "Click Here!", "MySub()")

then Hyper_Link() works exactly like HYPERLINK(), with the added feature of control over the .ScreenTip and .SubAddress.

However, when the Hyper_Link() formula is deleted from the cell, the hyperlink remains embedded there. By contrast, HYPERLINK() leaves no "residue" behind.

Could an extra line right before the end

        ' ....
        
        ' Clear hyperlink from cell when formula is changed in the future?
        Sudo_caller.Worksheet.Evaluate "= HYPERLINK()"
        
        ' Return the display text.
        Hyper_Link = TextToDisplay
        
        ' ...

alert Excel that this hyperlink should be cleared when the formula is edited, as (presumably) with HYPERLINK() itself?

Blocked by Protection

When protection is enabled on the worksheet, this UDF fails and returns a #VALUE! error. This occurs even when the cell itself, which contains the UDF in its formula, is not locked.

Obviously, the same does not apply to HYPERLINK().

Cap on Hyperlinks

Excel imposes a limit of 65,530 hyperlinks in a worksheet. While HYPERLINK() apparently skirts this limit, I worry that Hyper_Link() will not.

I tested Hyper_Link() on over 70,000 URLs, and it appears to work beyond the aforementioned limit. However...

Performance

The performance at scale is terrible. Just awful. Whereas HYPERLINK() populated the 70,000 cells almost instantly, my Hyper_Link() has frozen Excel indefinitely.

EmailSubject as New Feature

Since .Add does not include an argument for the .EmailSubject, I excluded the subject as a feature in Hyper_Link():

' Update the calling cell.
    Sudo_caller.Hyperlinks.Add _
        Anchor := Sudo_caller, _
        Address := Sudo_address, _
        SubAddress := Sudo_subAddress, _
        ScreenTip := Sudo_screenTip

For the sake of completeness in the API, should I include an EmailAddress parameter, and then modify the .EmailAddress after .Adding the hyperlink?

' ...

' ###############
' ## Sudo Mode ##
' ###############

' Parametric cache for sudo mode.
' ...
Private Sudo_emailSubject As String

' ...

' Deactivate sudo mode.
Private Sub Sudo_Deactivate()
    ' Clear the cache.
    ' ...
    Sudo_emailSubject = Empty
    
    ' Record the deactivation.
    Mod_isSudo = False
End Sub



' ##############
' ## User API ##
' ##############

' Generate a hyperlink within the calling cell.
Public Function Hyper_Link( _
    ' ...
    Optional ByRef EmailSubject As Variant _
) As String
    ' ...
    
    If Mod_isSudo Then
        ' ...
        
        ' Update the calling cell.
        Sudo_caller.Hyperlinks.Add _
            Anchor := Sudo_caller, _
            Address := Sudo_address, _
            SubAddress := Sudo_subAddress, _
            ScreenTip := Sudo_screenTip
            
        ' Set the email subject.
        Sudo_caller.Hyperlinks(1).EmailSubject = Sudo_emailSubject
        
        ' ...
    Else
        ' ...
        
        ' Cache the arguments for sudo mode.
        ' ...
        Sudo_emailSubject = EmailSubject
        
        ' ...
    End If
    
    ' ...
End Function

' ...

Caching Fails with Collection

My accustomed method of caching, with a Collection like Sudo_cache, fails in this particular application. While I am satisfied that Hyper_Link() works properly as it stands, it bugs me that the more elegant Sudo_cache fails for (apparently) no good reason.

' ...

' ###############
' ## Sudo Mode ##
' ###############

' Parametric cache for sudo mode.
Private Sudo_cache As New Collection

' ...

' Deactivate sudo mode.
Private Sub Sudo_Deactivate()
    ' Clear the cache.
    Set Sudo_cache = Nothing
    
    ' Record the deactivation.
    Mod_isSudo = False
End Sub



' ##############
' ## User API ##
' ##############

' Generate a hyperlink within the calling cell.
Public Function Hyper_Link( _
    ' ...
) As String
    ' ...
    
    If Mod_isSudo Then
        ' ...
        
        ' Update the calling cell.
        Sudo_cache("Caller").Hyperlinks.Add _
            Anchor := Sudo_cache("Caller"), _
            Address := Sudo_cache("Address"), _
            SubAddress := Sudo_cache("SubAddress"), _
            ScreenTip := Sudo_cache("ScreenTip")
            
        ' ...
    Else
        ' ...
        
        ' Cache the arguments for sudo mode.
        Sudo_cache.Add = Application.Caller.Cells(1, 1), "Caller"
        Sudo_cache.Add = Address, "Address"
        Sudo_cache.Add = SubAddress, "SubAddress"
        Sudo_cache.Add = ScreenTip, "ScreenTip"
        
        ' ...
    End If
    
    ' ...
End Function

' ...