Excel VBA Simulate Break or Complete Stop of Running Macro

Posted on 2009-12-17
Last Modified: 2012-05-08
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?

Question by:kyle972
    LVL 7

    Expert Comment

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


    Author Comment

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

    Expert Comment

    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


    Author Comment

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

    Expert Comment

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

    Accepted Solution

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


    LVL 50

    Expert Comment

    Wrong recommendation. The correct one is Accept http:#a26076478

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now