Results 1 to 3 of 3
  1. #1
    Matt Jones

    VBA Array issues

    Hi All
    I am not very proficient in VBA but have come across an issue where in xl2000(service pack 1 i think) i have written an array where it loads up about 6000x120 data points from one workbook and then dumps what i want into another workbook.

    Problem is it gets to stage where it opens up last workbook (3 in total) to loop through the data and pull out what i want but then stalls and crashes with an 'out of memory issue but my system has ~512mb ram and pagefile etc ~1.5gig.

    I know xl97 had issue where array elements had max of 4-6k but if i Redim the array why doesnt it clear it out and start again ? It seems like its either not redim'ing or maybe when it pastes the results it overloads the clipboard ??

    Any ideas ? I can attach the 'dodgy' code if needed ?

    Thanks in advance for any assistance !


  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 187 Times in 173 Posts

    Re: VBA Array issues

    Hi Matt,
    I think it would be helpful if you did post the code - there may be something specific in there that people have had problems with before.

    Microsoft MVP - Excel

  3. #3
    Matt Jones

    Re: VBA Array issues


    Here it goes basically my 'button' calls these procedures...

    Private Sub CommandButton1_Click()
    Call get_region_and_sector_info
    Call save_data
    End Sub


    See attached w'book with code, i can paste it here if preferred but pretty big and ugly ?

    Basically excel/vba ? falls over with an out of memory error when trying to open the 3rd workbook and load the data into the array before 'spitting out' what i want from it into the current workbook.

    Sorry about the delay in posting, hope it make some sort of sense ?



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts