Excel易失性功能崩溃 [英] Excel crash with Volatile functions

问题描述

如果用户尝试更改工作表名称，则在工作表上具有易失函数会更改DisplayAlerts设置，将导致Excel 2003中止.

Having a volatile function on a sheet that changes the DisplayAlerts setting will cause Excel 2003 to abort if the user tries to change the Sheet name.

显式选项
公共函数fnCrash(标记名作为字符串)作为整数
   应用程序.易失性(True)
    '取消对两个DisplayAlert的注释将导致工作表重命名崩溃
    Application.DisplayAlerts = False
   出错时转到err_handler
    fnCrash = -9999
    Application.DisplayAlerts = True
   退出功能
err_handler:
    MsgBox错误说明
结束功能

Option Explicit
Public Function fnCrash(tagname As String) As Integer
    Application.Volatile (True)
    'Uncommenting the two DisplayAlerts will cause a sheet rename crash
     Application.DisplayAlerts = False
     On Error GoTo err_handler
     fnCrash = -9999
     Application.DisplayAlerts = True
     Exit Function
err_handler:
    MsgBox Err.Description
End Function

要复制-

将以上功能复制并粘贴到VBA模块中

Copy and paste the above function to a VBA module

将公式= fnCrash(东西")添加到工作表单元格中.

Add the formula =fnCrash("Something") to a worksheet cell.

右键单击工作表标签并重命名. Excel失败，并显示C5错误.

Right click on the sheet tab and rename. Excel fails with a C5 error.

如果将DisplayAlerts行注释掉，或者将计算模式更改为手动"，或者在重命名工作表之前使该函数不可变，则它会起作用.

If you comment out the DisplayAlerts lines, or change the calculation mode to manual, or make the function not volatile before renaming the sheet, it works.

生产功能更复杂-这只是将其精简为精髓.

The production functions are more complicated - this just strips it down to its essence.

设置DisplayAlerts = False的原因是为了避免等待OLE".

The reason for setting DisplayAlerts=False is to avoid "Waiting for OLE" Excel popup messages that require user acknowledgement.

推荐答案

> 您好

Hello

 

谢谢您的发帖！我建议将您的问题发布到其中之一 Microsoft Office > Microsoft Office论坛 > Microsoft Office Excel论坛 > 使用公式和方程式  位于以下位置: http://social.answers.microsoft.com/Forums/en-us/excelform/threads

Thank you for your post!  I would suggest posting your question in one of the Microsoft Office > Microsoft Office Forums > Microsoft Office Excel Forums > Using Formulas and Equations  located here:  http://social.answers.microsoft.com/Forums/en-us/excelform/threads

祝您有美好的一天！

这篇关于Excel易失性功能崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！