Can you help me figure out why my macro is running slowly?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.

.

Relevant Pages

  • Re: ARRAY QUESTION...
    ... I'm working on a database right now and am writing a macro that will perform ... I'm not good at all with arrays and have no idea how ... Sub EmptyAllOutputTables() ... Dim tdf As DAO.TableDef ...
    (microsoft.public.access.modulesdaovba)
  • RE: populate column of filtered worksheet A with data found in workshe
    ... arrays two visibile columns. ... Dim serverpos As Variant ... Next with workbook B active, filtered, and sorted, I would like to search ...
    (microsoft.public.excel.programming)
  • Re: [PATCH] include/linux/slab.h: new KFREE() macro.
    ... Shoving it into a macro makes it no better. ... Then why do we have all the debugging options to catch re-use of the memory that has been ... trying to get their code correct and letting the existing safety tools ... (I used two arrays, not three, to save space. ...
    (Linux-Kernel)
  • Re: How do I assign values to an array?
    ... >The advantage is that MyValue is type Single() as specified by the ... I anticipated the OP's response that your Assign macro was too complicated. ... There *IS* both performance drag and additional storage overhead storing dynamic ... large arrays, in which case the function call and processing overhead would ...
    (microsoft.public.excel.programming)
  • populate column of filtered worksheet A with data found in workshe
    ... Workbook A has been filtered. ... arrays two visibile columns. ... Dim serverpos As Variant ...
    (microsoft.public.excel.programming)