EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA File & Directories Functions VBA FreeFile
Secondary Sidebar
VBA File & Directories Functions
  • VBA File & Directories
    • VBA Get Cell Value
    • VBA FileDialog
    • VBA Delete File
    • VBA Check File Exists
    • VBA DIR
    • VBA GetOpenFileName
    • VBA Write Text File
    • FileCopy in VBA
    • VBA FreeFile
    • VBA Solver

VBA FreeFile

By Madhuri ThakurMadhuri Thakur

VBA FreeFile

Excel VBA FreeFile

How often, being an Excel user, you open a file and work on the same? Very frequently, right? It is again possible to automate the tasks of opening the file in Excel using a powerful tool, VBA, which allows you to write macros and ultimately automates all the tasks starting from opening a file from a given location to use, save and close it on the same path. While talking about automating the process of file opening and closing through VBA, it becomes very important to Index the file so that the system can identify which file to use at the time of execution. We have a function available within VBA named FreeFile, which serves this purpose.

What is VBA FreeFile Function?

VBA FreeFile is a function that is categorized under the VBA function, which allows the system to reserve a number for the next file to be opened under VBA File Input Output for a smooth coding experience and reducing unnecessary bugs in your code. When you open a file through VBA File IO (Input/Output), it becomes absolutely mandatory to assign a unique number to that file because you may have multiple files on the location you are about to use to read, write and opening purpose through your code. How does the system know which file out of all those you want to open? The VBA FreeFile function identifies the unique number we assign to the file and makes it available for you to open, read or write in VBA.

The syntax for the FreeFile function is as below:

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,580 ratings)

Syntax of FreeFile

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

This function only takes one argument, RangeNumber, which has the standard values as zero (0) and one (1). If you specify zero, the file number will be assigned through the range of numbers 1 to 255 stepwise for each file you open. If you specify one, the file number will be assigned through the range 256 to 511 stepwise for each file you open. If no RangeNumber specified, zero will be considered by default.

How to Use the FreeFile Function in Excel VBA?

Below are the different examples to use the FreeFile function in Excel VBA.

You can download this VBA FreeFile Excel Template here – VBA FreeFile Excel Template

VBA FreeFile – Example #1

Follow the below steps to use FreeFile Function in Excel VBA.

Step 1: Open a new Visual Basic Editor (VBE) by clicking on the Visual Basic button under the Developer tab present in your Excel file or by hitting Alt + F11 button simultaneously.

VBA FreeFile Example 1-1

Step 2: Insert a new module in the VBE. Click on the Insert button. Select Module from the list of options available in Insert.

VBA FreeFile Example 1-2

Step 3: Define a new sub-procedure that can hold your macro.

Code:

Sub Example_1()

End Sub

VBA FreeFile Example 1-3

Step 4: Define two new variables, file_1, and file_2 as an integer. These two variables will hold the numbers we assign to each file we open throughout the macro.

Code:

Sub Example_1()

Dim file_1 As Integer
Dim file_2 As Integer

End Sub

VBA FreeFile Example 1-4

Step 5: Use the assignment operator to assign integer value 1 to the file_1 variable with the help of VBA FreeFile.

Code:

Sub Example_1()

Dim file_1 As Integer
Dim file_2 As Integer
file_1 = FreeFile

End Sub

VBA FreeFile Example 1-5

Step 6: Now, use the Open statement, which can be used to open a file in VBA.

Code:

Sub Example_1()

Dim file_1 As Integer
Dim file_2 As Integer
file_1 = FreeFile
Open "D:\Excel Content Writing\TextFile_1.txt" For Output As file_1

End Sub

Use Open statement Example 1-6

Here, we have specified the path on which the file is located. The For statement gives you options like how you want to open the file (for ex. as an output), and the As statement specifies the file number which we have specified using the FreeFile statement.

Step 7: Follow Step 5 and Step 6 to assign a new number to the second file through the file_2 variable and Open it.

Code:

Sub Example_1()

Dim file_1 As Integer
Dim file_2 As Integer
file_1 = FreeFile
Open "D:\Excel Content Writing\TextFile_1.txt" For Output As file_1
file_2 = FreeFile
Open "D:\Excel Content Writing\TextFile_2.txt" For Output As file_2

End Sub

VBA FreeFile Example 1-7

Step 8: Now, we would like to see the numbers which actually are assigned to both of the text files. You can run each line one by one with the help of the F8 key. However, the output will not be visible to you. Neither in Excel file nor in the Immediate window. Please add the following MsgBox statement, which pops a message box up with the numbers for each file.

Code:

Sub Example_1()

Dim file_1 As Integer
Dim file_2 As Integer
file_1 = FreeFile
Open "D:\Excel Content Writing\TextFile_1.txt" For Output As file_1
file_2 = FreeFile
Open "D:\Excel Content Writing\TextFile_2.txt" For Output As file_2
MsgBox "Value for file_1 is:" & file_1 & Chr(13) & "Value for file_2 is:" & file_2

End Sub

VBA FreeFile Example 1-8

In this line of code, the first line of the message box will contain the text “value for file_1 is: “ and the numeric value assigned using the FreeFile function. Chr(13) allows the next line to appear. The next line of the message box will read “Value for file_2 is: ” and the number assigned to the second file.

Step 9: Run this code by hitting the Run button or by pressing the F5 key.

VBA FreeFile Example 1-9

This is how the FreeFile function allows you to assign the number to the file you are going to open, read, close through VBA.

VBA FreeFile – Example #2

If we close the file we open every time, then the new file we open will always have a serial number as 1 every time it gets opened. Follow the steps below to see it through.

Step 1: Define a new sub-procedure that can hold your macro.

Code:

Sub Example_2()

End Sub

VBA FreeFile Example 2-1

Step 2: Define two variables file_1 and file_2, as an integer inside the newly defined sub-procedure.

Code:

Sub Example_2()

Dim file_1 As Integer
Dim file_2 As Integer

End Sub

Define variables Example 2-2

Step 3: Now, use FreeFile to assign a number to the given file and an Open statement to open that file through the location at which we have it.

Code:

Sub Example_2()

Dim file_1 As Integer
Dim file_2 As Integer
file_1 = FreeFile
Open "D:\Excel Content Writing\TextFile_1.txt" For Output As file_1

End Sub

Open Statement Example 2-3

Step 4: Use a MsgBox statement to add a message box for the number of the first files.

Code:

Sub Example_2()

Dim file_1 As Integer
Dim file_2 As Integer
file_1 = FreeFile
Open "D:\Excel Content Writing\TextFile_1.txt" For Output As file_1
MsgBox "Value for file_1 is: " & file_1

End Sub

Use MsgBox statement Example 2-4

Step 5: Use a Close statement to close the first file you opened through VBA.

Code:

Sub Example_2()

Dim file_1 As Integer
Dim file_2 As Integer
file_1 = FreeFile
Open "D:\Excel Content Writing\TextFile_1.txt" For Output As file_1
MsgBox "Value for file_1 is: " & file_1
Close file_1

End Sub

Use Close statement Example 2-5

Step 6: Now, follow the same procedure as in Step 5, Step 6, Step 7 but for the second file. It includes assigning value to file_2 variable using FreeFile function and opening it with the help of Open statement, using MsgBox to display the file number for the second file and finally closing the second file.

Code:

Sub Example_2()

Dim file_1 As Integer
Dim file_2 As Integer
file_1 = FreeFile
Open "D:\Excel Content Writing\TextFile_1.txt" For Output As file_1
MsgBox "Value for file_1 is: " & file_1
Close file_1
file_2 = FreeFile
Open "D:\Excel Content Writing\TextFile_2.txt" For Output As file_2
MsgBox "Value for file_2 is: " & file_2
Close file_2

End Sub

VBA FreeFile Example 2-6

Step 7: Run this code by hitting the Run button or F5.

VBA FreeFile Example 2-7

The first message box represents the value of file_1, which appears to be 1. The second message box represents the value of file_2, which also appears to be 1. This change is there because we have used the close statement to close the files one by one. Due to which, while initiating a new file, every time the old file and its number gets vanished from the system memory, and next file will be considered as a fresh file with numbers starting from 1.

Things to Remember

  • FreeFile returns a unique integer to every file we open, read, close through VBA File IO.
  • FreeFile has two values for argument RangeNumber (which is optional). Zero usually assigns the number between the range 1 to 255, and one assigns the numbers from 256 to 511.

Recommended Articles

This is a guide to VBA FreeFile. Here we discuss how the FreeFile function allows you to assign the number to the file you are going to open, read, close through Excel VBA, along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. VBA Subscript out of Range
  2. VBA GetObject
  3. VBA Number Format
  4. VBA Environ
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more