Mike wonders if there is a worksheet function that will show him the size, in bytes, of a workbook without using a macro.
The size of a workbook in Excel can become very large, depending on the information it contains. Keeping track of the size is important and can be accomplished a couple of different ways.
If you don't want to use a macro, Excel keeps track of various pieces of information about a file in the Properties dialog box. How you display the dialog box depends on the version of Excel you are using. If you are using Excel 2010 or Excel 2013, follow these steps:
Figure 1. The General tab of the Properties dialog box.
If you are using Excel 2007, follow these steps instead:
In the General tab, Excel displays the size of the file. You will also see other information about the file in this tab including the type of file and who created it. Manually obtaining the file size is simple using this process, but it does not allow you to see the workbook size on the worksheet itself. Unfortunately, there is no way around it; you will need to use a macro. The following is a good example of one you could use:
Function wbksize() myWbk = Application.ThisWorkbook.FullName wbksize = FileLen(myWbk) End Function
To use this macro within a worksheet, just type the following in any cell:
=wbksize()
The file size is displayed in bytes.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8030) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Finding the Size of a Workbook.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Need to know what the full path name is for the current workbook? With a simple macro you can display the full path name ...
Discover MoreNeed to save a workbook in more than one location? Here's a handy macro that can save your workbook in lots of different ...
Discover MoreIf you open a workbook and notice that Excel displays two windows for it, this has to do with how the workbook was saved. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2015-02-09 22:46:55
Kib Squared
1,048,576 is 1024^2
2013-04-17 08:04:01
Bryan
Good tip, but why bother to dimension a variable for such a simple operation (though it doesn't look like you are using Option Explicit, and therefore are not actually dimensioning it -- yikes!)?
You also have to watch out for ThisWorkbook -- if you put this function in an add-in or Personal.xlsb it will return the size of the workbook where the *code* is stored, not the workbook where the formula is typed.
A better solution is
wbksize = FileLen(Application.Caller.Worksheet.Parent.FullName)
It's still not perfect (it can ONLY be used as a UDF or it will throw an error), but it's simple enough and accurate. Lastly, readers might not know that in order to get the size in kb, you have to divide by 1,024, not 1,000. Similarly, to get mb you divide by 1,048,576.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments