Slow in program after running few times

[tang lk]

I write a simple VBA program to extract certain info from different files
(same format).

After running for few times, the program become very slow and need to close
and reopen the excel file again in order to make the VBA program faster.

Any idea why it happen ?

regards

 

[Guest]

Use your task manager to watch your system performance. Especially watch the
memory. If the program slows over time then you probably have a memory leak
or the program is not doing proper housekeeping. Ensure that you close all of
the connections that you open, empty any arrays that you fill and set objects
created to nothing after they are finished.

The other major casue of poor performance is not setting calculations off
when making major changes to spreadsheets. The more info you add teh more
calculations that are being run.

application.calculation = xlManual

Application.calculation = xlAutomatic

Hope this helps...

 

[Dave Peterson]

If you're code deletes/inserts rows or columns, maybe adding something like this
to the top will speed it up.

ActiveWindow.DisplayGridlines = False

If you leave those little dashed lines in the display, then excel figures it has
to determine where they go each time you insert/delete rows. And if you're
doing a lot of that kind of stuff, it can slow things down a lot.

 

[Norman Jones]

Hi Dave,

ActiveWindow.DisplayGridlines = False

Did you intend:

ActiveSheet.DisplayPageBreaks = False.

?

 

[tang lk]

Pls see my coding whether any changes is required.

regards
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Sub ImportH04a(Fname)
Dim i
Dim lastrow
Dim cheqdate, cheqdate1
Dim Fname1

Fname1 = Dir(Fname)

Workbooks.OpenText FileName:=Fname, Origin:=xlWindows, StartRow _
:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1,
2), Array( _
11, 9), Array(12, 1), Array(41, 9), Array(51, 2), Array(61, 2),
Array(71, 1), Array(80, 1), _
Array(95, 9))

cheqdate = Cells(7, 1).Value
cheqdate1 = CDate(cheqdate)


' start testing the date
For i = 2 To 40 Step 1
If Workbooks("H04.xls").Worksheets("Check").Cells(i, 1).Value =
cheqdate1 Then
MsgBox "The H04 with this date: " & cheqdate & " already exist!"
Windows(Fname1).Close SaveChanges:=False
Exit Sub
End If
Next i
' end testing the date

lastrow = ActiveSheet.UsedRange.Rows.Count

Application.ScreenUpdating = False

For i = lastrow To 1 Step -1

If Left(Cells(i, 1), 4) <> "0000" Then
Rows(i).Delete
Else
Cells(i, 5).Value = cheqdate
End If

Next

Cells.Select
Cells.EntireColumn.AutoFit

' Application.ScreenUpdating = True
'
'Windows(Fname1).Activate

ActiveSheet.UsedRange.Select
Selection.Copy

' switch to H04 file

Windows("H04.xls").Activate


Sheets("Data").Activate
lastrow = ActiveSheet.UsedRange.Rows.Count

Cells(lastrow + 1, 1).Activate

ActiveSheet.Paste

Application.CutCopyMode = False

Sheets("Check").Activate

' put in checkdate

For i = 2 To 40 Step 1
If Cells(i, 1).Value = "" Then
Cells(i, 1).Value = cheqdate
GoTo impcwl
End If
Next i

impcwl:

Application.ScreenUpdating = True


Windows(Fname1).Close SaveChanges:=False

End Sub

 

[Dave Peterson]

Yes I did.

Thanks Norman!

Those Gridlines don't seem to bother anything <vbg>.