CODEWITHSUNDEEP
excelvba
user1493046
user1493046

Reputation: 352

Cleaning cells with a VBA function

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 ?

Upvotes: 2

Views: 25426

Answers (2)

Santosh
Santosh

Reputation: 12353

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

Upvotes: 4

David Zemens
David Zemens

Reputation: 53623

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.

Upvotes: 6

Related Questions