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 String
s.
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 Name
s 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 Name
s. In renaming RunSudo()
as A1()
, we ensure that
Application.Run "A1()"
will not conflict with any such Name
s. 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 Name
s, 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 Name
s 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 Name
s, 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?
Sudo_argList
collection in theFail
section of theRange_SetValue
method because if the call fails initially then all subsequent calls will fail because of the duplicated keys. \$\endgroup\$Application.Run
fails on Mac. Testing on my own Mac which uses Excel for Mac 2011, this doesn't crash for me. HoweverApplication.Run "Test()"
runs the Test macro twice, where asApplication.Run "Test"
only calls the function once, as required. So it may be worth removing the brackets from your call... \$\endgroup\$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()
, inApplication.Run "RunSudo()"
, was I able to trigger "sudo mode", where VBA "lost track" of the context and thus permitted modifications. I have also consideredWorksheet.Evaluate()
, but I'm not sure how well it's supported across platforms... \$\endgroup\$