How to use FileLen function in VBA?

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

Please note that:

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

Unlock Your Potential

Excel

Basic - Advanced

Access

Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project