Run VBA code any time a particular range of cells is changed

[AChimpNamedCornelius]

Is there a way to cause a particular VB function I write to execute any time a value in a particular range of cells is changed?

I thought I had it when I thought I could do a sum formula, range on the cells to be checked, then call the function as a side effect of this sum. It was then that I realized I didn't know how to call VB from within an Excel cell formula.

1. Is there a way to call VB (say, sheet1.myFunction())?

2. Is there a better way to do what I want than to try to hack a side effect into a formula in a cell that depends on a sum (which "watches" for the changes for me.)


The cells would have text in them, not numbers, so using the sum-based formula would be iffy at runtime to me at best. What I really want to do is run a function on the cell value as a string. There are a lot of such cells (thousands) so ideally I would only need one external cell to activate the function (which scans all the cells I am concerned with) rather than have to paste a custom, relative formula into thousands of cells manually to have each one watch one cell, if you know what I mean.
This message was edited by AChimpNamedCornelius on 2002-08-23 13:09

 

[SteveInAlabama]

I tried this method as well - here's the catch that got me, maybe it is why you can't "get it to work" either: I was brushing up on User Defined Functions in Excel 2002 VBA Programmer's Reference (Bullen, et all), and found out that UDF's cannot do ANY structural spreadsheet changes, nor can they call code that does (pg 28). Excel doesn't give an error message, it simply ignores the code. It apparently is only designed to return a numerical value to a cell, nothing more. Makes sense I guess - just wish Excel would have told me rather than ignore me.

This applies to 2002 and I assume to 2003 also since that is what I'm running. Don't know about 2007.