当我关闭工作簿时，尝试清除不同工作簿中两个单元格中的值。运行时91错误 [英] Attempting to clear the values in two cells in a different workbook when I close the workbook. Run-time 91 error

问题描述

当我关闭我正在积极使用的工作簿时，我试图在另一个工作簿中清除两个单元格中的值。当我关闭我的工作簿时，我收到以下错误：

运行时错误'91'：对象变量或块变量未设置

使用调试和逐步通过我可以找到我收到错误的行，但对于我的生活我没有看到我'我错过了。

我调用我的宏：

 私人Sub Workbook_BeforeClose（Cancel As Boolean）
 
 TestClear 
 
 End Sub 
 
 Sub TestClear（）
 
 Application.ScreenUpdating = False 
 Application.DisplayAlerts = False 
 
 Dim SourceWb As Workbook，myWS As Worksheet 
 
设置SourceWb = Workbooks.Open（Workbook文件位置，在这种情况下为在网络共享）
设置myWS = SourceWb.Sheets（Sheet1）< ----这里是我收到运行时错误'91'
 
 myWS。范围（A1）。清除
 myWS.Range（B1）。清除
 
 SourceWb.C失去SaveChanges：= 1 
 ThisWorkbook.Close SaveChanges：= 1 
 
 Application.ScreenUpdating = True 
 Application.DisplayAlerts = True 
 
 End Sub 
  

解决方案

你是一个入侵者的受害者，理论上永远不会常规。 没有禁用事件，在 Workbook_BeforeClose 内，您调用 ThisWorkbook.Close ，这将再次导致 Workbook_BeforeClose 被调用，然后一次又一次。这导致未定义的行为（可能是

StackOverfow ！）

快速修复：禁用事件：

  Private Sub Workbook_BeforeClose（Cancel As Boolean）
 
 Application.EnableEvents = False'< ------ - 避免重复
错误恢复下一步
 TestClear 
 Application.EnableEvents = True 
 
 End Sub 
  / pre> 
I'm attempting to clear the values in two cells in a different workbook when I close the workbook I'm actively using.  When I close my workbook I receive the following error:

  
Run-time error '91': Object variable or With block variable not set
Using debug and stepping through I can find the line where I receive the error but for the life of me I'm not seeing what I'm missing.

I call my macro from:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    TestClear

End Sub

Sub TestClear()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim SourceWb As Workbook, myWS As Worksheet

    Set SourceWb = Workbooks.Open("Workbook file location, in this case it's on a network share")
    Set myWS = SourceWb.Sheets("Sheet1") <---- Here is where I receive the Run-Time error    '91'

    myWS.Range("A1").Clear
    myWS.Range("B1").Clear

    SourceWb.Close SaveChanges:=1
    ThisWorkbook.Close SaveChanges:=1

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

解决方案
You are victim of a re-entrant, theoretically never-ending routine. Without disabling events, inside Workbook_BeforeClose, you call ThisWorkbook.Close  which will again cause Workbook_BeforeClose to be invoked, then again and again. This causes undefined behavior (probably a StackOverfow!)

Quick-fix: disable events:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.EnableEvents = False '<------- To avoid re-entance
    On Error Resume Next
    TestClear
    Application.EnableEvents = True

End Sub


                        
这篇关于当我关闭工作簿时，尝试清除不同工作簿中两个单元格中的值。运行时91错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！