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

[Phil]

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.

 

[George Nicholson]

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]

Thanks for the quick response. In step 10 I use a lot of .Select.
What would be a better way to accomplish the following?

Sheets(shtData).Select
Union(Range(Cells(rowNum, colNum), _
Cells(rowNum, colNum)), _
Range(Cells(rowNum+8, colNum),
Cells(rowNum+10, colNum))).Select
With Selection
.Borders(xlEdgeLeft).LineStyle=xlContinuous
.Borders(xlEdgeLeft).ColorIndex=48
.Interior.ColorIndex=15
.Font.Name = "Arial"
.Font.Size = 9
End With

 

[Phil]

I ran the macro and it took about 3 minutes. Then I closed Excel,
reopened it, ran the macro and it took about 20 seconds. CPU Usage
was at about 50% and there was always plenty of available system
memory.

 

[George Nicholson]

**Aircode**:

Dim rng as Range

With Sheets(shtData)
Set rng = Union(.Range(.Cells(rowNum, colNum), _
.Cells(rowNum, colNum)), _
.Range(.Cells(rowNum+8, colNum),
.Cells(rowNum+10, colNum)))
End With

With rng
' as you have it for Selection, but make sure you only include those
properties that are actually being changed
' i.e., avoid re-setting properties that already have the properties you
want (as much as possible)
' The macro recorder (which looks like it might be the source of some of
your code) is notorious for redundant redundancy:
' change just one propery and it includes a complete list, which results
in a performance hit if left unedited
' (PrintSetup is the worst example of this, iirc) It seems you have
already edited this, but the point bears repeating.
End With

Set rng = Nothing
'*******

HTH

 

[Phil]

Thanks very much. I haven't had a chance to implement this yet but I
plan to do so tomorrow.