如何消除VBA动态范围内的循环引用?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (150)

我正在尝试创建一个用户定义的Excel函数,该函数部分地计算公式所在单元格之上的所有非空白单元格(从技术上讲,从定义范围内第一个单元格的特定单元格中计算)。我遇到的麻烦是复制公式下来会引起循环引用。我不希望其他用户遇到这个问题。如何避免循环引用?

我一直在努力解决以下问题:

Set CellOne = Range(“A10”)
Set CellTwo = Range(Selection.Address).Offset(-1, 0)
Set MyRange = Application.Range(Cell1:=CellOne.Address, Cell2:=CellTwo.Address)
CountNonBlanks = Application.WorksheetFunction.CountA(MyRange)

在复制时,此代码还会导致循环引用:

Set CellTwo = Range(ActiveCell.Address).Offset(-1, 0)

此问题似乎是由于引用相对于选择或活动的单元格造成的。我只想让MyRange在公式的上方结束一个单元格,而不管哪个单元格是活动的还是被选中的。

FWIW,用户定义公式的最终目的是返回字母表中的下一个字母,不管该公式在前面的字母下面有多少行。这个本机功能可以工作,但我希望能有一个更优雅的功能。解决办法:

=MID("abcdefghijklmnopqrstuvwxyz",COUNTA(A$10:A10)+1,1)

谢谢。

提问于
用户回答回答于

你不应该在工作表的UDF中使用选择或Activecell,因为这些都是不断变化的。要么将一个范围引用传递到UDF,要么使用application.caller将包含该UDF的单元格作为Range对象引用。

如果你发布了整个UDF或至少声明,我可以编辑这个响应以提供更具体的帮助。下面是一个例子。

Public Function nextLetter()

    'since no reference is passed in, you might want to make this volatile
    Application.Volatile

    With Application.Caller.Parent
        nextLetter = Chr(97 + Application.CountA(.Range(.Cells(10, "A"), _
                                         .Cells(Application.Caller.Row - 1, "A"))))
    End With

End Function

当启动单元被传入时,可供选择。

Public Function nextLetter2(startRng As Range)

    'since only a single cell reference is passed in, you might want to make this volatile
    Application.Volatile

    With Application.Caller.Parent
        nextLetter2 = Chr(97 + Application.CountA(.Range(startRng, _
                                         .Cells(Application.Caller.Row-1, startRng.Column))))
    End With

End Function

使用类似=nextLetter2(A$10)

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励