The Subtle Dangers of Dir()

For a long time, whenever I wanted to see if a file existed, I would use the following construct:

If Len(Dir(FullPath)) > 0 Then
    'The file exists

This approach, while commonly used, has a couple of drawbacks:

1. Dir() has terrible performance in certain corner cases, like when checking for the existence of a single file in a network folder with over 300,000 files (don't ask me how I know this).
2. More importantly, Dir() has side effects.  And they're global.

From the Dir() help page:

Dir returns the first file name that matches pathname. To get any additional file names that match pathname, call Dir again with no arguments. When no more file names match, Dir returns a zero-length string (""). After a zero-length string is returned, you must specify pathname in subsequent calls, or an error occurs.

It works...until it doesn't

This leads to some maddeningly insidious errors.  Consider the following simple loop.  This loop will process all the files with a ".txt" extension in the current directory.

FName = Dir("*.txt")
Do While Len(FName) > 0
    ProcessTheFile FName
    FName = Dir()
Loop

This works fine, unless ProcessTheFile calls Dir():

Sub ProcessTheFile(FName As String)
    Dim ArchiveExists As Boolean
    ArchiveExists = (Len(Dir(FName & ".old")) > 0)
End Sub

Uh-oh.  The call to Dir() inside the ProcessTheFile routine resets the Dir() function.  It's no longer looping through all the .txt files in the current directory.  It's now checking for every file named "SomeTextFile.txt.old" in the current directory.

Let's say the current directory looks like this:

- SomeTextFile.txt
- SomeTextFile.txt.old
- ThisTextFile.txt
- ThatTextFile.txt

At first glance, we would expect our original loop to process three files: SomeTextFile.txt, ThisTextFile.txt, and ThatTextFile.txt.  In reality, only SomeTextFile.txt would be processed.  

Now, imagine that the inner Dir() call was not in the ProcessTheFile() routine, where it's relatively easy to see the problem.  Instead, imagine the call to Dir() buried several layers deep, inside a function called from a method called from a function called from ProcessTheFile().  That might take hours to find (don't ask me how I know this).

This logic bug is the worst kind of bug, because it can go completely unnoticed.  Until the day it inevitably blows up.

The fix

If we don't need the wildcard functionality, this problem is very easy to work around.  We simply use the FileExists method of the FileSystemObject.  Of course, now we need at least two lines of code to check if a file exists: one line to instantiate the FileSystemObject and another line to call its FileExists method.

This may not seem like a big deal, but we programmers are a lazy bunch.  Unless we make the right way also be the easy way, we won't bother.  

So after years of lazily using my trusty (but dangerous) Len(Dir("...")) construct, I finally decided to do something about it.

I wrote a simple function that does little more than wrap the FileSystemObject code up so that I can use it as a one-liner.  I also maintained support for wildcard characters by falling back on the Len(Dir("...")) method if the passed file name included those characters.

Referencing the Microsoft Scripting Runtime

Many VBA developers prefer to use late-binding for everything, including accessing the FileSystemObject.  I'm no longer one of them.  While I minimize the number of references in my projects, I always include the Microsoft Scripting Runtime:

Early-binding provides IntelliSense and a small performance boost.  (The IntelliSense is especially helpful for Dictionaries--which are also a part of the Scripting Runtime--since the Key/Value arguments are backward from the Collection object; I can never remember which is which.)

The Code

As always, you won't find any error-handling boilerplate in my functions.  Feel free to add that in if you want, or just get yourself a copy of vbWatchdog so you don't have to do that anymore.

FileExists()

The comments in the code below include instructions for performing a project-wide RegEx Find & Replace if you have the excellent MZ-Tools addin.  Performing a project-wide find and replace for something like this can be a risky proposition, so I only recommend you do it if you have some form of reliable version control in place.  Otherwise, you're walking a high wire with no net.

'Convenience function to avoid creating a File System Object
'   This should be used in place of the Len(Dir()) construct because
'   Dir() has terrible performance compared to FileExists, especially in
'   certain use cases (e.g., checking for the existence of a single file
'   in a very large (300,000+ files) UNC directory, such as G:\Photos\)
'
'Includes support for wild-card characters ("*" and "?")
'--== Project-wide find & replace ==--
'We can use the MZ-Tools Find & Replace RegEx mode to do a program wide change:
'
' Find: Len\(Dir\(([^,]+)\)\) > 0
' Find: Dir\(([^,]+)\) <> ""
' Repl: FileExists($1)
'
' Find: Len\(Dir\(([^,]+)\)\) = 0
' Find: Dir\(([^,]+)\) = ""
' Repl: Not FileExists($1)
'
' Note: Some existing code may have defined FileExists() properties or functions
'       that will overlap and cause problems; to work around this, we can simply
'       add "FileFunctions." to fully qualify the function call:
'   FileFunctions.FileExists(PathToMyFile)
Function FileExists(FullPathOfFile As String) As Boolean
    Dim HasWildcards As Boolean
    HasWildcards = (InStr(FullPathOfFile, "*") > 0) Or (InStr(FullPathOfFile, "?") > 0)
    
    If HasWildcards Then
        FileExists = (Len(Dir(FullPathOfFile)) > 0)
    Else
        Dim oFSO As New Scripting.FileSystemObject
        FileExists = oFSO.FileExists(FullPathOfFile)
    End If
End Function

FolderExists()

The FolderExists function is analogous to the FileExists function.  It's nothing more than a wrapper around the FileSystemObject's FolderExists method.  It serves as another convenience function, helping to enhance code readability.  The VerifyFolder method mentioned in the comments can be found here.

'Returns True if the folder exists (and is accessible)
'   - does not create the folder if it is missing (use VerifyFolder() for that)
'   - trailing backslash is completely optional
'   - returns False if the full path to an existing file is passed to the function (and not just the folder part)
Function FolderExists(PathToFolder As String) As Boolean
    Dim oFSO As New Scripting.FileSystemObject
    FolderExists = oFSO.FolderExists(PathToFolder)
End Function

Image by strichpunkt from Pixabay

UPDATE [2024-04-13]: Added explicit return types (As Boolean) to both functions.