调用函数时触发「Invalid Procedure Call or Argument」错误的排查求助

问题说明 #

执行代码行copy = Application.WorksheetFunction.CountIfs(parameter_Rng, parametername, routing_Rng, routingname)时，触发Invalid Procedure Call or Argument运行时错误。新增Dim startrow As Long及startrow = getrowindex(sysnum, "SD Revision", "Test-Config-OCT")代码后出现该问题，getrowindex函数此前多次调用均能正常返回整数值。

错误原因分析 #

• 全局变量初始化顺序冲突：调用getrowindex时，全局变量parameter_Rng和routing_Rng尚未在Main过程中完成赋值（startrow = getrowindex(...)执行在前，Set parameter_Rng/ routing_Rng执行在后），导致CountIfs接收的是未初始化的无效Range对象。
• Range范围引用错误：Main过程中设置routing_Rng时，误用ws.Rows.Count替代SDtab.Rows.Count，导致范围边界无效。
• CountIfs参数要求不满足：CountIfs的所有条件区域必须具有相同的行数和列数，若parameter_Rng与routing_Rng行数不一致，会直接触发该错误。

解决方案 #

1. 消除全局变量依赖
   将getrowindex改为通过参数接收条件区域，避免作用域和初始化顺序问题，代码逻辑更清晰可控。

2. 修复Range范围引用
   修正routing_Rng的范围代码，确保使用正确的工作表对象：

Set routing_Rng = SDtab.Range(SDtab.Cells(startrow, RoutingStep), SDtab.Cells(SDtab.Rows.Count, RoutingStep).End(xlUp))

3. 调整代码执行顺序
   若仍需使用全局变量，需确保在调用getrowindex之前，完成parameter_Rng和routing_Rng的初始化。

4. 改用容错性调用方式
   使用Worksheet.CountIfs替代Application.WorksheetFunction.CountIfs，触发错误时返回错误值而非直接抛出运行时错误，便于调试：

copy = SDtab.CountIfs(parameter_Rng, parametername, routing_Rng, routingname)
If IsError(copy) Then
    MsgBox "CountIfs执行出错，请检查条件区域或参数", vbExclamation
    Stop
End If

修正后完整代码示例 #

Main过程 #

Global Parameter As Long, RoutingStep As Long
Public Sub Main() 

Dim wb As Workbook, ws As Worksheet, dict As Object, sysrow As Integer, sysnum As String, wsName As String, wbSrc As Workbook, SDtab As Worksheet 

Dim spectyp As Long, specmin As Long, specmax As Long
Dim cell As Range, syswaiver As Long, axsunpart As Long
Dim wavelength_col As Long, sweeprate_col As Long, coherence_length_col As Long, power_col As Long, kclock_depth_col As Long, kclock_count_col As Long 
Dim wavelength_value As Double, coherencelength_value As Double
Dim wavelength_rowindex As Double, wavelength_rowindex_1 As Double, coherencelength_rowindex As Double

Dim startrow As Long  
Dim parameter_Rng As Range, routing_Rng As Range 

' 补充SDtab初始化（原代码遗漏，需替换为实际工作表名）
Set SDtab = ThisWorkbook.Worksheets("Test-Config-OCT") 

Parameter = getcolumnindex(SDtab, "PARAMETER") 
RoutingStep = getcolumnindex(SDtab, "Routing Step 1") 

' 先初始化条件区域，再调用getrowindex
Set parameter_Rng = SDtab.Range(SDtab.Cells(1, Parameter), SDtab.Cells(SDtab.Rows.Count, Parameter).End(xlUp))
Set routing_Rng = SDtab.Range(SDtab.Cells(1, RoutingStep), SDtab.Cells(SDtab.Rows.Count, RoutingStep).End(xlUp))

startrow = getrowindex(sysnum, "SD Revision", "Test-Config-OCT", parameter_Rng, routing_Rng) 

End Sub

getrowindex函数 #

Function getrowindex(WDnum As String, parametername As String, routingname As String, _
                    paramRng As Range, routingRng As Range, _
                    Optional partialFirst As Boolean = False, Optional partialSecond As Boolean = False) As Long

Dim ws As Worksheet, rowname As Range, addr As String, copy As Variant, Output As Integer 
Set ws = ThisWorkbook.Worksheets(WDnum) 

Set rowname = ws.Columns(Parameter).Find(What:=parametername, Lookat:=IIf(partialFirst, xlPart, xlWhole), LookIn:=xlFormulas, MatchCase:=True) 

' 容错式调用CountIfs
copy = ws.CountIfs(paramRng, parametername, routingRng, routingname) 

If IsError(copy) Then
    Output = MsgBox("CountIfs执行出错，请检查条件区域是否有效", vbCritical)
    Stop
ElseIf copy > 1 Then
    Output = MsgBox("组合条件 '" & parametername & "' 和 '" & routingname & "' 出现多行匹配，请检查后重试", vbCritical) 
    Stop 
End If

If Not rowname Is Nothing Then 
    addr = rowname.Address 
    If partialSecond Then routingname = "*" & routingname & "*" 
    Do 
        If rowname.Offset(0, 1).Value Like routingname Then 
            getrowindex = rowname.Row 
            Exit Do 
        End If 
        Set rowname = ws.Columns(Parameter).FindNext(after:=rowname) 
    Loop While rowname.Address <> addr 
Else
    Output = MsgBox("未找到参数行 '" & parametername & "'，请检查后重试", vbCritical) 
    Stop 
End If 

End Function 

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