Share via

Excel for Mac crashes when using Range.Sort on range object

ASForrest 1 Reputation point
2021-05-09T23:46:05.42+00:00

I have an excel spreadsheet with 8 random numbers in cells A1 through A8. I then have two buttons, each running a VBA sub:

Sub Button1_Click()
Sheets("Sheet1").Range("A1:A8").Sort key1:=Sheets("Sheet1").Range("A1"), order1:=xlAscending
End Sub

Sub Button2_Click()
Dim oRange as Range
Set oRange = Sheets("Sheet1").Range("A1:A8")
oRange.Sort key1:=Sheets("Sheet1").Range("A1"), order1:=xlAscending
End Sub

When I click Button 1, the numbers in the cells are sorted in ascending order, as you'd expect. When I click Button 2, excel immediately crashes and closes with no warning or error message.

In theory they should work the same, as far as I can tell. The second button is defining exactly the same range to be sorted as the first, it's just defining it ahead of time instead of at sort time. If I change .Sort to .Select they both select the same range, so it is not an issue with how I'm defining my range, just with sorting it.

This only happens on excel for mac; on a PC this works fine.

The mac is an Apple silicon MacBook Air running Big Sur v11.3. Excel for mac is version 16.48 (both are the latest versions at the time of writing).

What can I do to resolve this, other than explicitly defining my ranges at the time of sorting instead of using a range object?

Developer technologies | Visual Basic for Applications

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.