Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro operations in serial fashion
Is it possible to create a macro that performs an operation, waits
for calculations to end and then moves onto the next operation in the macro in serial fashion ? I'm trying to automate a "calculate and export to text file" process that takes hours to finish. The goal is to run a single macro that allows me to walk away from the entire process instead of waiting for each set of calculations to end and manually starting the next operation. For example, a worksheet has 1,000 cells where each contains a formula. Additionally, the worksheet contains a macro that performs the following: 1) Changes an argument in the formula in each of the 1,000 cells, i.e., =SUM(A1,1) where the value in A1 is changed 2) Waits for calculations to end 3) Exports the worksheet to a text file 4) Changes the same argument again (A1) to another value 5) Waits for calculations to end 6) Exports the worksheet to a text file - Ronald K. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro operations in serial fashion
"kittronald" wrote in message
... Is it possible to create a macro that performs an operation, waits for calculations to end and then moves onto the next operation in the macro in serial fashion ? I'm trying to automate a "calculate and export to text file" process that takes hours to finish. The goal is to run a single macro that allows me to walk away from the entire process instead of waiting for each set of calculations to end and manually starting the next operation. For example, a worksheet has 1,000 cells where each contains a formula. Additionally, the worksheet contains a macro that performs the following: 1) Changes an argument in the formula in each of the 1,000 cells, i.e., =SUM(A1,1) where the value in A1 is changed 2) Waits for calculations to end 3) Exports the worksheet to a text file 4) Changes the same argument again (A1) to another value 5) Waits for calculations to end 6) Exports the worksheet to a text file - Ronald K. Not a direct answer to your question, but from reading your reply to JingleRock I see that you are wanting to advance from VBA beginner level skills. I suspect that your process "takes hours" in large measure because Excel is spending large amounts of time doing things like repainting the screen and recalculating after *each* formula update instead of only once after you have finished revising your formulas. Adding the code from this snip I came across on the MSDN blog some time back should speed your macro up dramatically: 'Get current state of various Excel settings; put this at the beginning of your code Dim screenUpdateState As Boolean Dim statusBarState As Boolean Dim calcState As XlCalculation Dim eventsState As Boolean Dim displayPageBreakState As Boolean With Application screenUpdateState = .ScreenUpdating statusBarState = .DisplayStatusBar calcState = .Calculation eventsState = .EnableEvents End With displayPageBreakState = ActiveSheet.DisplayPageBreaks 'turn off some Excel functionality so your code runs faster With Application .ScreenUpdating = False .DisplayStatusBar = False .Calculation = xlCalculationManual .EnableEvents = False End With ActiveSheet.DisplayPageBreaks = False 'your code goes here<< 'after your code runs, restore state; put this at the end of your code With Application .Calculation = calcState .EnableEvents = eventsState .DisplayStatusBar = statusBarState .ScreenUpdating = screenUpdateState End With ActiveSheet.DisplayPageBreaks = displayPageBreakState Excel VBA Performance Coding Best Practices Joseph Chirilov 12 Mar 2009 7:27 PM Comments 23 Today's author, Chad Rothschiller, a Program Manager on the Excel team, is back with a follow up from his previous post on VBA and Excel performance. http://blogs.msdn.com/b/excel/archiv...practices.aspx In your scenario, you will need to re-calculate each sheet after you complete your formula revision process on that sheet ... but you do not need to "change and restore state" for each sheet. Using the application.calculate method you can manually calculate a single sheet, or a specific range, using VBA code. From the built-in help: Excel Developer Reference Application.Calculate Method Calculates all open workbooks, a specific worksheet in a workbook, or a specified range of cells on a worksheet, as shown in the following table. Syntax expression.Calculate expression A variable that represents an Application object. Remarks To calculate Follow this example All open workbooks Application.Calculate (or just Calculate) A specific worksheet Worksheets(1).Calculate A specified range Worksheets(1).Rows(2).Calculate HTH! -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro operations in serial fashion
Clif,
Thanks for the in-depth help. I added the code, but I'm not sure there's a difference - although my design might have something to do with that. Using Excel 2007, I have an .xlsb workbook that contains two worksheets. On Sheet1, there are about 1,000,000 cells that each contain sixteen nested formulas. 1) In each cell, there's an initial IF test that determines whether the next of three other nested IF formulas should run. 2) Each nested IF formula contains four other function calculations. If the first IF formula evaluates TRUE, then calculation for that cell stops. Otherwise, the second IF formula runs and if it evaluates FALSE, the last IF formula runs. So in total, there's a maximum of about 16,000,000 calculations. On Sheet2, there's two radio buttons, ON and OFF, that set a TRUE / FALSE value. That value is what the initial IF formulas on Sheet1 use to determine if the formulas should further calculate. So on Sheet2, choosing the ON radio button triggers the formulas on Sheet1 to calculate. But since Sheet2 has the focus, screen repainting doesn't occur. The biggest bottleneck appears to be a third party COM add-in that provides four custom functions in each of the three nested IF formulas. Apparently, the COM application that returns the evaluated data takes too long to do so. This appears to cause all of the nested IF formulas to evaluate which is why it takes so long up to 3.5 hours in total. If there's a way to speed that up, I'd like to know. Until then, I'll be hoping The Great Pumpkin at Halloween will bring me a new Intel octo-core hyper-threaded computer. - Ronald K. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro operations in serial fashion
"kittronald" wrote in message
... Clif, Thanks for the in-depth help. I added the code, but I'm not sure there's a difference - although my design might have something to do with that. Using Excel 2007, I have an .xlsb workbook that contains two worksheets. On Sheet1, there are about 1,000,000 cells that each contain sixteen nested formulas. 1) In each cell, there's an initial IF test that determines whether the next of three other nested IF formulas should run. 2) Each nested IF formula contains four other function calculations. If the first IF formula evaluates TRUE, then calculation for that cell stops. Otherwise, the second IF formula runs and if it evaluates FALSE, the last IF formula runs. So in total, there's a maximum of about 16,000,000 calculations. On Sheet2, there's two radio buttons, ON and OFF, that set a TRUE / FALSE value. That value is what the initial IF formulas on Sheet1 use to determine if the formulas should further calculate. So on Sheet2, choosing the ON radio button triggers the formulas on Sheet1 to calculate. But since Sheet2 has the focus, screen repainting doesn't occur. The biggest bottleneck appears to be a third party COM add-in that provides four custom functions in each of the three nested IF formulas. Apparently, the COM application that returns the evaluated data takes too long to do so. This appears to cause all of the nested IF formulas to evaluate which is why it takes so long up to 3.5 hours in total. If there's a way to speed that up, I'd like to know. Until then, I'll be hoping The Great Pumpkin at Halloween will bring me a new Intel octo-core hyper-threaded computer. - Ronald K. Your worksheet is way over my head. Seems like I have read that nested formulas are always evaluated regardless of the result of the IF condition argument... though it's possible I'm confusing platforms. Also, I believe I've read that calling a volitile UDF (COM add-in custom function?) causes reclaculations. All in all, it sounds like the sheet design is causing excessive "over-calculation". It might be worth your while to start a new thread asking for help to improve your worksheet's calculation efficiency ... it could well be that there are other ways to get the needed results. If you do, I'd suggest starting with the summary you posted above, then post those 16 nested formulas -- assuming you can do that without violating any non-disclosure agreements or such. It would also be helpful to anyone inclined to help you if you could describe the calculation rules "in english" (or "psuedo code") -- when it comes to background information the rule generally is: the more the merrier! Good luck! -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro operations in serial fashion
On 8/1/2011 3:08 PM, Clif McIrvin wrote:
wrote in message ... Clif, Thanks for the in-depth help. I added the code, but I'm not sure there's a difference - although my design might have something to do with that. Using Excel 2007, I have an .xlsb workbook that contains two worksheets. On Sheet1, there are about 1,000,000 cells that each contain sixteen nested formulas. 1) In each cell, there's an initial IF test that determines whether the next of three other nested IF formulas should run. 2) Each nested IF formula contains four other function calculations. If the first IF formula evaluates TRUE, then calculation for that cell stops. Otherwise, the second IF formula runs and if it evaluates FALSE, the last IF formula runs. So in total, there's a maximum of about 16,000,000 calculations. On Sheet2, there's two radio buttons, ON and OFF, that set a TRUE / FALSE value. That value is what the initial IF formulas on Sheet1 use to determine if the formulas should further calculate. So on Sheet2, choosing the ON radio button triggers the formulas on Sheet1 to calculate. But since Sheet2 has the focus, screen repainting doesn't occur. The biggest bottleneck appears to be a third party COM add-in that provides four custom functions in each of the three nested IF formulas. Apparently, the COM application that returns the evaluated data takes too long to do so. This appears to cause all of the nested IF formulas to evaluate which is why it takes so long up to 3.5 hours in total. If there's a way to speed that up, I'd like to know. Until then, I'll be hoping The Great Pumpkin at Halloween will bring me a new Intel octo-core hyper-threaded computer. - Ronald K. Your worksheet is way over my head. Seems like I have read that nested formulas are always evaluated regardless of the result of the IF condition argument... though it's possible I'm confusing platforms. Also, I believe I've read that calling a volitile UDF (COM add-in custom function?) causes reclaculations. All in all, it sounds like the sheet design is causing excessive "over-calculation". It might be worth your while to start a new thread asking for help to improve your worksheet's calculation efficiency ... it could well be that there are other ways to get the needed results. If you do, I'd suggest starting with the summary you posted above, then post those 16 nested formulas -- assuming you can do that without violating any non-disclosure agreements or such. It would also be helpful to anyone inclined to help you if you could describe the calculation rules "in english" (or "psuedo code") -- when it comes to background information the rule generally is: the more the merrier! Good luck! I think that's great advice - I'm curious about what the nested formulas look like too. Also is there any way you could write your own functions to perform the calculations the COM add-in is doing? Would you mind telling us what the COM add-in is doing? Mike |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro operations in serial fashion
On 01/08/2011 16:17, kittronald wrote:
Clif, Thanks for the in-depth help. I added the code, but I'm not sure there's a difference - although my design might have something to do with that. Using Excel 2007, I have an .xlsb workbook that contains two worksheets. On Sheet1, there are about 1,000,000 cells that each contain sixteen nested formulas. 1) In each cell, there's an initial IF test that determines whether the next of three other nested IF formulas should run. 2) Each nested IF formula contains four other function calculations. If the first IF formula evaluates TRUE, then calculation for that cell stops. Otherwise, the second IF formula runs and if it evaluates FALSE, the last IF formula runs. So in total, there's a maximum of about 16,000,000 calculations. On Sheet2, there's two radio buttons, ON and OFF, that set a TRUE / FALSE value. That value is what the initial IF formulas on Sheet1 use to determine if the formulas should further calculate. I presume you have forced xlCalculation to manual whilst you update the formulas and then want to start the full calculation running and detect when it has finished. Forcing a fresh recalculation of all cells with screen updating either turned off or on. I have found for some perverse reason that XL2007 can sometimes be faster with screenupdating on!!! One way to test for completeness is to have a some cells with a formula at the last point to be computed (usually the last row, but this may not be guaranteed these days with multicore & multithreading). eg. A1 = RANDBETWEEN(1000000), A2 = RANDBETWEEN(1000000) And right at the end of the sheet A9999=A1+A2 Then in VBA you spend most of your time sleeping or giving other threads priority and once a minute or so you test to see if Cell A9999 contains the result of the most recent pair of random numbers. This may not be foolproof. Alternatively query the machine performance and computer a conservative estimated time to completion and then wait. So on Sheet2, choosing the ON radio button triggers the formulas on Sheet1 to calculate. But since Sheet2 has the focus, screen repainting doesn't occur. The biggest bottleneck appears to be a third party COM add-in that provides four custom functions in each of the three nested IF formulas. Apparently, the COM application that returns the evaluated data takes too long to do so. This appears to cause all of the nested IF formulas to evaluate which is why it takes so long up to 3.5 hours in total. If there's a way to speed that up, I'd like to know. Your best bet might be to cache locally all the answers that the COM database has already provided and look in the cache first. This only helps if you can implement a caching algorithm that is faster than the original lookup - and in VBA this is doubtful. Counting the number of fetches made by COM lookup might be enlightening - it is possible that the nested IF statements are actually executing more paths than you think. CHOOSE might be faster than nested IFs if you can cast your computation into the right form to use it. Until then, I'll be hoping The Great Pumpkin at Halloween will bring me a new Intel octo-core hyper-threaded computer. Looking carefully at the structure of the code is your best bet. Regards, Martin Brown |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro operations in serial fashion
Thanks for the follow-ups.
Per Clif's suggestion, I started a new thread below with more detail. "Improving Nested Formula Calculation Efficiency" - Ronald K. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identify text nums in scrambled fashion | Excel Discussion (Misc queries) | |||
changing math operations for math operations with = sign | Excel Programming | |||
Long running operations | Excel Programming | |||
killing empty spaces in unusall fashion .. | New Users to Excel |