VBA - excel closes the previous workbook on opening the new one

Question

I have a strange problem, I suscpect it's connected to the version of the Excel, but I'm not sure at all. I can't figure it out alone so I need your help. I have a macro, which operates on a fresh workbook - it's not saved anywhere, as the worker will save it manually afterwards. The macro is a .xlam format add-in, adding a couple of buttons to the ribbon and these buttons start the code. Inside the code I have simple lines for opening a new workbook, chosen earlier by an user:

Application.DisplayAlerts = False
    Set wbMPA = Workbooks.Open(MPA_file)
Application.DisplayAlerts = True

Earlier, the code sets active workbook as an object/workbook the macro will mainly work on (tried both versions):

Set dwb = Application.ActiveWorkbook

and later in the code

dwb.activate

OR:

dwb = ActiveWorkbook.Name

and then

workbooks(dwb).Activate

The lines are in separate subs, but the variable is globally declared.

The code works fine until the opening of wbMPA (watching it in the locals all the time). When I try to open the new file with the code above, the earlier workbook (dwb) just closes itself from unknown reasons. The error I get from the 1st method is this: error screenshot The second one gives a simple "Subscipt out of range". The errors, however, are not a problem. The problem is the cause of them, which is closing of the workbook from unknown reasons.

It happens only when I open the completely new workbook (using the excel icon on the Start bar) - when I do it from File -> New -> Blank Workbook using already opened workbook, the error does not occur.

Another strange thing - me and my colleague from work use 2013 version of Excel. I never have this error, she has it every time.

This is a general scheme of the code, other things are meaningless in this case because there are no other manipulations of workbooks/worksheets.

Dim dwb As Object
Dim wbMPA As Object

Sub_1()

Set dwb = ActiveWorkbook
Set wbMPA = Workbooks.Open(MPA_file)

Call Sub_2

End Sub 


Sub_2()

dwb.Activate

End Sub

I get an error on the activation of dwb in Sub_2, because it closes itself for God knows what the reason on the opening of wbMPA in the Sub_1.

Answer 1

If you have only opened a blank workbook (clicking Excel from Toolbar, for example) and then you open any named workbook before making any changes to the blank workbook, the blank workbook will disappear. I believe that is normal/expected behavior.

I can't speculate why this happens on one computer but not another, but this is always how I have observed new/blank documents (Excel, PowerPoint, Word) to behave, and assume this to be the normal behavior. You may have some different option/configuration on your Excel environment which is changing this default behavior, or maybe you are slightly altering the blank file before running the macro, and your co-worker isn't, etc.

A word of caution to avoid relying on ActiveWorkbook -- and especially in this case if the expectation is to always Set dwb to a new/blank workbook, the best way to do that is to explicitly create a new/blank workbook, rather than relying on the user to manually open a new/blank target workbook.

Set dwb = Workbooks.Add

If, on the other hand dwb must be assigned to some other known/existing workbook, then you should be either providing the file path to an Open statement, or the workbook name to the Workbooks collection.

On a related note, it's almost never necessary to Activate a workbook, see here:

How to avoid using Select in Excel VBA macros

And further note: your variables aren't globally scoped, they're scoped only to the module using Dim statement. A public declaration uses the Public keyword, not the Dim keyword. Both module-scoped and global-scoped should be used with caution (Public moreso than module-scoped) and in most cases it's preferable to pass objects/variables by reference to dependent subs and functions:

How to make Excel VBA variables available to multiple macros?