Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow in program after running few times
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow in program after running few times
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... "tang lk" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow in program after running few times
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. tang lk wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow in program after running few times
Hi Dave,
ActiveWindow.DisplayGridlines = False Did you intend: ActiveSheet.DisplayPageBreaks = False. ? --- Regards, Norman "Dave Peterson" wrote in message ... 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. tang lk wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow in program after running few times
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 "Jim Thomlinson" wrote in message ... 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... "tang lk" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow in program after running few times
Yes I did.
Thanks Norman! Those Gridlines don't seem to bother anything <vbg. Norman Jones wrote: Hi Dave, ActiveWindow.DisplayGridlines = False Did you intend: ActiveSheet.DisplayPageBreaks = False. ? --- Regards, Norman "Dave Peterson" wrote in message ... 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. tang lk wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Entering data slow program down | New Users to Excel | |||
Excel slow to close file/program | Excel Discussion (Misc queries) | |||
Running Program on a Mac | Excel Programming | |||
slow program in a loop | Excel Programming | |||
What's causing file to increase in size and slow program? | Excel Programming |