Get A Function's Return Value From Run

Question

I am running an Excel Macro from an Outlook Macro, and I need to get the Excel Macro's return.

My Outlook Macro's code looks like this:

Dim excelApp As Object

Set excelApp = CreateObject("Excel.Application")
excelApp.Workbooks.Open "C:\Users\meej\Documents\Book1.xlsm"

If excelApp.Run("ThisWorkbook.Foo", true) Then
    Debug.Print "TRUE"
Else
    Debug.Print "FALSE"
End If
excelApp.Quit
Set excelApp = Nothing

My Excel Macro looks like this:

Public Function Foo(ByVal result as Boolean) as Boolean
    Foo = result
    MsgBox Foo
End Function

My MsgBox pops up and shows true but in Outlook I always print "FALSE". Am I misusing Run?

From what I gather you are using Run properly as it will return a boolean which is what the if statement wants. The issue might lie in the result not being cast correctly. — Matt
– Matt, Commented Nov 3, 2014 at 16:56
@Matt Hmmm... but I'm seeing it print from the MsgBox correctly? — Jonathan Mee
– Jonathan Mee, Commented Nov 3, 2014 at 17:01
Might be on the wrong track but what is the result of this MsgBox VarType(result) inside your function — Matt
– Matt, Commented Nov 3, 2014 at 17:11
I just tested your code in outlook calling an excel document. The only thing different between what you did and what i did is that my call in Run was "Module1.Foo", False. My Immediate window printed properly. — Matt
– Matt, Commented Nov 3, 2014 at 17:23

Community · Accepted Answer · 2017-05-23 12:33:02Z

2

Somone else might have a more verbose answer but in short your logic is sound. Ive always had issues with calling custom functions from ThisWorkbook. Using your exact code I was able to get this working by moving the code into a module

If excelApp.Run("Module1.Foo", true) Then

This question shows a similar issue trying to use functions from ThisWorkbook as formulas

edited May 23, 2017 at 12:33

CommunityBot

11 silver badge

answered Nov 3, 2014 at 19:13

Matt

47k9 gold badges90 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jonathan Mee Over a year ago

I have another question floating around for UsedRange, it seems like ThisWorkbook might just fall into the same: "Just don't use it" category. I sure would like to know why Microsoft has stuff in there that just doesn't work as advertised.

Matt Over a year ago

@JonathanMee ThisWorkbook i think is more for the work book event if nothing else.

user3683880 · Accepted Answer · 2016-07-08 12:40:05Z

I used the Application.Run method in the context of Excel and MS Project. There seems to be no way to trigger a FUNCTION from Excel within a MS Project file rather than trigger a method. Later on i found a work around for letting Excel communicate with MS-Project:

FROM Excel:

Function doSomethingInProject()

 Dim params(0 To 1) As String
 params(0) = "Some param"
 params(1) = "RETURN_VALUE"

 Dim prj As Object
 Set prj = CreateObject("msproject.application")

 prj.FileOpen Name:=filename, ReadOnly:=False, FormatID:="MSProject.MPP"
 prj.Application.Run "runTheMethod", params

 prj.FileSave
 prj.FileClose
 prj.Quit

 Msgbox "return value is " & params(1)

End Function

WITHIN Project:

Sub runTheMethod(ByRef params)

 'do stuff with params(0)
 params(1) = "Return any value"

End Sub

I hope that helps someone, for me it took a bit of time to figure that out :)

Best regards

Joerg

Collectives™ on Stack Overflow

Get A Function's Return Value From Run

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related