Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Speed up your file

FastExcel
The best tool to optimise your Excel model!
Home > English site > Articles > Workbook Open Bug

Macro in Excel Stops After A Workbook.Open Command

Introduction

Sometimes Excel VBA behaves unexpectedly. Here is an example:

You are running code that is meant to open one or more files using the Workbooks.Open command. As soon as you hold down the shift key when this command is being processed, macro execution stops and your program is terminated entirely. This will even happen if you are e.g. editing an email message in Outlook whilst Excel VBA is processing the code in the background.

Cause

Excel is designed not to run Auto_Open and Workbook_Open code when a workbook is opened from the User interface whilst holding down the shift key. Unfortunately, this (desired) behaviour also applies when opening workbooks through VBA code.

Microsoft has confirmed this is a bug.

Resolution

Before opening a workbook through code, detect if the shift key is pressed and wait until it is released. The code sample below shows you how to do this:

 Option Explicit

'Declare API
Declare Function GetKeyState Lib "User32" _
(ByVal vKey As Integer) As Integer
Const SHIFT_KEY = 16

Function ShiftPressed() As Boolean
'Returns True if shift key is pressed
    ShiftPressed = GetKeyState(SHIFT_KEY) < 0
End Function

Sub Demo()
    Do While ShiftPressed()
        DoEvents
    Loop
    Workbooks.Open Filename:="C:\My Documents\ShiftKeyDemo.xls"
End Sub

This problem has also been listed in the Microsoft knowledge base:

Macro in Excel Stops After A Workbook.Open Command, February 26, 2005


Comments

Showing last 8 comments of 43 in total (Show All Comments):

 


Comment by: Brian K (10/26/2013 8:25:50 AM)

Hi,

I have a code will read a xxx.txt from my web server and oepn the workbook by "Workbooks.Open(FileName:=path & File)", it is fine in XP + Office 2003, but now in WIN7 + Office 2010, it is a long time waiting to download that text file (xxx.txt), would you please help, how can I fix it ?

 


Comment by: Jan Karel Pieterse (10/27/2013 5:43:06 PM)

Hi Brian,

Excel 2010 has stronger security regarding getting data from Web locations. I'm afraid I can't really help to improve the performance here.

Perhaps using a bit of VBA code that first downloads the file and then opens it from the hard disk?

 


Comment by: Akshat Bhatt (11/26/2013 7:43:41 AM)

Hi,

There is a macro been developed in an excel, problem is when I run that macro all other excel files gets open from where data is getting populated. I know this is happening due to the use of Workbook.Open command. But thing is I have to stop other files fom getting opened while I execute the code. How do I disable Workbook.Open command.


Thanks & Regards
Akshat

 


Comment by: Jan Karel Pieterse (11/26/2013 7:55:09 AM)

Hi Akshat,

Without seeing (part of) your code it is impossible to answer your question.

 


Comment by: Martin (6/27/2014 11:47:45 AM)

Hello Jan-Karel,

I hope you can help me because I'm at my wits' end.

This is all in an XP/XL2003 environment

A macro of mine dies as soon as it has opened an external .xls file using workbooks.open. What I mean is that after the debugger has executed the workbooks.open statement, the opened file stays open in Excel, but the debugger is no longer active and the call stack is empty. The problem is definitely not related to the shift button being pressed. It makes no difference if I open the file as read-only, add various 'on error' statements, or remove the workbook_open event from the target file.

What I do notice is that the opened excel file contains a bunch of "#NAME" errors, which only disappear when calculate full is executed using Ctrl-Alt-F9. If I open the external file directly (not using workbooks.open), the #NAME errors appear at first, and then go away automatically when Excel recalculates. The same thing happens if I save the external file after recalculation and re-open it.

I suspect that workbooks.open is bugging out on opening the file and finding the #name errors (or whatever caused them).

I'm not sure what is causing the #Name problem, although the external file has a lot of UDFs and Macros, including UDFs that evaluate names that refer to cells with UDFs in their formulas. Every so often I need to calculate.full to avoid #Errors when editing the external file.

Because the excel macro dies, I can't automate any processing of the target file in Excel 2003. If I run the macro in Excel 2010, it does not die after opening the same external file using workbooks.open, and everything works normally. Unfortunately the users of both sheets have XL 2003 and I can't change this.

Do you have any ideas for a workaround or a solution? Are you aware of any differences between workbooks.open in XL2003 and XL2010?

Many thanks in advance for having a look at this for me, I really appreciate it.

yours sincerely
Martin

 


Comment by: Jan Karel Pieterse (6/27/2014 2:45:26 PM)

Hi Martin,

I strongly suspect one (or more) of the UDF's run into a runtime error (which do not show up when the UDF is called from a worksheet cell or a conditional formatting formula!!).

UDFs require very strict design to make sure no runtime errors occur EVER when they are called. A RT error in a UDF can also cause the calculation chain to break and hence stop Excel from completing the calculation.

A simple way to test is to include an on error resume next at the top of each UDF.

Also, make sure none of the UDFs tries do do things UDFs are not allowed to do.

E.g. you are not allowed to change any formatting of cells and such, nor are you allowed to use the HasFormula property of a passed range object.

 


Comment by: Martin (6/27/2014 3:43:59 PM)

Hello Jan-Karel,

for checking the UDFs, the issue is that the UDFs in turn call a great variety of VBA functions. Wouldn't I have to put an error check in each one of these too? Also, how would an 'on error resume next' help detect a RT-error?

Why would the workbooks.open problem not occur under XL2010 if it's caused by a UDF RT-error? Is it simply because having a UDF RT-error causes arbitrary behaviour?

I will try to chase down if that is, in fact, the cause of the problem and let you know. If anything else crosses your mind in the meantime, do please let me know

 


Comment by: Jan Karel Pieterse (6/27/2014 4:05:15 PM)

Hi Martin,

The on error resume next was meant as a quick check to see if it alleviates the problem. Of course after that you would have to start finding out the root cause.

Indeed: RT errors triggered from a UDF can cause unexpected behavior. Excel 2010's calc engine is different from 2003's so I'm not surprised it behaves different in cases like these.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].