"Sudo Mode" in VBA to Let UDFs Modify Workbook

Question

Background

I am working on a VBA paradigm for modifying the Excel environment from a UDF. I call this "sudo mode", and I leverage Application.Run() to invoke a delegate procedure, like RunSudo() in my example.

' Delegate to sudo mode.
...
Application.Run GetSudoCall()
'               ^^^^^^^^^^^^^
'               "...RunSudo()"

By passing the entire call "RunSudo()" rather than the name "RunSudo", we "trick" VBA into "losing track" of the procedure, and we thus circumvent the "ban" on UDFs

A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel.

which forbids them to modify the environment (beyond the cell Application.Caller). The arguments for RunSudo() are cached in Sudo_argList

' Cache the arguments for sudo mode.
Sudo_argList.Add rng, "rng"
Sudo_argList.Add val, "val"

a Collection object at the modular level

' The parameteric cache for sudo mode.
...
Private Sudo_argList As New Collection

such that Application.Run() need only invoke an empty call "RunSudo()", without awkwardly splicing an arbitrary set of arguments as Strings.

Code

I demonstrate this paradigm here, in the Demo module, with a UDF aptly named Range_SetValue(). In an Excel formula

= Range_SetValue(X2:Z8, 5)

this UDF will overwrite the cells X2:Z8 with the number 5, whenever the formula is calculated.

Demo

' #######################
' ## Modular Constants ##
' #######################

' The name of the module.
Private Const MOD_NAME As String = "Demo"

' The name of the sudo procedure.
Private Const SUDO_NAME As String = "RunSudo"


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

' The parameteric cache for sudo mode.
Private Sudo_isActive As Boolean
Private Sudo_argList As New Collection

' Activate sudo mode.
Private Sub ActivateSudo()
    ' Record activation status.
    Sudo_isActive = True
End Sub

' Deactivate sudo mode (and clear cache).
Private Sub DeactivateSudo()
    ' Clear argument cache.
    Set Sudo_argList = Nothing
    
    ' Record deactivation status.
    Sudo_isActive = False
End Sub

' Generate the call to run sudo mode.
Private Function GetSudoCall() As String
    GetSudoCall = QualifyCall(SUDO_NAME, MOD_NAME, ThisWorkbook.Name)
End Function

' Modify the range value(s) in sudo mode.
Private Sub RunSudo()
    ' Catch any errors for housekeeping.
    On Error GoTo Catch
    
    ' Only perform task if sudo mode is properly activated.
    If Sudo_isActive Then
        ' Modify the range values.
        Sudo_argList("rng").Value = Sudo_argList("val")
        
        ' Deactivate sudo mode to prevent redundant calls.
        DeactivateSudo
    End If
    
    Exit Sub
    
' Housekeeping:
Catch:
    ' Prevent redundant calls...
    DeactivateSudo
    
    ' ...before passing on the error.
    Err_Raise
End Sub


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

' Set the value(s) for a range of cells, and report the outcome.
Public Function Range_SetValue( _
    ByRef rng As Range, _
    ByRef val As Variant _
) As Boolean
    ' Catch any errors for housekeeping.
    On Error GoTo Fail
        ' Cache the arguments for sudo mode.
        Sudo_argList.Add rng, "rng"
        Sudo_argList.Add val, "val"
        
        ' Delegate to sudo mode.
        ActivateSudo
        Application.Run GetSudoCall()
        
        ' Report success.
        Range_SetValue = True
        Exit Function
        
' Housekeeping:
Fail:
    ' Reset sudo mode...
    DeactivateSudo
    
    ' ...before reporting failure.
    Range_SetValue = False
End Function


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

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


' #############
' ## Helpers ##
' #############

' Format a fully qualified call to a procedure.
Private Function QualifyCall( _
    ByRef procName As String, _
    Optional ByRef modName As String = Empty, _
    Optional ByRef wbName As String = Empty _
) As String
    QualifyCall = procName & "(" & ")"
    
    If modName <> Empty Then
        QualifyCall = modName & "." & QualifyCall
    Else
        Exit Function
    End If
    
    If wbName <> Empty Then
        QualifyCall = FormatWbRef(wbName) & "!" & QualifyCall
    End If
