Running time of procedure increases

[Guest]

After adding a 'progress meter' to the report I am currently working on, I
discovered that the naming of the ranges which I perform first on each sheet
was taking up the vast majority of the time. I was investigating by adding a
timer to the naming procedure, and I discovered that each subsequent running
of the report takes longer than the previous time. The timer results were:

Sheet 1: 1.910156 seconds
Sheet 2: 7.582031 seconds

Sheet 1: 2.308594 seconds
Sheet 2: 9.082031 seconds

Sheet 1: 3.507813 seconds
Sheet 2: 14.23828 seconds

I don't really understand what's happening here, since I'm closing the
actual report file each time and just leaving the 'raw data' sheet open which
I use to copy from at the very beginning.

Could someone tell me what's causing the delay in the subsequent runs, and
if there is any way of preventing this increase?

Many Thanks,
Tara H

 

[Guest]

For reference, I'm using Excel 2007, and the procedure in question is as
follows:

Sub name_columns(cols_sheet As String)
Dim startTime As Single
Dim endTime As Single
startTime = Timer

With Sheets(cols_sheet)
Dim mycol
Dim prefix As String
prefix = Left(cols_sheet, 1) & "_"
For Each mycol In .Columns
Dim colName As String
colName = mycol.Cells(1, 1).Value
colName = prefix & to_range_name(colName)
If (colName <> "") Then
mycol.name = colName
End If
Next mycol
End With

With Range("1:1")
.Cells.HorizontalAlignment = xlLeft
.Cells.VerticalAlignment = xlTop
End With

endTime = Timer

Debug.Print (endTime - startTime & " seconds")

End Sub

to_range_name is a function to remove characters that can't be part of a
range name:

Function to_range_name(s As String)

s = Replace(s, " ", "_")
s = Replace(s, "%", "_pct")
s = Replace(s, "/", "_over_")
s = Replace(s, "(", "_")
s = Replace(s, ")", "_")
While (InStr(1, s, "__") > 0)
s = Replace(s, "__", "_")
Wend
If (Right(s, 1) = "_") Then
s = Left(s, Len(s) - 1)
End If

to_range_name = s

End Function

When I thought it was just taking a long time, I thought maybe all the
string operations above were the cause of it, but that doesn't seem to
explain why the time would _increase_.

Many Thanks,
Tara H