重新计算VBA公式excel

Question

我正试图找到一个解决方案，在更改单元格的颜色后自动重新计算VBA公式

Function SumColorColumns11(sumRange As Range) As Double
Dim cell As Range
For Each cell In sumRange
If cell.Interior.Color = 12611584 And cell.Column = 7 Then
SumColorColumns11 = SumColorColumns11 + 20
ElseIf cell.Interior.Color = 12611584 And cell.Column = 8 Then
SumColorColumns11 = SumColorColumns11 + 30
End If
Next cell
SumColorColumns11 = SumColorColumns11 / 100

当前，当我想重新计算VBA公式时，我会转到一个有公式的单元格，然后单击该公式，然后按enter键。

编辑后

Function SumColorColumns11(sumRange As Range) As Double
Dim cell As Range
For Each cell In sumRange
If cell.Interior.Color = 12611584 And cell.Column = 7 Then
SumColorColumns11 = SumColorColumns11 + 20
ElseIf cell.Interior.Color = 12611584 And cell.Column = 8 Then
SumColorColumns11 = SumColorColumns11 + 30
cell.Calculate        
End If
Next cell
SumColorColumns11 = SumColorColumns11 / 100

我已经找到了在用户更改时运行宏的其他解决方案，但我不知道如何将其应用于我的函数，因为它接收一个范围并返回一个值。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("C6:R393")) Is Nothing Then
MsgBox "hi"
End If
End Sub

Answer 1

您的问题是，当单元格颜色更改时，没有触发事件，而且重新计算对UDF也不起作用-正如您所说的，您必须手动触发它-所以我怀疑您能做的最好的事情是从其他频繁触发的事件中调用代码的一行-我建议使用SelectionChange事件-如下

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Change A1 in the following to the Cell you want the result in
Range("A1").Value = SumColorColumns11(Range("G1:H9"))  ' Change G1:H9 to your own range here
End Sub

然后，在你改变颜色后，只需移动到另一个单元格就会触发它——当然这并不理想，所以这不是你想要的答案，但这是一个粗略的解决方法。

+++++++++++++++++++++++++++++添加

添加以下内容是为了响应您对多行值的注释，并减少调用例程的频率

将其放入纸张模块申报区域

Public LastRng As Range, CalledB4 As Boolean

这就是修改后的SelectionChange事件代码

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
' This routine will write a value into Column A that is the sum of the colors in cells G & H of the same row
' It only calls your SumColorColumns11 when we moved out of a cell in the WatchRng (G2:H12)
'
Const WatchRng  As String = "G2:H12"
Dim xCel As Range, Rng2Chek As Range

If Not (CalledB4 = True) Then
CalledB4 = True
Else
If Not Application.Intersect(Range(WatchRng), LastRng) Is Nothing Then
For Each xCel In Application.Intersect(Range(WatchRng), LastRng).Cells
' The following line checks Columns G&H of the same row as xCel - for a different range change G & H
Set Rng2Chek = Range("G" & xCel.Row & ":H" & xCel.Row)
' The 1 in the following line means put result in column A - use 2 for B, 3 for C etc
xCel.Offset(0, 1 - xCel.Column).Value = SumColorColumns11(Rng2Chek)
Next xCel
End If
End If
Set LastRng = Target
End Sub

Answer 2

设置单元格内部颜色的方法有很多，我试图开发的方法都比最简单的解决方案(即频繁计算总数(更为丰富。下面的事件过程几乎做到了这一点。请试试。

在您想要操作的工作表的代码模块中安装以下过程(不是UDF所在的模块！(

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WatchCol  As Long = 12611584
Const WatchRng  As String = "G2:H12"
Const ResultRng As String = "G13:H13"

If Not Application.Intersect(Range(WatchRng), Target) Is Nothing Then
Range(ResultRng).Calculate
End If
End Sub

有两个范围需要定义。WatchRng等于函数的SumRange。我的程序将对该范围内的点击做出响应。ResultRng是要重新计算的范围。这就是调用UDF的地方。事实上，每次单击WatchRng中的任何位置时，都会重新计算此范围。但是，可以在不点击任何内容的情况下更改单元格的颜色。在这种情况下，WatchRng中的下一次单击将触发计算。我判断，接受这个缺陷比每次点击都要运行一个漫长的过程要轻一些，这个过程可以治愈缺陷，但会让你的床单反应迟钝。

在尝试许多想法的同时，我还回顾了您的UDF。由于它会被更多地调用，我试图简化它。然而，它没有你自己的设计所没有的功能。

Function SumColorColumns11(SumRange As Range) As Double
Dim Fun     As Double
Dim Cell    As Range

For Each Cell In SumRange
If Cell.Interior.Color = 12611584 Then
On Error Resume Next
Fun = Fun + Array(0.2, 0.3)(Cell.Column - 7)
End If
Next Cell
SumColorColumns11 = Fun
End Function