EXC_BAD_ACCESS MAC For Excel

Hi,

Performed all three steps above to no avail. My macro is pretty basic until a ColumnSort line. Which works perfectly on 2011 Excel with 2010 VB version 14.7.2 (120228). However, 2021 uses 2012 VB 7.1. My assessment is Devs didn’t do a lot of macro testing with the two to ensure VB 7.1 was compatible with Excel. 2021. Basic macros and VBA code will perform. Its when an intricate line of code with a “=“ of two ColumnSort routines tests the capability of the the app platform that the real test results occur. BTW I can actually place a break on this line with the old 2011 Excel and see the same error screen. But when it is removed it performs flawlessly. So the new edition 2021 doesn’t allow passing of this same functional equation with this newer Excel platform VB compiler.

Hi

It appears that the row number must be specified for D.

This works as expected:

Sub test()
Dim NumOfRows As Integer
NumOfRows = ActiveSheet.Range("A2:D10").End(xlUp).Rows
MsgBox NumOfRows
End Sub

One problem as I said originally is that the VBE compiler is not compatible with 2021 Excel. MS cannot simply create a new Excel edition without updating a compiler. The 2011 edition of Excel the original code performs without any run-time or object errors. Rewriting code is also producing now object errors even from code from MS VBA developers. Run this code with a CSV file and you will see the last line of code from a MS VBA developer now produces an object error. I am including my commented out code of which original ran on 2011.

Dim sd As Worksheet, rd As Range, filespec As Variant, wbOpen As Workbook, rdSheet As Worksheet, wkshtdest As Worksheet, wb As Workbook, ColumnSort As Long, NameFolder As String, xtitleid As String, newName As Variant

    NameFolder = "downloads folder"

    Set sd = ThisWorkbook.Sheets(strResponse)

    If strResponse = " " Then

        Exit Sub

    End If

    Set rd = sd.Range("A2:F300")

    filespec = Application.GetOpenFilename()

    Set wbOpen = Workbooks.Open(Filename:=filespec) ' ORIGINAL

    xtitleid = "Change To s1"

    newName = Application.InputBox("Name", xtitleid, "", Type:=2)

    Application.Sheets(1).Name = newName

    Sheets("s1").Activate

    Set rdSheet = wbOpen.Sheets("s1")

    rdSheet.Columns(6).EntireColumn.Delete

    ColumnSort = rdSheet.Cells(Rows.Count, 2).End(xlUp).Row

    ‘rdSheet.Range("A2:D" & ColumnSort).Sort Key1:=Range("A2:A" & ColumnSort), order1:=xlDescending, Header:=xlNo

    rdSheet.Range("A2:D").Cells(Rows.Count, 2).End(xlUp).Row

Hi,

Sorry for all the inconveniences it may have caused on your side.

Based on the description, it seems like the issue started because you have updated the Excel environment from Office 2011 to Office 2021. If so, as the Excel version has changed with a large difference/update, the possibility of the incompatible as your guess is reasonable. Hence, I'd like to suggest some general steps to troubleshoot from the Excel side and if the issue persists, it is suggested to modify the codes in the issue macros.

1.Clean the Excel cache folder(No need to do this if the Excel is newly installed recently.)

Open Finder > press Command + Shift + G > input ~/Library/Containers, remove Microsoft Excel folder to the desktop. Then, reopen Excel and run the macros to check the behavior.

2.Run Mac in Safe Mode.

This can help us to exclude any possible influences from other third-party apps or background programs that may have a conflict with the Excel app. See: How to use a "clean startup" to determine whether background programs are interfering with Office for Mac (microsoft.com) and kindly refer to the Apple article in the last one to get detailed steps.

3.Create a new user profile. See: Set up users, guests, and groups on Mac - Apple Support

This is the last step to help us to locate the issue cause as if it persists in a new user profile, it is most likely that the error is caused by the macro side or let's say the incompatibility between the macro and the Office 2021 Excel 16.55 environment.

Besides, do feel free to let me know if the same error will even happen when you run some basic macros in Excel.

Best Regards,

Mia