vbacreateobject

VBA: Shell.Application Namespace function fails dependent on variable declaration in calling sub


I have encountered a situation where the Shell.Application object's Namespace method appears to fail dependent on the absence of a variable declaration in the calling sub.

A simplified test case is below:

Function TestShellApplicationNamespace(folder)

Dim oShell: Set oShell = CreateObject("Shell.Application")
Dim oDir: Set oDir = oShell.Namespace(folder)

Debug.Print TypeName(folder)

If oDir Is Nothing Then
    Debug.Print "oDir is Nothing"
Else
    Debug.Print "oDir is not Nothing"
End If

End Function

Which is called by:

Sub CallTestShellApplicationNamespace()

Dim folder As String

folder = "C:\"

TestShellApplicationNamespace folder

folder2 = "C:\"

TestShellApplicationNamespace folder2

End Sub

The results I get from running this are:

String
oDir is Nothing
String
oDir is not Nothing

I'm unsure whether this is a bug in the VBA interpreter, or something I'm doing wrong.

EDIT: After submitting this, also found the following which is of relevance (although not exactly the same)

Excel VBA Shell.Namespace returns Nothing


Solution

  • Shell interface oddness, it wants the parameter passed by value so change the function prototype to:

    Function TestShellApplicationNamespace(ByVal folder As Variant)
    

    (Or call with extra parentheses)

    TestShellApplicationNamespace (folder)
    ret = TestShellApplicationNamespace((folder))
    

    You should really configure the IDE to not run code with undeclared variables at all.