An implementation of Visual Basic that is built into Microsoft products.
Excel for Mac crashes when using Range.Sort on range object
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?