CODEWITHSUNDEEP
excelexcel-2010vba
Roland
Roland

Reputation: 7853

How to reset referenced Workbook variables

I have a workbook A(A.xlsm) which references the workbook B(B.xlsm) via the Tools -> References menu in the VBA editor. A uses several macros in B. If I close A then B remains open and all variables maintain their current state. Therefore if I open A again, some macros in B will give errors because they have the state from previous incantations which is not compatible with A anymore.

Is there a way to reset B so that all variables are now in the original state? Alternatively is there a way to close B once A is closed?

Upvotes: 0

Views: 795

Answers (1)

FunThomas
FunThomas

Reputation: 29296

To reset the variables, I would suggest you write a CleanUp-Sub in the referenced workbook B and call it from A.

To close workbook B when A is closed needs a kind of hack. You cannot simply issue something like close Workbooks("B.xlsx"), it will throw an error (1004 - workbook is currenlty referenced by another workbook...).

Look at a trick that David Demner suggest here: https://stackoverflow.com/a/18132384/7599798. The idea is that in the referenced workbook, a closing routine is triggered but the close is not immediately executed. You call it when your workbook is closed, but the Close (of B will only be executed after the the close of the calling workbook (A) is done.

Upvotes: 1

Related Questions