VBA 为函数内的单元格赋值？

VBA Assign a value to a cell inside a function?

提问by nimo

Is there anyway that I can assign a value/name to cell within a function ?

无论如何，我可以为函数中的单元格分配一个值/名称吗？

Thank you

谢谢

EDIT

编辑

Sorry for being unclear, here is my requirement.

抱歉不清楚，这是我的要求。

I'm having a user defined function (=MyFunction()) which can be called from a excel sheet. Thus, I also having a menu button where I need to recall the all the functions calls to =MyFunction(), when user click a the button.

我有一个用户定义的函数 (=MyFunction())，它可以从 Excel 工作表中调用。因此，我还有一个菜单按钮，当用户单击按钮时，我需要在其中调用对 =MyFunction() 的所有函数调用。

My plan is to inside the MyFunction(), assign a name rerefence to the calling cell and store it inside vba. So I could have a array of cell names. Then I can recall these cell references when the menu button is clicked.

我的计划是在 MyFunction() 内部，为调用单元分配一个名称重新引用并将其存储在 vba 中。所以我可以有一个单元格名称数组。然后我可以在单击菜单按钮时调用这些单元格引用。

Please help me to achieve this. Is there a any better way of keeping cell references ?

请帮助我实现这一目标。有没有更好的方法来保持单元格引用？

回答by GSerg

EDITED:

编辑：

Ah, now I see. The easiest way to do it is to make a bogus argument: MyFunction(ByVal r As Variant), and, whenever you use this function on a sheet, provide exactly same cell as the argument: =MyFunction(A1). When the menu item is clicked, change the value in A1 to whatever, and all MyFunctions will recalculate.

啊，现在我明白了。最简单的方法是创建一个虚假的参数：MyFunction(ByVal r As Variant)，并且，每当您在工作表上使用此函数时，请提供与参数完全相同的单元格：=MyFunction(A1)。单击菜单项时，将 A1 中的值更改为任意值，所有 MyFunction 将重新计算。

Or, you can use Application.Volatilein the body of the function. This way it will recalculate each time any cell in any opened workbook is changed.

或者，您可以Application.Volatile在函数体中使用。这样，每次更改任何打开的工作簿中的任何单元格时，它都会重新计算。

You couldalso use a module-level collection to store references, too, but Excel sometimes just resets the project thus losing module-level variables. If you're brave enough to try:

您也可以使用模块级集合来存储引用，但 Excel 有时只是重置项目，从而丢失模块级变量。如果你有足够的勇气尝试：

Option Explicit

Private RefsToCalculate As New Collection

Public Function MyFunction() As Long
  Static i As Long

  i = i + 1
  MyFunction = i

  If TypeOf Application.Caller Is Excel.Range Then
    On Error Resume Next
    RefsToCalculate.Add Application.Caller, Application.Caller.Address
    On Error GoTo 0
  End If
End Function

Public Sub MenuButtonClicked()
  Dim i As Long

  For i = 1 To RefsToCalculate.Count
    RefsToCalculate(i).Dirty
  Next
End Sub

回答by d..

"I'm having a user defined function (=MyFunction()) which can be called from a excel sheet. Thus, I also having a menu button where I need to recall the all the functions calls to =MyFunction(), when user click a the button."

“我有一个用户定义的函数 (=MyFunction())，它可以从 Excel 表中调用。因此，我还有一个菜单按钮，我需要在其中调用 =MyFunction() 的所有函数调用，当用户单击按钮。”

This can easily be done without creating a cache to store a range of cells. However, you need to be careful with the calculation method. I believe that the code below ensures that your range will always be calculated, but: (1) If calc method is not manual, then Excel ultimately controls what is calculated, when and why, so it may recalculate other cells too. (2) Again, I do believe it guarantees recalculation of all cells with your function regardless of calc method, but I haven't tested the code below for tables and semi-automatic calculation method.

这可以轻松完成，而无需创建缓存来存储一系列单元格。但是，您需要注意计算方法。我相信下面的代码可以确保您的范围始终被计算，但是：（1）如果 calc 方法不是手动的，那么 Excel 最终会控制计算的内容、时间和原因，因此它也可能重新计算其他单元格。(2) 同样，我确实相信它可以保证使用您的函数重新计算所有单元格，而不管 calc 方法如何，但我还没有测试下面的表格和半自动计算方法的代码。

The code below offers two approaches:

下面的代码提供了两种方法：

(1) - Recalculate all cells containing a formula: the advantage is that you skip a loop and the code therein, the disadvantage is that you might force the recalculation of many more cells than you really need.

(1) - 重新计算包含公式的所有单元格：优点是您可以跳过循环和其中的代码，缺点是您可能会强制重新计算比您真正需要的多得多的单元格。

(2) - Build a range of interest and recalc that range: the disadvantage is that building that range may take some serious computational effort. The advantage is that, if calc method is set to manual, then I BELIEVE that Excel will only recalc the cell in that range.

(2) - 建立感兴趣的范围并重新计算该范围：缺点是建立该范围可能需要一些严重的计算工作。优点是，如果 calc 方法设置为手动，那么我相信 Excel 只会重新计算该范围内的单元格。

I guess the choice depends on the specific details of the problem you need to solve.

我想选择取决于您需要解决的问题的具体细节。

"My plan is to inside the MyFunction(), assign a name rerefence to the calling cell and store it inside vba. So I could have a array of cell names. Then I can recall these cell references when the menu button is clicked."

“我的计划是在 MyFunction() 内部，为调用单元格分配一个名称重新引用并将其存储在 vba 中。这样我就可以拥有一个单元格名称数组。然后我可以在单击菜单按钮时调用这些单元格引用。”

If you really want to follow this approach, or if you definitely need to create a cache of cells for the purpose you describe, then this can be done and, although rudimentary, it can even be built in such a way that it is preserved between Excel sessions. However, this requires some more work, a more advanced approach and it would still be pretty rudimentary. IMO, a clear overkill for this problem. To make matters worse, you would have to invoke code everytime a cell is updated to ensure that the cache is kept up to date, which could take a good hit on performace. As for GSerg's suggestion: that approach - as he himself mentions - does not give you any real control over the life of the cache itself. This is, everytime you reach the cache, you would have to check if Excel has wiped it out and, if this is the case, rebuild it.

如果你真的想遵循这种方法，或者如果你确实需要为你描述的目的创建一个单元缓存，那么这可以完成，虽然是基本的，它甚至可以以这样的方式构建Excel 会话。然而，这需要更多的工作、更高级的方法，而且它仍然是非常初级的。IMO，对于这个问题显然是矫枉过正。更糟糕的是，每次更新单元格时都必须调用代码以确保缓存保持最新，这可能会对性能造成很大影响。至于 GSerg 的建议：这种方法 - 正如他自己提到的 - 不会让您真正控制缓存本身的生命周期。也就是说，每次访问缓存时，您都必须检查 Excel 是否已将其清除，如果是这种情况，则重建它。

Conclusion: I'd recommed that you don't cache the cells. Instead, I'd suggest you try to find the cells you need to recalc on an on-demand basis, and force recalculation of those in the most optimal way you can find to do so. Still not convinced? In that case, use Application.Caller.Address(see code below) to retrieve the address of the cell invoking your function.

结论：我建议您不要缓存单元格。相反，我建议您尝试根据需要找到需要重新计算的单元格，并以您能找到的最佳方式强制重新计算这些单元格。还是不相信？在这种情况下，使用Application.Caller.Address（参见下面的代码）来检索调用您的函数的单元格的地址。

REMARK: Implemented and tested in Excel 2003. C#-style comment symbols included for formatting purposes.

备注：在 Excel 2003 中实现和测试。出于格式化目的包含 C# 样式的注释符号。

Option Explicit

Public Sub ReEvaluateMyFunction()

    On Error GoTo Handle_Exception

    Dim targetCells As Range
    Dim targetCell As Range
    Dim rangeToRecalc As Range

    /*'Workbook and worksheet names
    'hard-coded for the example*/

    Set targetCells = Application _
    .Workbooks("Book1") _
    .Worksheets("Sheet1").UsedRange _
    .SpecialCells(xlCellTypeFormulas)

    If targetCells Is Nothing Then Exit Sub

    /*'You can narrow down the range if you know
    'more about the function's return type, e.g.:

    '.SpecialCells(xlCellTypeFormulas, xlNumbers)
    '.SpecialCells(xlCellTypeFormulas, xlTextValues)

    'OPTION 1: re-calculate all cells in the range

    'Remark: unless calc method is set to "Manual", which
    'should give you full control, I think there's no
    'guarantee that other cells will not be recalculated*/

    If Application.Calculation = xlCalculationManual Then
        //'Use to force recalculation if calc mode is manual
        targetCells.Calculate
    Else
        //'Use this to force recalculation in other cases
        targetCells.Dirty
    End If

    Set targetCells = Nothing
    Exit Sub

    /*'OPTION 2: create a range specific to your
    'function and recalculate that range*/

    For Each targetCell In targetCells

        If targetCell.Formula = "=MyFunction()" Then

            If rangeToRecalc Is Nothing Then
                Set rangeToRecalc = targetCell
            Else
                Set rangeToRecalc = Union(rangeToRecalc, targetCell)
            End If

        End If

    Next targetCell

    //'Same comments as before
    If Application.Calculation = xlCalculationManual Then
        rangeToRecalc.Calculate
    Else
        rangeToRecalc.Dirty
    End If

    Set rangeToRecalc = Nothing
    Set targetCell = Nothing
    Set targetCells = Nothing

    Exit Sub

Handle_Exception:

    Set rangeToRecalc = Nothing
    Set targetCell = Nothing
    Set targetCells = Nothing

    MsgBox "An error has been found: " + Err.Description, vbCritical

End Sub

Public Function MyFunction() As String

    MyFunction = Application.Caller.Address

End Function