2

In a VBA macro in Excel, I'm attempting to call a function from one module in another module.

I am able to successfully call a function in another module... but only if I disregard the return value from the function.

When I attempt to call a function in another module and save the return value (MyReturnValue = Application.Run "Module2.MyFunctionInAnotherModule"), I get a Compiler Error: "Expected: end of statement".

Clearly I'm getting something wrong in the syntax of that statement, but I have been unable to find the right syntax.

Module1:

Public Sub WhatGives()
Dim MyReturnValue As String

    ' Calling a subroutine in another module works
    Application.Run "Module2.MySub"

    MyReturnValue = MyFunctionInThisModule
    MsgBox ("MyFunctionInThisModule( ) returned:  " & MyReturnValue)

    ' Calling a function in another module works if
    ' I discard the return value of the function
    Application.Run "Module2.MyFunctionInAnotherModule"

    ' But calling a function and saving its return
    ' value doesn't work. When I uncomment the following
    ' statements, the second one results in the
    ' compiler error: "Expected: end of statement"
    'Dim MyReturnValue As String
    'MyReturnValue = Application.Run "Module2.MyFunctionInAnotherModule"
    'MsgBox("MyFunctionInAnotherModule( ) returned:  " & MyReturnValue)

End Sub

Private Function MyFunctionInThisModule()
    MsgBox ("MyFunctionInThisModule() invoked")
    MyFunctionInThisModule = "Return value from MyFunctionInThisModule"
End Function

Module 2:

Private Sub MySub()
    MsgBox ("MySub( ) invoked")
End Sub

Private Function MyFunctionInAnotherModule() As String
    MsgBox ("MyFunctionInAnotherModule( ) invoked")
    MyFunctionInAnotherModule = "Return value from MyFunctionInAnotherModule"
End Function
0m3r
  • 12,286
  • 15
  • 35
  • 71
Brian Watrous
  • 21
  • 1

1 Answers1

0

You need to return your value to something. Try xyz = Module2.MyFunctionInAnotherModule

edit: Sorry, you'll also need to remove Private from your Function.

Function MyFunctionInAnotherModule() As String
    MsgBox ("MyFunctionInAnotherModule( ) invoked")
    MyFunctionInAnotherModule = "Return value from MyFunctionInAnotherModule"
End Function
SSlinky
  • 427
  • 2
  • 9
  • Thanks for responding, Sam. Unfortunately removing "Private" from the the function didn't work. Same compiler error. – Brian Watrous Feb 16 '16 at 16:21
  • Interesting. I was able to call it with no problems. Did you try calling it with xyz = Module2.MyFunctionInAnotherModule? i.e. remove Application.Run and the quotation marks. – SSlinky Feb 17 '16 at 02:43