Excel VBA排序使Excel for Mac崩溃 [英] Excel VBA sort crashes Excel for Mac

问题描述

我有一个带有大量VBA的excel电子表格.一切都可以在PC上正常运行.我现在正在安装了Office 365 for Mac的Mac上对其进行测试.Mac正在运行Big Sur v11.3.

I have an excel spreadsheet with a lot of VBA behind it. It all runs fine on a PC. I'm now testing it on a Mac with Office 365 for Mac installed. The mac is running Big Sur, v11.3.

所有更复杂的代码都可以正常工作，但是简单的排序功能会导致excel崩溃.它不会引发VBA错误-它只会关闭Excel.即使我使用 On Error GoTo Something ，所以我可以例如生成错误消息框，它甚至都不会这样做-遇到此行代码后，excel就消失了.

All of the more complicated code works just fine, but a simple sort function causes excel to crash. It doesn't throw a VBA error - it just flat out shuts down excel. Even if I use On Error GoTo Something so I can e.g. generate an error messsagebox, it won't even do that - as soon as it encounters this line of code, excel is gone.

导致崩溃的代码行是:

rngAllData.Sort key1:= Cells(3，colNum)，order1:= xlDescending

rngAllData 是有效范围，如果我将代码行更改为简单的 rngAllData.Select ，它将选择我希望排序的范围，没有问题.似乎似乎不喜欢Sort函数.

rngAllData is a valid range, and if I change the line of code to simply rngAllData.Select it selects the range I'm expecting to sort, no problems. It just doesn't seem to like the Sort function.

我创建了一个新的Excel文档，在列A中添加了一些随机数，并创建了一个调用 Range("A1:A8")的按钮.对key1:= Range("A1")进行排序/code>，结果是一样的-即时崩溃.因此，问题显然出在 Range.Sort

I have created a new excel document, added some random numbers into column A, and created a button that calls Range("A1:A8").Sort key1:=Range("A1") and the results are the same - instant crash. So the problem is clearly with the Range.Sort

鉴于没有收到任何错误消息，我该如何进行故障排除?如何修改代码，使其在PC和Mac上均可使用?

How do I go about troubleshooting, given I don't get any error messages? How can I modify the code so that it works on both PC and mac?

完整的子代码复制到下面，变量在其他地方声明，同样，这在PC上可以正常工作，因此它不应该像未声明的变量问题一样.

The full sub is copied below, the variables are declared elsewhere, and again, this works fine on a PC so it shouldn't be anything like a non-declared variable issue.

Sub SortData()
'This sub is called to sort table data
On Error GoTo errHandler

'Determine column number of clicked button
colNum = ActiveSheet.Buttons(Application.Caller).TopLeftCell.Column

'If the selected sort column is different to the last sorted column, reset the sort order back to default
If colNum <> lastSortCol Then sortOrder = False
lastSortCol = colNum

'Determine range of data
Set rngAllData = Range("A1").CurrentRegion.Offset(2)
Set rngAllData = rngAllData.Resize(rngAllData.Rows.Count - 2, rngAllData.Columns.Count - 1)

'Sort data
If sortOrder Then rngAllData.Sort key1:=Cells(3, colNum), order1:=xlDescending
If Not sortOrder Then rngAllData.Sort key1:=Cells(3, colNum), order1:=xlAscending
sortOrder = Not (sortOrder)
Exit Sub

errHandler:
MsgBox "Error in SortData.", vbCritical

End Sub

推荐答案

似乎Mac的excel不喜欢对范围对象进行排序.

It seems that excel for mac doesn't like to sort range objects.

我的数据表大小可能有所不同，因此我使用 Range("A1").CurrentRegion 查找要排序的数据范围.然后，我需要打折三行标题和最后一列(其中有些控件应该不进行排序)，所以我使用了 Range.Offset 和Range.Resize .而不是给我的 Range.Sort 一个巨大的序言并使该行延伸到页面的一侧，我想我只是创建一个范围对象来引用:

My data table can vary in size, so to find the range of data to sort, I use Range("A1").CurrentRegion. Then I need to discount the three header rows, and the last column (which has some controls that should not be sorted), so I use Range.Offset and Range.Resize. Rather than have a huge preamble to my Range.Sort and make the line stretch off the side of the page, I figured I'd just create a range object to refer to:

'Determine range of data
Set rngAllData = Sheets("Job List").Range("A1").CurrentRegion.Offset(2)
Set rngAllData = rngAllData.Resize(rngAllData.Rows.Count - 2, rngAllData.Columns.Count - 1)

'Sort data
If sortOrder Then rngAllData.Sort key1:=Cells(3, colNum), order1:=xlDescending
If Not sortOrder Then rngAllData.Sort key1:=Cells(3, colNum), order1:=xlAscending
sortOrder = Not (sortOrder)

当我在任何范围对象上尝试 Range.Sort 操作时，意外的行为就会出现.

The undesired behaviour appears as soon as I try a Range.Sort operation on any range object.

如果相反，我会长期这样做:

If, instead, I do it longhand:

'Determine range of data
Set rngAllData = Sheets("Job List").Range("A1").CurrentRegion.Offset(2)

'Sort data
If sortOrder Then Sheets("Job List").Range("A1").CurrentRegion.Offset(2).Resize(rngAllData.Rows.Count - 2, rngAllData.Columns.Count - 1)
.Sort key1:=Cells(3, colNum), order1:=xlDescending
If Not sortOrder Then Sheets("Job List").Range("A1").CurrentRegion.Offset(2).Resize(rngAllData.Rows.Count - 2, rngAllData.Columns.Count - 1)
.Sort key1:=Cells(3, colNum), order1:=xlAscending
sortOrder = Not (sortOrder)

...然后看起来很恐怖，但是可以正常工作.

...then it looks horrible, but it works.

我猜测这是Mac的excel中的错误，而不是我本来就做错的事情，但是由于我有一个更具体确定的问题，我将发布另一个线程，看看是否可以找到一个整理它的方式.

I'm guessing that this is a bug in excel for Mac, rather than something I'm inherently doing wrong, but I will post another thread now that I have a more specifically identified issue, and see if I can find a way to tidy it up.

后续帖子在这里

这篇关于Excel VBA排序使Excel for Mac崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！