Excel 16.64 in Office 365 for Mac, running on a MacBook Pro M1Max with 32 GB RAM. Same code also tested in Excel 16.64 on a 2012 MacBook Pro with Intel processor and 16 GB RAM. This bug has persisted for many months. The M1 Mac is running the latest MacOS Monterey. The Intel Mac is running the latest MacOS that it supports, which is Catalina.
I have an Excel spreadsheet that I use to manage courses that I teach. One of the course sessions uses a set of VBA macros which have been stable on my old Intel Mac, but which fail on my new M1 Mac. One very simple setup macro consistently hangs Excel (not responding to any keystrokes or mouse clicks). The only recovery available is to force-quit Excel. When I tried single-stepping this macro to discover where it was hanging, it worked perfectly. I eventually discovered that inserting a wait statement to suspend execution for one second allowed the macro to complete normally.
As far as I know, the VBA subsystem has been compiled by Microsoft into M1 code, so this is a Microsoft bug not a bug in Apple's Rosetta system used to run Intel code on M1 Macs.
I will paste in the relevant part of my macro code to show that it is totally straightforward.
The first portion is at the top of the macro file creating globals available to all macros:
' First set up global variables constants and utility routines used below
Dim TheButton As Object
Dim ButtonRow, ButtonCol As Integer, ButtonAddr As String
Dim PrepTime, TalkInterval, TalkTime, WarnTime As Date
Const OneSecond = 1 / 86400
Const OneMInute = 1 / 1440
Const TwoMinutes = 2 / 1440
Const FourMinutes = 4 / 1440
And now here is the first part of my Setup macro, up to the point where it hangs on M1 Macs
Sub Setup() ' Clear old data and formatting
Range("A4:G30").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("B5:G30").ClearContents
Range("A4").Select
' Initialize time interval values
PrepTime = ActiveSheet.Range("F1") * OneMInute
TalkInterval = PrepTime * 0.5
Application.Wait (Now() + OneSecond) 'Workaround for Excel bug. Without this wait Excel hangs.
If PrepTime >= FourMinutes Then ' VBA never executes this statement if the Wait is omitted
There is one more strangeness. If the Setup macro has run once successfully (with the Wait to prevent it from hanging) I can then comment out the Wait and the macro will continue to run properly for the duration of the Excel session. However if I save the version with Wait commented out, quit Excel, and start over I am back to having Excel hang as soon as I click the button in the spreadsheet that runs the Setup macro.
This totally reproducible behaviour is clearly a bug in the VBA compiler/interpreter.
