+ Reply to Thread
Results 1 to 10 of 10

Obscure VBA error? Application.Run conflicts with Application.GetOpenFileName

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Obscure VBA error? Application.Run conflicts with Application.GetOpenFileName

    I was working on a project today and was using Application.Run* to call a sub. The sub has a call to a File Picker (it uses Application.GetOpenFileName) and I was surprised that the select file window did not appear (instead it showed my error handling messages and exited the sub) so I started investigating.


    If I use immediate window to
    Please Login or Register  to view this content.
    then everything works normally (the File Picker appears)


    However if I use immediate window to
    Please Login or Register  to view this content.
    then the code screws up! It seems that everything works normally right up to the point of Application.GetOpenFileName. At that point, the file select window does not appear. The result returns False (as if I had cancelled selecting a file).


    I tried googling this issue and couldn't find anything conclusive.
    1. Is anyone else aware of this? (I'd be interested to hear your experiences)
    2. Is anyone aware of a solution/workaround for this issue? (Short of using Call!)


    * I normally use Call instead of Application.Run to call a sub. However I am currently working on some code where I am unable to use Call so I have to use Application.Run
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Obscure VBA error? Application.Run conflicts with Application.GetOpenFileName

    What happens if you don't use anything at all?
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Obscure VBA error? Application.Run conflicts with Application.GetOpenFileName

    Quote Originally Posted by millz View Post
    What happens if you don't use anything at all?
    Please Login or Register  to view this content.
    I just tested your suggestion. It runs normally. (Doesn't calling a macro without using "Call" do the same thing?)

    I still need the macro to work when I use Application.Run

  4. #4
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Obscure VBA error? Application.Run conflicts with Application.GetOpenFileName

    I don't know the exact reason why would they conflict, I don't have time to test it either. I also wouldn't use anything like 'Call' or 'Application.Run' if I don't need to, but if you really must, maybe rework the dialog? Try something other than Application.GetOpenFilename

    Maybe:
    Please Login or Register  to view this content.

  5. #5
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Obscure VBA error? Application.Run conflicts with Application.GetOpenFileName

    Change

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    assuming arg1 is a variable holding the value to be passed as a parameter, if arg1 is the actual value to be passed then place quotes around it
    If you like my contribution click the star icon!

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Obscure VBA error? Application.Run conflicts with Application.GetOpenFileName

    Thanks for the suggestion millz.

    I tried using Application.FileDialog but the window still didn't show. I suspect that using Application.Run is conflicting with all "Application." file pickers!

  7. #7
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Obscure VBA error? Application.Run conflicts with Application.GetOpenFileName

    Thanks for the suggestion OllieB.

    I tried
    Please Login or Register  to view this content.
    in the immediate window and received "Compile error: Expected: end of statement".
    I tried it again but this time with quotes around the argument I was passing and received the same error message.

  8. #8
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Obscure VBA error? Application.Run conflicts with Application.GetOpenFileName

    What puzzles me is that this Application.Run error doesn't appear to be known about?! (my assumption from a quick google search)

    I am interested in knowing WHY this conflict occurs.

    To assist testing this, I have created a sample module to demonstrate this conflict. See below:

    Please Login or Register  to view this content.
    Last edited by mc84excel; 10-14-2013 at 09:06 PM.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Obscure VBA error? Application.Run conflicts with Application.GetOpenFileName

    It should be Application.Run "macro1", arg1.

    When I change your example code to this it works.
    Please Login or Register  to view this content.
    I've no idea why the code acts like it does when you use Application.Run "ExampleOfRunConflict(11)"-, but that is kind of wrong after all.

    PS Why do you need to use Application.Run?
    If posting code please use code tags, see here.

  10. #10
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Obscure VBA error? Application.Run conflicts with Application.GetOpenFileName

    Norie - you have done it again.

    I had no idea that
    Please Login or Register  to view this content.
    was incorrect and
    Please Login or Register  to view this content.
    was correct. (I have never read this in any VBA book!)


    Re your P.S.
    • Answer part #1 - Ironic coincidence here I was using this in the "Add command buttons to Form" code which you have been helping me on! I wanted to delete the form before calling the Sub. So I called another sub that would kill this form and then Application.Run the subname which had previously been passed as an argument from the form! (For the record, this method actually did work. Except when I tried to run the sub with an argument and OpenFileName)
    • Answer part #2 - I don't need to use Application.Run anymore. I didn't expect this thread to be solved so I altered the code so that the form would unload before calling the sub. (Next best thing). I left this thread open as I was interested to know the logic behind the issue.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Catching error on Application.GetOpenFilename embed in Open For Input
    By Clevis in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-27-2013, 02:04 PM
  2. Application.GetOpenFilename Type mismatch error
    By brl8 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-03-2011, 04:56 PM
  3. [SOLVED] Application.GetOpenFileName
    By Chris in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-18-2006, 07:40 PM
  4. [SOLVED] [SOLVED] Application.GetOpenFilename vs Application.Dialogs(xlDialogsOpen)
    By Paul Martin in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-05-2005, 12:05 PM
  5. [SOLVED] Application.GetOpenFilename
    By Nigel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-23-2005, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1