End Function

' Format a workbook reference.
Private Function FormatWbRef(ByRef wbName As String) As String
    FormatWbRef = "'" & VBA.Replace(wbName, "'", "''") & "'"
End Function

Concern

Support on Mac

While Application.Run() itself is available on Mac, calls of the form

Application.Run "MyUDF()"

not only fail but also crash Excel! I first encountered this behavior when implementing sudo mode for another UDF of mine: Hyper_Link(). By contrast

Application.Evaluate "MyUDF()"

seems to work consistently...but I worry that Evaluate() is less reliable in general. From what I have seen, it seems that Application.Run() is the "weapon of choice" for serious VBA developers: stdVBA, Rubberduck, etc.

Naming Clashes

My biggest challenge in using Application.Run() is in avoiding any nomenclatural clashes with synonyms: procedures from other modules, and defined Names throughout Excel. I have tried many approaches, but all have their weaknesses:

Renaming as A1()

Like anything that resembles a cell address, the name A1 is illegal for defined Names. In renaming RunSudo() as A1(), we ensure that

Application.Run "A1()"

will not conflict with any such Names. However, it might plausibly clash with some A1() procedure from some other module.

Prefixing with Demo.

Since the . character is illegal in names for VBA objects, the qualifying call Demo.RunSudo() or Demo.A1() will be unambiguous among synonyms like Mod2.RunSudo() and Mod2.A1() from other modules. However, names like Demo.RunSudo() — and even Demo.A1() — actually are legal as Names, so we expose ourselves once again to those clashes.

Randomizing a Suffix

We could play the odds and suffix RunSudo() with a random sequence of alphanumerics

  RunSudo_2398hrfj092389h2434...jf9ge980h5675h6e()
'         |------------------...---------------|
'                     240 characters

such that the total length of the String

"Demo.RunSudo_2398hrfj092389h2434...jf9ge980h5675h6e()"

meets the limit of 255 characters on the Macro argument to Application.Run(). This greatly limits the odds of a coincidental clash...but I feel very uncomfortable releasing code that is "probably stable".

As for malicious interference, that is easily possible via a simple copy-paste, since Names may also have 255 characters. Furthermore, the length heavily encumbers normal usage, and it essentially rules out many practical applications (like hyperlinks, etc.).

Recursion on Range_SetValue()

It is possible to have Range_SetValue() perform recursion, and call itself via Application.Run():

' ...

If Sudo_isActive Then
    ' ...
    RunSudo
    ' ...
Else
    ' ...
    ActivateSudo
    Application.Run "Range_SetValue()"
    ' ...
End If

' ...

The benefits are dramatic. Since "Range_SetValue" is already a name exposed by Demo, then sudo mode does not expand the "nomenclatural footprint" of Demo. As such, we risk no further clashes, beyond those which already exist. Any such clash will render Range_SetValue() unusable and will be immediately visible:

However, this approach would require that all arguments to Range_SetValue() be Optional, to preserve the syntactic validity of the call "Range_SetValue()". Furthermore, it impacts security in a number of unpleasant ways, since the user may now access RunSudo() far more directly; the specifics are complex in my use case, so please take my word for it.

Full Qualification

My heart nearly leapt for joy when I realized one may qualify RunSudo() like so:

'Workbook''s Name.ext'!Demo.RunSudo()

Since the ! character is illegal in Names, this call would never clash with them, and the qualification automatically disambiguates it from synonyms in other modules and workbooks.

Then I realized with despair that a workbook may have a rather long name, and that every ' must be escaped by another '. Thus, the entire call may exceed 255 characters when the workbook's name is long or riddled with 's, a fact I confirmed (miserably) by testing.

Update

While the ! character is indeed illegal for a defined Name itself, it may still be used to qualify that Name. As such, a qualified Name like Demo.RunSudo would still clash with the function RunSudo():

'Workbook''s Name.ext'!Demo.RunSudo`
'Workbook''s Name.ext'!Demo.RunSudo()`

Question

How might I rigorously stabilize this approach against clashes on the name RunSudo, or more generally on any Sub like Foo() in one of several modules within one of several workbooks?

