VBA’s FileLen Unreliable!

Examine Files

I’ve been working on a personal project in which I need to index files. As part of the indexing process I retrieve the file size.

The Problem With FileLen

For years, in various databases, I have always used VBA’s built-in FileLen Function to quickly and easily return the file size (in bytes) and never experienced any issue.  Well, that was until today!

Sadly, I found out today that FileLen() is simply unreliable and that it returns negative values for certain files, thus crashing my application.

The Root Cause of This Bug

The issue here is that FileLen “Returns a Long specifying the length of a file in bytes.” and a Long data type can range in value between -2,147,483,648 to 2,147,483,647. So the issue being that the selected return variable type, Long, simply cannot handle files where the size exceeds 2,147,483,647 bytes which is essentially 2GB.

Now why, in all these years, has Microsoft never addressed this issue? Your guess is as good as mine. Since file sizes are now commonly in excess of 2 GB, FileLen() clearly is not a good choice for any developer anymore. This is a prime example of, as file sizes were allowed to increase in the OS, adjustments should have been made in the VBA library! Or at the very least, the documentation should have been updated to reflect this limitation.

Warning
Further, investigations have shown that depending on the size of the files, FileLen() does not always return a negative (-) value for files exceeding 2GB in size.

A Few Examples Of FileLen() Erroneous Values

File Size (KB) FileLen() Returned File Size (B / KB)
3 466 168 -745 590 784 / -728 116
4 823 758 644 560 896 / 629 454
5 168 892 997 978 112 / 974 588

So you can’t count on receiving a negative value for large files (as I originally thought), you may receive a positive value, inducing you to think you’ve gotten back a proper file size, but it will be erroneous (it’s actually is a numerical loop, but we have no means to know what iteration we are on to determine the proper value!).

So FileLen() is simply unreliable to the point of actually being dangerous to use.

For me the biggest issue is that it doesn’t err when it exceeds its capacity, but rather returns an erroneous value inducing you into thinking everything worked properly which simply isn’t the case! If it returned 0 or reported an error, then everything would be fine, but returning erroneous values is simply dangerous.

The Solution To Reliably Determining a File’s Size in VBA

Like with issues arising with the Dir Function, switching over to using the File System Object (FSO) is the solution here as well.

You can easily create a reusable function, but below is the general concept.

FileSize = CreateObject("Scripting.FileSystemObject").GetFile(FullFilePathAndFileNameWithExtension).Size

So a word to the wise, always use the FSO.GetFile().Size approach and avoid any potential headaches in your applications!

Where’s Microsoft in All of This?

As always, I have already notified Microsoft of this issue and requested they make mention of this limitation in their documentation (at a minimum). At the same time, I’m sure I’m not the first person to figure this all out and communicate it to MS. Time will tell if I am heard.

So Much For Feedback!
Well, more than a year has come and gone and nothing has been done, not even an acknowledgement of the issue or feedback. No notice in the help files.

As you can see, even MVPs truly have no influence, even in straightforward cases like this!

A Few Resources on the Subject

2 responses on “VBA’s FileLen Unreliable!”

  1. Andrew

    This is regrettably true. I have found another bug, I believe:
    FileLen(folderPath & fileName) and FileDateTime(folderPath & fileName) both crash if fileName contains a _ character. This is quite a downside if you separate words in a file name with _ !

    e.g. ? FileLen((“C:\Documents_en-US.txt”) in the immediate window. While your FSO no doubt works I’m disappointed, as I’ve thought dir, FileLen and FileDateTime etc were faster.