Calculations not keeping up with macro

Hello,

I have a spreadsheet with 51 tabs.  Tab_1 is a summary of data
contained on the remaining 50 sheets (such as averages and sums).  A
macro iterates through values of 1 - 100.  During each iteration a
variable with dependents changes on each of the 50 sheets and
resulting data for each iteration is output on the summary tab (output
such as the averages and sums for each iteration).

The macro works great when I step through it, the problems occur when
I run it at full speed.  The output data at each iteration does not
keep up with the macro, thus the outputs are inaccurate.  It's like
the macro runs faster than all the calculations are performed and
output.

Any suggestions to help alleviate this challenge would be greatly
appreciated.

Thanks!!
0
xl
3/9/2010 1:08:18 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
904 Views

Similar Articles

[PageSpeed] 6

Try adding

Application.DoEvents

at the end of each loop (or wherever you're triggering a recalculation).

Tim


"xl@lf" <bribri25@gmail.com> wrote in message 
news:1b799b09-f922-4dc9-b5dd-9491ebfb697b@c16g2000yqd.googlegroups.com...
> Hello,
>
> I have a spreadsheet with 51 tabs.  Tab_1 is a summary of data
> contained on the remaining 50 sheets (such as averages and sums).  A
> macro iterates through values of 1 - 100.  During each iteration a
> variable with dependents changes on each of the 50 sheets and
> resulting data for each iteration is output on the summary tab (output
> such as the averages and sums for each iteration).
>
> The macro works great when I step through it, the problems occur when
> I run it at full speed.  The output data at each iteration does not
> keep up with the macro, thus the outputs are inaccurate.  It's like
> the macro runs faster than all the calculations are performed and
> output.
>
> Any suggestions to help alleviate this challenge would be greatly
> appreciated.
>
> Thanks!! 


0
Tim
3/9/2010 4:45:22 AM
I tried the Doevents and it appeared to put the spreadsheet in manual
calculation mode.  The macro processed at a blazing speed and all the
output data was identical.  When the macro was finished I pressed F9
and it updated all the values.  Since I'm constantly updating values
manual calculations won't work for me in this macro.  Thanks Tim.
0
xl
3/9/2010 2:32:16 PM
I've never seen DoEvents put the application in manual calculation mode.

In any event, you might be better off putting Excel in manual calculation 
mode at the start of the macro, and calling Application.Calculate just 
before you want to pick up any results.  Don't forget to turn it back to 
automatic at the end of the routine.

Tim



"xl@lf" <bribri25@gmail.com> wrote in message 
news:970dceeb-8a0d-4346-8d2a-68f172140aa6@v20g2000yqv.googlegroups.com...
>I tried the Doevents and it appeared to put the spreadsheet in manual
> calculation mode.  The macro processed at a blazing speed and all the
> output data was identical.  When the macro was finished I pressed F9
> and it updated all the values.  Since I'm constantly updating values
> manual calculations won't work for me in this macro.  Thanks Tim. 


0
Tim
3/10/2010 5:59:45 AM
Reply:

Similar Artilces:

Macro needed to Paste Values and prevent Macro operation
Sub Macro1() ' ' Dim myBk As Workbook Set myBk = Workbooks.Open(Application.GetOpenFilename(, , "Select the File")) Range("To_Database").Copy ThisWorkbook.Worksheets(1).Range("A65536").End(xlUp)(1).Offset(2, 1) myBk.Close False End Sub In the above macro I need to prevent macros operating in the External File when it opens. I also need the Named Range ("To_Database") from the External File to be copied into the file from which this macro operates as PasteValues. At the moment the Formula in the Named Range is coming across, so that I'm ...

Help on Macro or Formula
Hi, i hope someone can help me. i need to create a formula that sits in a cell and looks for data. ( obvioiusly ). however, the formula needs to be in place even though the file from ehere the data comes from might not be there yet. ( i have to create a book that when a new file is created, the links are already in place ). i think it could work with an IF type formula for ( if B2="",""). here is my information. Cell description: A2 = Job no. B2 = Client Name D2 = Actual Spend on project Register!D2 = Job Description Register!H2 = Quoted Amount my path is S:\Clients\...

Macro to Create a Chart and Update it Automatically
I wonder if you can help me to create a macro to make a chart and update it automatically when I add more information in the following rows. Basically, this is the table a have (see below) and I just want the column “C” and the column “E” have a chart. Where the column “C” is the “x” axis and the column “E” is the “y” axis. I have created manually already but I have the go to source data to update the chart every time I have more information in the following rows. Since, I have several tables that make it difficult to me to handle it easily. Thanks in advance. Maperalia A B C D E 6/7...

Possible for statements in Publisher to do calculations?
Hi. I just got Publisher. I would very much like to use its "statements" layouts, but can it add up values and put the total as Microsoft Works can? Do you know what I mean? I want to type of a statement, enter values like: previous balance, payments, new invoices, etc. and have it tell me what the total is and print it out so I can mail to customer? Thanks for the help. Kimi wrote: > Hi. > > I just got Publisher. I would very much like to use its "statements" > layouts, but can it add up values and put the total as Microsoft Works > can? Do you ...

Outlook keeps Crashing #3
Soon after turning on Outlook 2000 I get the following message Microsoft Outlook has encountered a problem and needs to close. We are sorry for the inconvenience. I am prompted to select send error report and after I click send it closes/crashes Outlook. Please help Paul Try using the disc and reinstalling or choose the repair function from the office disc >-----Original Message----- >Soon after turning on Outlook 2000 I get the following >message Microsoft Outlook has encountered a problem and >needs to close. We are sorry for the inconvenience. I am >prompted ...

stop a formula from re-calculating
I have set up a spreadsheet to work out an amount to be paid, spread over 4 quarters of the year. Is there any way of stopping a cell from re-calculating without manual input of the figure? Becks wrote: > I have set up a spreadsheet to work out an amount to be paid, spread over 4 > quarters of the year. Is there any way of stopping a cell from re-calculating > without manual input of the figure? ---------------- If you want to permanently stop it, just copy the cell and then use "paste special" > "values" to paste it back over top of itself. Bill ...

Import data using macro
Hi, how do you use excel to import a text file using a macro to do the work for you? I start by creating a new workbook. Then turn on the macro recorder and open that text file. (Try not to make errors--it can make clean up troublesome.) Then format the worksheet (headers/footers/page layout/filters,freeze panes) exactly the way you want. When you're done, turn off the macro recorder. Close the imported file and back to your other workbook. Insert a giant button from the forms toolbar on the first worksheet (with instructions). Then save it. Now go into the vbe and start tweakin...

Auto-start macro
Hello, I have a simple macro in Excel named Macro1. How can I make Macro1 run automatically everytime I start Excel? Simple step-by-step replies please. Thanks in advance. Try renaming it to: Auto_Open Frank wrote: > > Hello, I have a simple macro in Excel named Macro1. How can I make Macro1 > run automatically everytime I start Excel? Simple step-by-step replies > please. Thanks in advance. -- Dave Peterson Thanks Dave, worked great! "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:4B8E5D84.65E7E0CB@veriz...

What may cause macro modules to be created by accident?
I understand how to remove macro modules, but I seem to have seen numerous posts by other Excel users who question how macros could have gotten into their workbooks to begin with; they say they didn't create any macros, so why the warnings when they open the workbook. I'm curious how this could happen accidentally. One reason I heard was that modules could be created if users accidentally select "view code" when right clicking on tabs. I tried to simulate this and no modules were created, so I'm skeptcial. Certainly, if a user begins recording a macro and then bails out, ...

how reopen the document with Enable Macros.office2007
how reopen the document with Enable Macros.office2007 ...

CD keeps opening automatically
Hi: After having installed printer, CD slot keeps opening automatically, as if I'm going to put a CD in it. Then, I need to keep closing it every two minutes or so. My question is; How to stay CD slot keeps closing? Thanks for your help in advance. "MapleE." <someone|@comcast.net> wrote in message news:uj#T$oHELHA.5724@TK2MSFTNGP05.phx.gbl... > Hi: > After having installed printer, CD slot keeps opening automatically, as if > I'm going to put a CD in it. Then, I need to keep closing it every two > minutes or so. > >...

SUMIF function not calculating from closed worksheet
We recently upgraded from Microsoft Office 2000 to 2007. Prior to the upgrade each department would access a spreadsheet that would load data from a common lookup spreadsheet The loading process was performed as follows: The lookup file and each department file would be opened to update the link for that month and then saved and closed. When the department head would open the department file, a question would appear asking if they wanted to update the link, if they answered no the spreadsheet would open and display the calculated data. If they answered yes, the file would o...

Calculating Fields and Manual Input
Hello, Is it possible to have a field that is both calculated or manually inputted?. For example, if field 1 and field 2 has data then calculate field 3. Can we also have the user simply enter the total amount into 3. What I have noticed is if I enter an amount directly into field 3 and field 1 and 2 do not have data the value goes back to 0 (which makes sense) but wondering if there is a way around this. Thanks! On Jul 1, 6:06 am, MDV1457 <MDV1...@discussions.microsoft.com> wrote: > Hello, > Is it possible to have a field that is both calculated or manually > inputted...

varible calculation
I am trying to create a calculation for haulage/trucking charges 1-300 kilos £ 30 300-400 kilos £ 35 400-500 kilos £ 40 What formula could I use to accomplish this?? -- kinsey assuming that you mean: 1-299 300-399 400- then try: =LOOKUP(A1,{0,300,400},{30,35,40}) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "kinsey" <kinsey@discussions.microsoft.com> wrote in message news:46C0E5C2-11A6-4AF7-9F2A-8D4D23F4498B@microsoft.com... &...

Using iterations to calculate circular references.
I am working with data that requires using circular references in some calculations. I checked the "iterations" box to allow for these calculations to work, and Ecxel seems to be calculating everything correctly. The problem is that after I close the workbook, when I later reopen it to continue working, all the cells that are a part of the circular reference display the #VALUE! error. I can fix this by deleting one cell that is part of the loop and then undoing that command, or by copying the formula from a cell, deleting it, and pasting it back into the same cell. I have to d...

Calculating Total from selections
Hello everyone, Can anyone help / make suggestions in achieving the following: I am trying to create an invoice that summarises the total of the products at the bottom of the spreadsheet. What I want to achieve is some way of having various products in a row which a user can select via some sort of check box (giving the product a boolean value). Each product would have a price value attached to it and when it is selected the price should be added to the total. This would therefore allow the user to select various products and the total would be calculated accordingly. This seems relativel...

calculating return in a range
Hi, I have a range of stock prices. with indication for buy or sell. and i want to calculate the return of the range for example : buy 522.73 buy 527.9 buy 528.81 buy 532.33 buy 535.1 buy 536.23 buy 537.03 buy 538.57 sell 537.54 sell 532.86 sell 538.14 sell 533.08 buy 531.22 I want to calculate the return for the first buy signal. the range is from the start to the first sell signal 537.54- 522.73 then the sell range untill the first buy signal 531.22- 537.54 and so on.... -- kman ------------------------------------------------------------------------ kman's Profile: http://www.e...

Outlook2003 : keeping a copy of PST on server
I can't seem find the place to make the change on the client's outlook to keep a copy of the pst file on exchange server. When I configured outlook I got the message to go into tools and select the option to keep a copy on server. I couldn't find that option and all mail was delivered to the client. When you go through owa all you will see is the new mail not delivered to the client. I'm going to hack at it and when I find something I will post my findings. But if someone already knows where to look I would appreciate that Thanks Norm found this article http://www...

Macros for worksheet copy & paste?
I have 6 different excel files. How can I combine all 6 of them in one single excel file under individual worksheets (E.g. Worksheet1 for File1, worksheet2 for File2 ....etc)? I do not wish to copy & paste it manually. Can I use a macro to take care of it? If so, where can I find further info on how to go about it? THANKS!! Assuming (1) this is a one-time need, and (2) your workbooks have only one sheet each, this would probably be faster done by hand: 1) With all the files open choose one of the files to be the receiving file. 2) Select the sheet in the subsequent books, one at...

