Quick Jump
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.


Example 1 - VBA FreeFile

Sub FreeFile_Demo()
Dim file1, 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

Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

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


Comments

I hope you’ll subscribe to my email list. Once you’ve subscribed, you’ll get access to a free copy of my Excel Add-In with tons of useful features and you’ll get monthly tutorials where I show you how to do more neat stuff with VBA!

Share this article with your corner of the world on Google+, Twitter and Facebook!


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.