vba - How can one see the size of the compiled subroutine in VBA Excel 2016?

Question

After finding a "procedure to large" error on a specific subroutine of a module, I am refactoring the code in the module. I was curious on how effective the refactoring is at reducing the compiled code size.

So I was wondering if there is a way to see the size of the compiled code of a subroutine in a Module of Excel 2016?

My attempts so far have been limited to searching online for methods to determine the size of a compiled module or subroutine in VBA.

It led me to the limits of VBA as listed in here. It does not mention a way to see the size of the procedure compiled, nor does it mention whether that is (practically) possible.

(As was mentioned in the comments, the current maximum size of the compiled procedure is 64K as stated here.)

1. I think the compiled procedure size is not 1 on 1 related to the number of lines. (Because that does not take into account short or long lines. But I am currently unsure about how the vba-procure is compiled, and consequently how lines contribute to the compiled file size, otherwise a solution could possibly be to compute the compiled file size.)
2. Nor is the procedure 1 on 1 dependent on the size of the code as stored as a '.txt' file. (because it can contain comments which do not contribute to the compiled code size)

Disclamer - I am well aware of the shortcomings of the old code I am modifying. That it is poorly written, I think this quote of Bill Gates illustrates it quite well:

Measuring programming progress by lines of code is like measuring aircraft building progress by weight.

I think refactoring, and breaking the code up in shorter subroutines is an appropriate first step. To monitor this process, in combination with the hard bottleneck presented by VBA as the Procedure too long- error led me to this question.

标签： vbaexcelcompiler-errors

Answer 1

不，你不能那样做。VBA 分几个阶段编译，部分是 p 代码，部分是解释，最终编译过程的大小（以 kb 为单位）并不是您需要处理的。

---

您需要紧急阅读Abstraction。触发此编译器错误的过程超过 10K 行代码。在适当的抽象级别上，一个健康的过程可能比那个小 500 到 1000 倍（不是开玩笑）——编译代码的大小是绝对没有意义的。

如果您担心编译代码的大小，那么您并不是在为人类编写代码。

代码不是为了编译器和运行时环境而编写的。代码是为人类维护人员编写的，用于阅读、理解、遵循、调试、修改、扩展等。没有任何抽象级别，代码是一系列无聊、令人极度麻木的可执行语句，它们总是冗余、低效且令人讨厌容易出错。

您可以使用第三方工具来分析 VBA 代码。MZ-Tools 3.0 是免费的，但最新版本不是。它有一个功能，可以告诉你每个模块的每个过程中有多少行代码，有多少被注释掉，是否有未使用的变量等。

Rubberduck是免费和开源的，正在积极开发中（免责声明：我拥有该项目的存储库），并且具有代码度量功能（不同于代码检查），它可能可以帮助您识别最有问题的区域（尽管解析一个 10K-班轮模块可能需要一段时间）：

行数是您的“代码行数”指标；圈复杂度是代码中不同可能执行路径的粗略指标（该指标在模块级聚合中的意义有多大值得商榷）；最大嵌套也是“箭头形”代码可能有多糟糕的一个指标。

这些指标中的高值表明您可能需要提取方法。