vba 使 Excel 函数影响“其他”单元格

Making Excel functions affect 'other' cells

提问by AlanR

Let's say that I create a Sub (not a function) whose mission in life is to take the active cell (i.e. Selection) and set an adjacent cell to some value. This works fine.

假设我创建了一个 Sub（不是函数），它的任务是获取活动单元格（即选择）并将相邻单元格设置为某个值。这工作正常。

When you try to convert that Sub to a Function and try to evaluate it from from spreadsheet (i.e. setting it's formula to "=MyFunction()") Excel will bark at the fact that you are trying to affect the value of the non-active cell, and simply force the function to return #VALUE without touching the adjacent cell.

当您尝试将该 Sub 转换为函数并尝试从电子表格中对其进行评估时（即将其公式设置为“=MyFunction()”）Excel 会因为您试图影响非活动的值而咆哮单元格，只需强制函数返回 #VALUE 而不触及相邻单元格。

Is it possible to turn off this protective behavior? If not, what's a good way to get around it? I am looking for something a competent developer could accomplish over a 1-2 week period, if possible.

是否可以关闭这种保护行为？如果没有，有什么好的方法可以绕过它？如果可能的话，我正在寻找有能力的开发人员可以在 1-2 周内完成的事情。

Regards, Alan.

问候，艾伦。

Note: I am using 2002, so I would favor a solution that would work for that version. Having that said, if future versions make this significantly easier, I'd like to know about it too.

注意：我使用的是 2002，所以我倾向于适用于该版本的解决方案。话虽如此，如果未来的版本使这变得更加容易，我也想知道它。

回答by Joe

It can't be done, which makes sense because:

这是不可能的，这是有道理的，因为：

• When a worksheet function is called, the cell containing the function is not necessarily the active cell. So you can't find the adjacent cell reliably.

• When Excel is recalculating a worksheet, it needs to maintain dependencies between cells. So it can't allow worksheet functions to arbitrarily modify other cells.

• 调用工作表函数时，包含该函数的单元格不一定是活动单元格。所以你不能可靠地找到相邻的单元格。

• Excel 在重新计算工作表时，需要维护单元格之间的依赖关系。所以它不能允许工作表函数随意修改其他单元格。

The best you can do is one of:

您能做的最好的事情是：

• Handle the SheetChange event. If a cell containing your function is changing, modify the adjacent cell.

• Put a worksheet function in the adjacent cell to return the value you want.

• 处理 SheetChange 事件。如果包含您的函数的单元格正在更改，请修改相邻的单元格。

• 在相邻的单元格中放置一个工作表函数以返回您想要的值。

Update

更新

Regarding the comment: "I'd like this function to work on a 'blank' spreadsheet, so I can't really rely on the SelectionChange event of spreadsheets that may not yet exist, but will need to call this function":

关于评论：“我希望这个函数在一个‘空白’电子表格上工作，所以我不能真正依赖于可能尚不存在的电子表格的 SelectionChange 事件，但需要调用这个函数”：

• Can you put your function in an XLA add-in? Then your XLA add-in can handle the Application SheetChange (*) event for all workbooks that are opened in that instance of Excel?

• 你能把你的函数放在 XLA 插件中吗？那么您的 XLA 加载项可以处理在该 Excel 实例中打开的所有工作簿的 Application SheetChange (*) 事件吗？

Regarding the comment: "Still, if you keep Excel at CalculationMode = xlManual and fill in just values, you should be just fine"

关于评论：“不过，如果您将 Excel 保持在 CalculationMode = xlManual 并仅填写值，您应该没问题”

• Even when CalculationMode is xlManual, Excel needs to maintain a dependency tree of references between cells so that it can calculate in the right order. And if one of the functions can update an arbitrary cell, this will mess up the order. Which is presumably why Excel imposes this restriction.

• 即使 CalculationMode 为 xlManual，Excel 也需要维护一个单元格之间引用的依赖树，以便它可以按正确的顺序进行计算。如果其中一个函数可以更新任意单元格，这将弄乱顺序。这大概是 Excel 强加此限制的原因。

(*) I originally wrote SelectionChange above, corrected now - of course the correct event is SheetChange for the Workbook or Application objects, or Change for the Worksheet object.

(*) 我最初在上面写了 SelectionChange，现在更正了 - 当然，正确的事件是 Workbook 或 Application 对象的 SheetChange，或 Worksheet 对象的 Change。

Update 2Some remarks on AlanR's postdescribing how to 'kinda' make it work using a timer:

更新 2 AlanR 的帖子中描述了如何“有点”使用计时器使其工作的 一些评论：

• It's not clear how the timer function ("Woohoo") will know which cells to update. You have no information indicating which cell contains the formula that triggered the timer.

• If the formula exists in more than one cell (in the same or different workbooks), then the UDF will be called multiple times during a recalculation, overwriting the timerId. As a result, you will fail to destroy the timer reliably, and will leak Windows resources.

• 目前尚不清楚计时器功能（“Woohoo”）如何知道要更新哪些单元格。您没有任何信息表明哪个单元格包含触发计时器的公式。

• 如果公式存在于多个单元格中（在相同或不同的工作簿中），则在重新计算期间将多次调用 UDF，覆盖 timerId。因此，您将无法可靠地销毁计时器，并且会泄漏 Windows 资源。

回答by GvS

I'm using Excel 2007, and it does not work. Excel mentions it creates a circular reference. I don't think you can alter other cells from a function, just return a value.

我正在使用 Excel 2007，但它不起作用。Excel 提到它创建了一个循环引用。我认为您不能从函数中更改其他单元格，只需返回一个值即可。

It's kind of functional programming, no side effects. If you could just alter other cells inside a function (used from a worksheet), then there's no way for Excel to know the order and what to recalculate if a cell changes.

这是一种函数式编程，没有副作用。如果您只能更改函数内的其他单元格（从工作表中使用），则 Excel 无法知道顺序以及单元格更改时要重新计算的内容。

This articlealso contains a lot of information about how Excel does recalculation. But it never states that the other cells are frozen.

本文还包含大量有关 Excel 如何进行重新计算的信息。但它从未说明其他细胞已被冻结。

I don't know what you are trying to do, but, why don't you just place another function in the adjacent cell, that takes the first cell as a parameter?

我不知道你想做什么，但是，你为什么不在相邻的单元格中放置另一个函数，它将第一个单元格作为参数？

Example:

例子：

Public Function Bar(r As Range) As Integer
  If r.Value = 2 Then
    Bar = 0
  Else
    Bar = 128
  End If
End Function

回答by PhiLho

According to How to Create Custom User Defined Excel Functions:

根据如何创建自定义用户定义的 Excel 函数：

Limitations of UDF's

• Cannot place a value in a cell other than the cell (or range) containing the formula. In other words, UDF's are meant to be used as "formulas", not necessarily "macros".

UDF 的限制

• 不能在包含公式的单元格（或区域）以外的单元格中放置值。换句话说，UDF 旨在用作“公式”，而不一定是“宏”。

So, it looks like it cannot be done.

所以，看起来是做不到的。

回答by AlanR

Thank you all for responding. It is possible to do this! Kinda. I say 'kinda' because technically speaking the 'function' isn't affecting the cells around it. Practically speaking, however, no user could tell the difference.

谢谢大家的回应。有可能做到这一点！有点。我说“有点”是因为从技术上讲，“功能”不会影响它周围的细胞。然而，实际上，没有用户能分辨出其中的区别。

The trick is to use a Win32 API to start a timer, and as soon as it goes off you do what you want to to whatever cell and turn off the timer.

诀窍是使用 Win32 API 来启动计时器，一旦它关闭，您就可以对任何单元格执行任何操作并关闭计时器。

Now I'm not an expert on how COM threading works (although I know VBA is Single Apartment Threaded), but be careful about your Timer running away with your Excel process and crashing it. This is really not something I would suggest as a solution to every other spreadsheet.

现在我不是 COM 线程如何工作的专家（虽然我知道 VBA 是单单元线程），但要小心你的 Timer 与你的 Excel 进程一起跑掉并使其崩溃。这真的不是我建议作为所有其他电子表格的解决方案的东西。

Just Make a Module with these contents:

只需使用以下内容制作一个模块：

Option Explicit

Declare Function SetTimer Lib "user32" (ByVal HWnd As Long, _
  ByVal IDEvent As Long, ByVal mSec As Long, _
  ByVal CallFunc As Long) As Long

Declare Function KillTimer Lib "user32" (ByVal HWnd As Long, _
  ByVal timerId As Long) As Long

Private timerId As Long

Private wb As Workbook
Private rangeName As String
Private blnFinished As Boolean

Public Sub RunTimer()

    timerId = SetTimer(0, 0, 10, AddressOf Woohoo)


End Sub


Public Sub Woohoo()

    Dim i As Integer

'    For i = 0 To ThisWorkbook.Names.Count - 1
'        ThisWorkbook.Names(i).Delete
'    Next

     ThisWorkbook.Worksheets("Sheet1").Range("D8").Value = "Woohoo"

     KillTimer 0, timerId

End Sub

回答by babbageclunk

While you can't do this in Excel, it's possible in Resolver One(although it's still a pretty odd thing to do).

虽然您无法在 Excel 中执行此操作，但可以在Resolver One 中执行此操作（尽管这样做仍然很奇怪）。

It's a spreadsheet that allows you to define custom functions in Python that you can then call from a cell formula in the grid.

它是一个电子表格，允许您在 Python 中定义自定义函数，然后您可以从网格中的单元格公式调用这些函数。

As an example of what you're asking, you might want to define a safeDividefunction that (instead of raising a ZeroDivisionError) told you about the problem by colouring the denominator cell, and putting an error message beside it. You can define it like this:

作为您所询问的示例，您可能想要定义一个safeDivide函数，该函数（而不是提高 a ZeroDivisionError）通过为分母单元格着色并在其旁边放置一条错误消息来告诉您问题。你可以这样定义它：

def safeDivide(numerator, cellRange):
    if not isinstance(cellRange, CellRange):
        raise ValueError('denominator must be a cell range')
    denominator = cellRange.Value
    if denominator == 0:
        cell = cellRange.TopLeft
        cell.BackColor = Color.Red
        cell.Offset(1, 0).Value = 'Tried to divide by zero'
        return 0
    return numerator / denominator

There's an extra wrinkle: functions that get passed cells just get passed the cell value, so to work around that we insist on being passed a one-cell cellrange for the denominator.

还有一个额外的问题：传递单元格的函数只会传递单元格值，因此为了解决这个问题，我们坚持传递一个单元格的单元格范围作为分母。

If you're trying to do unusual things with spreadsheets which don't quite fit into Excel, or you're interested in using the power of Python to work with your spreadsheet data, it's worth having a look at Resolver One.

如果您正在尝试使用不太适合 Excel 的电子表格做一些不寻常的事情，或者您有兴趣使用 Python 的强大功能来处理您的电子表格数据，那么值得一看 Resolver One。

回答by Ken Paul

Here's an easy VBA workaround that works. For this example, open a new Excel workbook and copy the following code into the code area for Sheet1(not ThisWorkbookor a VBA Module). Then go into Sheet1and put something into one of the upper-left cells of the worksheet. If you type a number and hit Enter, then the cell to the right will be updated with 4 times the number, and the cell background will become light-blue. Any other value causes the next cell to be cleared. Here's the code:

这是一个有效的简单 VBA 解决方法。对于此示例，打开一个新的 Excel 工作簿并将以下代码复制到Sheet1(not ThisWorkbookor a VBA Module)的代码区域。然后进入Sheet1并将某些内容放入工作表的左上角单元格之一。如果您键入一个数字并按 Enter，则右侧的单元格将更新为数字的 4 倍，并且单元格背景将变为浅蓝色。任何其他值都会导致下一个单元格被清除。这是代码：

Dim busy As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
  If busy Then Exit Sub
  busy = True
  If Target.Row <= 10 And Target.Column <= 10 Then
    With Target.Offset(0, 1)
      If IsNumeric(Target) Then
        .Value = Target * 4
        .Interior.Color = RGB(212, 212, 255)
      Else
        .Value = Empty
        .Interior.ColorIndex = xlColorIndexNone
      End If
    End With
  End If
  busy = False
End Sub

The subroutine captures all cell change events in the sheet. If the row and column are both <= 10, then the cell to the right is set to 4 times the changed cell if the value is numeric; otherwise the cell to the right is cleared.

该子例程捕获工作表中的所有单元格更改事件。如果行和列都 <= 10，那么右边的单元格如果值为数值，则设置为更改后的单元格的 4 倍；否则右边的单元格被清除。