Thorny VBA problem. Any creative solution appreciated.

[jeffstryer]

I understand that functions can only modify the cell they are called
from. However, if anyone has a solution to this problem, I would
greatly appreciate it.

A function F(N) does a vlookup in a table and returns a value.

If it cannot find N in the table, I want to add N to a list in
another table.

F() is called from an addin if that is relevant.

Any creative solution appreciated.

Thanks
Jeff Stryer

 

[Tim Williams]

Your only solution is likely to be to use something along the lines of
an event procedure (such as "calculate") instead of a UDF.


If F() is called from an add-in, then where is the table to be
modifed? In the activeworkbook?

Tim.

 

[jeffstryer]

Yes, the table to be modified is in the active workbook.

 

[Stephen Bullen]

Hi Jeff

I understand that functions can only modify the cell they are called
from. However, if anyone has a solution to this problem, I would
greatly appreciate it.

A function F(N) does a vlookup in a table and returns a value.

If it cannot find N in the table, I want to add N to a list in
another table.

F() is called from an addin if that is relevant.

Any creative solution appreciated.

You might be able to adapt one of my posts on Dick Kusleika's 'Daily
Dose of Excel' blog:

http://www.dicks-blog.com/archives/2004/12/22/functions-that-do-things/

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk