Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Excel VBA on Mac M1 hangs reproducibly

Copper Contributor

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. 

 

 

0 Replies