Answer 1

An alternative

This post is primarily about how one can get around the limitation of formulae being unable to modify areas outside of themselves. Is there a particular reason that this has to be done during the execution of the formula engine at all? Would executing it after be okay?

See the below code:

---

ThisWorkbook.cls

Public sudo As Object

---

UDFs.bas

Public Function Range_SetValue(rng As Range, val As Variant)
    Set ThisWorkbook.sudo = SudoMode.Create(stdCallback.CreateFromModule("UDFs", "Range_SetValue_").Bind(rng, val))
    Range_SetValue = True
End Function
Public Sub Range_SetValue_(rng As Range, val As Variant)
    rng.value = val
End Sub

---

SudoMode.cls

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "SudoMode"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'Should execution occur when the cell containing the formula itself is calculated (true), or also when parameters of caller are changed? (false)
#Const WithCallerBinding = False

Private bindings As Collection         'For storing callbacks to execute post-calculation
Private callerBindings As Collection   'For storing calling formulae
Private WithEvents app As Application  'For kickstarting engine
Attribute app.VB_VarHelpID = -1
Private isProcessing As Boolean        'To prevent runaway calculations

'Creates a post-formula-calculation execution binding
'@param {stdICallable} A callable function to execute
'@returns {SudoMode} The parent collection which stores and executes post-calculation bindings.
'  This object should be stored permanently, e.g. on ThisWorkbook.
Public Function Create(ByVal binding As stdICallable) As SudoMode
    Set Create = SudoMode
    Call Create.Bind(binding)
End Function

'Bind an executable binding to the global engine object
'@param {stdICallable} The binding to execute
Public Sub Bind(ByVal binding As stdICallable)
    bindings.Add binding
    #If WithCallerBinding Then
        On Error Resume Next
        callerBindings.Add True, Application.Caller.Address(True, True, xlA1)
    #End If
End Sub

'The execution engine itself
Public Sub RunAllBindings()
    If Not isProcessing Then
        isProcessing = True
            'Process bindings
            Dim binding As stdICallable
            For Each binding In bindings
                binding.Run
            Next
            
            'Clear bindings cache
            Set bindings = New Collection
        isProcessing = False
    End If
End Sub

'Check if the target was a previous caller
'@param {Range} Target to check
'@returns {Boolean} True if this was a previous caller, false otherwise.
Private Function isCallerBinding(target As Range) As Boolean
    On Error GoTo ErrorOccurred
    isCallerBinding = callerBindings.Item(target.Address(True, True, xlA1))
ErrorOccurred:
End Function

'Launch execution engine off a SheetChange event.
'@remark Bail as soon as possible
Private Sub app_SheetChange(ByVal Sh As Object, ByVal target As Range)
    #If WithCallerBinding Then
        If Not isCallerBinding(target) Then Exit Sub
    #End If
    If bindings.Count = 0 Then Exit Sub
    Call RunAllBindings
End Sub

'Automatic data binding on `new` keyword
Private Sub Class_Initialize()
    If Me Is SudoMode Then
        Set app = Application
        Set bindings = New Collection
        Set callerBindings = New Collection
    End If
End Sub

Also requires

• stdICallable from stdVBA.
• stdCallback from stdVBA.

Disclaimer: As the post author is likely aware, I am the author of stdVBA and both these modules.

---

One key piece of information to understand is whether the formula should update whenever the parameters change, as usual with formulae, or when the formula itself is changed / written in the cell. It's possible that this should be an optional parameter of create, but currently it's down under conditional compilation (see WithCallerBinding) because I initially felt it was unexpected behaviour.

---

Ultimately this still uses Application.Run but I don't think this will have any of the same issues which OP's original issue was, due to the nature of how/when the code is called.

Unfortunately outside the windows environment not much is known about. Calling functions by pointer (AddressOf) would be an option on windows but quite so on Mac.

Answer 2

I think this is a neat trick which can have some uses so many thanks to @Greg (the OP) for sharing. One use already identified by the OP in another post is the ability to add named ranges from a UDF in order to check if a name is valid - see his Parsec repo and his names validator question.

