+ Reply to Thread
Results 1 to 8 of 8

Macro works fine in Excel 2003 but not in 2007

  1. #1
    Registered User
    Join Date
    01-22-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Macro works fine in Excel 2003 but not in 2007

    I have a spreadsheet designed to adjust golf handicaps for a small group of golfing friends and record the results in an archive. It worked fine in Excel 2003 but crashes on the second use in Excel 2007.
    I have attached the spreadsheet. To see the problem go to Sheet "Scores" and click on Enter Sample Score, then "Update Handicap". The score and revised handicap will be recorded in the archive sheet.
    Do the same thing again and the programme crashes with run-time error 1004. I can't see a problem?
    I have looked at lots of forums and the best I can find is reference to Excel 2007 creating some "hidden variables" that don't clear properly but I can't understand it and can't see where my spreadsheet might do this.
    Any help will be appreciated.
    Attached Files Attached Files
    Last edited by jackdebnam; 01-24-2011 at 10:10 AM.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Macro works fine in Excel 2003 but not in 2007

    It doesn't crash for me. When you get the error, which line of code is highlighted when you hit the debug button?
    Martin

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Macro works fine in Excel 2003 but not in 2007

    I think the problem is because you are using .selection and therefore activating sheets unnecessarily,and the code is running ahead of the application.

    This is the first failure
    The fault occurs in Sub FormatArchive
    Line
    Please Login or Register  to view this content.
    When you run the debugger the code will run on without any adjustment.

    "Fixing" this sub then moves the problem to the next similarly designed sub.

    Getting rid of Selection in all instances will almost certainly cure this problem.

    Maybe some one else can follow this up, I will look back later and have a go if your problem isn't solved.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Macro works fine in Excel 2003 but not in 2007

    HI Jack,

    After looking through your code I have two suggestions.

    You have a lot of "Application.Run" commands that go to subroutines that are in your modules. I believe you should use the "Call" command instead for these.

    Secondly you have many SendKeys commands. I've found that with each SendKeys I had to follow it with a DoEvents command. http://support.microsoft.com/kb/179987 The idea is that you send a keystroke to the keyboard buffer but, because Windows may be working on another program running in the background, the keystroke goes to the wrong application or just gets lost. DoEvents tells VBA to wait until Windows OS comes back to this program (Excel) before continuing on with the VBA code.

    Also.
    You should remove all the Modules that have no code in them. Right click on the Module Name and Remove. You can (my style) put multiple subroutines in the same module to simplify your code.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Macro works fine in Excel 2003 but not in 2007

    Try this workbook, I'm still trying to clean up your macros, but check this so far.

    Let me know if you find any mistakes I might have made, so they can be corrected now.

    I haven't checked it in 2003 yet.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-22-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Macro works fine in Excel 2003 but not in 2007

    Thanks to the guys who responded particularly Marcol. The amended spreadsheet works brilliantly and all I need to do now is understand the problem. Back to school then. Thanks people. Jack

  7. #7
    Registered User
    Join Date
    01-22-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Smile Re: Macro works fine in Excel 2003 but not in 2007

    All sorted thanks.

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Macro works fine in Excel 2003 but not in 2007

    Avoid using Select and Activate wherever possible, they are seldom necessary.

    You also had, and possibly still have, many repeated calls, again avoid this it only slows your code for no good reason.

    The partly done workbook should point you in the right direction.

    As Marvin says look at your SendKeys calls, I didn't get 'round to figuring out why you needed them.

    Try organizing your subs in fewer modules and give them some useful names, grouping similar subs, etc makes them easier to find and edit, again I started to do this, but they could still be better organised.

    Glad to have helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1