VBA函数partnum_position触发Expected Array Error问题求助

我编写了两个用于查找指定表头列索引的VBA函数，其中sys_position函数运行正常，可弹出提示框；但partnum_position函数却触发了Expected Array Error错误。我能发现的两者唯一区别是，两个函数中Set sys_Rng = sht.Cells.Find()指定的表头内容不同。请问该问题的原因是什么？

原始代码 #

Main 过程

Public Sub Main()
Dim wb As Workbook, ws As Worksheet, system_position As Integer, partnum_position As Integer

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

 system_position = sys_position(ws)
 partnum_position = partnum_position(ws)

 End Sub 

sys_position 函数

Function sys_position(sys_Rng As Variant) As Integer 
Dim sht As Worksheet
Set sht = Worksheets("Sheet1") '定义起始工作表
Set sys_Rng = sht.Cells.Find("System Number(s)") '设置要查找的值

    If Not sys_Rng Is Nothing Then '如果找到则执行此操作
       MsgBox ("System Number Column is " & sys_Rng.Column)
    Else
       MsgBox ("No Header Found.")
    End If
Set sys_Rng = Nothing '清除变量
Set sht = Nothing '清除变量
End Function

partnum_position 函数

Function partnum_position(partnum_Rng As Variant) As Integer 
Dim sht As Worksheet
Set sht = Worksheets("Sheet1") '定义起始工作表
Set partnum_Rng = sht.Cells.Find("Part Number")

    If Not partnum_Rng Is Nothing Then
       MsgBox ("Part Number Column is " & partnum_Rng.Column)
    Else
       MsgBox ("No " & partnum_Rng & " Header Found.")
    End If
Set partnum_Rng = Nothing '清除变量
Set sht = Nothing '清除变量
End Function

编辑后代码 #

Main 过程

Public Sub Main()
 Dim wb As Workbook, ws As Worksheet, i As Range, dict As Object, system_position As Long, partnum_position As Long
Dim wbSrc As Workbook

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

Call position 
 End Sub 

position 子过程

Sub position() 

 Dim sht As Worksheet
 Dim sys_Rng As Range, partnum_Rng As Range, syswaivernum As Range, partnumber As Range

Set sht = Worksheets("Sheet1") '定义起始工作表
Set sys_Rng = sht.Cells.Find("System Waiver Number(s)") '设置要查找的值
Set partnum_Rng = sht.Cells.Find("Axsun Part Number")

If Not sys_Rng Is Nothing Then '如果找到则执行此操作
    MsgBox ("System Waiver Number Column is " & sys_Rng.Column)
Else
    MsgBox ("No Header Found.")
End If

If Not partnum_Rng Is Nothing Then
    MsgBox ("Axsun Part Number Column is " & partnum_Rng.Column)
Else
    MsgBox ("No " & partnum_Rng & " Header Found.")
End If

Set sys_Rng = Nothing '清除变量
Set partnum_Rng = Nothing '清除变量
Set sht = Nothing '清除变量
 End Sub

1. 核心错误原因 #

partnum_position函数报错的本质是未找到目标表头时的错误处理逻辑错误：
当Find方法找不到"Part Number"时，partnum_Rng会被赋值为Nothing（空对象），而你在错误提示代码里写了MsgBox ("No " & partnum_Rng & " Header Found.")——试图将空对象与字符串拼接，这会直接触发类型不匹配错误（即你看到的Expected Array Error）。

对比sys_position的错误处理，它直接显示固定文本"No Header Found."，没有尝试拼接空对象，因此不会报错。

2. 额外逻辑问题 #

原始代码还有几个不合理的地方：

• 函数参数sys_Rng/partnum_Rng完全未被使用，函数内部重新定义了工作表并覆盖参数变量，属于冗余且易混淆的写法
• 函数声明返回Integer，但实际未给函数赋值（比如sys_position = sys_Rng.Column），调用时接收返回值没有意义
• 用Integer存储列索引存在溢出风险，Excel列数超过32767时会报错，应改用Long类型

3. 原始代码修正方案 #

修复partnum_position的错误提示逻辑，同时优化函数参数与返回值：

Function partnum_position(sht As Worksheet) As Long
    Dim partnum_Rng As Range
    Set partnum_Rng = sht.Cells.Find("Part Number")

    If Not partnum_Rng Is Nothing Then
       MsgBox ("Part Number Column is " & partnum_Rng.Column)
       partnum_position = partnum_Rng.Column '给函数赋值，返回列索引
    Else
       MsgBox ("No Part Number Header Found.") '直接使用固定文本，避免拼接空对象
       partnum_position = 0 '未找到时返回0作为标记
    End If
    Set partnum_Rng = Nothing
End Function

'同步修正sys_position函数
Function sys_position(sht As Worksheet) As Long
    Dim sys_Rng As Range
    Set sys_Rng = sht.Cells.Find("System Number(s)")

    If Not sys_Rng Is Nothing Then
       MsgBox ("System Number Column is " & sys_Rng.Column)
       sys_position = sys_Rng.Column
    Else
       MsgBox ("No System Number Header Found.")
       sys_position = 0
    End If
    Set sys_Rng = Nothing
End Function

4. 编辑后代码的修正点 #

你将两个函数合并为position子过程的写法更简洁，但同样存在错误提示的问题：当前代码中partnum_Rng未找到时，还是会执行MsgBox ("No " & partnum_Rng & " Header Found.")，需要修改为固定文本：

If Not partnum_Rng Is Nothing Then
    MsgBox ("Axsun Part Number Column is " & partnum_Rng.Column)
Else
    MsgBox ("No Axsun Part Number Header Found.")
End If

内容的提问来源于stack exchange，提问作者user20114520