4
\$\begingroup\$

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

' ...
\$\endgroup\$
9
  • \$\begingroup\$ The VBE Refresh bug will slow down Excel considerably when using large number of UDFs. It's the very reason I have and use this repo. And 70000 is a lot of UDFs to make the bug felt. \$\endgroup\$ Dec 14, 2022 at 20:31
  • \$\begingroup\$ @CristianBuse Thanks for catching this, and also for creating such a handy repo! It might also salvage this solution for the Name validator. Quick question: your repo says "an async call can do the calculation outside of the UDF context". In breaking the UDF context, might LibUDFs.TriggerFastUDFCalculation() also cause VBA to "lose track" of the procedure, and thus circumvent the "ban" on UDFs modifying the environment? If so, "sudo mode" would already be achieved! \$\endgroup\$
    – Greg
    Dec 14, 2022 at 20:53
  • \$\begingroup\$ The whole purpose of the repo is to interrupt the current calculation and trigger a new one that avoids the Refresh Bug. While you can do anything within the callback that triggers the correct calculation, that only happens once per calculation cycle (not per UDF). Excel then proceeds to calculate each UDF as before. Sudo mode* does something per each UDF so my repo can't really help there. \$\endgroup\$ Dec 14, 2022 at 21:09
  • \$\begingroup\$ @CristianBuse Thanks for clarifying. :/ When you have some time, I'd love your input on "sudo mode", especially if there is a superior implementation. I aspire to create a module Sudo with functions Sudo.Run() and Sudo.CallByName(), which essentially execute Application.Run() and VBA.CallByName() in "sudo mode". Armed with these, one could theoretically build a set of "sudo objects" like SudoRange, where SudoRange.Foo uses Sudo.CallByName(rng, "Foo", ...) to mimic Range.Foo. Then one could call SudoRange.Foo directly in a UDF, like calling Range.Foo without the "ban". \$\endgroup\$
    – Greg
    Dec 14, 2022 at 21:19
  • \$\begingroup\$ I might have some time next week - this week is hectic, hence me just adding comments here and there. By the way, sudo mode can't do range formatting for example. I know it works with changing values but the code still runs in UDF mode and so it's still limited in what it can do. Try this UDFMode function and you will see what I mean. I.e. add a few Debug.Print UDFMode in and out of sudo and you will see that even though Excel "loses track" it's still in UDF mode. \$\endgroup\$ Dec 14, 2022 at 21:25

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.