Out of Memory Error

  • I am trying to create an employee survey which I intend to email to 5000 employees for completion and then get it emailed back to me for analysis

    I have a workbook with very little content( it just stores the responsesfrom the userform) and 1 userform

    The userfom has a multipage control, there are 11 pages, each page having :-

    25 frames, with 1 label and 4 option buttons in each frame
    and 1 command button per page.

    and now surprise surprise I am getting an out of memory error

    What can I do to get past this?

    Thanks Graham

  • Hi Wistow

    When do you get the memory problem? Does happen when you first Show the UserForm, or after working with it for some time?

    Basically as much detail as possible will help us help you.

  • PC = AMD 1700 with 512mb memory, 40gb Disk with 20gb free

    Windows XP pro
    TSR apps pop-up-ad filter and SpIDer Guard running

    Only Excel and VBA interface

    The problem ocours in the VBA interface in design mode when swapping between code and drawing of userform.

    I copied the file and then started taking pages off the multipage control and when I got to 7 of the 11 the fault stopped

    I don't want to go to separate userforms as selections made on the first page selects which pages show and takes the user through them which is easy on a multipage

    Thanks Graham

  • Hi Graham,

    Memoryhandling & Excel is not always a "happy" marriage...

    So even if we have 1 GB RAM and 256 MB graphic memory we can still receive "out of...."-message...

    XL put som different demands on graphics memory as well as on the availablel RAM due to memory leak and to the fact that XL use raster graphics.

    Following approach may work although is a long "workaround".

    - Open the original workbook
    - Open the new workbook
    - Copy a small amount of objects and then
    - close both workbooks and XL
    - repeat the procedure.

    Good Luck!

    ...and let see if Jack have some ace upon his sleeve :cool:

  • OK let s look simply

    From -

    Re : I copied the file and then started taking pages off the multipage control and when I got to 7 of the 11 the fault stopped

    Get back to this position and save:
    Add and additional tab - design as you would really have the form / tab and save

    Now close Excel and reopen this same workbook and run your procedure

    This will be with 8, so you get he same error?

    Kindest possible regards

    Jack in the UK

  • Hi Jack

    Thanks for the response

    Followed your suggestion, and I can add page 8 and more without any problem, however the moment I add the group of option buttons I get the error again.


  • OK forget the group option, try all but in that tab.

    and add 9 10 11 without the option group

    Let me know if this works i might have a few ideas, no promises i can not see the wkbk!

    Also if this fails, go back to 7 all in and add 8 9 10 11 with out the magic and see if all works,

    Jack in the UK

  • I can add as many pages as I want up to and including page 11.

    If I add option buttons to any pages 8 to 11 I get the error

    Is there a maximun number of objects/items or maximun number of 1 type of object that VBA or excel can handle?


  • There is:

    1 Userform having:-
    1 multipage control with 11 pages
    page 1 has:-
    10 labels, 10 textboxes, 3 frames 4option buttons, 9 checkboxes and 1 command button
    pages 2-10 have on each page:-
    20 frames, 20 labels, 80 option buttons and 1 command button

    The spread sheet has 3 sheets with no more than 300 cells containing data no formulas or formating

    In all a lot of objects


  • Since I haven't seen the wrkbook, let me see if this helps or not:

    Have you considered breaking it up into two (or more) seperate multipage controls? It makes it mildly more difficult to hide and show the appropriate control when needed, but it might help with the issue if the multipage container is running out of memory instead of excel.

    From the sounds of it, you might have the exact same controls on multiple pages, just with different questions/option values in each.

    If this is the case, then you might consider switching to using either a tabcontrol (if the user is supposed to see the tabs) or just change out the text/values in each frame when needed, keeping the responses neatly in arrays.

    You could keep the prompts hardcoded in your code, in an ini file, xml file, or somewhere else... that doesnt matter. What you dont want is over 1000 seperate controls all housed by other control... it just wont likely work due to the memory problem.

    the other possibility if the controls are different sizes and shapes, etc. is to build the controls onto the form dynamically... you just need to save a few properties worth of information about each control as to where it sits on the page... this would be more of a pain than if the controls never chaged except their values, but it is not too difficult. Perhaps if we could see the example maybe we could get you started on something...

  • Hi Putman Thanks for the reply

    Yes I have looked at breaking it up and XLDennis has been helping me with code for loading forms using a variable name

    Yes 10 of the pages are identical except for the questions(lables) against the option buttons the responses from the user and the title.
    I have not looked at a tabcontrol, I will look in the help file and I had not even thought about just reloading the controls on the same page, the questions are currently on a sheet in the workbook and loaded when the form is initalized

    Back to the drawing board

    thanks Graham

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!