Is Sudo Mode dangerous when modifying range values?

Consider the following scenario (in a random worksheet):

• cell A1 has the value 1
• cell B1 has the formula: =Range_SetValue(C1,A1)
• cell C1 should display the value 1 as soon as the B1 formula is entered
• cell D1 has the formula: =C1

Now, change the value of the A1 cell to another value. Cell C1 will by updated by the sudo call to the new value but D1 won't update although it should. The Application.Run trick literally breaks the calculation and this can lead to unwanted results.

Even though this trick can be used with care to avoid the above issue, I personally find this approach very dangerous and not suitable to release for others to use. Please note I am strictly referring to changing range values via sudo. Sudo itself can be great for other stuff.

One last point. Replacing:

Sudo_argList("rng").Value = Sudo_argList("val")

with:

With Sudo_argList("rng")
    .Value2 = Sudo_argList("val")
    .Dirty
End With

in the RunSudo method, finally makes cell D1 update but with the previous value instead of the current so still no good.

Modifying range values by deferring call

I defer UDF calls using the SheetCalculate event to achieve stuff like automatic filtering or applying range formatting. I've been doing this for years and it's completely safe. But, I NEVER use this approach for updating values. It's just too dangerous.

Consider the following scenario:

• Formula A updates the values of cell A and cell B via a deferred call to SheetCalculate
• Formula B updates the values of just cell A or just cell B via a deferred call to SheetCalculate

It's a circular refence/endless loop. Formula A will update cell B which will mark Formula B for recalculation which then updates cell B which marks Formula A for recalculation and so on.

Let's test this with some minimal code.

Class BookCalculateEvent:

Option Explicit

Private WithEvents m_book As Workbook
Private m_callbackName As String
Private m_ignoreErrors As Boolean

Public Sub Init(ByVal book As Workbook, ByVal callbackName_ As String, ByVal ignoreErrors_ As Boolean)
    Set m_book = book
    m_callbackName = callbackName_
    m_ignoreErrors = ignoreErrors_
End Sub

Private Sub m_book_SheetCalculate(ByVal sh As Object)
    If m_callbackName = vbNullString Then Exit Sub
    '
    If m_ignoreErrors Then On Error Resume Next
    Run "'" & Replace(m_book.Name, "'", "''") & "'!" & m_callbackName 'Application.Run not allowed by Bitdefender
    If m_ignoreErrors Then On Error GoTo 0
End Sub

Standard module:

Option Explicit

Private m_deferredCalls As New Collection
Private m_callback As BookCalculateEvent

Public Function RANGE_SET_VALUE(ByVal rng As Range, ByVal val As Variant) As Variant
    Application.Volatile False
    '
    If rng Is Nothing Then GoTo Fail
    If rng.Areas.Count > 1 Then GoTo Fail
    '
    Dim addressOfRange As String: addressOfRange = rng.Address(External:=True)
    '
    If Not CollectionHasKey(m_deferredCalls, addressOfRange) Then
        m_deferredCalls.Add Array(rng, val), addressOfRange
    End If
    If m_callback Is Nothing Then
        Set m_callback = New BookCalculateEvent
        m_callback.Init ThisWorkbook, "ExecuteDeferredCalls", True
    End If
    '
    RANGE_SET_VALUE = "Writing to: " & addressOfRange
Finalize:
    If Not Application.EnableEvents Then
        Application.EnableEvents = True 'Works in UDF mode as well
    End If
Exit Function
Fail:
    RANGE_SET_VALUE = VBA.CVErr(xlErrValue)
    Resume Finalize
End Function

Private Function CollectionHasKey(ByVal coll As Collection, ByRef keyValue As String) As Boolean
    On Error Resume Next
    coll.Item keyValue
    CollectionHasKey = (Err.Number = 0)
    On Error GoTo 0
End Function

