PDA

View Full Version : [SOLVED] Stability Issues in VBA macro



tilamok
05-10-2014, 04:31 AM
Hi

I wonder if someone could give me some advice.

I have a macro1 that loops continuously with no exit loop. It's purpose is, for each item on an Excel list, check if 2 "text" files exist on the network. These 2 files are produced at different times. If the 2 files exist, then if another Excel file (ExcelFile1) does not exist, then create and refresh ExcelFile1.

The purpose of the macro is to create the ExcelFiles as soon as the text files are available and this macro will mostly be run overnight.

The macro seems to work well for a couple of hours but the parent excel file that runs the macro1 (including the application) then closes with no error or warning.

Is there a way to make the Excel application more stable? I am wondering whether there is a maximum number of times that Excel will allow looping?

Thanks

Paul_Hossler
05-10-2014, 04:40 AM
without seeing the macro (or at least the key parts of it) it's hard to guess

tilamok
05-10-2014, 04:43 AM
Thanks for your prompt reply.
I do not have the macro code at the moment. I will post the code next week

ranman256
05-12-2014, 08:24 AM
It's possible in your looping, you could have objects causing memory leaks. Once you fill up memory, it could collapse Excel.

tilamok
05-13-2014, 08:56 AM
Hi This is the macro that loops continuously. It does call another macro but I know that other macro works.


Sub AS4_checkFiles()

If Not (ActiveWorkbook.ReadOnly) Then ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly

Application.ScreenUpdating = 0
Myfolder = "Output"
Myfile1 = "Model1.Completed"
FlagBothFilesExist = 0
Mypath = ActiveWorkbook.Path
AllExtractsCompleted = 0
Sheets("Inputs").Select
Dim rng As Range
Set rng = Range("R14", Range("R" & Rows.Count).End(xlUp))
counter = 1


Do Until AllExtractsCompleted > 0
Application.StatusBar = " WAITING " & counter


For Each c In rng

FlagBothFilesExist = 0
a1 = Range("D" & c.Row)
a2 = Range("L" & c.Row)

b1 = Left(a1, Len(a1) - 4)
b2 = Left(a2, Len(a2) - 4)

c1 = b1 & "\" & Myfolder & "\" & Myfile1
c2 = b2 & "\" & Myfolder & "\" & Myfile1

d1 = Len(Dir(c1))
d2 = Len(Dir(c2))

e1 = Range("R" & c.Row)

f1 = Mypath & "\" & Range("AS1_FldStoreLive") & "\" & e1
f2 = Len(Dir(f1))

If d1 > 0 And d2 > 0 And f2 = 0 Then
FlagBothFilesExist = 1
d1 = 0
d2 = 0
Application.StatusBar = f1
Call AS3_Refresh(c.Row)
Application.StatusBar = False
Else
FlagBothFilesExist = 0
End If
Next c
counter = counter + 1

Loop
Set rng = Nothing
Application.ScreenUpdating = 1
If ActiveWorkbook.ReadOnly Then ActiveWorkbook.Close savechanges:=False

End Sub

Kenneth Hobs
05-13-2014, 09:35 AM
You have an infinite loop unless your variable is a global one modified by the other routine.

tilamok
05-13-2014, 10:46 AM
You have an infinite loop unless your variable is a global one modified by the other routine.

Hi Ken

Thanks for your reply. Sorry I didn't make my request clear. The infinite loop is actually intentional. The issue I have is that the Excel application that runs this loop eventually closes with no warning message.
I was kindly asking for advice as to why this might be happening

Kenneth Hobs
05-13-2014, 11:40 AM
I don't know what your called routine is doing. It may be the cause of the problem. Infinite loops can be costly in memory usage.

DoEvents can sometimes help.

I like to give an out for infinite loops. This can let your code reset some things.
e.g.

Sub InfiniteLoop()
Dim n As Integer
Application.EnableCancelKey = xlErrorHandler
n = 0
MsgBox "WARNING: This macro will cause an infinite loop. Press" & _
" CTRL+BREAK to exit an infinite loop."
On Error GoTo EndNow
Do
' Because the "Do...Loop" is looking for the value of 'n'
' to equal 1, and because there is no code within the loop
' to change the value of the variable 'n' from it's initial
' value of zero, the "Do...Loop" will continue to loop indefinitely.
Loop Until n = 1
EndNow:
End Sub

For what you are doing, I would use a vb.net program and watch a folder. When the text file shows up, it would do what is needed or call a program that does it. There are some vba examples that watch folders but usually take more of a performance hit than the vb.net route.

tilamok
05-14-2014, 12:48 AM
I don't know what your called routine is doing. It may be the cause of the problem. Infinite loops can be costly in memory usage.

DoEvents can sometimes help.

I like to give an out for infinite loops. This can let your code reset some things.
e.g.

Sub InfiniteLoop()
Dim n As Integer
Application.EnableCancelKey = xlErrorHandler
n = 0
MsgBox "WARNING: This macro will cause an infinite loop. Press" & _
" CTRL+BREAK to exit an infinite loop."
On Error GoTo EndNow
Do
' Because the "Do...Loop" is looking for the value of 'n'
' to equal 1, and because there is no code within the loop
' to change the value of the variable 'n' from it's initial
' value of zero, the "Do...Loop" will continue to loop indefinitely.
Loop Until n = 1
EndNow:
End Sub

For what you are doing, I would use a vb.net program and watch a folder. When the text file shows up, it would do what is needed or call a program that does it. There are some vba examples that watch folders but usually take more of a performance hit than the vb.net route.

Ok thanks Ken.
I'll try that

Thanks very much to Ranman and Paul as well