Clear cell contents from a function

[chris1983]

I understand you can't change value of a cell from within a function. ie. =Function(B12)

If the value in B12 changes to a 1 then I want the VBA code to clear the contents of this particular cell.

Range("B12").ClearContents works great from a Sub but does not from within the Function code.

Any ideas?

[Leith Ross]

Hello Chris,

Can you provide an example of what you to do?

[chris1983]

This is a simplified version of the user defined function. In cell B13 I have =Monitor(B12) so if B12 changes value it will run the function. It displays the msgbox no problem but I cannot get it to clear the contents of B12. Works fine if I wrap Range("B12").ClearContents in a Sub.

Please Login or Register  to view this content.

[Leith Ross]

Hello Chris,

If you are planning to use this as User Defined Function (UDF) for the worksheet. It will not work. There are restrictions on what you can and can not do with a UDF. Have a look at the links below to see the restrictions.

Description of limitations of custom functions in Excel
Excel User-Defined Functions

[chris1983]

what would you recommend I do?

[Leith Ross]

Hello Chris,

If I knew more about what you were doing then I could give you some direction or a possible workaround. Can you post the workbook for review?

[Winon]

@ Leith Ross,

Hello Leith,

Nice to see you so active and ever helping. I am looking forward to the final outcome of this Thread.

[chris1983]

Hi Leith, apologies for the delay. I have attached a simple example of what I am trying to achieve. Hopefully you can figure out a workaround for this issue. Appreciation in advance.

[Winon]

Hello chris1983,

ClearContents works great from a Sub but does not from within the Function code

Then why don't you just add this code to Sheet1?

Please Login or Register  to view this content.

[chris1983]

Thanks for responding Winon, I have more complicated VBA that watches 1000 lines, reads the values of the column into an array and compares any new values (changes) to the existing values in the array. So this NEEDS to be a function unfortunately.

[Winon]

Hello chris1983,

Thank you for responding,

May I suggest you then upload a revised version of your WorkBook reflecting your exact scenario, so that we can see how to "uncomplicate" things a bit.

If I cannot help you, I would be more than willing to surrender to Leith Ross, he is, in my opinion outstanding!

[chris1983]

I see your point but I feel my code would just muddy the water, so I would just rather keep it simple: I need to clearcontents of a cell from a Function. It has to be from a function and if it can't be achieved then at least I have got a second opinion.

[mikerickson]

To do something like this, you need a combination of UDF and event code.
Put something like this in a normal module

Please Login or Register  to view this content.

Then in the ThisWorkbook code module

Please Login or Register  to view this content.

The idea is that:
1) a UDF cannot clear a cell when called from a worksheet.
2) a UDF can put a range into a collection, even when called from a worksheet
3) the Calculate event runs after all UDF's are calculated
4) the Calculate event can clear cells

The UDF, ClearIfOne, puts the appropriate cells into a collection. The contents of that collection are later cleared by the Calculate event. (I prefer to use the Calculate event in ThisWorkbook since I don't want the UDF to be restricted to use in only one sheet.)

You can adapt this approach if you want ClearIfOne to return something other than TRUE/False.

[chris1983]

Thnx Mike, it seems to have issues populating the CellsToClear collection with line:
CellsToClear.Add Item:=aCell, Key:=aCell.Address(, , , True)

With this line it doesn't return TRUE but if I comment it out it does. Can you shed any light on this?

[mikerickson]

What happens when you step through the code.
If aCell is already in the collection, there will be an error and the UDF will return False.
But then the Calculation event should run and the Set CellsToClear to Nothing, emptying it.

Code like this can be tricky (it may be nessesary to turn off calculation during the Calculate event) and I'm working from memory.

When I get off work, I'll put together a test situation and debug.

[chris1983]

Appreciate it thanks Mike

[mikerickson]

I found the problem, the collection has to be declared as a Public variable.
Here is the corrected code, including the ThisWorkbook code with proper declaration syntax.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

If I were using code like this in a project, I'd do it differently. ClearIfOne would have a second Trigger boolean argument.

But, as posted, its an example of how to control the clearing of a cell with a UDF.
A similar technique can be used to color cells or other sheet altering changes that are normally forbidden to a UDF.