Results 1 to 8 of 8
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Annoying WinAPI problem: ESC = Debug ((Excel VBA 2002))

    <P ID="edit" class=small>(Edited by jscher2000 on 26-Jul-03 16:08. Spotted a bug! (More important than my question was, actually, but the question stands nonetheless. :-( )</P>I'm porting something from Word VBA to Excel VBA and the FileOpen dialog isn't as flexible as I need, so I'm using the Windows API functions. When I click OK or Cancel, all is well, but if I hit ESC in the dialog, I get the message Code execution has been interrupted with Continue, End and Debug buttons. Continue works fine, but how can I make it stop doing this?

    <pre>Option Explicit

    Public Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
    "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

    Public Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
    End Type

    Public Function PickFile(Optional strPath As String = "c:", _
    Optional strFilter As String = "All File Types (*.*)") As String
    Dim OpenFile As OPENFILENAME
    With OpenFile
    .lStructSize = Len(OpenFile)
    '.hwndOwner = Application.Hwnd ' Requires Excel 2002
    .hwndOwner = 0 ' Makes the dialog "parent-less"
    '.hInstance = Application.hInstance ' Requires Excel 2002
    .hInstance = 0 ' This parameter really is not needed here...
    If Right(strFilter, 1) <> Chr(0) Then strFilter = strFilter & Chr(0)
    .lpstrFilter = strFilter
    .nFilterIndex = 1
    .lpstrFile = String(257, 0)
    .nMaxFile = Len(OpenFile.lpstrFile) - 1
    .lpstrFileTitle = OpenFile.lpstrFile
    .nMaxFileTitle = OpenFile.nMaxFile
    .lpstrInitialDir = strPath
    .lpstrTitle = "Choose File to Process"
    .flags = 0
    End With
    ' This doesn't solve the ESC problem
    'On Error Resume Next
    If GetOpenFileName(OpenFile) Then
    'On Error GoTo 0
    PickFile = Trim(OpenFile.lpstrFile)
    Else
    'On Error GoTo 0
    PickFile = vbNullString
    End If
    End Function

    Sub testPickFile()
    MsgBox PickFile
    End Sub</pre>

    Can anyone think of why this is happening or help with a way to trap it? Note: The hardcore code is from <!mskb=161286>Microsoft Knowledge Base Article 161286<!/mskb>, and I've tweaked it a bit for Excel and for my purposes, as well as wrapping it a bit differently. If you already have a better approach, as Ross Perot famously said, "I'm all ears."

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Annoying WinAPI problem: ESC = Debug ((Excel VBA 2002))

    Just a note that Andrew Cronnolly posted a similar bunch of code last Summer (04-Aug-02 17:07) in <post#=165284>post 165284</post#>, but it doesn't seem to have had any special provisions for the ESC issue. (I found it in my e-mail archives, in case you were wondering.) If any of you adopted that code (Troy Wells maybe?), can you test it and see if ESC causes DEBUG?

  4. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Annoying WinAPI problem: ESC = Debug ((Excel VBA 2002))

    I don't understand why you get an error, but try assigning the return value of GetOpenFileName to a variable:

    Dim lngRet As Long
    lngRet = GetSaveFileName(OpenFile)
    If lngRet = 0 Then
    PickFile = vbNullString
    Else
    PickFile = Trim(OpenFile.lpstrFile)
    End If

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,869
    Thanks
    0
    Thanked 78 Times in 74 Posts

    Re: Annoying WinAPI problem: ESC = Debug ((Excel VBA 2002))

    Jefferson,
    Have you tried adding
    <pre>Application.EnableCancelKey = xlDisabled</pre>

    to the start of your function and then
    <pre>Application.EnableCancelKey = xlInterrupt</pre>

    to the end? Should do what you need I think.
    Regards,
    Rory
    Microsoft MVP - Excel.

  6. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annoying WinAPI problem: ESC = Debug ((Excel VBA 2002))

    Recommend add error handling to sub that calls PickFile function, as illustrated in revised functions below. Note: need to add these declarations to module:

    Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

    Const E_CANCEL = 32755

    Revised subs:

    Public Function PickFile(Optional strPath As String = "c:", _
    Optional strFilter As String = "All File Types (*.*)") As String

    Dim OpenFile As OPENFILENAME
    Dim lngErr As Long

    With OpenFile
    .lStructSize = Len(OpenFile)
    '.hwndOwner = Application.Hwnd ' Requires Excel 2002
    .hwndOwner = 0 ' Makes the dialog "parent-less"
    '.hInstance = Application.hInstance ' Requires Excel 2002
    .hInstance = 0 ' This parameter really is not needed here...
    If Right(strFilter, 1) <> Chr(0) Then strFilter = strFilter & Chr(0)
    .lpstrFilter = strFilter
    .nFilterIndex = 1
    .lpstrFile = String(257, 0)
    .nMaxFile = Len(OpenFile.lpstrFile) - 1
    .lpstrFileTitle = OpenFile.lpstrFile
    .nMaxFileTitle = OpenFile.nMaxFile
    .lpstrInitialDir = strPath
    .lpstrTitle = "Choose File to Process"
    .flags = 0
    End With

    If GetOpenFileName(OpenFile) Then
    PickFile = Trim(OpenFile.lpstrFile)
    Else
    lngErr = CommDlgExtendedError()
    Select Case lngErr
    Case 0
    ' Cancel button or ESC key pressed:
    Err.Raise E_CANCEL, , "Cancel selected."
    Case Else
    Err.Raise lngErr, , "Unexpected error."
    End Select
    End If

    End Function

    Sub TestOpenFileDialog()
    On Error GoTo Err_Handler
    Dim strMsg As String

    MsgBox PickFile, vbInformation, "FILE SELECTED"

    Exit_sub:
    Exit Sub

    Err_Handler:
    Select Case Err.Number
    Case E_CANCEL ' = 32755
    ' Test msg:
    MsgBox "Cancel key or ESC key pressed.", vbCritical, "CANCEL"
    Resume Exit_sub
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "OPEN FILE DIALOG ERROR"
    Resume Exit_sub
    End Select

    End Sub

    Note: Tested in Access module but should work same way in Excel. In actual use you can comment out test msg in error handler. The key factor is need to call CommDlgExtendedError API function to raise error. To quote briefly from MSDN:

    The CommDlgExtendedError function returns a common dialog box error code. This code indicates the most recent error to occur during the execution of one of the common dialog box functions. If the most recent call to a common dialog box function succeeded, the return value is undefined. If the common dialog box function returned FALSE because the user closed or canceled the dialog box, the return value is zero. Otherwise, the return value is a nonzero error code.

    Not exactly intuitive, but what do you expect from a Windows API?

    HTH

  7. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Annoying WinAPI problem: ESC = Debug ((Excel VBA 2002))

    Mark, thanks for this. It should come in handy as I pursue the obsolete common dialog model. <img src=/S/clever.gif border=0 alt=clever width=15 height=15> But... I still get the same dialog (Debug places me on the Else line), so the code seems to be running correctly but for this annoying behavior. I wonder whether there might be some odd setting in my VBA environment that could be causing this? I'm using Office XP Developer Edition from an MSDN DVD, so it's nonstandard to say the least.

    Rory, your code does avoid the error, and yet (!) the ESC key still works to cancel the common dialog. This seems like a bizarre but happy contradiction. I wonder if there is some special reason this was needed in the Excel object model? It's in Word's object model, too, and I tested it there. It wasn't needed, but it doesn't seem to do any harm, so this appears to be a great workaround, at least for these two apps. <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>

    Hans, thanks, yes, I had tried that too, sorry I forgot to mention it in my post. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

    So the final version is attached. Not sure it's truly general purpose, but... I can hope. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  8. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annoying WinAPI problem: ESC = Debug ((Excel VBA 2002))

    The EnableCancelkey method only controls what happens to code execution when one presses the ESC key, not what happens whilst a dialog or form is showing. Obviously Excel VBA is under the impression you send it an ESC key when you hit it to escape from the getopenfile dialog, which IMO is a bug.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Annoying WinAPI problem: ESC = Debug ((Excel VBA 2002))

    Jefferson,
    My $.49 worth of opinion:

    My guess: he problem is the Office Dev environment. There is a setting somewhere that causes the dev addin to jump to debug mode when you press ESC. I no longer have the dev edition installed, so I can't search for it. But's that's my guess. You code works fine in "native" 2002 VBA.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •