Excel 2011 for Mac VBA El Capitan

We've had macros running fine in Excel 2011 on a 21.5" iMac running Yosemite 10.10.5.

Having 'upgraded' to a iMac (Retina 5K, 27-inch, Late 2015) running El Capitan 10.11.3, we now find macros failing with Date functions misbehaving. And that's just the first VBA bug we've come across.

Microsoft won't talk about any technical issues unless I pay them $500 and then they may well say they have't had this problem reported to them as their software hasn't changed and I should therefore go to Apple - whereas Apple say that they've had no issuers reported and so I should go to Microsoft!!!

In the immediate window:
Yosemite - ?Date results in 31/05/2016
El Capitan - ?Date results in 31/05/ (no year!)

Does anyone have experience of this problem?

Would upgrading to Excel 2016 make any difference?

Is there a workaround?

. . . or should I just downgrade back to Yosemite?

Many thanks,

Jon

I'm not familiar with Excel-Mac versions. So I cannot speak to version issues.

There may be alternate methods\functions to generate the date results. If you could describe how the Date results are generated and show the relevant code, perhaps we could suggest alternatives.

Don't know what the problem is.

What happens if you try...

?Format(Date,"mmm dd yyyy") should return Jun 01 2016
?CLng(Date) should return 42522

Originally Posted by wimblejon

Hi AlphaFrog and thanks for the feedback.
I tried both your suggestions (above) and they produce the correct answers.

. . . BUT 'If Date > Datevalue(Cells(r,23)) then . . .
gives an error 13 - and when I look at Date in the immediate window all I get is '02/06/' . . .and NO year!
Datevalue fails too.

Do you have ElCapitan on a Mac with Excel 2011?
If so, PLEASE try it for me - as I think I'm losing my mind - or ElCapitan has degraded VBA to a point where it can't be used!

Thank you,
Jon

I don't have a Mac to test. Haven't touched one since around 1985.

To eliminate a possibility, check if the system Regional Date settings are the same for both your Yosemite and El Capitan. Excel uses that date format to evaluate dates.

Originally Posted by wimblejon

. . . BUT 'If Date > Datevalue(Cells(r,23)) then . . .
gives an error 13 - and when I look at Date in the immediate window all I get is '02/06/' . . .and NO year!
Datevalue fails too.

As a workaround, does this work?
If Date > CDate(Cells(r,23)) then . . .
I'm assuming the cell has a string that can could be evaluated as a date.

Is your copy of 2011 fully patched? Are you using a non-English version of Office?

I have seen this mentioned once before by someone but a reboot cleared it up (and IIRC he had also deleted his Excel preferences plist file)

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.mrexcel.com/forum/excel-q...l-capitan.html