Macro in Excel Stopt na het Workbook.Open Commando

Inleiding

Soms gedraagt Excel VBA zich vreemd. Hier een voorbeeld:

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 heeft bevestigd dat dit een bug is.

Oplossing

Voordat je code een werkmap opent detecteren of de shift-toets ingedrukt is en wachten totdat deze is losgelaten. De code hieronder laat zien hoe dat moet:

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer
#Else
    Declare Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer
#End If

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

Dit probleem wordt ook vermeld in de Microsoft knowledge base:

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