DIR Function in VBA (Part 1)

In the previous article, Mytour acquainted you with the TRIM function in Excel VBA. In this article, Mytour delves further into the DIR function in VBA.

1. Utilizing DIR Function in VBA

The DIR function in VBA serves to retrieve the name of a file or folder using the path names of these files or folders.

For instance, assuming a directory stores a certain Excel file, we can employ the VBA DIR function to fetch the name of that Excel file (or the name of any file type).

Moreover, in cases where we want to retrieve the names of all Excel files in the directory (or all files that are not Excel files), we can also utilize the DIR function.

When using the DIR function once, it retrieves the name of the first file in the directory. To obtain the file names of other files, we can reuse DIR for this purpose.

DIR returns the name of the first file that matches the path name. To retrieve additional file names matching the path name, we simply call DIR again without any arguments. When no matching file names are found, DIR returns an empty string ('').

2. Syntax of the DIR function in VBA

The syntax of the DIR function in VBA is as follows:

Dir [ (path name [ ,attributes ] ) ]

Where:

- Path Name (pathname): This is an optional argument, and it can be either a file name or a directory name. If the path name is not found, the DIR function in VBA returns a string with a length of 0 ('').

- Attributes: This is an optional argument. We can use this argument to specify certain attributes, and the DIR function will return file names based on those attributes.

For example, if you want to list the hidden files or read-only files (along with files without attributes), you just need to specify it in this argument.

Here is a list of attributes available for use in the DIR function in VBA (we can use one or more of these attributes):

Constant Value Description

vbNormal 0 (Default) specifies files with no attributes.

vbReadOnly 1 Specifies read-only files along with files with no attributes.

vbHidden 2 Specifies hidden files along with files with no attributes.

VbSystem 4 Specifies system files along with files with no attributes. Not available on Mac OS X.

vbVolume 8 Specifies volume names; if other attributes are specified, vbVolume will be ignored. Not available on Mac OS X.

vbDirectory 16 Specifies directories or directories with files that have no attributes.

vbAlias 64 Specifies the file name as an alias. Only available on Mac OS X.

3. Using Wildcards and DIR Function in VBA

If you are using Windows, you can also use wildcards in the DIR function.

Note: These characters cannot be used together with the VBA function on Mac OS X.

Utilizing wildcard characters can be advantageous in various scenarios:

- When extracting the file names of a specific file type (e.g., .XLSX or .PPTX).

- In cases where there is a specific suffix/prefix in the file name, and you want to retrieve the names of these files/directories. For example, if you want to retrieve the names of all files with the prefix 2019, wildcard characters can be used for this purpose.

There are three wildcard characters in Excel, namely:

1. * (asterisk): Represents any number of characters. For example, 2019* gives you the names of all files with the prefix 2019.

2. ? (question mark): Represents a single character. For instance, 2019? gives you the names of all files starting with 2019 and having an additional character in the name, such as 2019A, 2019B, ... .

3. - (dash): This wildcard character is less commonly used, so we will skip going into specific details.

This article introduces the DIR function in VBA (Part 1), showcasing how to use the syntax of the DIR function in VBA. Stay tuned for Part 2 to delve deeper into understanding this special function. If you have any questions or queries, feel free to share your thoughts in the comments section below the article.