run VBA macro on module variable change

Question

Is there a way to run a Macro in a Module each time a certain variable in the same Module changes?

It is important that the scope is restricted to the Module which I believe makes techniques such as "Workbook_SheetChange" unacceptable.

I have a function that I desire to return an initial value, and then trigger another sub to update other portions of the worksheet, but all the code must be contained in the single module

Set/change the variable via a Sub, and have that sub trigger your macro. Is it in a regular module, or a sheet/class ? If in a class/sheet you can use a Property instead of a "bare" global. — Tim Williams
– Tim Williams, Commented Jul 10, 2014 at 21:10
I want to use a function to return an initial value to the worksheet and then trigger a macro after that has run as the UDF cannot modify other cells - that was unclear, sorry I am working in a regular module — Thomas
– Thomas, Commented Jul 10, 2014 at 21:12
I think you need to give more details as it's very unclear to me what you're trying to do. — Tmdean
– Tmdean, Commented Jul 10, 2014 at 21:14
a UDF cannot modify other cells, but a UDF can call another procedure that would modify the cells. — guitarthrower
– guitarthrower, Commented Jul 10, 2014 at 21:18

hnk · Accepted Answer · 2014-07-10 21:14:09Z

1

The way to go around this is

Maintain a global variable which you would like to monitor, say every 10 seconds
Run your monitoring Sub every 10 seconds using Application.OnTime (Google for how to use this)
Your monitoring Sub should compare the variable with an older copy which is also kept as a Global variable.
- If no change is detected, then do nothing.
- If change is detected, then update the PrevValue to reflect the updated value of your monitored variable, and then call whatever other Subroutine you need to call.

answered Jul 10, 2014 at 21:14

hnk

2,2221 gold badge15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Thomas Over a year ago

Thank you, this is what I needed. Do you know if Application.OnTime is any significant drain on system resources? This module would likely be running at all times and I want to ensure it is not too taxing on the user system.

guitarthrower Over a year ago

If I understand the method it would mean that the code is constantly running, but paused. Which means it would be unwise to do anything else in the file or on your computer unless the file is closed.

hnk Over a year ago

You won't even notice it (i.e. your monitoring SUB) if it's run every 15, 30 seconds. But the Sub that it calls might slow things down depending on how frequently changes take place. Best is to try and see. The last time I tried something like this, it was reasonably transparent to the user

hnk Over a year ago

@guitarthrower : Not really. It will run rather quietly in the background and if you're in the middle of updating a cell, etc. it'll just 'wait'. Try searching for the Application.Ontime Clock example on Google. Run it on your system and try playing with the spreadsheet simultaneously. You won't even notice it's presence

hnk Over a year ago

@guitarthrower, also, I'd be very happy to know more about non-polling ways to monitor arbitrary variables in any module. You can set up events but can't do that for any possible variable changing.

|

Collectives™ on Stack Overflow

run VBA macro on module variable change

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related