CODEWITHSUNDEEP
excelvbaoutlookreturn-value
Jonathan Mee
Jonathan Mee

Reputation: 38939

Get A Function's Return Value From Run

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?

Upvotes: 3

Views: 5935

Answers (2)

user3683880
user3683880

Reputation: 31

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

Upvotes: 2

Matt
Matt

Reputation: 46720

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

Upvotes: 2

Related Questions