不等式运算符在比较变量时失败

Question

我写了一个算法，可以按升序对整数值进行排序，但保持相邻单元格中的相应信息与之一致。它使用Arr作为排序单元格的快照，如果按升序排序，则构建值的索引数组（TagIndex），然后将TagIndex应用于该单元格和相邻单元格。

例如，它应该采取这个。。。

----------------------------------
| 5/14/12  |  87  |  91  |  102  |
| 12/8/11  |  96  |  81  |  93   |
| 9/30/10  |  75  |  101 |  74   |
| 4/26/08  |  107 |  95  |  64   |
----------------------------------

并按最左边的第二列排序，将其变成这样：

----------------------------------
| 9/30/10  |  75  |  101 |  74   |
| 5/4/12   |  87  |  91  |  102  |
| 12/8/11  |  96  |  81  |  93   |
| 4/26/08  |  107 |  95  |  64   |
----------------------------------

这是代码：

Dim cell as Range
Dim Arr, TempArr, BoundVal As Variant
For Each cell In ActiveSheet.ListObjects("Table2").ListColumns(targetColumn).DataBodyRange
    Arr = Split(cell.Value, Chr(10))
    ReDim TagIndex(0 To UBound(Arr)) As Variant
    For i = 0 To UBound(Arr)
        BoundVal = Arr(i) 'starts with first value and index
        TagIndex(i) = i   'as defaults
        For j = 0 To UBound(Arr)
            If Arr(j) < BoundVal Then  'if sorter finds a smaller value,
                BoundVal = Arr(j)      'flags it...
                TagIndex(i) = j        '...and its index as smaller,
            End If                     'keeps looking,
        Next j                         'leaves For loop with the smallest,
        Arr(TagIndex(i)) = 201         'and moves it up out of reach so sorter won't 
    Next i                             'flag it anymore (none of the values go above 200)
    For j = leftBoundColumn To rightBoundColumn 
        TempArr = Split(Cells(cell.Row, j).Value, Chr(10))
        For i = 0 To UBound(TempArr)
            Arr(i) = TempArr(TagIndex(i))
        Next i
        Cells(cell.Row, j).Value = Join(Arr, Chr(10))
    Next j
Next cell

这段代码一开始运行得很好，但我有两个独立的版本——一个用于对整数排序，另一个用于排序日期——我想要一个能同时处理这两个问题的版本。为此，我尝试在新版本中将BoundVal声明为Variant。当结果变得不稳定时，对MsgBox的热衷使用表明它在<接线员，试图告诉我96<201和对于107<75（但117/107没有，就像它应该有的那样）。

如果我回到将BoundVal声明为Integer，它开始对整数正常工作，但当我在日期上尝试它时，会出现类型不匹配错误。

比较Arr（j）<BoundVal？两者都是变体，都是字符串的后裔。有什么想法吗？

Answer 1

您需要远离所有这些Variant变量。这种类型（类似于将变量声明为Object）应该是您最后的选择。Variant可以是任何东西，甚至可能在代码过程中发生变化（如上所示）。因此，如果你用它们来将它们与其他东西进行比较，那么你可能会将Boolean（TRUE或FALSE）与Date、String或其他东西进行对比。VBA可以让你掌控一切！所以，我建议你就这么做。

话虽如此，由于我们缺少一些代码（显然，上面的代码是不完整的，因为使用了一些既没有声明也没有初始化的变量），这里有一些关于如何改进代码并可能自己获得工作结果的提示：

（1） 如果您想在一行中声明多个变量，那么您仍然需要为每个变量重复变量类型。所以，你需要写

Dim Arr as Variant, TempArr as Variant, BoundVal As Long

（2） 尽可能避免使用Variant，而是使用适当的类型。例如，BoundVal似乎存储整数或日期，因此数据类型Long可能是最好的。

（3） 数组通常（VBA默认值）在0到ArrayCount-1之间。因此，基数设置为0:Option Base 0https://msdn.microsoft.com/en-us/library/aa266179（v=vs.60）.aspx不过，为了让你的代码持续时间更长（更透明），我会在你的代码中包含这一行，并将你的循环更改为

For j = LBound(Arr) To UBound(Arr)

而不是

For j = 0 To UBound(Arr)

（4） 强迫自己声明所有变量，以确保您知道发生了什么：Option Explicithttps://msdn.microsoft.com/en-us/library/office/gg278855.aspx

（5） 尽可能使用.Value2而不是.Value以获得少量速度奖励。此外，这基本上只会以数字的形式给你日期，而不是日期²。

在所有上述更改之后，您的代码应该如下所示：

Option Base 0
Option Explicit
Sub tmpSO(Optional targetColumn As Long, Optional leftBoundColumn As Long, Optional rightBoundColumn As Long)
Dim cell As Range
Dim Arr As Variant, TempArr As Variant
Dim BoundVal As Long, TagIndex() As Long, i As Long, j As Long
'set the defaults for optional parameters
If targetColumn = 0 Then targetColumn = 2
If leftBoundColumn = 0 Then leftBoundColumn = 1
If rightBoundColumn = 0 Then rightBoundColumn = ActiveSheet.ListObjects("Table2").ListColumns.Count
For Each cell In ActiveSheet.ListObjects("Table2").ListColumns(targetColumn).DataBodyRange
    Arr = Split(cell.Value2, Chr(10))
    ReDim TagIndex(LBound(Arr) To UBound(Arr))
    For i = LBound(Arr) To UBound(Arr)
        'starts with first value and index, use default 0 if necessary
        If IsDate(Arr(i)) Then             'if the item can be interpreted as a date then
            BoundVal = CLng(CDate(Arr(i))) 'convert the string to a date and convert the date to a number
        Else                               'otherwise treat it as a number only
            BoundVal = CLng(IIf(IsNumeric(Arr(i)), Arr(i), 0))
        End If
        TagIndex(i) = i                    'as default
        For j = LBound(Arr) To UBound(Arr)
            If CLng(IIf(IsDate(Arr(j)), CDate(Arr(j)), Arr(j))) < BoundVal Then      'if sorter finds a smaller value,
                BoundVal = CLng(IIf(IsDate(Arr(j)), CDate(Arr(j)), Arr(j)))          'flags it...
                TagIndex(i) = j            '...and its index as smaller,
            End If                         'keeps looking,
        Next j                             'leaves For loop with the smallest,
        Arr(TagIndex(i)) = 201             'and moves it up out of reach so sorter won't
    Next i                                 'flag it anymore (none of the values go above 200)
    For j = leftBoundColumn To rightBoundColumn
        TempArr = Split(Cells(cell.Row, j).Value, Chr(10))
        For i = 0 To UBound(TempArr)
            Arr(i) = TempArr(TagIndex(i))
        Next i
        Cells(cell.Row, j).Value2 = Join(Arr, Chr(10))
    Next j
Next cell
End Sub

免责声明：我知道这个答案还不完整，上面的代码可能需要一些调整，然后才能真正做到你想要的。然而，代码工作没有错误（在我的系统上测试），并引入了一些最佳实践，让你朝着正确的方向前进，（可能）解决所有障碍。

_{²VBA中的所有日期和时间都是数字！日期存储为1899年12月31日至您希望存储的日期之间的天数，而时间始终存储为小数，表示一天中的一小部分。因此，0.5=半天=中午12点。0.75=一天中的三个季度=下午6点。今天的日期是2016年5月4日。这是1899年12月31日之后的42495天。因此，今天日期的.Value2为42495。}