Custom callbacks in VBA

No other answers in a week...for resolution's sake here's the best I could come up with:

  1. I built a helper module that resolves a ParamArray to individual arguments for the sake of calling CallByName. If you pass a ParamArray through to CallByName it will mash all the arguments into a single, actual Array and pass that to the first argument in the method you attempt to invoke.
  2. I built two ForEach methods: one that invokes Application.Run, and another that invokes CallByName. 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")   

Tags:

Vba

Callback