Calling a Sub inside a Sheet module on Excel for Mac 2011

[mcomp72]

I have written an Excel workbook for Windows, and am now trying to make it work on Excel for Mac 2011. Several times in my code I call subs that are inside different worksheet objects. With Windows, it works fine to call them like this:

Sub MySub

If x = 1 Then Sheet1.AnotherSub

End Sub

However, on Mac it gives me: Runtime Error '1004' - Application-defined or object-defined error

FYI -- "AnotherSub" is not a Private sub.

If I try to call a sub that is inside a regular Module in this same way, it works fine. For instance:

If x = 1 Then Module1.DifferentSub

The above works.

I tried doing it like this, but this also gave the same error.

If x = 1 Then Call Sheet1.AnotherSub

Anyone know why I am getting the error, and how I can fix it?

 

[RoryA]

Are you sure the error isn’t actually being caused by the routine you’re calling?

 

[mcomp72]

Seems like you may be right. The sub that is being called is tied to a button click, and clicking the button on Windows works fine, but on Excel for Mac 2011 it gives a 400 error. I stepped through the code, and it seems to throw the error in this section of the code:

    Range("N53:S55").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.0499893185216834
        .PatternTintAndShade = 0
    End With

I think it gave the error on the ".Pattern = xlSolid" line, but it may have been the one before or after.

I tested it on Excel for Mac 2016 and it did NOT give me the error. The Sub worked perfectly, just like on Windows. Anyone know VBA on Mac well enough to know why this section of code would cause a 400 error on Excel for Mac 2011?

 

[mcomp72]

Since this is technically a different question than the one I first asked, should I create a new post to ask this second question?

 

[mcomp72]

I'm actually going to go ahead and post this question on the MS forums, because I am hoping more people familiar with VBA on Mac may be there. I know it's a relatively small number of developers that do VBA for Mac compared to Windows.

Just posted it to the MS forums. You can find it here:
https://answers.microsoft.com/en-us...-8705-43e4-9ffe-637b604bbbc2?tm=1547000599294

 

[StephenCrump]

Are you sure that the code works on everything but Excel 2011 for Mac when the circumstances are identical?

I don't use Mac, but based on the more detailed question you posted to MS forums, the code works on Excel for Mac for Range("N55:S55") but errors later for Range("N53:S55") ...

... which suggest that it's not a Mac issue, but something about the particular run circumstances?

My first guess would be worksheet protection:

- Has the code protected the sheet in the meantime?

- Or perhaps does the second occurrence refer to a different, protected, worksheet?

 

[RoryA]

Does it help if you don't select first:

    With Range("N53:S55").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.0499893185216834
        .PatternTintAndShade = 0
    End With

 

[mcomp72]

Stephen: since it works fine on Excel 365 for Windows and Mac, that makes me think there is nothing wrong with the code itself. If it were a protection issue, wouldn't it fail on ALL of the platforms? But to answer your other questions, both sections of code refer to the same worksheet, and the sheet was not being protected in between the two sections.

Rory, before I saw your reply, I ended up changing the first section of code so that the Range was N53:S55, and I deleted the second section of code, since that what was giving the error. After that, it worked fine. I can't figure out why it was doing it, but at least the problem has gone away now.

Thanks to you both for trying to help!