为什么不使用自定义函数进行字段重新计算？ [英] Why does not field with custom function to get name recalculate?

问题描述

我使用vba函数获取单元格名称（取自在Excel中检索单元格名称< a>）

I use a vba function to get cell name (taken from Retrieving Cell name in Excel)

Public Function CellName(oCell As Range) As Variant
  Dim oName As Name

  For Each oName In ThisWorkbook.Names
    If oName.RefersToRange.Parent Is oCell.Parent Then
        If Not Intersect(oCell, oName.RefersToRange) Is Nothing Then
            CellName = oName.Name
            Exit Function
        End If
    End If
  Next
  CellName = CVErr(xlErrNA)
End Function

当单元格的名称不存在时，它显示错误 - 这当然是预期的行为。但是，当我命名另一个单元格（我想要获得的名称）时，单元格中的错误仍然有效。重新计算没有帮助。我需要改变其他单元格的值（我可以改变其值或改变其公式中的另一个单元格的值）或其公式，因此该单元格中的值将被重新计算，所以我的单元格与 CellName 函数正确刷新。

When the name of the cell does not exist, it shows error -- and that is of course intended behaviour. However, when I then name the other cell (the one which name I want to get), the error in my cell is still active. Recalculating does not help. I need then to change the value of the other cell (I can change its value or change value of yet another cell that is in its formula), or its formula, so the value in that cell would be recalculated, so my cell with CellName function gets properly refreshed.

我没有看到为什么，我能做什么来简单化当我命名单元格时，单元格刷新指向？

I don't see the point why, and what can I do to simple make the cell refresh when I name the cell I point to?

这是Excel 2007，文件类型为xlsm。

This is Excel 2007, file type xlsm.

推荐答案

我想你需要在UDF开头设置 Volatile

I think you need to set the function as Volatile

添加以下代码：

application.volatile

请参阅 MSDN库：易失性方法[Excel 2003 VBA语言参考]

这篇关于为什么不使用自定义函数进行字段重新计算？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！