Overflow in Excel 2010, 64 bit due to Array size

Question

I try to declare the following array in Excel VBA 2010 on a 64 bit machine with 16 GB RAM:

Public MyArray(1 To 100, 1 To 100, 1 To 6000, -2 To 2, ,-2 to 2, 1 To 3) As Double

If I run the code Excel crashes immediately.

If I reduce the size of the array into:

Public MyArray(1 To 100, 1 To 100, 1 To 6, -2 To 2, ,-2 to 2, 1 To 3) As Double

Everything runs fine.

Anyone knows the cause of the crash in the first array or how to solve it?
I think it is strange Excel cannot handle the array due to 16 GB of RAM?

Thank you!

Mike

qPCR4vir · Accepted Answer · 2013-03-13 13:10:19Z

1

Public MyArray
   (1 To 100,     --->           100
    1 To 100,     ---> * 100  = 10 000
    1 To 6000,    ---> * 6000 = 60 000 000
    -2 To 2,      ---> * 5    = 300 000 000
      ,    ? 
    -2 to 2,      ---> * 5    = 1 500 000 000 
    1 To 3        ---> * 3    = 4 500 000 000
    ) As Double   ---> * 8    = 36 000 000 000

36 GB ??

answered Mar 13, 2013 at 13:10

qPCR4vir

3,5821 gold badge24 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2165379 Over a year ago

Thanks a lot! So I have to maximize this to 16 000 000 000.

qPCR4vir Over a year ago

Well, you need memory for many other thing too... The actual limmit will be smaller

Christian Sauer Over a year ago

What in heaven requires such a excel-array?! Reduce it to something suitable for excel or use a database...

user2165379 Over a year ago

I just realize the actual limit is smaller. Probably due to other things. I experience the actual limit is only around 5 GB.

user2165379 Over a year ago

Christian, I have many observations with different labels. The problem is that I want to extract the Median instead of the Average for all observation with a specific label. For this reason one of the dimensions is 6000. In this dimension all the observations are stored with a specific label from the other dimensions.

Collectives™ on Stack Overflow

Overflow in Excel 2010, 64 bit due to Array size

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related