Application.run doesn't work with module

Question

I have two modules. In one module I want to run a sub from the other module indirectly. According to MS and a multitude of online ressources this should work - but it doesn't. What could be the problem?

'Module: "Helpers"

Public Sub ubTest()
  MsgBox "ubTest is called"
End Sub


'Another Module -> I also tried this from a form and a class...

Public Sub test()
  Dim s As String
  Helpers.ubTest             'works

  s = "ubTest"
  Application.Run s        'works

  s = "Helpers.ubTest"
  Application.Run s        'doesn't work

End Sub

(Obviously this is a test - in the real application I will have multiple modules and will not always have control over the procedure names - so I have to use the module-prefix)

I tried to /decompile and compact the database - no luck there either.

Answer 1

The Access Application.Run Method help topic says this about the Name parameter:

'If you are calling a procedure in another database use the project name and the procedure name separated by a dot in the form: "projectname.procedurename".'

So I think when you supply "modulename.procedurename" (ie "Helpers.ubTest"), Access thinks your modulename is the name of a VBA project. Since it can't find a project named Helpers, it throws error #2517, " ... can't find the procedure 'Helpers.ubTest.'"

Unfortunately, I can't find a way to do what I think you want with Application.Run. I hoped "projectname.modulename.procedurename" would work, but that also triggered the 2517 error.

Answer 2

The answers of @HansUp and @Johannes (although I don't quite understand his solution) (and lot's of trial and error) lead me to this knowledge:

• Application.Run within Access won't try to use the first part of the procedurename (before the dot) as Module name, only as Project name
• Thus, the most important lesson, if you want to call a specific function name of a module through Application.Run, the function must be named uniquely in the whole project.

See the Microsoft docs:

• Application.Run in Access
• Application.Run in Excel
• Application.Run in Word

For example, two Modules that will have the same function DoSomething will always throw error 2517 when called through Application.Run: Access can't find the right function then. You'll have to either rename (one of them) or create a wrapper function with a globally unique name for this goal.

So:

' Module1
Public Function GetName() As String

' Module2
Public Function GetName() As String

Should be changed to for example:

' Module1
Public Function Module1_GetName() As String

' Module2
Public Function Module2_GetName() As String

Or, add a wrapper for calls through Application.Run:

' Module1
Public Function GetName() As String
    GetName = "Module1"
End Function

Public Function Module1_GetName() As String
    Module1_GetName = GetName()
End Function

To test my gained knowledge, I made a wrapper function around Application.Run calls that, upon an error 2517, will replace the dot in the procedure name with an underscore and rerun, so the wrapper functions within the modules will be automatically called. This makes the code more or less compatible with Excel Application.Run. If you then create wrapper functions in your modules like my second example your 'good to go'.

Private Function RunApplicationFunction(Name As String, Value As Variant) As Variant
    On Error GoTo TryUniqueName
    
    Set RunApplicationFunction = Application.Run(Name, Value)
    Exit Function
    
    ' In MS Access we catch Error 2517 and try to call the function with the application/module name prefixed to it
    ' So WebHelpers.ParseXML becomes WebHelpers_ParseXML
TryUniqueName:
    If Err.Number = 2517 And Application.Name = "Microsoft Access" And InStr(Name, ".") > 0 Then
        Set RunApplicationFunction = Application.Run(Replace(Name, ".", "_"), Value)
    Else
        Err.Raise Err.Number
    End If
End Function