CODEWITHSUNDEEP
ms-accessvba
user3791372
user3791372

Reputation: 4665

What does this subtle VBA code change with brackets mean?

The following code opens the default application for a filename:

Public Function OpenDefaultApplication(filename As String)
    Dim sh As Object
    Set sh = CreateObject("Shell.Application")
    sh.Open (filename)
    Set sh = Nothing
End Function

However, removing the brackets from the line opening the file means the file is not opened:

    sh.Open filename    ' does not work!

However, if I remove the code from a function, the code works successfully without brackets.

Dim sh As Object
Set sh = CreateObject("Shell.Application")
sh.Open filename ' works if not inside a function
Set sh = Nothing

What is this subtle different and why do the brackets become necessary inside a function?

Upvotes: 1

Views: 185

Answers (2)

Andre
Andre

Reputation: 27634

Well, that was interesting.

You've hit a very odd behavior of the Shell.Open method.

Shell.Open(ByVal vDir As Variant) As Integer

Note that the parameter is defined as Variant, not String.
That's because you can also do e.g.

sh.Open 36

to open the Windows folder.

Apparently because of this, sh.Open filename doesn't work (though it's the correct syntax), since filename is passed as Reference.

sh.Open (filename) works, but is a rather ugly hack.

Better solutions:

1) Declare filename as Variant, if you insist on using Shell.Open:

Public Sub OpenDefaultApplication(filename As Variant)
    Dim sh As Object
    Set sh = CreateObject("Shell.Application")
    sh.Open filename
End Sub

2) Use the correct method to open files, Shell.ShellExecute

Public Sub OpenDefaultApplication(filename As String)
    Dim sh As Object
    Set sh = CreateObject("Shell.Application")
    sh.ShellExecute filename
End Sub

Upvotes: 2

Andre
Andre

Reputation: 27634

sh.Open filename

is actually the correct syntax.
Or alternatively

Call sh.Open(filename)

The original statement

sh.Open (filename)

evaluates filename and then passes it to sh.Open.
If you tried this syntax with a method that has two parameters:

sh.SomeMethod (filename, parameter2)

you'd get a syntax error.

So the question is:
What do you pass as filename to the first function, and what is filename in the second code example?

EDIT
For more clarification, add this to the beginning of OpenDefaultApplication:

Debug.Print filename
Debug.Print (filename)

and see the result in the Immediate Window (Ctrl+G).

Upvotes: 2

Related Questions