totn Excel Functions

MS Excel: How to use the DIR Function (VBA)

This Excel tutorial explains how to use the Excel DIR function with syntax and examples.

Description

The Microsoft Excel DIR function returns the first filename that matches the pathname and attributes specified. To retrieve additional filenames that match pathname and attributes, call DIR again with no arguments.

The DIR function is a built-in function in Excel that is categorized as a File/Directory Function. It can be used as a VBA function (VBA) in Excel. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

Syntax

The syntax for the DIR function in Microsoft Excel is:

Dir [( path [, attributes ] ) ]

Parameters or Arguments

path
Optional. It the path to a file, folder, or directory. If the path is not found, the DIR function will return a zero-length string.
attributes

Optional. It is the sum of the file attributes. File attributes can be one or a combination of the following values:

VB Constant Value Explanation
vbNormal 0 Normal (Default)
vbReadOnly 1 Read-only
vbHidden 2 Hidden
vbSystem 4 System file
vbVolume 8 Volume label
vbDirectory 16 Directory or folder
vbAlias 64 File name is an alias

Note

You can use wildcard characters to specify multiple files. The patterns that you can choose from are:

Wildcard Explanation
* Allows you to match any string of any length (including zero length)
? Allows you to match on a single character
Dir ("")

Returns

The DIR function returns a string value.

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

  • VBA function (VBA)

Example (as VBA Function)

The DIR function can only be used in VBA code in Microsoft Excel.

Let's look at some Excel DIR function examples and explore how to use the DIR function in Excel VBA code:

Dir("C:\instructions.doc")
Result: "instructions.doc"

Dir("C:\in*.doc")
Result: "instructions.doc"

Dir("C:\instruction?.doc")
Result: "instructions.doc"

For example:

Dim LResult As String

LResult = Dir("C:\instructions.doc")

In this example, the variable called LResult would now contain the filename of the instructions.doc file.

Frequently Asked Questions


Question: How can I use the DIR function to test whether a file exists?

Answer: This can be done with a formula that utilizes a combination of the DIR function, IF function, and LEN function.

For example:

If Len(Dir("c:\Instructions.doc")) = 0 Then
   Msgbox "This file does NOT exist."
Else
   Msgbox "This file does exist."
End If

Question: I'm not sure if a particular directory exists already. If it doesn't exist, I'd like to create it using VBA code. How can I do this?

Answer: You can test to see if a directory exists using the VBA code below:

If Len(Dir("c:\TOTN\Excel\Examples", vbDirectory)) = 0 Then
   MkDir "c:\TOTN\Excel\Examples"
End If

In this example, the code would first check to see if the c:\TOTN\Excel\Examples directory exists. If it doesn't exist, the MKDIR statement would create a new directory called Examples under the c:\TOTN\Excel directory.