excel - 在一张工作表上重新计算 Excel VBA 函数会破坏其他工作表

Question

我做了一个函数来计算给定月份的项目数。

A 列是月份，B 列是该月的项目数。

单元格 B1 具有:

=countItems(A1)

Excel数据:



代码:

Function countItems(month)
    Application.Volatile
    Dim count As Integer
    If Not (month = 0) Then
        count = 0
        Do While Not (Cells(month.row + count + 1, 3) = 0)
            count = count + 1
        Loop
        countItems = count
    Else
        countItems = ""
    End If
End Function

我将公式从 B1 拖到 B500 并且每个月都能正常工作。如果相应的 A 单元格中没有月份，则该公式不返回任何内容。

我在结构相似的数据集上有多个使用相同公式的工作表。
每当此工作表 1 的 B 列中的值更新时，其他工作表也会更改。但是，工作表 2 将更新 使用工作表 1 中的 C 列.

如果我重新计算了工作表 2，工作表 1 将使用工作表 2 中的 C 列进行更新。

该函数通过检查它在找到空白单元格之前可以在 C 列中读取多远来计算给定月份中的项目数，这表明该月份已经结束。
工作表 2 在第一个月有 1 个项目，但由于工作表 1 有 3 个项目(计数第 1 到第 3 行并在第 4 行停止)，它仍将返回 3。
Sheet 2 的第二个月从第 3 行开始。但由于该函数正在读取 Sheet 1 中的 C 列， 再计算 1 个项目后，它将进入空白单元格(计数第 3 行并在第 4 行停止) .因此，无论 Sheet 2 Month 2 中有多少项目，它都会返回 1。

该函数始终使用正确的 A 列，并且仅在 A 列中有日期的 B 列中显示一个数字。

结果是只有一张纸可以有正确的值，这样做会破坏其他纸。

我目前无法解决这个问题，因为我是 VBA 新手。

我曾想过让所有函数的单元格引用都包含对当前单元格工作表的自引用，但我不知道该怎么做，也不知道它是否可行。

编辑:我不能让它这样工作，但是 Application.Caller.Offset()以相对单元格位置作为解决方案。我仍然想知道是否有办法使用绝对单元格位置。

工作表没有组合在一起。

Answer 1

这是因为在传递给函数的范围和函数作为“调用者”的范围“感觉”之间存在“时空偏移”

您可以通过如下修改功能代码来查看此行为

Function countItems(month)
    Application.Volatile
    Dim count As Integer

    Dim r As Range
    Dim p As Variant, a As Variant

    Set r = Application.Caller '<~~ retrieve the actual "calling cell" of the current function "instance"
    p = r.Parent.Name
    a = r.Address

    MsgBox p & " vs " & month.Parent.Name & vbCrLf & a & " vs " & month.Address '<~~ compare the function "calling cell" vs the "passed cell"

    If Not (month = 0) Then
        count = 0
        Do While Not (Cells(month.Row + count + 1, 3) = 0)
            count = count + 1
        Loop
        countItems = count
    Else
        countItems = ""
    End If
End Function

你会看到 msgboxs 提示，显示函数“调用单元格”和“传递的单元格”地址和/或工作表之间的差异

因此，为了避免这种行为，您可以依赖“仅调用范围”，如下所示:

Option Explicit

Function countItems(month)
    Application.Volatile
    Dim r As Range

    Set r = Application.Caller '<~~ retrieve the actual "calling cell" of the current function "instance"

    'the "calling cell" is the one with the called function in its formula, i.e. in column "B" as per your data structure. then ...
    If Not IsEmpty(r.Offset(, -1)) Then '<~~ ... the column with dates are one column to the left, and  ...
        Do While Not IsEmpty(r.Offset(countItems + 1, 1)) '<~~ ...  the values to be checked for are one column to the right
            countItems = countItems + 1
        Loop
    End If
End Function