Public Sub ExecuteDeferredCalls()
    Static isOn As Boolean
    '
    If isOn Then Exit Sub
    If m_deferredCalls.Count = 0 Then Exit Sub
    Dim isRepaintOn As Boolean: isRepaintOn = Application.ScreenUpdating
    Dim arr As Variant

    isOn = True
    If isRepaintOn Then Application.ScreenUpdating = False
    On Error Resume Next
    Do
        arr = m_deferredCalls(1)
        arr(0).Value2 = arr(1)
        m_deferredCalls.Remove 1
    Loop Until m_deferredCalls.Count = 0
    On Error GoTo 0
    '
    If isRepaintOn Then Application.ScreenUpdating = True
    isOn = False
End Sub

Now write =RANGE_SET_VALUE(C1:C2,A1) in cell B1. The value of cell C1 and C2 will be updated each time cell A1 is changed. Also any other formulas linking to C1 or C2 get updated properly e.g. cell D1 has formula =C1 (unlike via sudo).

Now write =RANGE_SET_VALUE(C1,A1) formula in cell B2. Boom - circular reference/endless loop.

Just to clarify, it does NOT matter if the call is deferred to a SheetCalculate event or to a Win API timer or even using a modeless form for an async call. The issue is that we're interfering with the calculation and we should not.

The solution provided by @Sancarn defers logic until the SheetChange event is triggered. Repeat the same steps: =RANGE_SET_VALUE(C1:C2,A1) in cell B1 and then =RANGE_SET_VALUE(C1,A1) in cell B2. Same issue - an endless loop.

---

I hope that the above examples are enough to deter anyone to change range values via sudo or deferred calls. Instead use formulas or one-time-run macros to update range values.

UDF Mode

Public Function UDFMode() As Boolean
    Dim dispAlerts As Boolean: dispAlerts = Application.DisplayAlerts
    
    On Error Resume Next
    Application.DisplayAlerts = Not dispAlerts 'Cannot be changed in UDF mode
    On Error GoTo 0
    
    UDFMode = (Application.DisplayAlerts = dispAlerts)
    If Not UDFMode Then Application.DisplayAlerts = dispAlerts 'Revert
End Function

If we sprinkle a few Debug.Print UDFMode around the OP's code we can immediately see that we are still in UDF Mode from the moment the UDF is entered up until it's exited.

The implication is that we're still not out of the ban imposed by Excel. It just happens that some stuff works (like changing range values or adding named ranges) but not all do.

For example changing:

Sudo_argList("rng").Value = Sudo_argList("val")

with:

Sudo_argList("rng").NumberFormat = Sudo_argList("val")

in the RunSudo method, simply does nothing, the format is not updated for the target range. As a side note, using a deferred call works fine for applying formatting.

Stabilizing the approach

Finally, to address the OP's actual question, it would be nice if the approach can be stabilised for some of the legitimate uses (like adding named ranges or hyperlinks but not changing range values).

The OP already mentioned the dot (.) and the exclamation mark (!) characters and ruled them out because of clashes. How about the logical not sign (¬)? The following code is valid:

Sub Test¬()
End Sub

while the ¬ character is not allowed in Excel Names.

As far as I know, Application.Run only needs the workbook name when you specifically want to target another workbook. If book name is not provided then it simply defaults to the current book. Lest test this:

• Create 2 new workbooks - no need to save.
• In Book1 add a standard code module with this code:

Sub Test()
    Application.Run "Test2"
End Sub

• In Book2 add a standard code module with this code:

Sub Test2()
    MsgBox "Called"
End Sub

• Execute Test method in Book1. We get this:
  

So, there is no need for a book name when the target is the workbook where the code is running i.e. ThisWorkbook and we can use the ¬ character to avoid clashing with Named Ranges. Of course Application.Run "Book2!Test2" would work.

If we use the ModuleName.MethodName call we can avoid clashes with other modules. However, we don't need to - we could instead declare the target method as:

Public Sub MethodName¬(Optional ByVal dummy As Boolean)

Here's why:

• We use Public as this ensures there is no other method with the same name that is also Public (it would not compile) and so this would ensure Run calls our method rather than other Private methods with same name
• We use Sub because we don't want the method to appear as a function in Excel. If there's a need to return a value we can use a private module level variable to store the result and then read it after Run does it's thing
• We use a dummy optional parameter to hide method i.e. it will not be displayed in Excel's Macros dialog (Alt+F8)