Excel VBA in Excel 2003

[griffolo78]

Hi everybody,
I have written a macro in exel 2003 that have to open and close other excel files (saving them) a lot of times.
Now, although the macro works well, there is the following problem: as the macro goes on, the processes of
opening and closing the files become slower and slower. It seems to me like the memory accumulates data,
reading them at each opening operation. Can anyone tell me about this strange behaviour?
Take into account that, even after rebooting the operating system, the macro works slowly.

P.S. The computer has two processors AMD64 and has a lot of memory .

Thanks in advance

griffolo 78

 

[mincatil2006]

Hello grilffolo 78

I'm just wondering if your macro create the instance of the excel application each time the macro opens the external workbook. If so, I guess the problem may be due to the increasing number of the instance that have not been removed from the memory. I would recommend to set 'nothing' to the object and erase arrays at the end of the code. That would reduce the straints on the memory.

I hope this would help. But your problem may be due to the other causes.

Regards.

 

[griffolo78]

Hello mincatil2006,
First of all thanks for your speed reply. Unfortunately, the problem still remains even I reboot the OS.
For solving the problem I had to reset the excel registry configuration. That strange thing. It is like some
registry keys are permanently modified by my macro, but I don't know why. Anyway, till I don't resolve this
problem, I have to reset the registry config from time to time.

Regards,

griffolo78

 

[mincatil2006]

Hello again. griffolo 78

It is very strange that you have to revert the setting on the registry in order to solve the problem on your computer. If you don't mind, could you post the original code, I would like to review it and might give you some suggestions to prevent it from happening.

Regards,
mincatil2006

 

[griffolo78]

Code for You

Hi mincatil2006 ,

Here is the heart of the procedure. Some files are open to be written (cycled in "i4" index) and several files are open one by one to be read (cycled in "i5" index) for each "i4". The other cycles ("i1","i2" and "i3") increase only the time of the procedure and are not important (the input data are read from some
cells in the sheet I called "InputData").
I hope this info is enough for you to understand the problem.

Thanks a lot in advance,

griffolo78.


Main Code:

For i1 = 0 To (nPrTotF - 1)
PrTotF = "" & ThisWorkbook.Sheets("InputData").Cells(rowPrTotF, colStart + i1).Value
nPrTotS = lungPPT(i1)
For i2 = 0 To (nPrTotS - 1)
PrTotS = "" & ThisWorkbook.Sheets("InputData").Cells(rowPrTotS, colStart + i2).Value
For i3 = 0 To (nPrF - 1)
PrFeed = ThisWorkbook.Sheets("InputData").Cells(rowPressF, colStart + i3).Value

For i4 = 0 To (nThick - 1)
address = mainPath1 & "\PFtot=" & PrTotF & " kPa, PP=" & PrTotS & " kPa\Temp - PF=" & PrFeed & _
" kPa, PP=" & PrTotS & " kPa, d=" & memThick(i4) & " micron"
MainFileName = "CPC vs Permeance.xls"
Workbooks.Open Filename:=address & "\" & MainFileName 'Open the file on which to write;
Windows(MainFileName).Activate
Sheets("Data").Select
Range("B1").Value = PrFeed

For i5 = 0 To (nTemp - 1)
ExcFileName = "T=" & Temp(i5) & "°C; d=" & memThick(i4) & " micron;.xls"
ChDir address
Workbooks.Open Filename:=address & "\" & ExcFileName 'Open the file from which to read;
Cell1 = "" & Col1 & (RowIni + i5)
Cell2 = "" & Col2 & (RowIni + i5)
Sheets("TMEq").Select
Range("B12:O12").Select
Selection.Copy
Windows(MainFileName).Activate
Sheets("Data").Select
Range(Cell1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
Range(Cell2).Select
Range(Cell2).Value = Temp(i5)
Windows(ExcFileName).Activate
ActiveWindow.Close 'Close the file from which to read;
Next i5

Windows(MainFileName).Activate
ActiveWindow.Close SaveChanges:=True 'Close the file on which to write;
Next i4

Next i3
rowPressF = rowPressF + 1
Next i2
rowPressF = rowPressF + 1
rowPrTotS = rowPrTotS + 1
Next i1

 

[mincatil2006]

Hello griffolo 78

The first thing came to mind when I looked at your code is that your code hasn't clear the copied values from the clipboard. I guess that slows down the computer significantly after looping through many excel spreadsheets. I suggest to put 'CutAndCopyMode = False' to where it's relevant. My suggestion is to put it under the line 'Selection.PasteSpecial...'

Also, I suggest you to read the article in the following link. It explains some ways to reduce the execution time of the macro. I found some simple things could reduce the execution time significantly and save a lot of memory draining.

http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

Regards,
mincatil2006