Calling function from sheet module of another workbook-CodePudding

When I try to call a sub and a function that is inside a sheet module of another workbook, only the sub calling works. The function, even with the public scope, returns a void (zero lenght) string. Is it possible to fix that?

Code inside the sheet module of Sheet1 of Book1.xlsm:

Sub sMyMsg()
    MsgBox "hey sub"
End Sub

Public Function fMyMsg() As String
    fMyMsg = "hey func"
End Function

The code below is in a stardand module of another workbook (Book2.xlsm) that is in the same folder as Book1.slsm:

Sub Test_Sub() 'It works
    Application.Run "Book1.xlsm!Sheet1.sMyMsg"
End Sub

Sub Test_Func() 'It doesn't work. Gives me a void (zero lenght) string
    Dim s As String
    s = "test"
    s = Application.Run("Book1.xlsm!Sheet1.fMyMsg")
    MsgBox s
End Sub

CodePudding user response：

Simple to fix - don't put the code in a worksheet (or ThisWorkbook) module if you need to call it with Run. :)

Note: it's actually not relevant to the problem that the calling code is in a different workbook. It wouldn't work like that from the same workbook.