What does this subtle VBA code change with brackets mean?

Question

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?

Why are you using a function for something with no return value? — SierraOscar, Oct 08 '15 at 08:33
On an unrelated note, [Shell.Open](https://msdn.microsoft.com/en-us/library/windows/desktop/bb774086%28v=vs.85%29.aspx) is designed to open folders, [Shell.Execute](https://msdn.microsoft.com/en-us/library/windows/desktop/gg537745%28v=vs.85%29.aspx) to open files. — Andre, Oct 08 '15 at 08:52
@MacroMan aren't sub / function interchangeable? If only visual basic was a real language... — user3791372, Oct 08 '15 at 10:22
Please post your full code, including the definition of `filename`. How exactly are you calling `OpenDefaultApplication`? How do you run your second example? As A.S.H wrote, you can't run code outside of a function/sub. — Andre, Oct 08 '15 at 10:25
@user3791372 A `Function` is used to return a value, a `Sub` is a sub-routine that doesn't return a value but may use functions within it. This is universal in programming - and FYI visual basic _is_ a real language, so is [tag:vb.net], [tag:vb6] and [tag:vba] (and your question is actually regarding VBA, not VB). There are many office programmers that could take that comment as a slur against their profession. — SierraOscar, Oct 08 '15 at 10:27
`Call sh.Open(filename)` fails, `Call sh.Open((filename))` succeeds, the difference is that in the latter example ByVal semantics are used in that the method receives a copy of filename. This will likely by a COM oddity. What do you mean when you say *"remove the code from a function"* — Alex K., Oct 08 '15 at 10:32
@AlexK.Ahh, so it's just complaining that it wants a copy of the string, not the string itself? By "remove the code from a function", I meant run the code outside of a separate isolated function, e.g. an event handler — user3791372, Oct 08 '15 at 10:39
I meant `Shell.ShellExecute`, sorry. `sh.ShellExecute filename` works fine and is the correct method to use. — Andre, Oct 08 '15 at 10:41
Possible duplicate of [VBA What's the underlying difference between call Sub or Function with or without parentheses](http://stackoverflow.com/questions/23796803/vba-whats-the-underlying-difference-between-call-sub-or-function-with-or-withou) — SierraOscar, Oct 08 '15 at 10:52

Andre · Answer 1 · 2015-10-08T07:07:59.983

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

score 2 · Answer 2 · answered Oct 08 '15 at 11:04

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

What does this subtle VBA code change with brackets mean?

2 Answers2