Running macro operations in serial fashion

[kittronald]

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.

 

[Clif McIrvin]

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/archive/2009/03/12/excel-vba-performance-coding-best-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!

 

[kittronald]

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.

 

[Clif McIrvin]

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!

 

[Mike S]

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

 

[Martin Brown]

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

 

[kittronald]

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.