2

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.

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

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.

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

Community
  • 1
  • 1
user1493046
  • 352
  • 1
  • 3
  • 17

2 Answers2

6

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's answer, above, should do the trick.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
4

Instead of UDF you can use events. Please put the below code on any sheet code section.

limitations of UDF

 Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Application.EnableEvents = False

    If Not Intersect(Target, Range("$A$1:$B$9")) Is Nothing Then

        ' your code here
        Range("$A$1:$B$9").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.

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:

  • 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. enter image description here

Santosh
  • 12,175
  • 4
  • 41
  • 72
  • I pasted the code, but what am I supposed to do with it? I need it to be run from a cell, but since it's a subroutine I assume it can't? – user1493046 Apr 28 '13 at 13:00
  • Ok, but I need to call it somewhere, don't I ? Because simply pasting it doesn't do anything.. – user1493046 Apr 28 '13 at 13:07
  • 1
    @user1493046 Its an Worksheet_Change event and would be triggered when values in Range("A1:B9") are changed and any logic you can write in this change event. – Santosh Apr 28 '13 at 13:08
  • ok unfortunatly the event is not triggered. Thanks for helping man ;p – user1493046 Apr 28 '13 at 13:13
  • 1
    @user1493046 i have updated the answer with a screenshot. I hope it helps. – Santosh Apr 28 '13 at 13:19
  • It works when I add the data manually in the cells of the range. However I use a web query to fill these cells, and in that case the event doesn't get triggered. Thanks for yout time man ;p – user1493046 Apr 28 '13 at 13:20
  • + 1 for suggesting an alternative :) @user1493046: Please see this [link](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) to avoid possible endless loop – Siddharth Rout Apr 28 '13 at 13:30