vba Excel 大小突然变得非常大

Excel size grew very large all of a sudden

提问by Alvi John

My Excel file(contains macros) that was just 4 MB in size all of a sudden grew into a 12MB file... i made only some minor changes in the vba code and i dont think that is the reason for this.... What else could be the reason for this? Any Suggestions on how to repair this?

我只有 4 MB 大小的 Excel 文件（包含宏）突然变成了 12MB 文件……我只对 vba 代码做了一些小改动，我认为这不是原因……还有什么可能是这个原因？有关如何修复此问题的任何建议？

回答by InContext

Most likely in the data sheets you have many rows with just formatting. Go into each sheet and delete all rows under the data and then resave the spreadsheet. An easy way to tell is the vertical navigation bar will extend a long way below were your data ends.

很可能在数据表中，您有很多行只是格式化。进入每个工作表并删除数据下的所有行，然后重新保存电子表格。一种简单的判断方法是，如果您的数据结束，垂直导航栏将在下方延伸很长一段距离。

回答by Patrick Honorez

It might be a formatting issue. If Philip A Barnes suggestions are not enough (or if you want an automated solution), check the Microsoft Excel Excess Formatting Cleaner Add-in

可能是格式问题。如果 Philip A Barnes 的建议还不够（或者如果您想要一个自动化的解决方案），请查看Microsoft Excel Excess Formatting Cleaner Add-in

回答by bonCodigo

Another alternative:

另一种选择：

If it's not too much to create a new sheet, you may follow this link help to re-build your sheet

如果创建新工作表不是太多，您可以按照此链接帮助重新构建您的工作表

from above post:

从上面的帖子：

PURPOSE:

目的：

• decrease filesize
• increase speed
• avoid some strange behaviour of all kind

• 减少文件大小
• 增速
• 避免各种奇怪的行为

WHAT are we talking about ?

我们在说啥啊 ？

During the development of a workbook+project you are creating, deleting, moving all kinds of items: formats, cells, controls, shapes, modules, variables, .... Some garbage is staying in "the background" of your file. It's like Excel remembers things which are useless for you: f.i. variables which doesn't exist anymore. It's like there are knots in the formulareferences.

在工作簿+项目的开发过程中，您正在创建、删除、移动各种项目：格式、单元格、控件、形状、模块、变量……一些垃圾留在文件的“背景”中。就像 Excel 会记住对您无用的东西：不再存在的 fi 变量。这就像公式参考中有结。

So you may read further the entire article to help you with, if you choose this path.

因此，如果您选择此路径，您可以进一步阅读整篇文章以帮助您。

---

---

With reference to my comment:

参考我的评论：

"Images? Formatting? Loading bulk data? : is your macro responsible for that? And clean your code to remove any unused objects. Flushing helps. Any formula that you added to sheets in bulk? Anything not in use, should be dump out. :) "

“图像？格式化？加载批量数据？：你的宏负责吗？并清理你的代码以删除任何未使用的对象。刷新有帮助。你批量添加到工作表的任何公式？任何不使用的东西都应该转储出来。 :)”

We have usually encountered this issue with following items:-

我们通常在以下项目中遇到此问题：-

• links (could be dde links, other third party links like downloading prices, links to other sheets etc)
• formulas (could be working but volatile, could be broken and unused yet triggering)
• pivot tables (old, new)
• formatting, normal and conditional formatting
• bulk load of data somewhere that you may not notice

• 链接（可能是 dde ​​链接、其他第三方链接，如下载价格、其他工作表的链接等）
• 公式（可能有效但不稳定，可能被破坏和未使用但触发）
• 数据透视表（旧的，新的）
• 格式，正常和条件格式
• 在您可能没有注意到的地方批量加载数据

回答by CuberChase

I have this problem with a large excel add-in with plenty of code and then some where it grows from ~5Mb up to ~8Mb with minor code changes. In difference to the fixes in the other answers above, I do notchange any sheet formatting, it is only code. I find the following program pretty awesome in knocking back down to size but have been unable to eliminate the problem by other means.

我有一个带有大量代码的大型 excel 加载项的问题，然后是一些代码更改从 ~5Mb 增长到 ~8Mb 的地方。与上述其他答案中的修复不同，我没有更改任何工作表格式，它只是代码。我发现以下程序在缩小尺寸方面非常棒，但无法通过其他方式消除问题。

http://www.appspro.com/Utilities/CodeCleaner.htm

http://www.appspro.com/Utilities/CodeCleaner.htm