• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 652
  • Last Modified:

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?

0
kyle972
Asked:
kyle972
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now