Memory limitations with VBA

[ChrisD]

Hello,

Does anyone know the memory limitations of Excel VBA (2003 or 2007), please?

I'm working with 3D variant arrays and reach a critical limitation when I
increase dimensional requirements for a key variable beyond approx
2000x58x207. Consequently, the "out-of-memory run-time error 7" message
appears.

I have a 64-bit workstation running Windows XP 64 with 8GB RAM and 4
processors. In theory, I should be able to access most of the available
memory with this 64-bit system configuration. However, I understand that
Excel is a 32-bit application and as such was only designed to utilize less
than 2GB RAM.

I'd appreciate any advice as to a workaround solution.

Regards,

Chris Dandre.

 

[Gary''s Student]

Make sure you have Dim'ed the arrays to require as few bytes as possible.

 

[Jan Karel Pieterse]

Hi ChrisD,

I'm working with 3D variant arrays and reach a critical limitation when I
increase dimensional requirements for a key variable beyond approx
2000x58x207.

Wow, that is a large array. And because you're using a variant array, mem
consumption of that number may amount to as much as 336,168,000 bytes (14
bytes per element is minimum, depending on the exact datatype you're
storing!!). I'm not surprised VBA chokes.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[RB Smissaert]

I encountered the same problem (although only in rare occasions) and the
solutions was to move away from arrays and write to file in the form of
the free SQLite database. Don't know what you use your array for, but
this might be an option.

RBS

 

[ChrisD]

Hi RBS,

Thanks for taking the time to respond to my post. The arrays contain
historical financial information on stocks. One of my development priorities
is migration to SQL server, and your experience/suggestion supports this
initiative.

Thanks again for your help.

Regards,

Chris.

 

[bart.smissaert]

Hi RBS,

Thanks for taking the time to respond to my post. The arrays contain
historical financial information on stocks. One of my development priorities
is migration to SQL server, and your experience/suggestion supports this
initiative.

Thanks again for your help.

Regards,

Chris.

Looks the time has come then to move to a database now.

RBS