"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:

9

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?

• Cristian Buse: Make sure you clear the Sudo_argList collection in the Fail section of the Range_SetValue method because if the call fails initially then all subsequent calls will fail because of the duplicated keys.
• Greg: @CristianBuse Thanks, good catch!
• Greg: @CristianBuse I just now "patched" that bug.
• Sancarn: Thanks for letting us know that Application.Run fails on Mac. Testing on my own Mac which uses Excel for Mac 2011, this doesn't crash for me. However Application.Run "Test()" runs the Test macro twice, where as Application.Run "Test" only calls the function once, as required. So it may be worth removing the brackets from your call...
• Greg: @Sancarn Thanks checking it out! If I recall, Application.Run "RunSudo" failed due to permissions: VBA "kept track" of the calling context, and thus blocked any modifications to the environment from the UDF. Only by using the parentheses (), in Application.Run "RunSudo()", was I able to trigger "sudo mode", where VBA "lost track" of the context and thus permitted modifications. I have also considered Worksheet.Evaluate(), but I'm not sure how well it's supported across platforms...

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.

• Cristian Buse: You should have an Application.EnableEvents = True in the Range_SetValue method to make sure the event does get fired. I use this approach a lot but mainly with the SheetCalculate event to defer stuff like filtering or formatting via UDF.
• Cristian Buse: Anyway, I'm sure you already know all this but just wanted to say that rarely there's a good reason to keep events off outside of running a macro e.g. while UDFs are running.
• Sancarn: @CristianBuse Thanks for sending that, I never knew about this, makes total sense though! Very cool.
• Cristian Buse: Hi, just published StateLossCallback. This takes the 'magic' trick mentioned here to the next level as now there are no more crashes is the user presses the Stop button and also there are 2 ways of calling back: by name and by address. I would be grateful to hear what you think about it
• Sancarn: Lol, funky way of calling by pointer :P I think I'd generally prefer DispInvokeFunc though to invoke the function, though i suppose this also has benefits when vba disconnects?

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

scenario

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.

scenarioUpdated

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:
  err

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)

• Greg: Wow, I never knew about the ¬ character! This changes everything: now Demo.RunSudo¬() becomes an unambiguous call, without any possible clash with either other modules (due to the Demo. prefix) or defined names (due to the ¬ character)! Obviously, I'll have to consider all the other implications for stability, which you mention in your very thorough post. But thank you so much for highlighting this! I'll definitely be reading this over in detail. :)
• Sancarn: Not surprised that my code can cause infinite loops, nor the original. Definitely worth noting though :) I think you could probably use Range_SetValue(), but just have to be very careful about when you do use it. Interesting about the ¬ character. I've made a few highlighters in the past and pretty sure that character wasn't included. Good to know :)
• Greg: @CristianBuse Out of curiosity, did you ever try Application.Run() or .Evaluate() on "Test¬()"? I tried it recently for "Demo.RunSudo¬()", and it returned Error 2015 (rather than actually erroring). I suspect these two functions cannot evaluate the ¬ character at all. I suspect the reason is that, according to this chart, the ¬ character is permitted nowhere in an Excel name.
• Cristian Buse: Hi @Greg, Darn indeed. Sorry to hear the special character route did not work. As a personal note, I've been on paternity leave for a couple of months and so inactive but I will become more active in the weeks to come and will at some point look at your arg mate repo as promised. Cheers!
• Greg: Hi @CristianBuse, and congratulations! As for the repo, I actually have a new one coming out very soon...though I might need to rework it from a .bas module to a .cls class, for the sake of user experience. It's called GitHelp, and it helps a VBA developer easily set up rich, online documentation (typically .md files on their GitHub) for their module ("MyMod"). The Excel user can then run Help__MyMod("MyFun"), and their browser will open the webpage for MyFun() from MyMod!