VBA运行时错误1004：无法获取WorksheetFunction类的VLOOKUP属性

问题背景 #

代码正常运行七个月，近几周持续触发运行时错误1004，提示“无法获取WorksheetFunction类的VLOOKUP属性”，触发行：

A = WorksheetFunction.VLookup(P, TableArray, 8, False)

特殊现象：当P不存在时系统可正常提示“未找到数据”，但P实际存在时必触发错误。

相关代码 #

Sub import()

Dim 1_wb As Workbook
Dim Sheet1, Sheet2 As Worksheet
Dim A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S As Variant
Dim FirstRow, LastRow As Long
Dim FirstColumn, LastColumn As String
Dim TableArray As Range

Set Sheet1 = Worksheets("Sheet1")

P = Sheet1.Range("C4").Value
If P = "" Then
    Sheet1.OLEObjects("TextBox1").Object.Text = ""
    MsgBox "Please Input P"
    Exit Sub
End If

Application.ScreenUpdating = False
Set 1_wb = Workbooks.Open(FileName:="x.xlsm", ReadOnly:=True)
Set Sheet2 = 1_wb.Worksheets("Sheet2")
FirstColumn = "F"
LastColumn = "GP"
FirstRow = 23
LastRow = 3617
Set TableArray = Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn))
Application.ScreenUpdating = True

On Error Resume Next
    Sheet.Columns(6).Hidden = False
On Error GoTo 0

If Not Sheet1.Range("F:F").Find(P, , xlValues, xlWhole) Is Nothing Then

    A = WorksheetFunction.VLookup(P, TableArray, 8, False)
    B = WorksheetFunction.VLookup(P, TableArray, 42, False)
    C = Format(WorksheetFunction.VLookup(P, TableArray, 32, False), "dd/mm/yyyy")
    D = WorksheetFunction.VLookup(P, TableArray, 22, False)
    E = Format(WorksheetFunction.VLookup(P, TableArray, 24, False), "dd/mm/yyyy")
    F = Format(WorksheetFunction.VLookup(P, TableArray, 25, False), "dd/mm/yyyy")
    G = WorksheetFunction.VLookup(P, TableArray, 23, False)
    H = WorksheetFunction.VLookup(P, TableArray, 27, False)
    I = WorksheetFunction.VLookup(P, TableArray, 29, False)
    J = WorksheetFunction.VLookup(P, TableArray, 85, False)
    K = WorksheetFunction.VLookup(P, TableArray, 84, False)
    L = WorksheetFunction.VLookup(P, TableArray, 35, False)
    M = WorksheetFunction.VLookup(P, TableArray, 30, False)
    N = WorksheetFunction.VLookup(P, TableArray, 175, False)
    O = WorksheetFunction.VLookup(P, TableArray, 171, False)
    Q = WorksheetFunction.VLookup(P, TableArray, 117, False)
    R = WorksheetFunction.VLookup(P, TableArray, 39, False)
    S = WorksheetFunction.VLookup(P, TableArray, 163, False)
    
    Sheet1.OLEObjects("TextBox1").Object.Text = "A: " & A & vbNewLine & _
                                                "B: " & B & vbNewLine & _
                                                "C: " & C & vbNewLine & _
                                                "D: " & D & vbNewLine & _
                                                vbNewLine & _
                                                "E: " & E & vbNewLine & _
                                                "F: " & F & vbNewLine & _
                                                "G: " & G & vbNewLine & _
                                                "H: " & H & vbNewLine & _
                                                vbNewLine & _
                                                "I: " & I & vbNewLine & _
                                                "J: " & J & vbNewLine & _
                                                "K: " & K & vbNewLine & _
                                                "L: " & L & vbNewLine & _
                                                "M: " & M & vbNewLine & _
                                                vbNewLine & _
                                                "N: " & N & vbNewLine & _
                                                "O: " & O & vbNewLine & _
                                                "Q: " & Q & vbNewLine & _
                                                "R: " & R & vbNewLine & _
                                                "S: " & S
    1_wb.Close SaveChanges:=False
Else:
    1_wb.Close SaveChanges:=False
    Sheet1.OLEObjects("TextBox1").Object.Text = ""
    MsgBox "No Data Found for " & P & "."
End If

End Sub

排查与解决方案 #

1. 修复TableArray的工作表归属问题 #

代码中TableArray未指定所属工作表，默认指向当前激活工作表，可能与目标Sheet2不一致，导致查找范围错误。修改为：

Set TableArray = Sheet2.Range(Sheet2.Cells(FirstRow, FirstColumn), Sheet2.Cells(LastRow, LastColumn))

2. 替换为容错性更强的Application.VLookup #

WorksheetFunction.VLookup在目标单元格返回错误值（如#N/A、#VALUE!）时会直接抛出1004错误，改用Application.VLookup返回错误值而非报错，配合IsError处理：

' 示例：处理A值的获取
Dim vResult As Variant
vResult = Application.VLookup(P, TableArray, 8, False)
A = IIf(IsError(vResult), "", vResult)

' 日期格式处理示例
vResult = Application.VLookup(P, TableArray, 32, False)
C = IIf(IsError(vResult), "", Format(vResult, "dd/mm/yyyy"))

3. 统一数据类型匹配 #

检查P的数据类型与TableArray第一列的类型是否一致，若存在文本/数字不匹配，统一转换类型：

' 将P转为文本类型，匹配查找列的文本格式
P = CStr(Sheet1.Range("C4").Value)

4. 验证查找范围的列数是否足够 #

确认TableArray的列数是否覆盖所有使用的列索引（最大为175），可添加代码验证：

Dim totalColumns As Long
totalColumns = Sheet2.Columns(LastColumn).Column - Sheet2.Columns(FirstColumn).Column + 1
If totalColumns < 175 Then
    MsgBox "查找范围列数不足，当前仅" & totalColumns & "列，需至少175列"
    1_wb.Close SaveChanges:=False
    Exit Sub
End If

5. 修复未定义的Sheet变量 #

代码中Sheet.Columns(6).Hidden = False的Sheet未声明，属于笔误，改为目标工作表（如Sheet1）：

On Error Resume Next
    Sheet1.Columns(6).Hidden = False
On Error GoTo 0

内容的提问来源于stack exchange，提问作者Noelia Peiró