Memory limitations with VBA
 

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.

Memory limitations with VBA
 

Make sure you have Dim'ed the arrays to require as few bytes as possible.
--
Gary''s Student - gsnu200718

Memory limitations with VBA
 

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

Memory limitations with VBA
 

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" wrote in message
...
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.

Memory limitations with VBA
 

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.

"RB Smissaert" wrote:

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" wrote in message
...
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.

Memory limitations with VBA
 

On 2 May, 00:15, ChrisD wrote:
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.

"RB Smissaert" wrote:
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" wrote in message
...
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.


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

RBS