Excel "Out of Memory" error running macro only occurs on the machine with the most memory

Question

The following subroutine allocates memory for several arrays and then displays a msg box. it runs fine on my PC and everyone's laptop, except mine. I get an "Out of memory" error on my machine which has more memory than everyone else. I have 32 GB of memory.

We are both running a 32 bit version of Excel on the same hardware. I tried to reboot to clear all memory and I only run Excel after rebooting. This does not help me. When i check my Task manager, it shows that I have very low memory usage (around 15%).

If I start removing the dimension statements to allocate less memory, this routine eventually runs.

But why am I running out of memory on my laptop and how can I get it to work?

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

If you have a version that supports LAA, 32bit Excel running on a 64bit OS makes 4GB available to Excel. (Without LAA, Excel can use only 2G). It makes no difference how much physical memory you have.

That 4GB is total memory. It is consumed by the Excel app itself, all workbooks you have open, any addins installed, and vba code running.

You are allocating close to 3GB to those arrays. With whatever else Excel is allocating you are hitting the limit.

As per your comment, removing an addin clearly made the difference between > 4GB and < 4GB