How to prevent Excel from crashing when renaming a

Question

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:

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 > 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" & CStr(r.Row) & ":M" & 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 >= 1 And Not satisfied
  End If
  Call reaktiver
End Function

The problem is, as far as I can discern, with the second function, because it contains the line Application.Volatile. 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 this thread on an Excel-forum, which claims that the problem occurs because of the function mentioned above.

Apparently

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

I am not using Application.DisplayAlerts = False in my code, but the symptoms are similar enough that I feel confident it is the volatile function which causes the problems:

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. Application.CalculateFull on the Workbook_SheetChange-event?

Answer 1

Your deaktiver and reaktiver almost certainly Subs because you parse nothing to them. There aren't many functions that take no arguments!

If either deaktiver or reaktiver try to change any application, workbook, worksheet or range property then the calling function will fail! This would include such things as setting Application.DisplayAlerts = False for example.

Functions in a worksheet can only change the value of the cell in which they reside. They can only return the function result.

You use functions to calculate - you subs to change things.

Subs can call functions but functions shouldn't be calling subs.