How to use EOF function in VBA?

In VBA (Visual Basic for Applications), the EOF function is used to determine if the end of a file opened for Input or Binary mode has been reached. EOF stands for End Of File. The EOF function returns True when there are no more data to be read from a file, and False otherwise. It is typically used in a loop to continue reading data until the end of a file is encountered.

Basic Syntax

VBA


EOF(fileNumber)

Here, fileNumber is an Integer representing the file number that was specified when the file was opened using the Open statement. Below is an example of using the EOF function while reading a text file:

VBA


Sub ReadFromFile()
    Dim fileNumber As Integer
    Dim filePath As String
    Dim fileContent As String
    
    ' Set the path to the file
    filePath = "C:pathtoyourtextfile.txt"
    
    ' Open file for input
    fileNumber = FreeFile() ' Get free file number
    Open filePath For Input As #fileNumber
    
    ' Read from file until EOF is reached
    Do Until EOF(fileNumber)
        Line Input #fileNumber, fileContent ' Read a line of text
        ' Print the line to the Immediate Window (Ctrl + G in the VBA editor)
        Debug.Print fileContent
    Loop
    
    ' Close the file
    Close #fileNumber
End Sub

In this example:

FreeFile() is used to get an available file number to use for opening the file.
Open filePath For Input As #fileNumber opens the specified file in Input mode.
A loop is set up with Do Until EOF(fileNumber). Inside the loop, Line Input reads each line of the text file one at a time until EOF(fileNumber) returns True.
After reading the file, Close #fileNumber is used to close the file.

It’s important to note that if you try to use EOF on an unopened file or a closed file, it will produce a runtime error. Make sure that the file is properly opened before checking for its End Of File status.

facebooklinkedin