how to create a "persistent" variable definition?

Is it possible to create a "dependent" variable that is recalculated automatically when its "independent" variables are changed. For example, can I somehow define y = 3*x +5 such that y is instantly updated when x changes somewhere later in the code? As of now, I would have to re-write the "y=3*x+5" line of code after every time x *might* change.

I tried searching for this but I have no idea what to search for exactly.

Thanks very much!

If you declare Y as a variable why would you have to rewrite the equation?

You can make it a function

Please Login or Register  to view this content.

Then use it like this

Please Login or Register  to view this content.

after x changes.

I don't believe there is any way to do this without specifically telling VBA to execute the y=... statement.

The example you give is such a simple expression, I don't know why it seems undesirable to simply include the y=... statement after every x=... statement. This is probably how I would do it.

A couple of thoughts that might spark some additional thinking.

1) Instead of defining x as a value (long or double or whatever), could you define x and y as ranges referring to specific cells in Excel? It is not the kind of programming I do, but I think that you can then have Excel's calculate event perform the y calculation for you. A quick test procedure:

Please Login or Register  to view this content.

In my test this created a correct list of y values, even though I did not specifically tell it to execute the y=... statement. This is because Excel was watching for the change to x and would auto calculate y every time it changed x. An approach like this will mean that you will have to make sure that nothing in your VBA code would interfere with Excel's calculation event.

2) In many ways what I think you are describing is kind of like a subroutine. I don't know if experienced programmers still use Gosub...Return statements (http://msdn.microsoft.com/EN-US/libr.../gg251648.aspx), but that might be another way to do it. Define a subroutine for changing x and y, then your main program flow can call the subroutine everytime you want to change x (and y).

Please Login or Register  to view this content.

3) I think many experienced programmers might prefer to use individual sub procedures rather than having the subroutine defined like in 2. This requires thinking about how you will transfer variables from one procedure to another. Here's a possibility using procedure level variables:

Please Login or Register  to view this content.

The first effort relies on Excel and its calculate event to "magically" calculate y evertime x changes. The other two approaches do require VBA to explicitly tell the computer to calculate y, but we have structured the code so that changing x and calculating y occur together.