VBA Excel Function for returning file size in byte

There is a very nice and simple VBA function, which was not mentioned so far, FileLen:

FileLen("C:\Temp\test file.xls")

It returns the size of the file in bytes.

In combination with looping through files in a directory it's possible to achieve what you originally wanted (get sizes of files in a folder).

Here how to use it in Excel Cell:

 =GetDirOrFileSize("C:\Users\xxx\Playground\","filename.xxx")

If you have a german Windows than:

=GetDirOrFileSize("C:\Users\xxx\Playground\";"filename.xxx")

Here is the function for the VBA modul: (Just enable the Developer tools, and copy and paste this into a new modul)

Function GetDirOrFileSize(strFolder As String, Optional strFile As Variant) As Long'Call Sequence: GetDirOrFileSize("drive\path"[,"filename.ext"])   Dim lngFSize As Long, lngDSize As Long   Dim oFO As Object   Dim oFD As Object   Dim OFS As Object   lngFSize = 0   Set OFS = CreateObject("Scripting.FileSystemObject")   If strFolder = "" Then strFolder = ActiveWorkbook.path   If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"   'Thanks to Jean-Francois Corbett, you can use also OFS.BuildPath(strFolder, strFile)   If OFS.FolderExists(strFolder) Then     If Not IsMissing(strFile) Then       If OFS.FileExists(strFolder & strFile) Then         Set oFO = OFS.Getfile(strFolder & strFile)         GetDirOrFileSize = oFO.Size       End If       Else        Set oFD = OFS.GetFolder(strFolder)        GetDirOrFileSize = oFD.Size       End If   End IfEnd Function   '*** GetDirOrFileSize ***