title: How to prevent Excel from crashing when renaming a sheet in a workbook with UDFs
body: <p>I have a worksheet which contains two UDFs. The contents of them (apart from one line) isn't that interesting for the problem at hand, but for the curious, here they are:</p>
  
  <pre><code>Function finn_prioritert_oppgave(nummer As Long) As String
    Dim i As Long, r As Range, c As Range
  
    Set r = Range(PDCA.Range("N11"), PDCA.Range("N1048576").End(xlUp))
  
    If Not Intersect(r, PDCA.Range("N10")) Is Nothing Then
      Set r = PDCA.Range("N11")
    End If
  
    For Each c In r
      If Not IsEmpty(c) Then
        nummer = nummer - 1
      End If
      If nummer = 0 Then
        Exit For
      End If
    Next
  
    If nummer &gt; 0 Then
      finn_prioritert_oppgave = CVErr(xlErrNA)
    Else
      finn_prioritert_oppgave = c.Offset(0, -11).Value
    End If
  
  End Function
  
  Function finn_status_oppgave(oppgave As Range) As String
    Application.Volatile
    Dim r As Range, i As Long, satisfied As Boolean
  
    Call deaktiver
    Set r = Range(PDCA.Range("C11"), PDCA.Range("C1048576").End(xlUp))
    Set r = r.Find(what:=oppgave, LookIn:=xlValues, lookat:=xlWhole)
  
    finn_status_oppgave = ""
  
    If Not r Is Nothing Then
      Set r = PDCA.Range("J" &amp; CStr(r.Row) &amp; ":M" &amp; CStr(r.Row))
  
      i = 4
      satisfied = False
  
      Do
        If Not IsEmpty(r.Cells(1, i)) Then
          Debug.Print PDCA.Range("J9:M9").Cells(1, i)
          finn_status_oppgave = PDCA.Range("J9:M9").Cells(1, i)
          satisfied = True
        End If
        i = i - 1
      Loop While i &gt;= 1 And Not satisfied
    End If
    Call reaktiver
  End Function
  </code></pre>
  
  <p>The problem is, as far as I can discern, with the second function, because it contains the line <a href="https://msdn.microsoft.com/en-us/library/office/ff195441.aspx" rel="nofollow noreferrer"><code>Application.Volatile</code></a>. I have struggled with the workbook all morning, having no luck in determining why Excel crashes, when I try to rename a sheet in it. Finally trying to google it after having combed through my code, looking for errors, I came across <a href="http://www.mrexcel.com/forum/excel-questions/601843-excel-crashes-when-worksheet-renamed.html" rel="nofollow noreferrer">this thread</a> on an Excel-forum, which claims that the problem occurs because of the function mentioned above. </p>
  
  <p>Apparently </p>
  
  <blockquote>
    <p>if you have a volatile function (your code function contains the
    "Application.Volatile (True)" tag), where you change the DisplayAlerts
    status (for example, "Application.DisplayAlerts = False"), then if you
    change the name of a worksheet within the workbook that contains a
    function with these two commands, Excel (up to 2010, not tested with
    2013 yet) will crash</p>
  </blockquote>
  
  <p>I am not using <code>Application.DisplayAlerts = False</code> in my code, but the symptoms are similar enough that I feel confident it is the volatile function which causes the problems:</p>
  
  <p><img src="https://i.stack.imgur.com/2xQTO.png" alt="Error-message"></p>
  
  <p>Is there anything I can do to prevent this error from occuring, or should I just make it non-volatile, and make the calculation happen using e.g. <code>Application.CalculateFull</code> on the <code>Workbook_SheetChange</code>-event?</p>