none
can "Application.run" return value if macro code is in excel.worksheet ? RRS feed

  • Question

  • Hi All

    I have 3 macros  as follows:

    macro1:

    sub main()

    dim rtnVlu1 as variant, rtnVlu2 as variant

    rtnVlu1=Application.Run("'" & ActiveWorkbook.Name & "'!" & "Module1" & "." & "GetDate")

    rtnVlu2=Application.Run("'" & ActiveWorkbook.Name & "'!" & "Sheet1" & "." & "GetDate")

    end sub

    macro2: in a normal module

    Public Function GetDate() As Variant   
        GetDate = CDate("2016/1/1")
    End Function

    macro3: in a excel worksheet(name="sheet1")

    Public Function GetDate() As Variant   
        GetDate = CDate("2016/1/1")
    End Function

    Although the Macro2 is equivalent to Macro3, but rtnVlu1 do have return value and  rtnVlu2 do not .

    Is it any way to call a function in excel worksheet and get return value ?

    Sunday, July 17, 2016 3:36 AM

Answers

  • Try making them public variables. A public variable is declared in a standard module at the top before any subs. Making the variables public this way makes them available to all subs in all modules.

    Public rtnVlu1 as variant

    Public rtnVlu2 as variant


    Regards, OssieMac

    • Marked as answer by CrabHsu Monday, July 18, 2016 4:15 PM
    Sunday, July 17, 2016 3:57 AM
  • Since I will call the function with variable name in worksheet, so use "Application.Run" (It seems the only way.)

    No, that is also possible with VBA.

    Andreas.

    Sub Test()
      Dim Result
      Result = CallByName(Sheet1, "GetDate", VbMethod)
    End Sub

    • Marked as answer by CrabHsu Thursday, July 21, 2016 11:43 AM
    Monday, July 18, 2016 6:58 PM

All replies

  • Try making them public variables. A public variable is declared in a standard module at the top before any subs. Making the variables public this way makes them available to all subs in all modules.

    Public rtnVlu1 as variant

    Public rtnVlu2 as variant


    Regards, OssieMac

    • Marked as answer by CrabHsu Monday, July 18, 2016 4:15 PM
    Sunday, July 17, 2016 3:57 AM
  • Always refer to the objects directly, never use Application.Run, it's not necessary in 99% of all cases.

    Andreas.

    Sub Main()
      Dim rtnVlu1 As Variant, rtnVlu2 As Variant
      'Without a module specifier VBA searches the regular modules:
      rtnVlu1 = GetDate
     
      'Specify the regular module if you have 2 modules with the same method:
      rtnVlu1 = Module1.GetDate
      rtnVlu2 = Sheet1.GetDate
     
      'Full specified:
      rtnVlu1 = VBAProject.Module1.GetDate
      rtnVlu2 = VBAProject.Sheet1.GetDate
      rtnVlu2 = ThisWorkbook.Sheets("Sheet1").GetDate
    End Sub

    Sunday, July 17, 2016 8:03 AM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel Developer:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, July 18, 2016 2:11 AM
  • Hi Andreas

    Thanks for your suggestion.

    Since I will call the function with variable name in worksheet, so use "Application.Run" (It seems the only way.)

    thank you for your assistance.

    Monday, July 18, 2016 4:15 PM
  • Hi OssieMac

    Thank you for your your assistance.

    After some searching , I believe this is the best solution to my situation.

    Monday, July 18, 2016 4:19 PM
  • Since I will call the function with variable name in worksheet, so use "Application.Run" (It seems the only way.)

    No, that is also possible with VBA.

    Andreas.

    Sub Test()
      Dim Result
      Result = CallByName(Sheet1, "GetDate", VbMethod)
    End Sub

    • Marked as answer by CrabHsu Thursday, July 21, 2016 11:43 AM
    Monday, July 18, 2016 6:58 PM
  • Thank you, Andrea

    It works

    Thursday, July 21, 2016 11:44 AM