Why is the return result of ExecuteExcel4Macro always False?

Question

While using ExecuteExcel4Macro to run a Excel macro in Python, I always get the False result, here is the code executed:

import win32com.client

filename = r'E:\excel.xls'
xlApp = win32com.client.Dispatch('Excel.Application')
xlApp.visible = 1
xlBook = xlApp.Workbooks.Open(filename)

strPara = xlBook.Name + '!Macro1()'
res = xlApp.ExecuteExcel4Macro(strPara)
print res

xlBook.Close(SaveChanges=0)

and the output of "print res" statement is: False

After I search the usage of ExecuteExcel4Macro on MSDN, I get the following information:

ExecuteExcel4Macro -- Runs a Microsoft Excel 4.0 macro function and then returns the result of the function. The return type depends on the function.

Then I get confused: since macro in Excel is always a "Sub procedure" and a "Sub procedure" in VBA has no return result, how can a Excel macro return a result? Then what does the False result in the above example stand for?

After that, I try ExecuteExcel4Macro within Excel(2003) by coding not in Python but in VBA:

Sub RunMacro()
    res = ExecuteExcel4Macro("excel.xls!Macro1()")
    MsgBox CStr(res)
End Sub
Sub Macro1()
    MsgBox "in Macro1"
End Sub

and the "res" string shown in MsgBox is the same: False

1.Why is the return result of ExecuteExcel4Macro always False?

2.What should I do if I want to run an Excel macro in Python and to get the exit status of the Excel macro function?

---

Updated at 2011.10.28:

Sub TEST()
    res = Application.Run(MacroToRun)
    MsgBox CStr(res)
End Sub

Function MacroToRun()
    MacroToRun = True
End Function

After I run TEST Macro in Excel 2003, I get this:

A dialog with the information "Error 2015".

Answer 1

You can call a VBA UDF using this Excel 4 Macro with the ExecuteExcel4Macro method:

retval = Application.ExecuteExcel4Macro("EVALUATE(""Book1!some_UDF()"")") 

The use of the Excel 4 Macro Function EVALUATE() won't allow you to step into the UDF named "some_UDF" in VBE debug mode as you could if you had called the UDF from an Excel 4 Macro (see Example).

I have also noticed that Application.Caller cannot be mentioned at all in the UDF, otherwise Excel crashes.

Example

Excel 4 Macro Sheet with the following (cell $A$1 being a named "Macro1" via Name Manager):

$A$1: Macro1
$A$2: =RETURN(Book1!some_UDF())

Excel Worksheet in cell

$A$1: =Macro1()

As a conclusion, I suspect this Example to works correctly only because Excel uses its own Application.Evaluate to get the result of the VBA UDF (lets say integer 10), and then running the Excel 4 Macro as if it where:

$A$1: Macro1
$A$2: =RETURN(10)

Indeed, prior to the VBA era, in 1992, when there was only Excel 4.0 Macro Functions, =RETURN(Book1!some_UDF()) could not possibly work, its inconceivable (not verified but I can't see how it could works...)

So to have exactly the same behavior in VBA only, the call would be:

retval = Application.ExecuteExcel4Macro("EVALUATE(" & Application.Run("Book_XL4M!test_10") & ")")

Answer 2

Some additional information from the year 2020. N.B.: I am using Excel Pro plus 2016 This comment is just here to save someone else the pain and lost time I have gone through. This maybe obvious to some programmers but I didn't know nor could I find anything on the internet advising me of the problem/restriction.

After a lot of painful work I have discovered you cannot run ExecuteExcel4Macro within a function(UDF). Also worked out you cannot call a sub that runs ExecuteExcel4Macro form within a function(UDF). It just ended the function at the ExecuteExcel4Macro and returned #Value! in the cell. I did not have an error capture system running. This may be the issue with the above problem but I'm not sure if there is a different result on older excel. Background Info: I have a cell I wanted to test if the user had changed the colour or if the conditional formatting had changed the colour. I wanted to know what colour was showing on the screen. I tried a function (UDF) but had an issue with .displayformat. You cannot use .displayformat in a function. So I thought I could use the Get.Cell(63,cell). To do that I needed to use ExecuteExcel4Macro but it would not work in a function(UDF). It would crash the function with Error2015. However it did work in a macro by itself. I was using a named range with a formula of =GET.CELL(63,INDIRECT("rc",FALSE)) for the conditional formatting and this worked without incident.

Some of the code I was using to test in a function(UDF). After TempA was defined I would jump to Temp = ...

TempA = Application.ExecuteExcel4Macro("Sqrt(4)")

TempA = Application.ExecuteExcel4Macro("GET.CELL(42)")

TempA = "GET.CELL(63,HistorySheet!" & MyCell.Address(ReferenceStyle:=xlR1C1) & ")"

Temp = ExecuteExcel4Macro(TempA)

The issue of ExecuteExcel4Macro in a function(UDF) may be obvious to some programmers but I didn't know nor could I find anything on the internet let me know of the problem/restriction. Hoping this will help someone in the future

Answer 3

You may define Excel functions, not by starting with sub but with function:

Function Area(Length As Double, Optional Width As Variant)
    If IsMissing(Width) Then
        Area = Length * Length
    Else
        Area = Length * Width
    End If
End Function

This should return something. This something is the content of the variable named after the function (here Area).