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

Originally Posted by millz

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

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

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!

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.

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.

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?

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.