The VBA Dir function is crucial to exploring any file system with the use of VBA – whether in Excel or other MS Office applications. In some case true you can resort to a Select File Dialog, however, in other cases you need to be able to easily match files or directories in file systems in VBA. Today, let us learn how to use the Dir function.

VBA Dir Syntax

The Excel Dir function returns the first filename (directory) that matches the path and the provided attributes. To retrieve subsequent filenames (directories) that match the path and attributes call the Dir function again without providing ANY arguments.

The Dir function by default DOES NOT TRAVERSE the entire directory structure provided by the given path. It will match filenames (directories) matching the path and attributes within only the given path. Read on to learn how to use Dir to traverse the whole directory structure.

VBA Dir example

Let’s start with a simple example before we jump into more advanced examples. Let’s see how to explore the C:\Root\ directory:

Dim path As String, currentPath As String  
path = "C:\Root\"

currentPath = Dir(path)
Do Until currentPath = vbNullString
    Debug.Print currentPath
    currentPath = Dir()
Loop

Notice that I highlighted on purpose both calls to the Dir function. The first Dir(path) call will return the first filename within the C:\Root\ directory. Any subsequent Dir() (notice no arguments) calls will return next files under the directory. Also as I have not provided any attributes the Dir function will only match against vbNormal (normal files)!

Using attributes

Assuming the same directory structure as in the previous example let’s change the default vbNormal attribute to vbDirectory. This will allow us to explore not only file matched under the given path but also the directories.

Dim path As String, currentPath As String  
path = "C:\Root\"

currentPath = Dir(path, vbDirectory)
Do Until currentPath = vbNullString
    Debug.Print currentPath
    currentPath = Dir()
Loop

Notice that now we should be seeing, aside from our file, also directories being listed. The output:

Similarly you can use the other VBA Dir attributes to search for hidden, system files, aliases and volumes. Most attributes include normal files so be aware the the files listed WON’T BE LIMITED to just e.g. hidden files.

Using wildcards

The VBA Dir function works great also with wild cards:

• * – multiple character wildcard, including zero-length
• ? – single character wildcard

Some common examples:

Dir("C:\HelloWorld.txt")
'Matches: HelloWorld.txt

Dir("C:\Hello*World.txt")
'Matches: HelloWorld.txt, HelloThereWorld.txt, Hello_World.txt etc.

Dir("C:\?elloWorld.txt")
'Matches: HelloWorld.txt, MelloWorld.txt, YelloWorld.txt etc.

Traversing directories

The VBA Dir function unfortunately by default does not traverse the entire directory, exploring subfolders. In the examples we were limited to just listing the files and folders of the C:\Root directory:

A simple recursive VBA Dir implementation won’t do either as the Dir function is reset whenever it is called globally with any arguments. Fortunately, we can use a native VBA object called the VBA Collection. We will stack all subsequent directories onto our Collection and then explore them recursively. The TraversePath function will do just that:

Sub TraversePath(path As String)
    Dim currentPath As String, directory As Variant
    Dim dirCollection As Collection
    Set dirCollection = New Collection
    
    currentPath = Dir(path, vbDirectory)
    
    'Explore current directory
    Do Until currentPath = vbNullString
        Debug.Print currentPath
        If Left(currentPath, 1) <> "." And _
            (GetAttr(path & currentPath) And vbDirectory) = vbDirectory Then
            dirCollection.Add currentPath
        End If
        currentPath = Dir()
    Loop
    
    'Explore subsequent directories
    For Each directory In dirCollection
        Debug.Print "---SubDirectory: " & directory & "---"
        TraversePath path & directory & "\"
    Next directory
End Sub

Let’s try it out on our C:\Root directory example:

Sub Test()
    TraversePath "C:\Root\"
End Sub

The TraversePath should not traverse all directories within our Root directory and list any normal files and folders: