删除工作表后不再声明VBA全局变量 [英] VBA Global variables no longer declared after deleting worksheet

问题描述

我有一些公共工作表变量，这些变量在打开工作簿时首先被初始化.我有一个基本上可以执行此操作的按钮:

I have some public worksheet variables that are first initialized when the workbook is open. I have a button that does this essentially:

Dim Response As Variant

Response = MsgBox("Are you sure you want to delete this worksheet?", vbYesNo + vbExclamation, "Confirm Action")
If Response = vbNo Then
    GoTo exit sub
End If

'Save workbook prior to deletion as a precaution
ThisWorkbook.Save

ActiveSheet.Delete

由于某种原因，在此运行之后，不再声明那些工作表变量，而我每次都必须重新初始化它们.我尝试在.Delete之后添加我的InitVariables宏调用，但仍然无法正常工作.

For some reason after this runs, those worksheet variables are no longer declared and I have to reinitialize them every time. I tried adding my InitVariables macro call after the .Delete and it still doesn't work.

为什么会发生这种情况?

Any reason why this might be happening?

推荐答案

原因实际上很简单-工作表是VBA中的一个类，即使其代码模块与项目的其余部分一起被编译，即使它是空的.当您删除工作表并停止执行代码时，下次运行某些代码时，VBE必须重新编译项目，因为您删除了代码模块.这会导致您的自定义类扩展失去其状态.

The reason is actually really simple - a Worksheet is a class in VBA, and its code module gets compiled along with the rest of your project even if it's empty. When you delete a worksheet and let code execution stop, the next time you run some code the VBE has to recompile the project because you removed a code module. That causes your custom class extensions to lose their state.

请注意，除非代码停止运行并重新编译，否则这不会发生.效果很好:

Note that this does not happen unless the code stops running and is recompiled. This works just fine:

Sheet1.foo = 42        'foo is a public variable in Sheet1
Sheet2.Delete
Debug.Print Sheet1.foo 'Prints 42

这篇关于删除工作表后不再声明VBA全局变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！