使用 VBA 功能清洁单元
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16262930/
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
Cleaning cells with a VBA function
提问by user1493046
I want to create a VBA function, myFunction(),that writes content in cells and when the calculation is done, it would clear all the data it wrote. (I want to call it from a cell with =myFunction()) To clear the content, I've put this line at the end, to clean up before finishing:
我想创建一个 VBA 函数，myFunction(),它在单元格中写入内容，当计算完成时，它会清除它写入的所有数据。（我想从与小区叫它=myFunction()）要删除的内容，我已经把这个线的尽头，结束前清理：
ActiveSheet.Range("$A:$B").ClearContents
The problem is that is that it doesn't clear anything. However, I noticed that if I put that line above in a subroutine, and then assign that subroutine to a button, the content will be cleared when I click the button.
问题是它没有清除任何东西。但是，我注意到如果我将上面那行放在一个子程序中，然后将该子程序分配给一个按钮，当我单击该按钮时，内容将被清除。
When I run the code from the window of the program "Microsoft Visual Basic" (with the play button), the code works perfectly (the content gets cleared) but when I call the function from a cell, the cleaning part doesn't work anymore. Here is the code:
当我从程序“Microsoft Visual Basic”（带有播放按钮）的窗口运行代码时，代码运行良好（内容被清除）但是当我从单元格调用该函数时，清理部分不起作用了。这是代码：
Function myFunction()
ActiveSheet.Range("$A:$B").Clear
End Function
When I click in a cell and type =myFunction(), the content in the range $A$1:$B$9 is NOT cleared.
However if I create a subroutine (instead of a function), and call it with a button, the content IS cleared.
当我单击单元格并键入 时=myFunction()，$A$1:$B$9 范围内的内容不会被清除。但是，如果我创建一个子程序（而不是一个函数），并用一个按钮调用它，内容就会被清除。
Why won't it work when called by myFunction() ? How can I solve this ?
为什么它在被 myFunction() 调用时不起作用？我该如何解决这个问题？
回答by David Zemens
Why won't it work when called by myFunction() ?
为什么它在被 myFunction() 调用时不起作用？
A function, called from the Worksheet, cannot manipulate objects on the worksheet, it can only return a value to the cell wherein the function has been called from. I believe this is to prevent circular reference and infinite loops.
从工作表调用的函数不能操作工作表上的对象，它只能向调用该函数的单元格返回一个值。我相信这是为了防止循环引用和无限循环。
The loophole is that a function called from within a subroutine canmanipulate worksheet objects, but that's probably not a good habit to get in to.
漏洞是从子程序中调用的函数可以操作工作表对象，但这可能不是一个好习惯。
As a best practice, use Subroutines to manipulate objects, and Functions only to return values -- whether to the sheet or to a subroutine.
作为最佳实践，使用子例程来操作对象，而函数仅用于返回值——无论是返回到工作表还是子例程。
How can I solve this ?
我该如何解决这个问题？
@Santosh's answer, above, should do the trick.
上面@Santosh 的回答应该可以解决问题。
回答by Santosh
Instead of UDF you can use events. Please put the below code on any sheet code section.
您可以使用事件代替 UDF。请将以下代码放在任何工作表代码部分。
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Not Intersect(Target, Range("$A:$B")) Is Nothing Then
' your code here
Range("$A:$B").Clear
End If
Application.EnableEvents = True
End Sub
The above code is like a workaround. It will be triggered when values in Range("A1:B9") are changed from excel interface.
上面的代码就像一个解决方法。当 Range("A1:B9") 中的值从 Excel 界面更改时将触发。
A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:
由工作表单元格中的公式调用的用户定义函数无法更改 Microsoft Excel 的环境。这意味着这样的函数不能执行以下任何操作：
- Insert, delete, or format cells on the spreadsheet.
- Change another cell's value.
- Move, rename, delete, or add sheets to a workbook.
- Change any of the environment options, such as calculation mode or screen views.
- Add names to a workbook.
- Set properties or execute most methods.
- 在电子表格中插入、删除或格式化单元格。
- 更改另一个单元格的值。
- 将工作表移动、重命名、删除或添加到工作簿。
- 更改任何环境选项，例如计算模式或屏幕视图。
- 将名称添加到工作簿。
- 设置属性或执行大多数方法。
for more details read the link provided earlier.
有关更多详细信息，请阅读之前提供的链接。
