vba - 查找ParamArray元素零的行

Question

我有一个数组，其中列A中列出了不同的文章名称。现在，我想将与其中一些文章名称对应的值相加。由于文章名称的数量不是恒定的，因此我将这些名称作为ParamArray传递。

所有这些对于在ParamArray末尾的元素都有效，但是当我尝试查找放置在ParamArray(0)中的商品编号的行时，会出现错误消息“对象变量或未设置块变量”。尽管如此，访问ParamArray(0)并不是问题，但是Vba拒绝找到相应的行。

这是调用该函数的代码(col_ML是相加的值的列):

.Cells(63, col_year).Value = Compute_sum_ML(col_ML, "17.8.32.000", "17.8.42.000")

这是函数本身:

Function Compute_sum_ML(col_ML As Integer, ParamArray article() As Variant) As Double

  Dim row_article As Integer
  Dim result As Double

  row_article = 0
  result = 0

  For i = 0 To UBound(article, 1)
    row_article = d_ML.Range("A:A").Find(What:=article(i),LookIn:=xlValues).row
    If row_article <> 0 Then
      result = result + d_ML.Cells(row_article, col_ML).Value
    End If
  Next i

  Compute_sum_ML = result
End Function

我也尝试将ParamArray定义为字符串，因为只有字符串会传递给它，但这迫使我将其定义为变体。

Answer 1

以下对我有用。我已将您的工作表代码名称引用替换为Worksheets("Sheet1")。

注意我添加了一个测试，如果找到匹配。

Option Explicit
Public Sub test()
    Debug.Print Compute_sum_ML(2, "17.8.32.000", "17.8.42.000")
End Sub

Public Function Compute_sum_ML(ByVal col_ML As Integer, ParamArray article() As Variant) As Double
    Dim row_article As Long, i As Long, result As Double, found As Range

    row_article = 0
    result = 0

    For i = 0 To UBound(article, 1)
        Set found = Worksheets("Sheet1").Range("A:A").Find(What:=article(i), LookIn:=xlValues)
        If Not found Is Nothing Then
            row_article = found.Row
            result = result + Worksheets("Sheet1").Cells(row_article, col_ML).Value
         'Exit For  ''<==If only one match wanted.
        End If
        Set found = Nothing
    Next i
    Compute_sum_ML = result
End Function

数据: