kyle972
asked on
Excel VBA Simulate Break or Complete Stop of Running Macro
I have an excel add in that grabs information from the web real time based upon some inputs and displays it in a cell. I currently am trying to build a macro to change those inputs, which will cause the add in to go out and update the "live cell" based upon the input changes and then I want to record the value of the cell displaying information from the add in. The end goal is to have a record of various inputs and the corresponding results.
However, while my macro runs the addin is not quick enough to retrieve the information and display it before the macro has moved on. Thus, I am recording wrong values or no values at all.
If i use the application.wait function it causes the add in to stop executing as well. I need a way to "stop" the macro to allow the add in to continue running and update and then resume my VBA code. Every method I have tried so far pauses excel which pauses the add in and I still dont get the updated results.
Any creative ideas to simulate a break and then resume after a few seconds?
However, while my macro runs the addin is not quick enough to retrieve the information and display it before the macro has moved on. Thus, I am recording wrong values or no values at all.
If i use the application.wait function it causes the add in to stop executing as well. I need a way to "stop" the macro to allow the add in to continue running and update and then resume my VBA code. Every method I have tried so far pauses excel which pauses the add in and I still dont get the updated results.
Any creative ideas to simulate a break and then resume after a few seconds?
ASKER
No luck with DoEvents. I really think VBA has to completely stop and then start again in order for the add in to process correctly.
Any more ideas?
Any more ideas?
can you end the sub then have a worksheet change event on the "live cell" that fires when its updated
Sub Inputs()
'do some stuff to populate the addin
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'This needs to be in the worksheet module
'Change the Intersert from A1 to your "live cell"
If Not Intersect(Target, Range("A1")) Is Nothing Then
'Do Some more stuff
End If
End Sub
ASKER
That doesn't seem to work either. The live cell is populated by a formula telling the addin what to retrieve and it doesn't look like excel is picking it up as a change when it changes.
ok, my final thoughts. If these dont work then I dont know...
first have you tried applicatiion.calculate to force it to update before moving on
if that doesnt work, maybe a timer which will function similar to application.wait, but not stop the code
i = Timer
Do While Timer() < i + 10 ' seconds
Loop
first have you tried applicatiion.calculate to force it to update before moving on
if that doesnt work, maybe a timer which will function similar to application.wait, but not stop the code
i = Timer
Do While Timer() < i + 10 ' seconds
Loop
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Wrong recommendation. The correct one is Accept http:#a26076478
Mods, can you please restart? Asker and experts, this is the new recommendation.
Mods, can you please restart? Asker and experts, this is the new recommendation.
DoEvents