Function To Dynamically Display File Size

[Guest]

I need a function that will dynamically calculate the current workbook's size
(either in KB of MB) and display it. I wrote the following wrapper function:

Public Function FileSize() As Long

FileSize = FileLen(ActiveWorkbook.Path & "\" & ActiveWorkbook.Name)

End Function

It works, except that if you add or remove any data, you have to save,
close, and re-open the file for the new size to display. Are there any
options that will show the file size after it is saved?

Thanks.

 

[Dave Peterson]

I didn't have to close and reopen, but I did have to recalculate the workbook.

Just adding:

application.volatile

To the top of your sub would make that your UDF would recalc whenever excel
did. (You could be one calculation behind.)

And the workbook has to be saved--since you're asking for the filesize and that
doesn't really exist until it's a file.