17/2/2020 VBA FreeFile for Foolproof File IO - wellsr.
com
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
Make powerful macros with our free VBA Developer Kit
There’s a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA
Developer Kit below. It’s full of tips and pre-built macros to make writing VBA easier.
Sure, I'll take a free VBA Developer Kit
Enter your email address
Get my free kit
https://wellsr.com/vba/2016/excel/vba-freefile-for-foolproof-file-IO/ 1/4
17/2/2020 VBA FreeFile for Foolproof File IO - wellsr.com
Tutorial - VBA FreeFile
FreeFile Explanation
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 file1 is given a value of 1 since no other files were opened in the macro
before issuing the FreeFile command. By storing the results of the FreeFile command to a variable, you’re
able to call that variable in your Open statements and in your Print or Write statements.
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 file2 . A value of 2 is returned since the value of 1 is already taken
by file1 and this file is already open.
Example 2 - VBA FreeFile
If you closed file1 before issuing the FreeFile command the second time, it would yield a value of 1 again.
Let’s take a look!
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.
https://wellsr.com/vba/2016/excel/vba-freefile-for-foolproof-file-IO/ 2/4
17/2/2020 VBA FreeFile for Foolproof File IO - wellsr.com
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.
FreeFile Arguments
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).
https://wellsr.com/vba/2016/excel/vba-freefile-for-foolproof-file-IO/ 3/4
17/2/2020 VBA FreeFile for Foolproof File IO - wellsr.com
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.
That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form
below.
Oh, and if you have a question, post it in our VBA Q&A community.
https://wellsr.com/vba/2016/excel/vba-freefile-for-foolproof-file-IO/ 4/4
