CODEWITHSUNDEEP
vbaexcelcrashinfinite-loop
BruceV
BruceV

Reputation: 113

VBA infinite loops

Is there a construct that will protect a VBA function from crashing Excel when it enters an infinite loop? It's not a big deal, just inconvenient when you forgot to save your latest tranche of changes before running.

On a more general note, surely it's not beyond the capabilities of MS to keep the reset button enabled in this situation. What happens here is the screen goes white, and the only way out seems to be to open task mgr and close the application.

Upvotes: 1

Views: 4602

Answers (2)

istee1
istee1

Reputation: 21

Hello this is my first answer/post. Basically I set start time in the beginning of the procedure and then compare with now() within the loop. If the difference is grater than some value (0.0001 ~ 10 seconds) user is asked to either interrupt the procedure or continue (and "start" variable is reset to now() ).

This specific procedure goes through all non-blank cells in range and formats the numeric values to as thousand separated.

I hope that it will be helpful for others:

Sub someVBA()
Dim rng As Range, Cell As Range
Dim yn As String
Dim start As Double
'this VBA code formats all numeric cells as #, ##0

start = Now()

Set rng = ActiveSheet.Columns("A:FZ").SpecialCells(xlCellTypeConstants)

For Each Cell In rng
  If IsNumeric(Cell.Value) Then Cell.NumberFormat = "#,##0"

  'if the difference between start and now is > approx. 10 seconds
  If Now() - start > 0.0001 Then
    roz = MsgBox("You probably stuck in a loop. Press YES to exit, otherwise you will continue", vbInformation + vbYesNo)
      If roz = vbYes Then
        Exit For
      Else
        start = Now()
      End If
  End If
Next Cell

End Sub

Upvotes: 1

anefeletos
anefeletos

Reputation: 702

Recently I confirmed this solution:

Even if a DoEvents statement is missing from the loop, you can still break the procedure event:

Keep esc key pressed while clicking with the mouse the taskbar icon. This way you force Excel receive focus and listen the keyboard.

Upvotes: 2

Related Questions