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, Mar 18 '16 at 08:04

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 15 '14 at 19:00

Mr. Mascaro

2,693
1
11
18

Hi J Barker. Thanks for a quick reply. If I'm understanding what you're saying, then I would respectfully disagree. A macro stored in a Code Module of a workbook can be called within that workbook without the explicit name prefix or specifier. Buttons or other controls within its worksheets don't seem to require it, nor do User Defined Functions. If it's a macro stored in another workbook than the caller, then, yeah. Anyway, I don't think that's my question. My problem is with calling the built-in ExecuteExcel4Macro VBA Method. But maybe I'm misunderstanding. – MacroMangler Oct 15 '14 at 20:18
I pasted the text directly from the MS Help on that method. If you think you know more than MicroSoft, neither I nor anyone else can help you. – Mr. Mascaro Oct 15 '14 at 20:20
Wow. Sorry, man. I'm really not trying to anger you. No, I don't think I know more than MS. And I do think you or others can help me. Yes, I saw that paragraph on the page for the Method, but I'm not able to see how it applies here. But I'll think harder on it. At the risk of making things worse, could you describe what I would need to do in my bit of sample code to make it work? – MacroMangler Oct 15 '14 at 20:45
You simply need to add the workbook and worksheet references to your ranges. – Mr. Mascaro Oct 15 '14 at 20:46

score 0 · Answer 2 · answered Mar 18 '16 at 09:02

Here is what worked for me (MS Excel 2010 under Windows smthg): you have to specify the workbook + Sheet before referring to the Cells; and also make the R1C1 conversion.

Sub TestExcel4Macro()
    On Error GoTo ErrorTrap
    Dim arg As String
    Dim this_workbook As String
    ' workbook named "myBook" having a sheet called "mySheet" where my data is
    this_workbook = "[myBook.xlsm]mySheet!"

    arg = "SUM(" & this_workbook & "A32:A34)"
    arg = MakeR1C1("SUM(A32:A34)")

    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

score -1 · Answer 3 · edited Jan 23 '15 at 15:19

-1

Have you tried defining arg as Variant instead of String?

edited Jan 23 '15 at 15:19

guneykayim

5,210
2
29
61

answered Jan 23 '15 at 14:55

Eric Groft

1

ExecuteExcel4Macro Method fails in Excel 2011 on Mac with 1004 Error

3 Answers3

Quote: