1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: EXCEL: Is ScreenUpdating=False working im my macro?

Discussion in 'Business Applications' started by guo1121, Nov 9, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. guo1121

    guo1121 Thread Starter

    Joined:
    Nov 9, 2007
    Messages:
    6
    Hi

    I started to use VBA recently. I have a macro, which use solver to get some solution. The worksheet contains about 14,000 rows of data and I need to use solver as many times. In the macro, I added Application.ScreenUpdating=False at the beginning and Application.ScreenUpdating=True at the end. But it seems to me that the screen kept updating. I can see the cells changing values one by one. It took more than one and a half hour to finish the macro. I guess the Application.ScreenUpdating=False doesn't really work in my macro. But I couldn't figure out the reason. Any help or suggestions are greatly appreciated.

    Since my data is pretty large, I created a "vbtry.xls' and write the macro in a simple manner. But it uses the solver repeatedly. The macro name is "test". As you can see, the screen keeps flashing if you run the macro. My understanding is that: with Application.ScreenUpdating=False, the window should look like freezing and the data should be posted all at once at the end. Am I right on this? What's wrong with my macro?

    Please see the attached file. Thanks.
     

    Attached Files:

  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    In your example you have not defined SolverSolve, Solverfinish, and SolverOK. When I run the macro the code bugs out. Are you missing some code? Explain how the macro is supposed to work and what these variables or procedures are. Is there an add-in missing from the project?

    Regards,
    Rollin
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Solver will often turn ScreenUpdating back to True by itself. I don't think it is quite known as to why, but your best bet is to put Application.ScreenUpdating = False inside the start of your loop. Don't forget to try putting off the other events as well, depending on what you have. I use a sub routine for this....
    Code:
    Sub ToggleEvents(blnState As Boolean)
    'Originally written by firefytr
        With Application
            .DisplayAlerts = blnState
            .EnableEvents = blnState
            .ScreenUpdating = blnState
            If blnState Then .CutCopyMode = False
            If blnState Then .StatusBar = False
        End With
    End Sub
    ... and then I just call in my code like so ...
    Code:
    Sub MySub()
        Call ToggleEvents(False)
        'your code here...
        CallToggleEvents(True)
    End Sub
    And here are some good VBA resources pertaining to Solver...

    http://support.microsoft.com/kb/843304
    http://www.vertex42.com/ExcelArticles/excel-solver-examples.html

    HTH
     
  4. guo1121

    guo1121 Thread Starter

    Joined:
    Nov 9, 2007
    Messages:
    6
    Thanks Rollin and Zack.

    Rollin, in order to use Solver function, we need to establish a reference to the Solver add-in first.

    I figured out the cause. The line: 'SolverFinish KeepFinal:=1', tells Microsoft Excel to keep the final solution in the changing cells. If omitted, it is same as set it at 1. If I comment out this line, everything is fine and the Screen doesn't update. Therefore, I guess this line kept turn the ScreenUpdating on in my original Macro.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/649979

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice