Out of memory error.

[LHW]

Does anyone know how to solve the 'out of memory' error in excel vba?

 

[Dag Johansen]

Hi,

I think you need to supply more information. If you run
out of memory it basically means you need to keep less
data in memory at any given time, which means you have to
persist state elsewhere (such as a file). That of course
affects the basic design of the solution, so it may lead
to a completely different implementation in the end.

However, unless you are dealing with very large volumes of
data, it seems likely that you are not using memory
efficiently. In particular, if you use user-defined types,
be aware that they are always marshalled by value; i.e.
whenever you pass the type as a parameter to a function,
the data structure is copied - you cannot pass it by
reference. Such a design can therefore

In short, you will have much better chances of actually
getting help if you provide more information; preferrably
with the offending code.

Good luck, happy coding!

Dag

 

[LHW]

Hi Dag,

Thank you so much. Following is the code.

'Workbooks.OpenText FileName:=pFileName, _
' Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=
_
' Array(Array(0, 2), Array(17, 1), Array(18, 4), Array(39, 1),
Array(44, 1), Array(46, 2), _
' Array(57, 1), Array(63, 2), Array(74, 1), Array(81, 4), Array(101,
1), Array(126, 2), Array _
' (137, 1), Array(143, 1), Array(164, 1), Array(165, 1), Array(180,
1), Array(185, 4), Array( _
' 206, 4), Array(225, 4), Array(246, 4), Array(266, 4), Array(285,
1), Array(299, 1), Array( _
' 314, 1), Array(328, 1), Array(330, 1), Array(344, 1), Array(357,
1), Array(368, 1), Array( _
' 379, 2), Array(397, 2), Array(405, 1), Array(428, 1), Array(499,
1), Array(511, 1), Array( _
' 4512, 1), Array(4524, 1), Array(8526, 2), Array(8543, 1),
Array(8544, 1), Array(8546, 1), _
' Array(8557, 1), Array(12559, 1), Array(12560, 2), Array(12577, 1),
Array(12595, 4), Array _
' (12615, 4), Array(12635, 1), Array(12637, 2), Array(12648, 1),
Array(12679, 4), Array( _
' 12699, 1), Array(12720, 1), Array(12731, 1), Array(12762, 2),
Array(12778, 1), Array( _
' 12849, 1), Array(12862, 1), Array(16863, 1), Array(16874, 1),
Array(17004, 1), Array( _
' 17005, 4), Array(17025, 1), Array(17036, 1))

 

[Leh Hung Wong]

Hi Dag,

Thank you so much. Following is the code.



'Workbooks.OpenText FileName:=pFileName, _
' Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:= _
' Array(Array(0, 2), Array(17, 1), Array(18, 4), Array(39, 1),
Array(44, 1), Array(46, 2), _
' Array(57, 1), Array(63, 2), Array(74, 1), Array(81, 4),
Array(101, 1), Array(126, 2), Array _
' (137, 1), Array(143, 1), Array(164, 1), Array(165, 1),
Array(180, 1), Array(185, 4), Array( _
' 206, 4), Array(225, 4), Array(246, 4), Array(266, 4),
Array(285, 1), Array(299, 1), Array( _
' 314, 1), Array(328, 1), Array(330, 1), Array(344, 1),
Array(357, 1), Array(368, 1), Array( _
' 379, 2), Array(397, 2), Array(405, 1), Array(428, 1),
Array(499, 1), Array(511, 1), Array( _
' 4512, 1), Array(4524, 1), Array(8526, 2), Array(8543, 1),
Array(8544, 1), Array(8546, 1), _
' Array(8557, 1), Array(12559, 1), Array(12560, 2), Array(12577,
1), Array(12595, 4), Array _
' (12615, 4), Array(12635, 1), Array(12637, 2), Array(12648, 1),
Array(12679, 4), Array( _
' 12699, 1), Array(12720, 1), Array(12731, 1), Array(12762, 2),
Array(12778, 1), Array( _
' 12849, 1), Array(12862, 1), Array(16863, 1), Array(16874, 1),
Array(17004, 1), Array( _
' 17005, 4), Array(17025, 1), Array(17036, 1))

 

[Tom Ogilvy]

http://support.microsoft.com/?kbid=134826
XL: "Out of Memory" Message Using the OpenText Method