Custom callbacks in VBA
No other answers in a week...for resolution's sake here's the best I could come up with:
- I built a helper module that resolves a ParamArray to individual arguments for the sake of calling
CallByName
. If you pass a ParamArray through toCallByName
it will mash all the arguments into a single, actualArray
and pass that to the first argument in the method you attempt to invoke. - I built two
ForEach
methods: one that invokesApplication.Run
, and another that invokesCallByName
. As noted in the question,Application.Run
only works for user-defined global (public module) methods. In turn,CallByName
only works on instance methods, and requires an object argument.
That still leaves me without a way to directly invoke built-in global methods (such as Trim()
) by name. My workaround for that is to build user-defined wrapper methods that just call the built-in global method, for example:
Public Function FLeft( _
str As String, _
Length As Long) As String
FLeft = Left(str, Length)
End Function
Public Function FLTrim( _
str As String) As String
FLTrim = LTrim(str)
End Function
Public Function FRight( _
str As String, _
Length As Long) As String
FRight = Right(str, Length)
End Function
...etc...
I can now use these to do things like:
' Trim all the strings in an array of strings
trimmedArray = ForEachRun(rawArray, "FTrim")
' Use RegExp to replace stuff in all the elements of an array
' --> Remove periods that aren't between numbers
Dim rx As New RegExp
rx.Pattern = "(^|\D)\.(\D|$)"
rx.Global = True
resultArray = ForEachCallByName(inputArray, rx, "Replace", VbMethod, "$1 $2")