VBA Global variables no longer declared after deleting worksheet

Question

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

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?

Answer 1

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