How to use LOF function in VBA?

In VBA (Visual Basic for Applications), the LOF function is used to return the size of an open file in bytes. The LOF function stands for “Length of File.” To use the LOF function, you must first open a file using the Open statement, and you need to associate it with a file number. Here’s the syntax for the LOF function:

fileLength = LOF(fileNumber)
Where fileNumber is a number associated with the open file. Here’s a simple example illustrating the usage of the LOF function:

Sub GetFileSize()
    Dim fileNum As Integer
    Dim filePath As String
    Dim fileSize As Long
    ' The path to the file we want to check
    filePath = "C:\example\example.txt"
    ' Open the file for binary access read only
    fileNum = FreeFile() ' Get a free file number
    Open filePath For Binary As #fileNum
    ' Get the file size using the LOF function and store it in fileSize variable
    fileSize = LOF(fileNum)
    ' Close the file
    Close #fileNum
    ' Display the file size
    MsgBox "The size of the file is: " & fileSize & " bytes"
End Sub
In this example, the file located at “C:\example\example.txt” is opened for binary access, then the LOF function is used to get the size of the file in bytes. Finally, the file is closed and the file size is displayed in a message box. Keep in mind the following points when working with files in VBA: It is essential to close any files you open once you are done with them to free up system resources. Using FreeFile() is a best practice because it ensures you get a file number that is not already in use. The file path should be changed according to where your file is located. Make sure the path and the file exist to avoid runtime errors. If you need to use the file size for further processing, you would use the fileSize variable as required in your VBA code. Make sure you have appropriate error handling in place (e.g., checking whether a file exists before trying to open it) to make your code more robust.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

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