查找ParamArray的元素零的行时出错

Question

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

所有这些都适用于从一个到最后一个块的元素，但是当我试图查找放置在ParamArray(0)中的文章编号所在的行时，会出现错误消息"Object variable or with ParamArray variable not set“。然而，访问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定义为字符串，因为只有字符串会传递给它，但这迫使我将其定义为variant。

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

数据：

​

​