Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB edit not responding (Excel 2000 SP-3)

    I'm trying to debug some VB code in the VB editor and it's refusing to step through the code. When I press F8 the cursor moves to the next line of code and highlights in yellow as I expect, but the code is not executed. When I click on the run button, it doesn't.
    The code is stepping out of a loop prematurely but never at the same record and I'm trying to find out why.
    Can there be a relationship between the behaviour of VBE and the code or is there something wrong with my installation?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VB edit not responding (Excel 2000 SP-3)

    What are the indications that it doesn't execute?

    Add a line like:
    Msgbox "test"

    In the code and run it. Does the msgbox popup? If so the code is running. Perhaps the screen is not updating when you step through
    (do you have a line:
    Application.screenupdating = false
    in the code)

    Steve

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

    Re: VB edit not responding (Excel 2000 SP-3)

    I have seen this happen sometimes, especially inside user defined function containing an error, called from a worksheet cell.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB edit not responding (Excel 2000 SP-3)

    Steve,
    I have the VBE window restored so I can see the spreadsheet behind it. One of the lines of code is supposed to Activate a different sheet and it does not.
    Screen updating is not off. The macro is running fine up to some indeterminate point, so I am watching it do its thing.
    There is no user-defined function as suggested by Jan.

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

    Re: VB edit not responding (Excel 2000 SP-3)

    Perhaps you could attach a (zipped) copy of the workbook. Remove any sensitive information from the copy.

  6. #6
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB edit not responding (Excel 2000 SP-3)

    Hans,
    Thank you for your interest. The original file has a great deal of information I cannot share, so I have made changes that will hopefully not mask the problem.

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

    Re: VB edit not responding (Excel 2000 SP-3)

    What macro is exhibiting the problem?

    BTW,

    Avoid all of the screen flicker by using

    Application.Screenupdating=False
    at the start
    and
    Application.Screenupdating=True
    at the end of your macro.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB edit not responding (Excel 2000 SP-3)

    BrokerFeesPartTwo is the problem.
    In some cases it is reassuring to the user to see the macro do its thing. I use the ScreenUpdating on longer macros and after the user is familiar with it.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VB edit not responding (Excel 2000 SP-3)

    Could you be more specific about where the problem occurs (which loop) and what is the "sympton" of the code "not working"

    Steve

  10. #10
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB edit not responding (Excel 2000 SP-3)

    Steve,
    I don't think I can be specific. The original post describes the problem. There is no particular point in the code at which the problem occurs , but it's during the "For Each cell In FirmRange" loop that the code just seems to cease functioning.

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

    Re: VB edit not responding (Excel 2000 SP-3)

    You use "On Error Resume Next" twice in the code, when you want to test if a folder exists. You don't have "On Error GoTo <Label>" or "On Error GoTo 0" after that, so errors in the rest of the code will go unchecked. You should restore error handling before the line beginning with "wbkFees.SaveAs".

  12. #12
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB edit not responding (Excel 2000 SP-3)

    Hans,
    Your error-handling advice is well-taken. I will try that.

  13. #13
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB edit not responding (Excel 2000 SP-3)

    Hans,
    Following your post about my error-trapping I reviewed and revised my process and moved the folder checking out of the loop. Now that other errors are no longer ignored, a new problem has been revealed! On the 20th record, the advanced filter fails. Have I overloaded a stack somewhere or something? Do you have the time or patience to look at my revised code?

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VB edit not responding (Excel 2000 SP-3)

    Could you tell us what you are running and what line gives the error and what are the values of the variabls in the line of code.

    Steve

  15. #15
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB edit not responding (Excel 2000 SP-3)

    Steve,
    The macro is "BrokerFeesPartTwo" and it's breaking at line 158 with "AdvancedFilter method of Range class failed"
    Thanks

Page 1 of 2 12 LastLast

Posting Permissions

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