ExecuteExcel4Macro Method fails in Excel 2011 on Mac with 1004 Error

Question

So I'm another one of those wanting to use the ExecuteExcel4Macro Method call to retrieve data from specific cells and lookup ranges in closed workbooks. I have seen lots of examples and answers to problems here and elsewhere. I am (or will be) using a variation of a routine credited to John Walkenbach, and referenced here and on other forums. (See thread for 9311188.)

The call to ExecuteExcel4Macro fails with an error "1004 - Method 'ExecuteExcel4Macro' of object '_Global' failed". For me, that's not a lot to go on. I have double checked the directory paths, file and sheet names, all that. The DIR() function finds the file okay. I've even put the files in the root directory to eliminate path complexities or too-long of an argument to the Method. One complication is that I'm on a Mac with OS 10.8 and using Excel 2011. Mac OS uses ":" instead of "" for directory delimiters.

But I don't really need to get into all that because the problem seems to be something fundamental about the cell reference addressing. I can't get ExecuteExcel4Macro to execute successfully within the same worksheet with an Excel Function that addresses any cell or range, never mind about a remote, closed worksheet reference. So I have condensed my example code to the essentials – no remote reference, just functions on cells in one worksheet.

In the example below I have a simple routine that executes some sample Excel Functions and displays a MessageBox with either the successful result or the error message, along with the argument to the Method call. There's also a function that will convert the A1 style references to R1C1 when needed. The list of Functions are within the routine, just comment/uncomment as needed to execute whichever one to test.

Function MakeR1C1(A1Formula As String) As String

MakeR1C1 = Application.ConvertFormula( _
    Formula:=A1Formula, _
    fromReferenceStyle:=xlA1, _
    toReferenceStyle:=xlR1C1, _
    ToAbsolute:=xlAbsolute)

End Function

Sub TestExcel4Macro()
    On Error GoTo ErrorTrap
    Dim arg As String

'    arg = "GET.CELL(42)"
'    arg = "CHAR(65)"
'    arg = "LEN(""ABCDE"")"
'    arg = "SUM(2,5,8)"
'    arg = "INFO(""directory"")"
'    arg = "INFO(""numfile"")"
'    arg = "SUM(A32:A34)"
'    arg = "SUM(ValList)"
'    arg = MakeR1C1("SUM(A32:A34)")
'    arg = "SUM(R32C1:R34C1)"

    Rtn = ExecuteExcel4Macro(arg)

        MsgBox "COMPLETED" & Chr(13) & _
                    "arg: " & arg & Chr(13) & _
                    "Return Value: " & Rtn
Exit Sub

ErrorTrap:
    Beep
    MsgBox "FAILED" & Chr(13) & _
                    "arg: " & arg & Chr(13) & _
                    "Error number: " & Err & Chr(13) & _
                    Error(Err)
End Sub

The first six all work just fine, returning the values you would expect:

arg = "GET.CELL(42)"         This returns the left margin, or whatever that is;
arg = "CHAR(65)"             Good, you get an "A" for that;
arg = "LEN(""ABCDE"")"       Nice, that's a 5;
arg = "SUM(2,5,8)"           Okay, 15;
arg = "INFO(""directory"")" Yep, the directory path of the active workbook with the macro;
arg = "INFO(""numfile"")"    And the number of sheets in the workbook (plus 1? whatever).

So from this I know I'm accessing the Method correctly; it does work; you don't use the "=" in the argument; and the two INFO() Functions tell me it's able to access info about this workbook; i.e. it doesn't require explicit full directory pathway to find itself.

Now some functions that make reference to cells in the worksheet. These all work fine as a Formula in a cell in the worksheet. But they fail as a call to the Method, with the respective error codes:

arg = "SUM(A32:A34)" 13 - Type mismatch
As expected, the Method requires R1C1 style references.

arg = "SUM(ValList)" 13 - Type mismatch
Okay, not too surprising, so it won't work with a named range. Too bad, I was counting on that.

arg = MakeR1C1("SUM(A32:A34)") 1004 - Method 'ExecuteExcel4Macro' of object '_Global' failed
Now the puzzlement. The MakeR1C1() converts the A1 addressing okay to "SUM(R32C1:R34C1)".

arg = "SUM(R32C1:R34C1)" 1004 - Method 'ExecuteExcel4Macro' of object '_Global' failed
And setting the argument explicitly with the R1C1 style fails the same.

I'll be really embarrassed if this is due to something simple and obvious. But I'll risk it because I'm stumped.
If it's not so simple then, Gurus, have at it. If I get this simple reference addressing problem figured out, then the remote file reference should fall into place, too.

I'll be especially appreciative of anyone who can test these in a Windows version and let me know what you get. That's what I'm most worried about – a Mac incompatibility that I can't fix.

Thanks to all in advance.
PS: I hope I have marked up all the above correctly, I tried.

Edit: Maybe I should have mentioned that to run my TestExcel4Macro() subroutine, I just mash the F5 key while in the VBA editor.

Hi, I get exactly same errors in Windows with MS Excel 2010 . — J. Chomel

Mr. Mascaro Mr. Mascaro · Accepted Answer · 2014-10-15T19:00:49

Quote:

The Microsoft Excel 4.0 macro isn't evaluated in the context of the current workbook or sheet. This means that any references should be external and should specify an explicit workbook name. For example, to run the Microsoft Excel 4.0 macro "My_Macro" in Book1 you must use "Book1!My_Macro()". If you don't specify the workbook name, this
method fails.

ExecuteExcel4Macro Method fails in Excel 2011 on Mac with 1004 Error

3 Answers

Quote: