Excel VBA向子程序传递数组时遇类型不匹配问题求助

1. 数组传递的语法错误 #

调用子程序时，数组参数不需要加括号，原代码里的Call runFinished(sFrDt, sToDt, arrBuyer())会直接导致类型不匹配，需修改为：

Call runFinished(sFrDt, sToDt, arrBuyer)

或者省略Call的简洁写法：

runFinished sFrDt, sToDt, arrBuyer

2. 未正确利用传入的数组参数 #

你的runFinished子程序虽然声明接收arrBuyer()数组，但SQL语句里直接硬编码了BuyOne、BuyTwo等单元格名称，完全没用到传入的数组，既浪费了传递逻辑，还可能因未定义变量引发编译错误。

修复方式：基于数组动态拼接SQL的IN条件 #

在runFinished中遍历传入的数组，提取对应单元格的实际采购商代码，再拼接成符合SQL语法的IN子句：

Sub runFinished(sFrDt As String, sToDt As String, arrBuyer() As Variant)
    Dim SQL As String
    Dim buyerCodes As String
    Dim i As Integer
    
    ' 拼接采购商代码为IN子句格式
    buyerCodes = ""
    For i = LBound(arrBuyer) To UBound(arrBuyer)
        ' 获取单元格中的实际采购商代码
        buyerCodes = buyerCodes & "'" & Range(arrBuyer(i)).Value & "',"
    Next i
    ' 移除最后一个多余的逗号
    buyerCodes = Left(buyerCodes, Len(buyerCodes) - 1)

    ' 添加新工作表
    ActiveWorkbook.Worksheets.Add

    ' 显示查询条件
    Cells(1, 1) = "Run Date: " & Now()
    Call MergeLeft("A1:B1")

    Cells(2, 1) = "Criteria:"
    Cells(2, 2) = "From " & Range("reqFrDT") & " -To- " & Range("reqToDt")

    ' 拼接SQL语句
    SQL = "select a.StockCode [Finished Part], a.QtyToMake, FQOH,FQOO,/*FQIT,*/FQOA,  b.Component [Base Material], CQOH,CQOO,CQIT,CQOA " & _
    "from ( " & _
    "    SELECT StockCode, sum(QtyToMake) QtyToMake " & _
    "    from [MrpSugJobMaster] " & _
    "    WHERE 1 = 1 " & _
    "    AND JobStartDate >= '" & sFrDt & "' " & _
    "    AND JobStartDate <= '" & sToDt & "' " & _
    "    AND JobClassification = 'OUTS' " & _
    "    AND ReqPlnFlag <> 'I'  AND Source <> 'E' Group BY StockCode " & _
    "    ) a " & _
    "LEFT JOIN BomStructure b on a.StockCode = b.ParentPart " & _
    "LEFT JOIN ( " & _
    "            select StockCode, sum(QtyOnHand) FQOH, Sum(QtyAllocated) FQOO, Sum(QtyInTransit) FQIT, Sum(QtyOnOrder) FQOA " & _
    "            from InvWarehouse " & _
    "            where Warehouse in ('01','DS','RM') " & _
    "            group by StockCode " & _
    ") c on a.StockCode = c.StockCode " & _
    "LEFT JOIN ( " & _
    "            select StockCode, sum(QtyOnHand) CQOH, Sum(QtyAllocated) CQOO, Sum(QtyInTransit) CQIT, Sum(QtyOnOrder) CQOA " & _
    "            from InvWarehouse " & _
    "            where Warehouse in ('01','DS','RM') " & _
    "            group by StockCode " & _
    ") d on b.Component = d.StockCode "
    SQL = SQL & _
    "LEFT JOIN InvMaster e on a.StockCode = e.StockCode " & _
    "WHERE 1 = 1 " & _
    "and e.Buyer in (" & buyerCodes & ") " & _
    "ORDER BY a.StockCode "
End Sub

3. 额外潜在问题修复 #

• 原循环中chkFind = False的判断逻辑：Match函数返回0表示未找到目标，建议改为chkFind = 0，判断更精准：

  If Range(arrBuyer(i)) = vbNullString Or chkFind = 0 Then
  

• 在模块顶部添加Option Explicit，强制所有变量声明，避免未定义变量引发的隐性错误。

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