"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 :-)