Vba Editor Freezing

  • hello,
    I am having problems with the VBA Editor freezing at the most inconvenient of times when writing code and then clicking to the Sheet I'm working on, it just freezes up the whole of Excel and it has to be shut down and opened up again.
    It seems like a memory problem at first, but there is sufficient memory
    ( 768Mgs, OS is XP Pro, Excel 2000, 9.02).


    The work is not lost becuase I'm able to Save even at PC Re-boot, fortunately.


    I've tested on 2 different PC's and the same happens. The only way to avoid the freeze it seems, is before I switch to view a worksheet, is to Save then click off VBA Editor and re-open.
    When doing so, another symptom is it seems a huge amount of memory is freed up, ( pardon the terminology)


    There is also a message that pops up when re-opening that same workbook that there are Formulas Linked to another workbook, but unable to find any after tediously searching every worksheet by using "Find, Look in Formula" there is nothing found, yet.


    This freezing may happen twice a minute whilst pointing the mouse cursor from any VBA Edit window or worksheet and it's getting to a point of repetitiously having to Save, click off VBA Editor, and Click back on again each time I need to look at a respective Worksheet.
    This only happens when I am doing fixes in VBA Editor, but whilst the workbook is running with no VBA editor opened, the very very long macro works fine.
    I hope the above symptoms will help solve this time consuming issue.
    Any suggestions would be appreciated,
    Thanks

  • Re: Vba Editor Freezing


    A little hard to guess without seeing the workbook maybe you could post it?


    I seem to remember having a similar problem when I had links to workbooks on a network. Maybe you could try edit - links. Break all links?


    HTH,


    Sam

  • Re: Vba Editor Freezing


    Reafidy
    Thanks for the reply, it is not possible to post this WkBook, it's my entire project.
    I can only post the symptoms hoping to find out why.
    When I look in Edit/Links, there IS a link reference ( Source File) to a previous workbook but does not seem to Find in the opened workbook.
    Is there another way to Find and eliminate this Source Reference ?

  • Re: Vba Editor Freezing


    It's really difficult to guess without knowing more. Aside from doing what the other people suggested, do you have a lot of arrays or other memory-intensive formulas (sum products, vlookups, etc.)? That might be consuming a lot of resources. Any On-Time macros? What about Hidden or even Very Hidden worksheets? Sometimes it helps if you paste all your code into Notepad, delete your modules, save your workbook, then create new modules and paste the code back in (don't forget to make a back-up copy first). Could be a corrupt workbook too, in which case you'd need to copy everything to a new one.

  • Re: Vba Editor Freezing


    Thanks for all those clues, it's a mix of a few things mentioned, but it became apparent it's something chewing up memory, process of elimination should see it solved I think.
    will post back as soon as I have it sorted.


    cheers

  • Re: Vba Editor Freezing


    OK, this glitch was fixed, I never actually found where exactly the problem was, but deleted Sheet by Sheet one at a time and it eventually vanished then inserted a new sheet.

Participate now!

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