Excel "Out of Memory"错误运行宏只发生在内存最多的机器上

Question

下面的子例程为几个数组分配内存，然后显示一个消息框。它在我的 PC 和每个人的笔记本电脑上运行良好，除了我的。我的机器上出现“内存不足”错误，它的内存比其他人都多。我有 32 GB 的内存。
我们都在同一硬件上运行 32 位版本的 Excel。我尝试重新启动以清除所有内存，并且仅在重新启动后运行 Excel。这对我没有帮助。当我检查我的任务管理器时，它显示我的内存使用率非常低(大约 15%)。
如果我开始删除维度语句以分配更少的内存，则该例程最终会运行。
但是为什么我的笔记本电脑内存不足，我怎样才能让它工作呢？

Sub Test()
  Dim A(482) As Date
  Dim B(482) As Integer
  Dim C(482) As Integer
  Dim D(482, 100) As Double
  Dim e(482, 100) As Double
  Dim f(482, 100) As Double
  Dim g(16000) As String
  Dim h(16000) As String
  Dim i(16000) As Single
  Dim j(16000) As String
  Dim k(16000) As String
  Dim l(16000) As Integer
  Dim m(16000) As String
  Dim n(16000) As Date
  Dim o(16000) As Integer
  Dim p(16000) As String
  Dim q(16000) As String
  Dim r(16000) As Double
  Dim s(16000) As Date
  Dim t(16000) As Integer
  Dim u(16000) As Double
  Dim v(16000) As Double
  Dim w(16000) As Integer
  Dim x(16000) As Double
  Dim y(16000) As Double
  Dim a1(16000) As Double
  Dim a2(16000) As String
  Dim a3(16000) As Double
  Dim a4(16000, 482) As Integer
  Dim a5(16000, 482) As Integer
  Dim a6(16000, 482) As Integer
  Dim a7(16000, 482) As Integer
  Dim a8(16000, 482) As Double
  Dim a9(16000, 482) As Double
  Dim a10(16000, 482) As Double
  Dim a11(16000, 482) As Double
  Dim a12(16000, 482) As Double
  Dim a13(16000, 482) As Double
  Dim a14(16000, 482) As Double
  Dim a15(16000, 482) As Double
  Dim a16(16000, 482) As Double
  Dim a17(16000, 482) As Double
  Dim a18(16000, 482) As Double
  Dim a19(16000, 482) As Double
  Dim a20(16000, 482) As Double
  Dim a21(16000, 482) As Double
  Dim a22(16000, 482) As Double
  Dim a23(16000, 482) As Double
  Dim a24(16000, 482) As Double
  Dim a25(16000, 482) As Double
  Dim a26(482) As Double
  Dim a27(16000, 482) As Double
  Dim a28(16000, 482) As Double
  Dim a29(16000, 482) As Double
  Dim a30(16000, 482) As Double
  Dim a31(16000, 482) As Double
  Dim a32(16000, 482) As Double
  Dim a33(16000, 482) As Double
  Dim a34(16000, 482) As Double
  Dim a35(16000, 482) As Double
  Dim a36(16000, 482) As Double
  Dim a37(16000, 482) As Double
  Dim a38(16000, 482) As Double
  Dim a39(16000, 482) As Double
  Dim a40(16000, 482) As Double
  Dim a41(16000, 482) As Double
  Dim a42(16000, 482) As Double
  Dim a43(200, 2) As Double
  Dim a44(200) As Double
  Dim a45(200, 2) As Double
  Dim a46 As Date
  Dim a47(482, 100) As Double
  Dim a48(482) As Double
  Dim a49(482) As Double
  Dim a50(482) As Double
  Dim a51(482) As Double
  Dim a52(482) As Double
  Dim a53(482) As Double
  Dim a54(482) As Double
  Dim a55(482) As Double
  Dim a56(482) As Double
  Dim a57(482) As Double
  Dim a58(482) As Double
  Dim a59(482, 482) As Double
  Dim a60(482) As Double
  Dim a61(482) As Double
  Dim a62(482, 100) As Double
  Dim a63(482) As Double
  Dim a64(482) As Double
  Dim a65(482) As Double
  Dim a66(482) As Double
  Dim a67(482) As Double
  Dim a68(482) As Double
  Dim a69(482) As Double
  Dim a70(482) As Double
  Dim a71(482) As Double
  Dim a72(482) As Double
  Dim a73(482) As Double
  Dim a74(482) As Double
  Dim a75(482) As Double
  Dim a76(16000) As Double
  Dim a77(16000, 482) As Double
  Dim a78(16000) As Double
  Dim a79(16000, 482) As Double
  Dim a80(16000) As Double
  Dim a81(482) As Double
  Dim a82(16000, 482) As Double
  Dim a83(16000) As Double
  Dim a84(16000) As Double
  Dim a85(16000, 482) As Double
  Dim a86(16000, 482) As Double
  Dim a87(16000, 482) As Double
  Dim a88(16000) As Double
  Dim a89(16000) As String
  Dim a90(16000) As String
  Dim a91(16000, 482) As Double
  Dim a100(482) As Double
  Dim a101(482) As Double

  Dim a102(40) As Double
  Dim a103(40) As Double
  Dim a104(40) As Double
  Dim a105(40) As Double
  Dim a106(40) As Double
  Dim a107(40) As Double
  Dim a108(40) As Double
  Dim a109(40) As Double
  Dim a110(40) As Double
  Dim a111(40) As Double
  Dim a112(40) As Double
  Dim a113(40) As Double
  Dim a114(40) As Double

  Dim a115(482) As Double
  Dim a116(482) As Double
  Dim a117(482) As Double
  Dim a118 As Double
  
  MsgBox ("Hello!")
End Sub

Answer 1

如果您有支持 LAA 的版本，在 64 位操作系统上运行的 32 位 Excel 使 Excel 可以使用 4GB。 (没有LAA，Excel只能使用2G)。您拥有多少物理内存没有区别。
那4GB是总内存。它由 Excel 应用程序本身、您打开的所有工作簿、安装的任何插件和运行的 vba 代码使用。
您正在为这些阵列分配接近 3GB 的空间。无论 Excel 分配什么，你都达到了极限。
根据您的评论，删除插件显然会导致 > 4GB 和 < 4GB