The FileLen function in VBA (Visual Basic for Applications) is used to get the size of a file in bytes. Before you use it, make sure that the file exists because if the file is not found, the function will trigger a runtime error.

Here is how you can use the FileLen function in VBA

• Open Excel and press ALT + F11 to open the VBA editor.
• Insert a new module by right-clicking on any existing module or on the VBAProject, then choose Insert > Module.
• In the module, you can create a new subprocedure and use the FileLen function within it.

Example

Sub GetFileSize()
    Dim filePath As String
    Dim fileSize As Long

    ' Set the path to the file.
    filePath = "C:\example\myfile.txt"
    
    ' Check if the file exists to prevent a runtime error.
    If Dir(filePath) <> "" Then
        ' Retrieve the file size in bytes.
        fileSize = FileLen(filePath)
        MsgBox "The size of the file is: " & fileSize & " bytes.", vbInformation
    Else
        MsgBox "File not found: " & filePath, vbExclamation
    End If
End Sub

When you run the GetFileSize subroutine, it will display a message box with the size of the specified file in bytes.

Notes

• You must have the necessary file permissions to access the file.
• In case the file is especially large, you might want to use a Double or Currency type instead of Long to avoid overflow errors, though starting with VBA 7 (in Office 2010 and later), Long is sufficient for files up to 2 GB.
• The Dir function is used before FileLen to check if the file exists to avoid a runtime error.

Follow us on Social Media

facebooklinkedin

Unlock Your Potential

Excel

Access

Power BI

Help us grow the project