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

Call Macro1(arg1)

then everything works normally (the File Picker appears)


However if I use immediate window to

Application.Run "Macro1(arg1)"

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?

Macro1 arg1

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

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!

Change

Application.Run "Macro1(arg1)"

to

Application.Run "Macro1" arg1

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 OllieB.

I tried

Application.Run "Macro1" arg1

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:

Option Explicit
Private Msg As String, Title As String, Ans As Integer, Config As Integer


Public Sub Test_Call_Ver()
    Config = vbInformation
    Msg = "The test sub will be run by using CALL."
    Msg = Msg & vbNewLine & "During the sub, an Application.GetOpenFileName will be attempted."
    Msg = Msg & vbNewLine & vbNewLine & "PREDICTION: The sub will run without any conflicts"
    Ans = MsgBox(Msg, Config, Title)
    
    Call ExampleOfRunConflict(11)
End Sub


Public Sub Test_Run_Ver()
    Config = vbInformation
    Msg = "The test sub will be run by using APPLICATION.RUN."
    Msg = Msg & vbNewLine & "During the sub, an Application.GetOpenFileName will be attempted."
    Msg = Msg & vbNewLine & vbNewLine & "PREDICTION: The sub will not display the file picker box"
    Msg = Msg & "and it will appear to run the sub _TWICE_! (Note the error message box repeated)"
    Msg = Msg & vbNewLine & vbNewLine & "Why does APPPLICATION.RUN conflict when CALL does not?!"
    Ans = MsgBox(Msg, Config, Title)

    Application.Run "ExampleOfRunConflict(11)"
End Sub


Private Sub ExampleOfRunConflict(ByRef bytArg1 As Byte)
    Dim strSelectedFilePath As String

Debug.Print vbNewLine & Now() & ": About to call GetSingleFile_FullPath"
    strSelectedFilePath = GetSingleFile_FullPath("Excel Workbook" & ", *.XLS*", "Select an Excel workbook", False)
Debug.Print vbNewLine & Now() & " wb = " & strSelectedFilePath
Debug.Print vbNewLine & Now() & " WB Len = " & Len(strSelectedFilePath)

    If Not Len(strSelectedFilePath) > 1 Then
        MsgBox "No file selected"
    Else
        MsgBox "Success! The rest of the code will run now"
    End If
End Sub

Private Function GetSingleFile_FullPath(Optional ByRef FileFilter As String, _
                                        Optional ByRef Title As String, _
                                        Optional ByRef ReturnFileNameOnly As Boolean) As String
    Dim FilePath As Variant

    If Application.ScreenUpdating = False Then Application.ScreenUpdating = True

    If Len(FileFilter) > 0 Then 'file filter argument passed
        Debug.Print vbNewLine & Now() & " About to GetOpenFileName"
        Debug.Print "FileFilter = " & FileFilter
        Debug.Print "Title = " & Title
        Debug.Print "ReturnFileNameOnly = " & ReturnFileNameOnly
        FilePath = Application.GetOpenFilename(FileFilter:=FileFilter, Title:=Title, MultiSelect:=False)
    Else 'no filter/allow all files
        FilePath = Application.GetOpenFilename(Title:=Title, MultiSelect:=False)
    End If

    Debug.Print "FilePath " & FilePath

    If FilePath = False Then Exit Function 'user cancelled

    GetSingleFile_FullPath = FilePath
    FilePath = vbNull

    If ReturnFileNameOnly = True Then
        GetSingleFile_FullPath = Mid(String:=ReturnFileNameOnly, Start:=InStrRev(GetSingleFile_FullPath, Application.PathSeparator) + 1)
    End If
End Function

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

When I change your example code to this it works.

 Application.Run "ExampleOfRunConflict", 11

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

Application.Run "SubName(arg)"

was incorrect and

Application.Run "SubName", arg

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.