I have a dynamic array goShareInfo, which contains a dynamic array of
Type PriceInfo. In order for me to be able to save/read the array
properly, I have to save the dimensions of goShareInfo and set up the
array next time in the LoadShares procedure when I reload the
spreadsheet. Why do I have to do this when each element of IndexStruct
also contains an array of PriceInfo, but I don't have to do the same for
these - the arrays seem to get set up automatically.

I am using Office 2003 and if try and reload the array on the fly excel
just hangs

Type PriceInfo
ShareDate As Date
SharePrice As Double
End Type

Type IndexStruct
Index As String
Epic As String
FailureCount As Long
ShareName As String
zzNumDisp As Integer
ShareDets() As PriceInfo
End Type

Public goShareInfo() As IndexStruct



Sub LoadShares()

Dim liFile As Integer
Dim liNumShares As Long
Dim lsPath As String


lsPath = ThisWorkbook.Path & "\" & cSaveFile
liFile = FreeFile
Open lsPath For Binary As liFile
Get liFile, , liNumShares
ReDim goShareInfo(liNumShares)
Get liFile, , goShareInfo
Close liFile

End Sub


Sub SaveShares()

Dim liFile As Integer
Dim liNumShares As Long
Dim lsPath As String


lsPath = ThisWorkbook.Path & "\" & cSaveFile
If Dir(lsPath) < "" Then Kill (lsPath)
liFile = FreeFile
Open lsPath For Binary As liFile
liNumShares = UBound(goShareInfo)
Put liFile, , liNumShares
Put liFile, , goShareInfo
Close liFile

End Sub
--
Mike News

In message
at 00:35:27 on Sun, 9 Sep 2007, Mike
S wrote
I have a dynamic array goShareInfo, which contains a dynamic array of
Type PriceInfo. In order for me to be able to save/read the array
properly, I have to save the dimensions of goShareInfo and set up the
array next time in the LoadShares procedure when I reload the
spreadsheet. Why do I have to do this when each element of IndexStruct
also contains an array of PriceInfo, but I don't have to do the same for
these - the arrays seem to get set up automatically.

I am using Office 2003 and if try and reload the array on the fly excel
just hangs

Type PriceInfo
ShareDate As Date
SharePrice As Double
End Type

Type IndexStruct
Index As String
Epic As String
FailureCount As Long
ShareName As String
zzNumDisp As Integer
ShareDets() As PriceInfo
End Type

Public goShareInfo() As IndexStruct



Sub LoadShares()

Dim liFile As Integer
Dim liNumShares As Long
Dim lsPath As String


lsPath = ThisWorkbook.Path & "\" & cSaveFile
liFile = FreeFile
Open lsPath For Binary As liFile
Get liFile, , liNumShares
ReDim goShareInfo(liNumShares)
Get liFile, , goShareInfo
Close liFile

End Sub


Sub SaveShares()

Dim liFile As Integer
Dim liNumShares As Long
Dim lsPath As String


lsPath = ThisWorkbook.Path & "\" & cSaveFile
If Dir(lsPath) < "" Then Kill (lsPath)
liFile = FreeFile
Open lsPath For Binary As liFile
liNumShares = UBound(goShareInfo)
Put liFile, , liNumShares
Put liFile, , goShareInfo
Close liFile

End Sub

I've played around with this and been more patient. What is happening
is that memory usage is gradually rising, up to the point where I get an
"Out Of Memory" error. goShareInfo had 102 elements and each could
contain up to 5 years worth of ShareDets rows i.e. typically 1260 rows.

I reduced the number of ShareDets records to about 1 years worth (i.e.
252 records) for the same 102 shares, and a similar thing was
happening. Memory usage would shoot up over a 30-60 second period, but
eventually I would get control of the program back. The array is
correctly set up with the 102 rows in goShareInfo, the contents are all
OK look, and the memory usage would stay at its high level

By dimensioning the goShareInfo array up front, the data loads in less
than a second and memory usage hardly alters. So why is this the case?
--
Mike News

In message
at 18:50:51 on Tue, 11 Sep 2007, Mike
S wrote
By dimensioning the goShareInfo array up front, the data loads in less
than a second and memory usage hardly alters. So why is this the case?

Sorry I was lying. The array must have been initialised when I was
playing around with it

In my example of reading liNumShares (whose value is 102), and then
using ReDim goShares(liNumShares), this causes the memory usage to go
up. However if I manually RedDim goShares(102) this works fine.

Very strange indeed
--
Mike News

In message
at 20:55:27 on Thu, 13 Sep 2007, Mike
S wrote
Sorry I was lying. The array must have been initialised when I was
playing around with it

In my example of reading liNumShares (whose value is 102), and then
using ReDim goShares(liNumShares), this causes the memory usage to go
up. However if I manually RedDim goShares(102) this works fine.

Very strange indeed

I think I've found the solution to this now
--
Mike News

In message
at 17:55:16 on Fri, 14 Sep 2007, Mike
S wrote
In message
at 20:55:27 on Thu, 13 Sep 2007, Mike
wrote
Sorry I was lying. The array must have been initialised when I was
playing around with it

In my example of reading liNumShares (whose value is 102), and then
using ReDim goShares(liNumShares), this causes the memory usage to go
up. However if I manually RedDim goShares(102) this works fine.

Very strange indeed

I think I've found the solution to this now

Soln to upgrade to patch to SP2 wasn't correct. I changed my num vars
from Long's to Integer's even though UBound returns a type long - this
seems to be what does the trick, although I haven't tried it enough
times to give me confidence yet
--
Mike News