Measuring Sheet Size Using VBA

[Riley_Johnson]

Any ideas to improve this code?

This macro is intended to calculate the size of each sheet in Excel. In practice, it will trim fat from a template frequently used at work. For instance, a decision that might arise from its use would be to remove some of the sheets from the base template and provide access to them separately so that they can be copied to the workbook and plugged into the model on an as-needed basis.

The procedure’s logic flows like this: In the workbook to be analyzed (call this the subject workbook), paste and run this procedure. The procedure creates two files: a file for recording the analysis (call this the output file) and a blank workbook to reconstruct the subject workbook (call this the tmp file). Then, the procedure copies each sheet of the subject workbook into the tmp file, deletes all named ranges, saves, and records the size of the tmp file. The sheet index, name, and tmp file size are recorded in the output file, and the loop continues.

At the end of the procedure, the data in the output file can be used to calculate the difference between each tmp file size, thus giving a moderately accurate record of the sheet size. I am struggling to account for the size contributions of named ranges, macros, and the metadata in the procedure rather than figuring this out afterward.

I’ve saved blank workbooks and noticed those are about 64 KB, so this would decrease the size of the first sheet. If named ranges are deleted at the end of the procedure, the difference in file size between the tmp file and the subject file results from their presence and any macros. So, if you convert the subject file to an xlsx, the macros are removed, and the difference between that file and the tmp file should be the named ranges, and the difference between the subject file and that file is the size of the macros.

Output of record file:

Screenshot of Output File

Here is the code:

Sub getFileSizeOfAllSheetsInWorkbook()

    'path objects
    Dim path As String
    path = Application.ActiveWorkbook.path

    'New workbook objects to record the outputs
    Dim objRecordWorkbook As Workbook
    Dim objRecordWorksheet As Worksheet
    
    Set objRecordWorkbook = Excel.Application.Workbooks.Add
    Set objRecordWorksheet = objRecordWorkbook.Sheets(1)
      
    'New workbook objects
    Dim objNewWorkbook As Workbook
    Dim objNewWorksheet As Worksheet
    
    'Create a new workbook and rebuild the current one from scratch, worksheet by worksheet
    Set objNewWorkbook = Excel.Application.Workbooks.Add
    
    'Save the temporary workbook
    objNewWorkbook.SaveAs _
        FileName:=path & "\" & "TMP", _
        FileFormat:=xlOpenXMLWorkbook, _
        CreateBackup:=False

    'iterate through all the sheets in the active workbook
    For i = 1 To ThisWorkbook.Sheets.Count

        'Get sheetName for fileName
        Dim sheetName As String
        sheetName = ThisWorkbook.Sheets(i).Name
        
        'Copy the desired worksheet to the new workbook
        ThisWorkbook.Worksheets(sheetName).Copy After:=objNewWorkbook.Sheets(1)
        
        'Delete all of the named ranges
        
        Dim RangeName As Name

        On Error Resume Next
        For Each RangeName In objNewWorkbook.Names
            objNewWorkbook.Names(RangeName.Name).Delete
        Next
        On Error GoTo 0
        
        'save the tmp workbook
        objNewWorkbook.Save

        'get the file size of the new workbook
        Dim fileSizeBytes As Double
        fileSizeBytes = FileLen(objNewWorkbook.FullName)
        
        Dim fileSizeKB As Double
        fileSizeKB = fileSizeBytes / 1024
        
        'paste the sheet index, sheet name, and filelength in the recorder file
        objRecordWorksheet.Cells(i, 1) = i
        objRecordWorksheet.Cells(i, 2) = ThisWorkbook.Sheets(i).Name
        objRecordWorksheet.Cells(i, 3) = Format(fileSizeKB, "0.00")

    Next i
    
    'delete the file with the copied sheet
    'Kill objNewWorkbook.FullName

    'Insert the titles of the data into the records worksheet
    With objRecordWorksheet
         .Rows(1).Insert
         .Cells(1, 1) = "INDEX"
         .Cells(1, 1).Font.Bold = True
         .Cells(1, 2) = "NAME"
         .Cells(1, 2).Font.Bold = True
         .Cells(1, 3) = "SIZE KB"
         .Cells(1, 3).Font.Bold = True
         .Columns("A:C").AutoFit
    End With

    'Save the record workbook with all the data
    objRecordWorkbook.SaveAs FileName:=path & "\" & "SheetSizesOutputFile", _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

[#2 · Accepted solution]

@Riley_Johnson If the workbook is in xlsx or xlsm format, you could look in the zip container to see the size of each sheet's xml file:

Using my editOpenXML example code you could easily extract the xml of any sheet and measure how large it is by taking the Len of the returned (xml) string. See https://jkp-ads.com/articles/excel2007fileformat02.asp 

[#3]

exceel

[#4]

@Jan Karel Pieterse Thank you for sharing! This is probably the most technical I've gotten with Excel before. That said, please pardon me if this is a dumb question.

I noticed that once the object was zipped back together, there was no procedure to convert it back to a .xlsx or .xlsm file. So, in effect, is the original .xlsx or .xlsm file destroyed? This is my folder after running the demo procedure in EditOpenXML 004.

[#5]

I haven't visited that code in ages. But I think when the class instance is properly destroyed at the end of the code, it should automatically clean up after itself.