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?

kyle972Asked:
Who is Participating?
 
patrickabCommented:
>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.

It's unlikely to be the speed of the add-in that's the trouble. Instead you will find that it is the internet that's the trouble and is the main reason for the failure of the macro. If your connection is slow and the server you are connecting to is slow then there is no way that it can keep pace with a macro. A shortish macro will execute in tenths of a second and the response time from a server over the internet can be up to 30 seconds. There is a total mis-match between what is needed rather than what is achieve by the system. It's a problem for us all not just you.

You report that 'Wait' stops the add-in. Do you know what's in the add-in or is it a commercial add-in? If you know the VBA code can you post it here.

Patrick

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

  DoEvents
0
 
kyle972Author Commented:
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?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
harr22Commented:
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

0
 
kyle972Author Commented:
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.
0
 
harr22Commented:
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
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Wrong recommendation. The correct one is Accept http:#a26076478

Mods, can you please restart? Asker and experts, this is the new recommendation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.