Is there a way to delete the beginning part of an array so it doesn't overflow?

omahadivision · #1 01-15-2014, 08:08 PM

Thanks! While reprogramming, I found another problem: I had dimmed "count" as an integer, and apparently Excel doesn't think there's integers above 30,000! I now have the program read the file twice by closing and reopening it and only put the end of the file in the array, and I got it to run on a 70k line file on a seven year old laptop in only a few seconds.

omahadivision · #2 01-15-2014, 08:20 PM

I also thought that I would post the code in case anyone else in the future tries a similar method:

Counts whole file (megacount is the new counter I made to create the entire file)

Code:

Do While Not fo.AtEndOfStream 'Counts all lines
fo.readline
Megacount = Megacount + 1
Loop

Close and reopen:

Code:

fo.Close
Set fo = fso.OpenTextFile(FName, 1)

Getting the end of the array:

Code:

countagain=0 'This is required to count what line the file is on during the second run
Count=0

If Megacount < 10000 Then 'For small files under 10000 lines, entire array is imported
       Do While Not fo.AtEndOfStream 'Puts the rest of the file into an array after title line
       Count = Count + 1
       ReDim Preserve AllArray(Count)
       AllArray(Count) = fo.readline
       Loop
       
    Else 'Bigger files above 10000 lines.  'Cycles through beginning of file without putting anything in array
        Do Until Countagain > Megacount - 9999
        fo.readline
        Countagain = Countagain + 1
        Loop
        
        Do While Not fo.AtEndOfStream 'Actually puts things in array, Count is the array counter
        ReDim Preserve AllArray(Count)
        AllArray(Count) = fo.readline
        Count = Count + 1
        Loop
 End If

The advantage of using three counters (Count, countagain, megacount) instead of two is that the first imported line (10,000 from end of file) is in AllArray(0), and the last line is in AllArray(Count)

BobBridges · #3 01-15-2014, 11:22 PM

I kinda thought that reading the file twice wouldn't hurt you. If it gets to be more than a few megs in size, then it may be time to try a more complex method.

By the way, about integers greater than 32K; I think you'll find — let me look it up just to be sure .... yes, the Integer data type is stored in two bytes of storage and can therefore go "from -32,768 to 32,767". But the Long data type uses a full word (four bytes) of storage and can hold values "from -2,147,483,648 to 2,147,483,647". And in Office 2010 there's even a LongLong type that doubles storage yet again (8 bytes, 64 bits) and holds "-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807". So you can probably hold more data in your arrays than you feared.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Delete bookmark which doesn't have a name	rarulkumar	Word VBA	2	04-19-2013 03:19 AM
Control direction of resizing textarea on overflow	noodle	PowerPoint	0	03-31-2013 04:52 AM
Convert String Array to Integer Array from a User Input?	tinfanide	Excel Programming	4	12-26-2012 08:56 PM
Files beginning ~$	RobinW	Word	1	06-28-2012 05:35 PM
Before the table but after beginning of doc?	Jaymond Flurrie	Word VBA	2	08-16-2011 06:27 AM