Stability Issues in VBA macro

[tilamok]

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]

without seeing the macro (or at least the key parts of it) it's hard to guess

[tilamok]

Thanks for your prompt reply.
I do not have the macro code at the moment. I will post the code next week

[ranman256]

It's possible in your looping, you could have objects causing memory leaks. Once you fill up memory, it could collapse Excel.

[tilamok]

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]

You have an infinite loop unless your variable is a global one modified by the other routine.

[tilamok]

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]

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]

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