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.
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
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):
|vbSystem||4||System file. Not available on the Macintosh.|
|vbDirectory||16||Directory or folder.|
|vbArchive||32||File has changed since last backup. Not available on the Macintosh.|
|vbAlias||64||Specified file name is an alias. Available only on the Macintosh.|
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
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
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.
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
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,
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
When we look at the bitstring representation of 2, we get
10. Leading zeros can be added indefinitely, so
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.
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.