在VBA中使用VLOOKUP()函数

Question

我们在一个工作表中有一个简短的表格，如下所示：

A1到A3单元格中的值是以下公式的结果：

=DATE(1999,12,15)
=DATE(1945,1,18)
=DATE(2020,2,23)

如果我在工作表单元格中尝试使用VLOOKUP()函数：

=VLOOKUP(DATE(1945,1,18),A1:B3,2)

我得到了正确的结果(Moe)：

然而在VBA中：

Sub RetrieveName()
    Dim d As Date, nam As String, rng As Range
    d = DateSerial(1945, 1, 18)
    Set rng = Range("A1:B3")
    nam = Application.WorksheetFunction.VLookup(d, rng, 2)
    MsgBox nam
End Sub

我遇到了一个错误：

目前我们的解决办法是使用：

Sub RetrieveName2()
    Dim d As Long, nam As String, rng As Range
    d = 16455
    Set rng = Range("A1:B3")
    nam = Application.WorksheetFunction.VLookup(d, rng, 2)
    MsgBox nam
End Sub

我真的看不出原始代码有什么问题?

- Gary's Student

---

声明d为double类型并使用cdbl(dateserial(1945,1,18))。不确定原因，但我认为VBA将日期视为字符串，并且从工作表中传入的值是一个数字。 - Scott Craner

或者您可以将 d 保留为日期类型，但在调用 VLOOKUP 函数时使用 cDbl(d)。我怀疑原因是 Excel 将试图将日期变量转换为 Excel 日期，这不是本地的数据类型，而是一个带有格式说明的双精度浮点数：如果您直接将 d 作为日期传递给 Range.Value，它会将值作为数字传递并将其格式化为日期。如果您使用 Range.Value2，则它将显示为没有日期格式的数字。 - Charles Williams

如果您将d定义为一个变量，并从工作表中检索它使用d=Range("a2")，默认情况下为.Value，那么VLOOKUP函数会失败：这是始终使用.Value2的另一个原因！ - Charles Williams

请查看此链接：https://dev59.com/2nrZa4cB1Zd3GeqP9v1m - Tom Sharpe

@TomSharpe 谢谢！ - Gary's Student

Answer 1

Scott Craner说在将VBA日期转换为Double之前传递给VLOOKUP函数是可行的，这是正确的。
我认为问题在于Excel和VBA使用Range.Value规则将值传回VLOOKUP函数。Excel没有真正的日期数据类型-Excel中的日期是带有日期格式的双精度浮点数。 当传递VBA日期返回到Excel时，Range.Value会尝试将格式为日期的Excel数字转换为VBA日期，反之亦然，但是VLOOKUP函数不理解带有格式代码的数字。

Sub RetrieveName()
    Dim d As Double, nam As Variant, rng As Range
    d = DateSerial(1945, 1, 18)
    Set rng = Range("A1:B3")
    nam = Application.WorksheetFunction.VLookup(d, rng, 2)
    MsgBox nam
End Sub

Sub RetrieveName2()
    Dim d As Variant, nam As Variant, rng As Range
    '
    ' note Range("a2").value will fail
    '
    d = Range("a2").Value2
    Set rng = Range("A1:B3")
    nam = Application.WorksheetFunction.VLookup(d, rng, 2)
    MsgBox nam
End Sub

- Charles Williams