Excel VBA FreeFile

FreeFile is a function in VBA that is available only as a VBA functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more, not as a worksheet function. VBA FreeFile function returns the unique integer number to the file, which is opened and preserves the next available file number.

We usually open files from our computer to either write something or read-only. While referring to those files, we must refer with a unique integer number. The VBA FreeFile function allows us to determine that unique integer number to assign to opening the file to read, write, and open files using VBA.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA FreeFile (wallstreetmojo.com)

Now, take a look at the syntax of the OPEN statement.

OPEN [File Path Address] For [Mode to Open] As [File Number]

File Path Address: We need to mention the file address we are trying to open on our computer.

Mode to Open: While opening the file, we need to know what model we will apply. We can use three ways here, “Input Mode,” “Output Mode,” and “Append Mode.”

Input mode to read only the file.

Output mode to wipe out the existing data and insert new data.

Append mode to add new data while retaining the existing data.

File Number: With this argument, we can refer to the file we are opening—this is where the “FreeFile” function plays a vital role and returns the unique integer number.

How to Use the FreeFile Function in Excel VBA?

Now, look at the code below.

Code:

Sub FreeFile_Example1()

Dim Path As String Dim FileNumber As Integer

Path = “D:Articles2019File 1.txt” FileNumber = FreeFile

Open Path For Output As FileNumber

Path = “D:Articles2019File 2.txt” FileNumber = FreeFile

Open Path For Output As FileNumber

End Sub

Now, let me decode the above code for you to understand.

First, we have declared two variables.

Dim Path As String, Dim FileNumber As Integer

Then, we assigned the file path with its name.

Path = “D:Articles2019File 1.txt”

Then for the one more variable, I have assigned the FREEFILE function.

FileNumber = FreeFile

Then, we used the Open statement to open the text file in the file mentioned above.

Open Path For Output As FileNumber

Now, we will run line-by-line code by pressing the F8 key and see the value of the variable “FileNumber.”

It shows the file number as 1. So, the free file function automatically reserves this number for the opening file. So, while running this, we must open no other files.

We will keep executing the next line of VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more and see the file number if we jump to the next line.

Now it says 2. So the FreeFile function reserves the unique integer number 2 to the second opening file.

FreeFile Function Always Returns 1 if we Close the Excel File.

We need to look at how the VBA FreeFile function always returns one if we close the opened file before opening the second Excel file.

For example, look at the below code.

Sub FreeFile_Example2()

Dim Path As String Dim FileNumber As Integer

Path = “D:Articles2019File 1.txt” FileNumber = FreeFile

Open Path For Output As FileNumber Close FileNumber

Path = “D:Articles2019File 2.txt” FileNumber = FreeFile

Open Path For Output As FileNumber Close FileNumber

End Sub

We will again execute the code line by line by pressing the F8 key.

It says 1 as usual.

Now, we will progress to the next level.

Even in the second attempt, it says 1.

It happens because we have used the Close file statement. As a result, FreeFile recognizes the newly opened file as fresh and returns the integer number as 1.

This article has been a guide to VBA FreeFile. Here, we discuss using the FreeFile function in Excel VBA to return the unique integer number to the file opened, with practical examples and a downloadable Excel template. Below you can find some useful Excel VBA articles: –

  • VBA CounterVBA Text FunctionVBA Val FunctionVBA GetObject FunctionVBA MsgBox (Yes/No)