用VBA功能清洁电池 [英] Cleaning cells with a VBA function

问题描述

我想创建一个VBA函数， myFunction（），在单元格中写入内容，当计算完成后，它将清除所有写入的数据。 （我想从 = myFunction（）的单元格中调用它）要清除内容，我已经将此行放在最后，在完成之前进行清理： / p>

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:

ActiveSheet.Range("$A$1:$B$9").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.

当我从程序Microsoft Visual Basic（使用播放按钮）的窗口运行代码时，代码工作完美（内容被清除），但是当我从单元格调用该功能时，清洁部件不起作用了。
这里是代码：

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:

Function myFunction()
  ActiveSheet.Range("$A$1:$B$9").Clear
End Function

当我点击单元格并输入 = myFunction（），$ A $ 1：$ B $ 9范围内的内容不会被清除。
但是如果我创建一个子程序（而不是一个函数），并用一个按钮调用它，内容被清除。

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（）调用时不起作用？如何解决这个问题？

Why won't it work when called by myFunction() ? How can I solve this ?

推荐答案

为什么在myFunction（） ？

Why won't it work when called by 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 can manipulate 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的答案，上面，应该做的伎俩。

@Santosh's answer, above, should do the trick.

这篇关于用VBA功能清洁电池的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！