VBA FreeFile for Foolproof File IO

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

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.

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.

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).

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.