Estimating size of worksheets

[goblin]

Does anyone know if it is possible to 'easily' estimate the size of worksheets in a workbook.

I don't care if it is VBA code, or via raw Excel file parsing or however it is done. I just want to see how much each sheet is contributing to the total size of the workbook.

/Goblin

 

[Damon Ostrander]

Hi Goblin,

I don't know any way to directly obtain the size of an individual worksheet among many in a workbook. However, you should be able to indirectly determine the size by simply copying the worksheet to a new workbook, and looking at the size of that workbook (you will have to save the workbook to determine the file size). This could easily be automated via a macro to produce a list of the sizes of all the sheets in a workbook. There probably is some fixed amount of overhead required for a workbook--this value for Excel 2000 is 13824 bytes, as can be determined by saving a workbook with one empty worksheet. This overhead value should be subtracted from the size of the new workbook containing the sheet you want to measure the size of. Once your code saves the new file, it can get its size via the expression:

filelen(activeworkbook.FullName)

 

[goblin]

Seems to work OK. Thanks for the brute force idea :wink:

Sub SizeOfWorksheets()
    Dim wb As Workbook, wbResults As Workbook, wbTmp As Workbook
    Dim sh As Worksheet, shResults As Worksheet
    Dim lSheets As Long, rw As Long
    Dim fs As Object
    Dim filename As String
    
    filename = Application.DefaultFilePath & "\Crapname.xls"
        
    lSheets = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 1
    Application.DisplayAlerts = False
    
    Set wb = ActiveWorkbook
    Set wbResults = Workbooks.Add
    Set shResults = wbResults.ActiveSheet
    
    shResults.Cells(1, 1).Value = "Worksheet"
    shResults.Cells(1, 2).Value = "Number of Bytes"
    shResults.Cells(1, 3).Value = "Less Overhead"
    
    rw = 2
    For Each sh In wb.Worksheets
        Set wbTmp = Workbooks.Add
        sh.Copy after:=wbTmp.Worksheets(1)
        wbTmp.Worksheets(1).Delete
        wbTmp.SaveAs filename
        shResults.Cells(rw, 1).Value = sh.Name
        shResults.Cells(rw, 2).Value = FileLen(filename)
        shResults.Cells(rw, 3).Value = shResults.Cells(rw, 2).Value - 13000
        wbTmp.Close
        rw = rw + 1
    Next
    
    shResults.Columns("A:C").AutoFit
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.DeleteFile filename
    Application.SheetsInNewWorkbook = lSheets
    Application.DisplayAlerts = True
End Sub

/Goblin

 

[goblin]

Perhaps the above implementation wasn't so great. I have a workbook with 4 worksheets, filesize 2,9MB, but the sum of all the individual worksheets is 3,6MB.

The sheets are very dependent on each other, but the dependency is one way: 1->2->3->4

Sheet 4 which I though would be smallest by far (as it only contains sums and averages from the others, which are the actual data sheets) is by far the largest, reported at 1,9MB.

So, if anyone has another idea of how to estimate worksheet sizes, please share...

Goblin

 

[Damon Ostrander]

Hi again Goblin,

I'm not surprised the total is greater than the sum of the parts. Hmmm, where have I heard that before? Anyway, there are several things that can contribute to this:

1. I mentioned that the workbook overhead is 13824 bytes. This is for a workbook with no workbook-level or standard module-level macros, no extra workbook builtin or custom properties (e.g., Summary annotation), etc.

2. I don't know what affect links between worksheets have on sheet size--whether it decreases the size when you break them as is necessary when you measure their size individually.

3. There are other customizations that also add to the workbook-level overhead. Toolbar customizations, add-ins and VBE references loaded (the list of add-ins must be stored somewhere in the workbook).

4. The Excel "garbage collection" capability is very poor and artifacts from previous changes can increase worksheet and workbook size. Copying the sheets to a new workbook may clear out some of the garbage. It is well known that exporting macros from a workbook, deleting them, and re-importing them almost always reduces the size of the workbook. It is possible that the same is true with worksheets, and especially if they contain event code modules with code.

So, how does one get around all this? Here's what I suggest: instead of copying the sheets to a new workbook, create a full "dummy" copy of your workbook. Write a macro that adds an empty sheet to the workbook, then deletes all the other the sheets one at a time, saving the workbook each time (this is why it must be a dummy copy). Record the file size before and after each save. The difference is the size of the sheet that was deleted. At the end the size of the workbook with the one blank sheet remaining is the workbook overhead, and this will probably be bigger than the 13824 bytes for a pristine workbook--although with this method the overhead does not need to be known, but might be of academic interest. As before, it would be a good idea to write this macro in a different workbook that the one you're evaluating.

 

[TommyGun]

Another check goblin, is to check on the "used range" of each sheet. A lot of times Excel will "store" formats inside cells that you are not using, and this can greatly increase the size of workbook.

Go through each of your sheets, and check the size of the vertical scroll bar. If the size is very small, then you might have formats that are stored on your sheets. Go to your last row of actual data, select everything below it, and delete. Repeat for your columns. Save your workbook, and check the size now.

Just one option, and something that I run into a lot.

 

[goblin]

Much better now. The summed size of the individual worksheets is now almost exactly the same as the size of the original workbook. But this may only hold true for this specific book.

It is composed of 4 worksheets, where the dependency is 1->2->3 and then 1->4

Sheet1 is about 700*100 (~70000) cells of raw data, no links

Sheet2 is about 225*100 (~22500) cells holding links to Sheet1 with a sum here and there, summarizing sheet 1.

Sheet3 is about 50*100 (~5000) cells holding links to Sheet2, summarizing sheet 2.

Sheet4 is about 650*35 (~22750) cells holding nothing but SUM and AVERAGE of ranges in Sheet1.

The size of the sheets is reported as:

800000, 620000, 100000 and 1500000 for sheets 1-4 respectively. Guess the SUM and AVERAGE formulas require a fair amount of space. I've checked, double checkedl and triple checked that I'm not using excess space on the worksheets. Had that happen to me once. I refuse to let it happen again!!! :D

Anyway, this is good enough. If anyone is interested here is the modified code:

Sub SizeOfWorksheets2()
    Dim wb As Workbook, wbResults As Workbook
    Dim sh As Worksheet, shResults As Worksheet
    Dim lSheets As Long, rw As Long, sizeBefore As Long, sizeAfter As Long
    Dim i As Long
    Dim fs As Object
    Dim filename As String
    
    filename = Application.DefaultFilePath & "\Crapname.xls"
        
    lSheets = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 1
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set wb = ActiveWorkbook
    wb.SaveAs filename
    wb.Worksheets.Add before:=wb.Worksheets(1)
    Set wbResults = Workbooks.Add
    Set shResults = wbResults.ActiveSheet
    
    shResults.Cells(1, 1).Value = "Worksheet"
    shResults.Cells(1, 2).Value = "Number of Bytes"
    
    rw = 2
    For i = wb.Worksheets.Count To 2 Step -1
        Set sh = wb.Worksheets(i)
        shResults.Cells(rw, 1).Value = sh.Name
        wb.Save
        sizeBefore = FileLen(filename)
        sh.Delete
        wb.Save
        sizeAfter = FileLen(filename)
        shResults.Cells(rw, 2).Value = sizeBefore - sizeAfter
        rw = rw + 1
    Next
    
    shResults.Columns("A:B").AutoFit
    wb.Close
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.DeleteFile filename
    Application.SheetsInNewWorkbook = lSheets
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Goblin