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.

The syntax for the `EOF` function is simple:

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:

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