如何在使用UDF重命名工作簿中的工作表时防止Excel崩溃 [英] How to prevent Excel from crashing when renaming a sheet in a workbook with UDFs

问题描述

我有一个包含两个UDF的工作表。他们的内容（除了一行）对于手头的问题来说并不那么有趣，但对于好奇，他们是：

 函数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））
 
如果不相交（r，PDCA.Range（N10））是Nothing然后
设置r = PDCA。范围（N11）
结束如果
 
对于每个c在r 
如果不是IsEmpty（c）然后
 nummer = nummer  -  1 
结束如果
如果nummer = 0然后
退出
结束如果
下一个
 
如果nummer> 0然后
 finn_prioritert_oppgave = CVErr（xlErrNA）
 Else 
 finn_prioritert_oppgave = c.Offset（0，-11）.Value 
 End If 
 
结束函数
 
函数finn_status_oppgave（oppgave As Range）As String 
 Application.Volatile 
 Dim r As Range，I As Long，satisfied As Boolean 
 
调用deaktiver 
设置r =范围（PDCA.Range（C11），PDCA.Range（C1048576）。结束（xlUp））
设置r = r.Find（what：= oppgave，LookIn： = xlValues，lookat：= xlWhole）
 
 finn_status_oppgave =
 
如果不是没有，那么
设置r = PDCA.Range（J& CStr（r.Row）&M&CStr（r.Row））
 
i = 4 
 satisfied = False 
 
 Do 
如果不是IsEmpty（r.Cells（1，i））然后
 Debug.Print PDCA.Range（J9：M9）。Cells（1，i）
 finn_status_oppgave = PDCA.Range J9：M9）。细胞（1，i）
满意= True 
结束If 
i = i  -  1 
循环当我> = 1而不满足
结束如果
调用reaktiver 
结束函数
  

问题是，只要我能辨别出第二个功能，因为它包含行 Application.Volatile 。我一直在努力工作簿，没有运气确定为什么Excel崩溃，当我尝试重命名一个工作表。最后试图通过我的代码梳理我的代码，寻找错误后，我遇到了这个线程在Excel论坛上，声称由于上述功能而出现问题。

显然

如果你有一个volatile函数（你的代码函数包含
Application.Volatile（True）标签），您可以在其中更改DisplayAlerts
状态（例如Application.DisplayAlerts = False），那么如果
更改名称工作簿中包含这两个命令的
函数的工作表，Excel（至2010年，未使用
2013测试）将崩溃

我没有在我的代码中使用 Application.DisplayAlerts = False ，但症状类似，足以证明它是挥发性的功能导致问题：

有什么我可以做的，以防止发生这种错误，或者我应该使其非易失性，并使用例如 Application.CalculateFull 在 Workbook_SheetChange -event？

解决方案

你的解职者和reaktiver几乎肯定是Subs，因为你没有解析他们。没有很多功能没有参数！

如果任何一个解压者或reaktiver尝试更改任何应用程序，工作簿，工作表或范围属性那么调用函数就会失败！这将包括例如设置 Application.DisplayAlerts = False 。

工作表中的功能只能更改它们所在的单元格的值。他们只能返回函数结果。

您可以使用函数进行计算 - 您可以更改事物。

Subs可以调用函数，但函数不应该调用subs。

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?

解决方案

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.

这篇关于如何在使用UDF重命名工作簿中的工作表时防止Excel崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！