vba code to enable macros
Hi, Can we write a vba code to enable macros. In most of the sites it says we cant do that . The only thing we can do is change the macro setting or work around is hide the sheet which has macros. But none of them will work in my case. Can we change an excel security setting to from macro on open so that macros are enabled. No. If we can do that with code, then so could a hacker with bad intentions. Mike F "varsha12" <varsha12@discussions.microsoft.com> wrote in message news:DBEB1614-C956-491B-B351-02DD9F711891@microsoft.com... > Hi, > Can we w...

Macro on a protected worksheet in a shared workbook.
Hi, I have a macro in protected worksheets that can't run once the Workbook is Shared. I have wrapped the Macro code so as the worksheet is unprotected for the time the Macro runs, and unable the Autofilter. The code is as follow at the moment: Sub Newaction() Sheets("Critical Path").Unprotect ("") ' Newaction Macro ' Macro recorded 17/11/2004 by Clifford ' ' Selection.AutoFilter Field:=1, Criteria1:="=" Sheets("Critical Path").EnableAutoFilter = True Sheets("Critical Path").Protect contents:=True, userInter...

excel ? on automatic calculations
Hi, I use a lot of spreadsheets where I'll enter one data item into, sa for example cell A1, and then I'll enter several formulas tha reference the data in A1 (also formulas indirectly referencing A1, b referencing another formula that's related to A1) . I know how to select and drag down the formulas to perform the sam operations on data that is entered in cell A2, A3, etc. But due to th nature of my work, this leads to A LOT of dragging. I've noticed that for certain formulas (but only a few), when I ente new data in a new row, Excel will automatically drag down the adjace...

How to run a macro when user enters a value in a cell?
See the question about vbasic functions below also. Hi serdar, You may want to use the Change event of the worksheet. For example copy the following code to the VBA module of your active worksheet and try changing value in cell [A1]: '---code start---- Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.[a1]) Is Nothing Then _ Call Test End Sub Sub Test() MsgBox "Hello!" End Sub '---code end---- Reagrds, KL "serdar" <yuzbasioglu@kablonet.com.tr> wrote in message news:usl70aQaFHA.900@tk2msftngp13.phx.gbl.....

Help !!!!!! Excel 97 keeps crashing out of my file.
I have a workbook that has been in existance for several years and is continually being developed. After adding some additional graphics I suddenly started to get The Program error Dialog Box : "Excel.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created". I've deleted the graphics and I continue to get the error whenever I try to copy to the clipboard. I am running Excel 97 under Windows 2000 on A DEll Latintude 640 laptop. Any one any suggestions as to what has happened and how I can rescue the situation? The...

unable to run macros
When I tried to run a macro that I got from someone on this website it told me that "The macros in this project are disabled...." I can't figure out how to enable them. I'm okay with this Macro not working however I've linked some cells between worksheets and whenever I change the cell that contains the original information it gives me that error message again. I need to know how to enable Macros or else Delete the macro i have in there. Please help. Thanks in Advance Travis close the workbook go to <tools><macros>security and select medium reopen the...