VBA数据类型不匹配

Question

我正在使用日期作为我的数据，我认为这可能会导致一些问题。

Sub test()

    Dim counter As Long
    For counter = 1 to 10

        Dim fltArr(0 to 9)
        Dim X 
        Dim Largest As Date

        For items = 3 to 12
        fltArr(items-3) = Cells(items, 6)
        Next

        X = fltArr
        Largest = Application.Large(X, counter)

    Next
End Sub

行中似乎出现了不匹配错误

Largest = Application.Large(X, counter)

我认为这可能是由于Application.Large给出了一个整数而不是日期。我该怎么解决这个问题呢？

Answer 1

Value2属性和Value属性之间的唯一区别是Value2属性不使用Currency和Date数据类型。通过使用Double数据类型，可以将使用这些数据类型格式化的值返回为浮点数。Reference

您所要做的就是更改行

fltArr(items-3) = Cells(items, 6)

至

fltArr(items-3) = Cells(items, 6).Value2

尝尝这个

Sub test()
    Dim counter As Long
    Dim Largest As Date

    For counter = 1 To 10
        Dim fltArr(0 To 9) As Variant
        Dim X As Variant

        For items = 3 To 12
            fltArr(items - 3) = Cells(items, 6).Value2
        Next

        X = fltArr

        Largest = Application.Large(X, counter)
        Debug.Print Largest
    Next
End Sub

Answer 2

Large不喜欢使用日期数组。如果您将数组声明为doubles，它将返回您想要的值(使用您的代码)：

Sub test()

    Dim counter As Long
    For counter = 1 To 10

        Dim fltArr(0 To 9) As Double
        Dim X() As Double
        Dim Largest As Date

        For items = 3 To 12
        fltArr(items - 3) = Cells(items, 6)
        Next

        X = fltArr
        Largest = Application.Large(Range("F3:F12"), counter)
        Debug.print Largest

    Next
End Sub