Link to home
Start Free TrialLog in
Avatar of kyle972
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?

Avatar of harr22
harr22
Flag of United States of America image

Not sure if it would work in this case but try adding a line

  DoEvents
Avatar of kyle972
kyle972

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?
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

Open in new window

Avatar of kyle972

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
ASKER CERTIFIED SOLUTION
Avatar of patrickab
patrickab
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Wrong recommendation. The correct one is Accept http:#a26076478

Mods, can you please restart? Asker and experts, this is the new recommendation.