• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
EDUCBA

EDUCBA

MENUMENU
  • Resources
        • Excel Charts

          • Histogram Chart Excel
          • Basic Excel Formulas
          • Text to Columns in Excel
        • Excel Charts
        • Excel Tips

          • Excel Gantt Chart
          • IFERROR with VLOOKUP
          • Data Table in Excel
        • Excel Tips
        • Excel Tools in Excel

          • Stacked Column Chart
          • Cheat Sheet of Excel Formulas
          • Excel Data Validation
        • Histogram chart in excel
        • Others

          • Resources (A-Z)
          • Excel Functions
          • Financial Functions in Excel
          • Logical Functions in Excel
          • Lookup Reference Functions in Excel
          • Maths Function in Excel
          • TEXT and String Functions in Excel
          • View All
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course 1
        • All in One Bundle

          All-in-One-Excel-VBA-Bundle
        • Excel course

          Excel-Training
        • Others

          • Excel advanced course
          • VBA Course
          • Excel Data Analysis Course
          • Excel for Marketing Course
          • Excel for Finance Course
          • View All
  • 120+ Courses All in One Bundle
  • Login

VBA FreeFile

Home » Excel » Blog » VBA » VBA FreeFile

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

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

The syntax for FreeFile function is as below:

Syntax of FreeFile

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

Popular Course in this category
VBA Training (3 Courses, 12+ Projects) 3 Online Courses | 13 Hands-on Projects | 45+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.7 (2,695 ratings)
Course Price

View Course

Related Courses
Excel Advanced Training (14 Courses, 21+ Projects)Excel Data Analysis Training (12 Courses, 6+ Projects)

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 assignment operator to assign integer value 1 to 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 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 message box will contain the text “value for file_1 is: “ and the numeric value assigned using 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 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 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 it’s 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 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 downloadable excel template. You can also go through our other suggested articles –

  1. VBA Check File Exists | Excel Template
  2. VBA Subscript out of Range (Examples)
  3. GetObject Function in Excel VBA
  4. How to Use Number Format in VBA?
  5. VBA Environ

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar
Excel Functions Tutorials
  • VBA
    • VBA Max
    • VBA Environ
    • VBA Not
    • VBA LOOKUP
    • Programming in Excel
    • VBA Name Worksheet
    • VBA ScreenUpdating
    • VBA Do Loop
    • VBA Declare Array
    • VBA Macros
    • VBA Delete Sheet
    • VBA Editor
    • VBA Error Handling
    • VBA Goal Seek
    • VBA DateDiff
    • VBA On Error Resume Next
    • VBA Examples
    • VBA Counter
    • VBA Val
    • VBA Me
    • VBA Double
    • VBA Variable Types
    • VBA FreeFile
    • VBA Switch Case
    • VBA Unprotect Sheet
    • VBA Format Number
    • VBA ReDim
    • VBA IF Statements
    • VBA DoEvents
    • VBA Public Variable
    • VBA Columns
    • VBA Cdate
    • VBA Selection Range
    • VBA Hide Columns
    • VBA COUNTA
    • VBA Insert Row
    • VBA Protect Sheet
    • VBA AutoFill
    • VBA IF Not
    • VBA GetObject
    • VBA Debug Print
    • VBA Collection
    • VBA Text
    • VBA Randomize
    • VBA Variant
    • VBA Selection
    • VBA Right
    • VBA Date Format
    • VBA Rename Sheet
    • VBA Paste Values
    • VBA InputBox
    • VBA Conditional Formatting
    • VBA Pause
    • VBA Pivot Table
    • VBA Message Box
    • VBA OFFSET
    • VBA ByRef
    • FileCopy in VBA
    • VBA StrConv
    • VBA SUB
    • VBA Boolean
    • VBA Global Variables
    • VBA Worksheets
    • VBA IIF
    • VBA Close UserForm
    • VBA Variable Declaration
    • VBA Join
    • VBA Dictionary
    • VBA Operators
    • VBA Charts
    • VBA For Each Loop
    • VBA FileDialog
    • VBA Format
    • VBA MID
    • VBA GetOpenFileName
    • VBA DatePart
    • VBA Code
    • VBA Option Explicit
    • VBA FileSystemObject (FSO)
    • VBA ASC
    • VBA UserForm
    • VBA Cells
    • VBA InStrRev
    • VBA Class Module
    • VBA Constants
    • VBA Length of String
    • VBA DateSerial
    • VBA StrComp
    • VBA Array Length
    • VBA Check File Exists
    • VBA Ubound
    • VBA CDBL
    • VBA Activate Sheet
    • VBA DateValue
    • VBA Borders
    • VBA Comment
    • VBA Intersect
    • VBA Workbook
    • VBA Concatenate
    • VBA Name
    • VBA Find and Replace
    • VBA Tutorial
    • VBA CSTR
    • VBA Save As
    • VBA Hyperlink
    • VBA Print
    • VBA Switch
    • VBA END
    • VBA Like
    • VBA Set
    • VBA excel programming
    • VBA Call Sub
    • VBA Object
    • VBA RoundUp
    • VBA ArrayList
    • VBA Named Range
    • VBA PowerPoint
    • VBA Block Comment
    • VBA OverFlow Error
    • VBA Insert Column
    • VBA Lcase
    • VBA List Box
    • VBA Delete File
    • VBA Clear Contents
    • VBA TextBox
    • VBA Font Color
    • VBA Range Cells
    • VBA INT
    • VBA UCASE
    • VBA Value
    • VBA Remove Duplicates
    • VBA Break for Loop
    • VBA Sleep
    • VBA Do Until Loop
    • VBA Union
    • VBA Long
    • VBA Copy Paste
    • VBA Data Types
    • VBA Delete Column
    • VBA Enum
    • VBA IsEmpty
    • VBA 1004 Error
    • VBA RegEx
    • VBA IsNumeric
    • VBA Paste
    • VBA Transpose
    • VBA Left
    • VBA Delete Row
    • VBA Integer
    • VBA Active Cell
    • VBA InStr
    • VBA Round
    • VBA Subscript out of Range
    • VBA Dim
    • VBA Replace
    • VBA Sort
    • VBA String
    • VBA Split
    • VBA Wait
    • VBA MOD
    • VBA Time
    • VBA TIMER
    • VBA While Loop
    • VBA Date
    • Excel VBA MsgBox
    • VBA IFError
    • VBA Color Index
    • VBA Match
    • VBA Case
    • VBA Arrays
    • VBA GoTo
    • VBA On Error
    • VBA Range
    • VBA Do While Loop
    • VBA Number Format
    • VBA Loops
    • VBA TRIM
    • VBA Find
    • VBA Select Case
    • VBA Else If
  • Excel Functions (10+)
  • Excel Tools (97+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (12+)
  • Lookup Reference Functions in Excel (30+)
  • Maths Function in Excel (39+)
  • TEXT and String Functions in Excel (25+)
  • Date and Time Function in Excel (20+)
  • Statistical Functions in Excel (55+)
  • Information Functions in Excel (4+)
  • Excel Charts (44+)
  • Excel Tips (195+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (14+)
  • Excel Formula and Functions (20+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • VBA Training
  • Excel Advanced Training
  • Excel Data Analysis Training
Footer
About Us
  • Who is EDUCBA?
  • Sign Up
  •  
Free Courses
  • Free Online Excel Course
  • Free Vba Course
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
Resources
  • Resources (A To Z)
  • Excel Charts
  • Excel Tips
  • Excel Tools in Excel
  • Excel Functions
  • Financial Functions in Excel
  • Logical Functions in Excel
  • Lookup Reference Functions in Excel
  • Maths Function in Excel
  • TEXT and String Functions in Excel
  • Date and Time Function in Excel
  • Statistical Functions in Excel
  • Information Functions in Excel
Apps
  • iPhone & iPad
  • Android
Support
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions

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

EDUCBA

Download VBA FreeFile Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Let’s Get Started
Please provide your Email ID
Email ID is incorrect

Limited Period Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More

Limited Period Offer - Limited Period Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More