Introduction | Example | Tutorial | Applications | Comments
Introduction - VBA FreeFile
The VBA FreeFile function reserves the next available file number for VBA file IO (input/output). When you open a file for writing or reading in VBA, you must refer to that file with a unique integer file number. The FreeFile function determines this unique file number for you so you can use it to open, read, and write files using VBA.
VBA FreeFile for Foolproof File IO. How is that for alliteration?
The examples in this tutorial will show you how to use VBA FreeFile for your next Excel project. FreeFile is nice, but it’s meaningless if you don’t truly know how to manipulate files with VBA. To become a true file I/O expert, you should look at our comprehensive VBA File I/O Cheat Sheet filled with 50+ tips for working with files and over 30 file input/output macro examples.
Example 1 - VBA FreeFile
Sub FreeFile_Demo() Dim file1 as Integer, file2 As Integer file1 = FreeFile 'Returns value of 1 Open "C:\Users\Ryan\Documents\wellsr\MyTextFile.txt" For Output As #file1 Print #file1, "This is file1" file2 = FreeFile 'Returns value of 2 Open "C:\Users\Ryan\Documents\wellsr\YourTextFile.txt" For Output As #file2 Print #file2, "This is file2" Close #file1 Close #file2 End Sub
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheet with just one click.
Tutorial - VBA FreeFile
It’s important to store the results of the VBA FreeFile function in a variable so you can use it later.
In the above example, the variable
By the time you issue the FreeFile command the second time, the command returns a value of 2 and this integer is stored in the variable
Example 2 - VBA FreeFile
If you closed
Sub FreeFile_Demo2() Dim file1 as Integer, file2 As Integer file1 = FreeFile 'Returns value of 1 Open "C:\Users\Ryan\Documents\wellsr\MyTextFile.txt" For Output As #file1 Print #file1, "This is file1" Close #file1 file2 = FreeFile 'Returns value of 1 Open "C:\Users\Ryan\Documents\wellsr\YourTextFile.txt" For Output As #file2 Print #file2, "This is file2" Close #file2 End Sub
In this example, the FreeFile command returns a value of 1 twice, because we already closed file #1. This means #1 is available to use again!
Example 3 - VBA FreeFile
Let’s look at one more example to drive the point home about how the FreeFile function behaves. In this example, we’ll manually open files with file numbers #1 and #3, then we’ll use the FreeFile function to identify the next available file numbers for 2 additional files.
Sub FreeFile_Demo3() Dim file1 as Integer, file2 As Integer Open "C:\Users\Ryan\Documents\wellsr\AnotherFile.txt" For Output As #1 Print #1, "blah blah" Open "C:\Users\Ryan\Documents\wellsr\AndAnotherFile.txt" For Output As #3 Print #3, "blah blah" file1 = FreeFile 'Returns value of 2 Open "C:\Users\Ryan\Documents\wellsr\MyTextFile.txt" For Output As #file1 Print #file1, "This is file1" file2 = FreeFile 'Returns value of 4 Open "C:\Users\Ryan\Documents\wellsr\YourTextFile.txt" For Output As #file2 Print #file2, "This is file2" Close #1 Close #3 Close #file1 Close #file2 End Sub
Just as we expected, the VBA FreeFile function marches through, realizes files #1 and #3 are open, so the first time FreeFile is invoked it assigns a value of #2. The second time it’s called, FreeFile skips over #3 since #3 is already opened and it assigns the next free file number, which is #4.
The VBA FreeFile function in Excel (and other Office Applications) doesn’t require any arguments, although it does accept an optional argument. By default, the FreeFile function returns the next integer between 1 and 255 that isn’t already opened by your macro. However, if you pass the FreeFile function a value of 1, it will search for the next available file between numbers 256 and 511:
file1 = FreeFile(1)
Proper VBA file IO etiquette is to use a file number between 1 and 255 for files you do not want accessible to other applications and to use file numbers between 256 and 511 for files that may be used by other applications.
Don’t let this confuse you. Nine times out 10, just enter the FreeFile command without any arguments, like this:
file1 = FreeFile
This is the same as passing the FreeFile function an argument of 0, Freefile(0).
Application Ideas - VBA FreeFile
Hopefully these examples have illustrated how the FreeFile function can be used to save you a lot of heartache. It’s incredibly convenient when you don’t know how many files will be opened and when your macro mixes reading and writing an unknown number of files.
A good example of where I use FreeFile in my applications is when I have users selecting files via a dialog box and I want to write an error log or status log. Instead of picking an arbitrarily large integer for my file number, I use the FreeFile function to simply pick the next free file number.
I hope you’ll subscribe to my free wellsrPRO VBA Training Program. Once you’ve subscribed, you’ll get access to a free copy of my Excel Add-In with tons of useful features, including a way to import macros from all my tutorials without ever leaving Excel! You’ll also get email tutorials where I show you how to do more neat stuff with VBA!