If it takes between 20 seconds and several minutes to process roughly the
same amount of data, then the first question I would have would be where is
the data? Are the files local or on a network? Any chance the "slowdown" is
entirely due to network issues (i.e., the code is simply waiting for file
access rather than any problem with the code itself)?

Only if there is a direct relationship between data being processed and
execution duration would I seriously question code effieciency. Steps 4, 7 &
9 (maybe 10) strike me as the most likely candidates for code performance
issues, if there are any.
Make sure 9 & 10 have NO (or very, very, very, very minimal) use of .Select,
..Selection, .Activate or other related performance hogs.

HTH


"Phil" wrote in message
oups.com...
I have a macro that is reading data sequentially from 150 text files.
Sometimes the entire process takes 15-20 seconds, and other times it
takes a couple of minutes. I can't figure out why there is such a
time variance in the amount of time it takes to run.

Here are the main parts of the process:

1. Declare variables including dynamic arrays.
2. Size the arrays to 1000 elements using ReDim.
3. Create new Excel workbook.
4. Open first text file and read values into the arrays. Usually
there are 100 values per array.
5. Resize the arrays to 100 elements (or however many are necessary,
always much less than 1000) using ReDim.
6. Close the text file.
7. Perform some computations.
8. Set Application.ScreenUpdating = False
9. Write computed values to the workbook opened in step 3.
10. Format cells.
11. Loop through steps 4 to 10 until all text files have been
processed.
12. A little more formatting.
13. Save workbook and end macro.

If you have any suggestions, please let me know. Thanks.