如何在 Excel 2003 中使用 VBA 对混合日期和常规格式数据的列进行排序

Question

首先，如果我在最佳 VBA 实践中犯了很多错误，我深表歉意。我一直在专门为这个项目学习语言，而且我可能做错了很多事情，如果我让任何人感到畏缩，我很抱歉。

接下来，问题来了。我正在尝试按日期对范围（保存在一列中）进行排序，这与工具菜单上的排序功能在对“任何看起来像数字的东西作为数字”进行排序时的工作方式完全相同。该列是英国区域设置日期和“常规”格式单元格中保存的文本字符串的混合，这些单元格本质上只是日期。换句话说，像这样简单的事情：

Range(rngFirstCell, rngLastCell).Sort Key1:= 2, Order1:=xlAscending, _
 DataOption1:=xlSortTextAsNumbers, Header:=xlYes

应该可以解决问题。事实上，我认为唯一的例外是录制的代码使用 xlGuess 作为标头，并包含 OrderCustom 值 1，这正是宏录制器生成的内容。不用说，我已经尝试过记录代码，得到了相同的结果。

问题是，而不是得到：

Type    Date
gen     01/3/2008
date    02/4/2008
date    17/4/2008
gen     25/7/2009

我得到：

Type    Date
date    02/4/2008
date    17/4/2008
gen     01/3/2008
gen     25/7/2009

由于这适用于更高版本的 Excel，我得出结论这是 2003 年的错误。我当前的解决方案是首先将列中所有单元格的 NumberFormat 属性设置为“ d/m/yyyy”，然后迭代它们并将每个值替换为 CDate(Cell.Value) 的结果。它使排序工作。重新格式化包含 20 个条目的列也需要 10 秒，因为工作表和 VBA 之间有太多交互（根据我的阅读，速度很慢）。由于我需要按代码排序的某些数据集完全有可能有数百个单元格长，因此我需要更快的数据集。

任何人都可以建议更好的方法吗？

为了清楚起见，我现在使用的代码看起来非常像：

Range(rngFirstCell, rngLastCell).Columns(2).NumberFormat = "d/m/yyyy"
Dim intIndex As Long, varCellRef As Variant
For intIndex = 0 to Range(rngFirstCell, rngLastCell).Columns(2).End(xlDown).Row
    Set varCellRef = Range(rngFirstCell, rngLastCell).Columns(2)(intIndex)
    varCellRef.Value = CDate(varCellRef.Value)
Next
Range(rngFirstCell, rngLastCell).Sort Key1:= 2, Order1:=xlAscending, _
 DataOption1:=xlSortTextAsNumbers, Header:=xlYes

Answer 1

您说在循环中引用工作表很慢是正确的，但是可以通过将数据复制到变体数组并循环遍历它，然后复制回工作表来避免这种情况：

Dim rngFirstCell As Range
Dim rngLastCell As Range

' Setting a sample range for my testing...
Set rngFirstCell = [B12]
Set rngLastCell = [C131084]

Dim dat As Variant
Dim rng As Range
Dim i As Long
Set rng = Range(rngFirstCell, rngLastCell) ' this includes the header row
dat = rng.Columns(2)
rng.Columns(2).NumberFormat = "d/m/yyyy"
Dim intIndex As Long, varCellRef As Variant

For i = 2 To UBound(dat, 1)
    dat(i, 1) = CDate(dat(i, 1))
Next
rng.Columns(2) = dat

rng.Sort Key1:=rng.Cells(1, 2), Order1:=xlAscending, _
 DataOption1:=xlSortTextAsNumbers, Header:=xlYes

这在 << 中的示例数据集上运行。 1 秒（约 130,000 行）

注意，我花了几个星期才让它运行