VBA subtleties

So I'm fiddling with VBA under an old version of Excel and I want to use the file system object.

First you have to "create" an fso with:


set fso = createobject("Scripting.FileSystemObject")

Once you do this you can start using ths filesystemobject.

We can get a folder reference like this:

set myfol = fso.GetFolder("TESTFOLDER")

and I've created a folder called TESTFOLDER that we can access in the same directory where I've put my excel file.

And let's try passing a folder variable to a subroutine.

sub test_passing_folder(afolder)
debug.print afolder
end sub

and we can call it with


ThisWorkbook.test_passing_folder(myfol)

and we get a result like:

G:\TESTFOLDER

printed to the immediate window.

but what is the actual type? Is it a folder or a string? Let's print the typename too.


sub test_passing_folder(afolder)
debug.print afolder, typename(afolder)
end sub

and lets call it with parentheses around myfol.

set myfol = fso.GetFolder("TESTFOLDER")
thisworkbook.test_passing_folder(myfol)
and we get this:

G:\TESTFOLDER String

but why is it a string?

because I put the subroutine call in parentheses which converted it into a string.

Now let's try calling it without parentheses:


set myfol = fso.GetFolder("TESTFOLDER")
test_passing_folder myfol

and we get

G:\TESTFOLDER Folder

I'm so used to calling functions in other languages where you wrap the parameters in parentheses that I naturally put them in, but in this case, it converts the folder into a string.

Here's a quick test that you can type into the VBA immediate window:

set fso=createoject("scripting.filesystemobject")
debug.print typename(fso)
FileSystemObject

ThisWorkbook.test_passing_folder fso.GetFolder("TESTFOLDER")
G:\TESTFOLDER Folder

? typename(fso.getfolder("TESTFOLDER"))
Folder
? typename((fso.getfolder("TESTFOLDER")))
String



Why does it convert it to a string? I think because the parentheses trigger a default conversion to Folder.Path. If you go to the Object Browser in VBA and find the Folder type and choose Path it will say in the description that Path is the default member of the Scripting.Folder object.


The other thing that I've got to remember is to use "SET" when dealing with objects.

fso=createobject("scripting.filesystem")

doesn't work.

You have to use the set keyword.

set fso=createobject("scripting.filesystem")