Personal xlsb file size seems too big.

[mehidy1437]

Hello Experts

My personal xlsb file size is 17.4 MB, it seems the size is too big.
After exporting all module in bas format, all the macro files size is only 505 KB
Why is it so?
Is it okay to have a xlsb file with 17.4MB or more?

My all macro is in a personal xlsb file.
I have a total 70+ modules in my personal xlsb file.

Please share your comments.

 

[Wookiee]

That is weird! I've heard of file bloat before, but it seems that's usually due to cell formatting and such. My Personal.xlsb has 6 forms, 2 modules, and probably about 100 various macros and it's only 432KB.

I suggest copying all the macro text to a new file and saving over your old one, but I don't know how feasible that is, as you mentioned that you multiple modules.

 

[Dave Patton]

Do you have a backup file of your Personal.xlsb? Is it also a large file?
Can you build a new Personal.xlsb one or a few modules at time and check the file size?

One or more of your modules may have some extraneous data.

It will help if you can identify what is creating the increase in file size.

What do you mean by "My all macro"?

Do you have 70 bas files? Are any of these files larger than the others?

 

[mehidy1437]

That is weird! I've heard of file bloat before, but it seems that's usually due to cell formatting and such. My Personal.xlsb has 6 forms, 2 modules, and probably about 100 various macros and it's only 432KB.

I suggest copying all the macro text to a new file and saving over your old one, but I don't know how feasible that is, as you mentioned that you multiple modules.

I have used below code to import all the exported module/bas file in a new personal.xlsb file.
Now my file size is only 409kb.
Thanks for your suggestion.

Sub importmoduleTOpersonalXLSBfile()

Dim oXL, oBook, oSheet

    ' Create a new instance of Excel and make it visible.
    Set oXL = CreateObject("Excel.Application")
    oXL.Visible = True

    ' Add a new workbook and set a reference to Sheet1.
     oXL.Workbooks.Open oXL.StartupPath & "\Personal.xlsb"
     Set oBook = oXL.Workbooks("Personal.xlsb")
     
     Set oSheet = oBook.Sheets(1)

    'Import previously created BAS module file
     oXL.VBE.ActiveVBProject.VBComponents.Import "C:\Macros\module1.bas"
     oXL.VBE.ActiveVBProject.VBComponents.Import "C:\Macros\module2.bas"
End Sub

Above code I have found here, VBA code to import bas file in personal xlsb file

 

[mehidy1437]

Do you have a backup file of your Personal.xlsb? Is it also a large file?
Can you build a new Personal.xlsb one or a few modules at time and check the file size?

One or more of your modules may have some extraneous data.

It will help if you can identify what is creating the increase in file size.

What do you mean by "My all macro"?

Do you have 70 bas files? Are any of these files larger than the others?

Yes, my backup file also in same size.
Yes, I have created one as on post #4
And the file size is okay now.

Thanks for your suggestion.