How To Use VBA GetAttr To Gather File Information

Sometimes you may find it useful to know basic file attributes of the files or filepaths with which you’re interacting. If you read and then write back to a file, it’s important to know if the original is read-only, in which case your write will fail, or if it’s a system file, in which case the write will probably also fail but if it succeeds, you could damage the system. Other times, you may just want to check that a string indeed points to a valid file or a directory.

The VBA GetAttr function will, unsurprisingly, return basic attributes of a file. Keep in mind, this is a function so it outputs a value; it is not a property of a file object! You cannot write to GetAttr to change the attributes of a file. Likewise, you can’t use this function to make files read-only or hide files.

GetAttr works in a rather clever way, so we’ll outline bitwise operations to help our understanding. Don’t worry, though, you can skip that section if you prefer.

• The Possible Attributes
• The Output
• Unmixing Attributes
• Decomposing Bitwise AND

The Possible Attributes

There are 7 possible attributes and more than one may be true for a given filepath, though there are some mutually exclusive ones.

Here are the attributes, straight from the Microsoft documentation (literally - this is their table):

The first column is the VBA name, which is a human-understandable name for each of the constants in the second column. In your macros, you can use the vbXX name interchangeably with the number itself.

The Output

So what exactly does the GetAttr function output? The only output is a single integer value equal to the sum of all the true attributes. Whichever attribute is true for your particular input will appear in the function output.

The simplest case is one where the file satisfies only one attribute. Let’s say myFile is hidden and this is the only attribute from the above list. Then the statement GetAttr(myFile) will evaluate to 2.

Slightly more complicated is a hidden system file. Here we have to include 4 and 2 in the sum, so the output is 6: GetAttr(myFile) = vbHidden + vbSystem = 6.

What’s the value for a hidden directory? vbHidden + vbDirectory = 2 + 16 = 18. A hidden read-only system file? vbReadOnly + vbHidden + vbSystem = 7.

The reason the numbers are successive powers of 2 is twofold: every combination of attributes will always give a unique number and the mechanics of binary addition make bitwise operation easier.

This clever technique means multiple attributes can be represented by a single number without losing any information, like multiple dimensions collapsing into one.

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

Unmixing Attributes

You could look at a table of all of the possible outputs and know which attributes are present. However, most of the time we are only interested in one attribute. Possible values for read-only inputs are 1, 3, 5, 35, and others. But it would be cumbersome to test each of these. To make it easier, we can rely on the cleverness of binary bitwise operations.

To see whether a particular attribute is present, you’ll need to use the AND operator and set the resulting value to an integer:

iReadOnly = GetAttr(myFile) And vbReadOnly

If the output is zero, vbReadOnly is not an attribute of this file. If the output is anything other than zero, vbReadOnly is present. Here’s a full macro example to test the read-only attribute using a VBA If-Then statement:

Sub VBA_GetAttr_Demo()
Dim myFile As String
Dim iReadOnly As Integer
myFile = "C:\Users\Public\MySpreadsheet.xlsm"
iReadOnly = GetAttr(myFile) And vbReadOnly
If iReadOnly <> 0 Then
    'File is read-only
Else
    'File is not read-only
End If
End Sub

To drive the message home, let’s run through a second example to test whether a a path is a directory or a file:

Sub VBA_GetAttr_Demo_Dir()
Dim myPath As String
Dim iDir As Integer
myPath = "C:\Users\Public"
iDir = GetAttr(myPath) And vbDirectory
If iDir <> 0 Then
    'A directory was selected
Else
    'Not a directory
End If
End Sub

This will work whether or not you have trailing slash at the end of your file path.

Testing is as easy as that. To use GetAttr you don’t need any more information, but if you’re curious how we can break down the sum, read the next section.

Decomposing Bitwise AND

So how can we test whether a number is really part of the sum? By using bitwise AND.

Every number in computing is represented by a string of bits, which can be on/true or off/false, usually represented as 1 and 0, respectively. There are two operations we can do with these bits, AND and OR, which come from mathematical logic. For this article, the important point is that both sides of an AND operation must be true/on/1 for the output to be 1. If one or both sides is false/off/0, then the output is 0.

When we look at the bitstring representation of 2, we get 10. Leading zeros can be added indefinitely, so 10 = 00000010 = 0010. The important part is the trailing zeros (and 1s).

The bitstring representation of 4 is 100 and 8 is 1000. It is best to read these as “one zero zero” and “one zero zero zero” rather than “one hundred”, “one thousand”, etc. 6 is 2 + 4, or 110 = 010 + 100. Here are the bitstrings of the attribute values above:

00 = 0000000
     6543210 <-- raise 2 to this power if 1 appears in the column
01 = 0000001
02 = 0000010
04 = 0000100
08 = 0001000
16 = 0010000
32 = 0100000
64 = 1000000

Because each value in the table above is a power of 2, all bits in the bitstring representation will be zero except one of them. For this reason, adding any of these numbers together will never “flip a bit” and carry over to the next column, as each number is fully represented in its own column.

To see whether 4 is “part of” 6, we can check whether each 1-bit in 4 has a corresponding 1-bit in 6. Remember this is bitwise operation, so we need to go column-by-column:

06 = 0000110
  AND
04 = 0000100
xx = 0000100 <-- bitwise output where bits in top and bottom row are BOTH 1

In the 3rd column from the right, which is four’s designated column, we have a 1-bit in 6. The bitwise AND operator output produces a non-zero number, and hence 4, vbSystem, is part of 6. If we have 14 (2 + 4 + 8), can you work out how the calculations look to determine whether vbArchive is present?

This trick does not work if the possible inputs for the sum are not powers of 2.

The VBA GetAttr function provides information about the attributes of a file using a summation technique that provides a unique number for each combination of attributes. Again, the VBA GetAttr function is a function, not a file property, so you can’t use it to change file attributes. You can only use it to determine which file attributes are present.

Whether a particular attribute is present can be determined using the bitwise AND operator. Bitwise operations go bit-by-bit rather than taking the bitstring as a whole, and the gaps in the decimal representation of the attributes make bitwise AND able to detect whether an attribute is true or not.

Bitwise operations can be complicated, but the GetAttr function, and VBA in general, don’t have to be. If you want to learn VBA without all the unnecessary coding complications, subscribe using the form below. We’ll walk you through a free step-by-step training program to making mastering VBA a breeze.