vba 自定义函数不会自动更新（设置设置为自动计算！）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17088433/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Custom function will not update automatically (settings are set to auto calculations!)
提问by Dan Van
I have a custom function that detects if a cell is bold
我有一个自定义函数来检测单元格是否为粗体
Function isBold(cellBold)
If cellBold.Font.Bold = True Then
isBold = 1
ElseIf cellBold.Font.Bold = False Then
isBold = 0
Else
isBold = 0
End If
End Function
Puts 1 in a cell if the reference cell is bold and 0 if it is not bold This works well and all the first time around but if I make the reference cell bold the number stays at 0. Automatic calculations are on, the only way for the function to calculate again is to retype the function
如果参考单元格为粗体则将 1 放在单元格中，如果它不是粗体则为 0 这很有效，并且所有第一次，但如果我将参考单元格设为粗体，则数字保持为 0。自动计算已开启，这是唯一的方法再次计算的函数是重新输入函数
回答by Ripster
Adding Application.Volatile to the top of your function will make it auto update when the workbook change event is fired.
将 Application.Volatile 添加到函数顶部将使其在触发工作簿更改事件时自动更新。
Function isBold(cellBold)
Application.Volatile
If cellBold.Font.Bold = True Then
isBold = 1
ElseIf cellBold.Font.Bold = False Then
isBold = 0
Else
isBold = 0
End If
End Function
This will not help you if you just bold a result but you can add an event to the sheet you're working on
如果您只是将结果加粗，这将无济于事，但您可以将事件添加到您正在处理的工作表中
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
End Sub
If both of these things are in place, your formula will update every time you select a different cell which may work well enough for you. However, I suggest using this method with caution because if you have a very large number of formulas this could slow things down.
如果这两项都到位，则每次您选择一个可能对您来说足够好用的不同单元格时，您的公式都会更新。但是，我建议谨慎使用此方法，因为如果您有大量公式，这可能会减慢速度。
-Edit- This should fix the copy paste issue.
-Edit- 这应该解决复制粘贴问题。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim col As Range
For Each col In ActiveSheet.UsedRange.Columns
col.Calculate
Next
End Sub
回答by user2140261
OK, So I'll be the first to admit this is not an ideal solution, and is pretty hackish. But i think it will fix your solution.
好的，所以我将第一个承认这不是一个理想的解决方案，而且非常hackish。但我认为它会解决您的解决方案。
After adding the volatile line to your code as so:
在将 volatile 行添加到您的代码中后，如下所示：
Function isBold(cellBold)
Application.Volatile True
If cellBold.Font.Bold = True Then
isBold = 1
ElseIf cellBold.Font.Bold = False Then
isBold = 0
Else
isBold = 0
End If
End Function
First Change your Workbook_Open to this:
首先将您的 Workbook_Open 更改为：
Private Sub Workbook_Open()
Sheets("Sheet1").rngLastCell = Range("A1").Address
Sheets("Sheet1").fntLastCell = Range("A1").Font.Bold
End Sub
Then on the worksheet you are working with (In my example Sheet1) Add this to the Worksheet function:
然后在您正在使用的工作表上（在我的示例中Sheet1）将其添加到工作表函数中：
Option Explicit
Public fntLastCell As Boolean
Public rngLastCell As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Sheet1").Range(rngLastCell).Font.Bold <> fntLastCell Then
Calculate
End If
Sheets("Sheet1").rngLastCell = Target.Address
Sheets("Sheet1").fntLastCell = Target.Font.Bold
End Sub
Now to have it work you must Save then close, Then re open your worksheet.
现在要让它工作，你必须保存然后关闭，然后重新打开你的工作表。
This works by setting 2 global variables each time you select a new cell.
这是通过每次选择一个新单元格时设置 2 个全局变量来实现的。
a Boolean variable that states weather the Last Cell selected WAS PREVIOUSLY Bold or not. And a String Variable that references that same Cell. So, you can now check the Bold state of the cell you exited (when it was entered) against the Current Bold State of the cell you just existed and if there was a change it will calculate the workbook. Otherwise nothing will happen.
一个布尔变量，表明选择的最后一个单元格是否以前是粗体。还有一个引用同一个单元格的字符串变量。因此，您现在可以根据您刚刚存在的单元格的当前粗体状态检查您退出的单元格的粗体状态（当它进入时），如果有更改，它将计算工作簿。否则什么都不会发生。
Hope this works and helps,
希望这有效并有所帮助，
Cheers
干杯
