Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Weird random problem with macro (Excel 2000 SR-1)

    Hi everyone,

    I have a very weird probelm with a macro I created a while ago in Excel 97. I upgraded my Office version to 2000 SR-1 about a month ago, and until now the macro was running fine. Yesterday and today, however, I keep getting random errors when running the macro, random meaning that the every time I run the macro, I get either an error at a DIFFERENT place in the macro, or no errors at all! In 3 tests, I got once a problem at the line "Sheets("NITEranges").Delete" (at the end of the macro), once at the very beginning "cel.Activate" and once it ran fine!! I'm pulling my hair already, as I can't figure out what is wrong! I am attaching the macro in a small txt file, hope anyone can come up with the reason for this weirdness and randomness of the errors!
    Btw, I sent this file to a colleauge in another country, and she told me that she has also random errors on it, and once while running it, it even crashed her Excel, giving this error along the way (she has Excel 2000, SP-3):

    EXCEL caused an invalid page fault in
    module EXCEL.EXE at 0187:3008ee48.
    Registers:.....

    Thanks,
    K

    P.S. Just had to add this, as it is getting way too funny: ran a further test right now, and the macro ran just fine again...until I went to Windows Explorer and checked the file size create...75Mb!!! (my original file was less than 1 Mb)!

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Weird random problem with macro (Excel 2000 SR-1)

    This sounds like a file corruption.

    Try if any of these help to resolve the issue:

    - make a backup copy of the file <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    - Download ROb Bovey's code cleaner from the Excel MVP page below and use it on your project.

    - Save the workbook as filetype html
    - close the file and reload the html version.
    - save-as normal Excel workbook again.

    Now check if all is well.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Weird random problem with macro (Excel 2000 SR-1)

    Jan Karel Pieterse has pointed you at possible causes and solutions.


    Your macro keeps on selecting ranges. Macros usually run better if you use ranges instead of selections. I have attached a modified version of your macro.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Weird random problem with macro (Excel 2000 SR-1)

    I ran the codecleaner and followed you steps, when I now saved the file (didn't give me error messages this time) I got a filesize of 21 Mb (down from 75, but still way too much compared to the < 1M I was having before. I can't send it like this to the client unfortunately, not with this file size...
    Does it mean the file has to be rebuilt from scratch. I suppose? It's a lot of work in there and I was trying to avoid that if possible...

    Regards,
    K

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Weird random problem with macro (Excel 2000 SR-1)

    You could check all sheets of the file:

    - see if control-end gets you where you would expect (if not, delete obsolete rows and columns)
    - see if there are large ranges which have (unneeded) special formatting (deleting unneeded rows and columns sometimes clears that too)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    New Lounger
    Join Date
    Apr 2003
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Weird random problem with macro (Excel 2000 SR-1)

    I think you can solve the problem of growing files by exporting the offending module and reimporting it.

Posting Permissions

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