VBA Dir Function: Usage, Examples, and Pitfalls

The VBA Dir function returns the first file or folder name that matches a specified path pattern, and subsequent calls with no arguments return the next match. You use it to loop through files in a folder, check if a file exists, or test for the existence of a directory. This article explains the syntax, shows practical examples, and covers the common mistakes that trip up beginners.

What Is the Dir Function?

The Dir function is a built-in VBA function that returns a string representing the name of a file, directory, or folder that matches a given pattern. It is often described as the VBA equivalent of the wildcard search you do in Windows Explorer. The function has two modes: the first call with a pathname returns the first match; each subsequent call with no arguments returns the next match. When no more matches exist, it returns an empty string.

Its most common uses are:

• Checking whether a specific file exists before opening or deleting it.
• Looping through all files in a folder to process them one by one.
• Checking whether a folder exists before creating or writing to it.
• Listing subfolders or files matching a pattern like *.xlsx.

Syntax and Parameters

The full syntax is:

Dir[(pathname[, attributes])]

Both arguments are optional, but you must supply pathname on the first call. The attributes argument is a numeric constant that filters the search. Common values are:

• vbNormal (0) – files with no special attributes.
• vbReadOnly (1) – read-only files.
• vbHidden (2) – hidden files.
• vbDirectory (16) – folders and directories.
• vbVolume (8) – volume label (drive letter).

You can combine attributes by adding them together. For example, vbHidden + vbDirectory returns both hidden files and folders. Note that if you use vbDirectory, the function returns folder names as well as files, but it also includes the special entries . and .. in some cases.

How It Works: The Stateful Behavior

The most confusing aspect of Dir is its statefulness. When you call Dir with a pathname, VBA stores the search state internally. Each subsequent call with no arguments continues the same search, returning the next match. This means you cannot call Dir with a new pathname while a search is in progress without losing your place.

Here is a simple loop that prints all Excel files in a folder:

Dim fileName As String
fileName = Dir("C:\Reports\*.xlsx")
Do While fileName <> ""
    Debug.Print fileName
    fileName = Dir()  ' no arguments continues the search
Loop

Notice that the first call includes the path, and the loop uses an empty Dir() to get the next file. When Dir() returns an empty string, the loop ends. If you forget the second call, you will only get the first file.

Common Mistakes and Pitfalls

Several issues trip up even experienced VBA developers.

• Forgetting the second call: If you only call Dir once, you get only the first match. Always loop with an empty Dir() call.
• Using a new path inside a loop: If you call Dir with a new pathname while a search is active, the previous search is abandoned. This can cause infinite loops or missed files.
• Empty string confusion: An empty string means “no more matches,” but it also means “no match found” on the first call. You cannot distinguish between a folder with no files and an invalid path.
• Attribute combinations: Using vbDirectory alone returns folders but also files. If you want only folders, you must filter the results yourself by checking the file attributes with GetAttr.
• Path separators: On Windows, use a backslash (\) in the path. A forward slash (/) works in some contexts but is not guaranteed.
• Network paths: UNC paths like \\Server\Share\ work, but you must include the trailing backslash for folder checks.

Alternatives to Dir

While Dir is the classic method, VBA has other options depending on your needs.

• FileSystemObject (FSO): The Scripting.FileSystemObject provides a more object-oriented approach. You can get a Folder object and iterate its Files collection, which gives you file names, sizes, and dates directly. It is often easier to read and less error-prone.
• FileDialog: If you want the user to pick a file or folder manually, the FileDialog object is better than parsing Dir results.
• Application.FileSearch (legacy): This was available in older Excel versions but has been removed. Do not use it.

Here is the same file-listing loop using FSO:

Dim fso As Object, folder As Object, file As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("C:\Reports")
For Each file In folder.Files
    Debug.Print file.Name
Next file

FSO requires a reference to “Microsoft Scripting Runtime” (Tools → References) or late binding with CreateObject. It gives you more control and avoids the statefulness issue entirely.

Performance and Limitations

Dir is generally fast for a few hundred files, but it can be slow if you call it repeatedly in a loop that also does file I/O. The function is not recursive – it only searches the specified folder, not subfolders. To search subfolders, you need to write a recursive procedure or use FSO’s GetFolder method combined with recursion.

Another limitation is that Dir returns only the file name, not the full path. If you need the full path, you must concatenate the folder path with the returned name. For example:

Dim fullPath As String
fullPath = "C:\Reports\" & Dir("C:\Reports\*.xlsx")

Be careful with trailing backslashes – if the folder path already ends with a backslash, do not add another.

Frequently Asked Questions

Why does Dir return an empty string even though the file exists?
This usually happens because you used the wrong attribute or path. Check that the path is correct and that you included the file extension. Also, if the file is hidden, you need to add vbHidden to the attributes.

Can I use Dir to get a list of subfolders?
Yes, but you must use vbDirectory and then filter out the . and .. entries. The function returns folders and files together, so you need to check each result with GetAttr to see if it is a folder.

What is the difference between Dir and FileSystemObject?
Dir is simpler and does not require a reference, but it is stateful and returns only names. FSO gives you a collection of File and Folder objects with properties like size, date, and type, and it is easier to use for recursive searches.

Does Dir work with wildcards?
Yes, you can use * to match any number of characters and ? to match a single character. For example, *.txt matches all text files, and report?.xlsx matches report1.xlsx but not report10.xlsx.

Why does my loop never end when using Dir?
This usually happens because you call Dir with a new pathname inside the loop, which resets the search. Always use an empty Dir() call to get the next item, and ensure the loop exits when the return value is an empty string.

Can I use Dir to check if a drive exists?
Yes, you can check for a drive letter by using Dir("D:\") with the vbDirectory attribute. If the drive does not exist, it returns an empty string.

Related

• Add a Game Overlay in Streamlabs Desktop
• HP Inkjet Printer: Do You Need Both Cartridges to Print?
• Mouse Double Click? Fix It in 5 Minutes