Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add in hangs (VBA in Excel 2003)

    Hi all

    It's a long time since I last posted.

    I have a very peculiar problem with an add in I am using.
    The add in takes data input by the user on a .xls sheet (saved as an .xlt) then opens a Log, puts the data in the Log, saves the log, then saves the form for later further processing in the same manner the add in then closes the form, the log and the instance of Excel it initiated.
    This was all working well until I attempted to add a few more simple features and in the process set some breakpoints to see what was happening.
    When I added the breakpoints I assumed that when the add in closed everything the breakpoints would be cleared.
    Next time I ran the system the breakpoints were still active but were no longer visible in the code and could not be cleared.
    I have cut and pasted all code to a new module and this appears to have cleared the breakpoints, however this seems temporary as the program still halts at a save Log command, pressing Esc selecting Debug and selecting the continue arrow resumes execution and everything works.

    What the hell is going on !!!!! ????

    Is there any way to clear these invisible breakpoints ?

    Any Help would be greatly appreciated

    Ralph Davies
    Sydney
    Australia

  2. #2
    New Lounger
    Join Date
    Apr 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add in hangs (VBA in Excel 2003)

    Ralph,
    This appears to be a hardware error - between the Keyboard and the screen!!!
    ( aka operator error) !

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Add in hangs (VBA in Excel 2003)

    Is it possible that you have the same code in two places and one set is running but you are editing the other?
    How are your breakpoints set?

    I think we would need to see the code to work out where the breakpoints are coming from but perhaps you could try to run it on another machine to see if the same problem occurs.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add in hangs (VBA in Excel 2003)

    No
    I assure you this is a real problerm

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add in hangs (VBA in Excel 2003)

    Thanks for the Reply Andrew

    1. I have checked that only one instance of the xla code is in use.
    2. I set breakpoints in the usual way, by clicking in the left margin of the Visual Basic Editor.
    3. The code is extensive and all works fine so I don't think I will post it until I get some more feedback.
    4. Tried running it on another machine and got the same result.

    Any other Ideas would be appreciated.

    Thanks again for the reply.

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Add in hangs (VBA in Excel 2003)

    I realize it's no help to you at the moment, but I always set "breakpoints" with the Stop statement. Then I can run a Find acorss the project and get rid of them when I'm done with them. I don't know how you can search for the other kind of breakpoints. I mean, other than the way you are now, running the code.

    When good code modules go bad, the VBE equivalent of power-cycling your project is to export them, remove them, and re-import them. If nothing else works, try it.

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

    Re: Add in hangs (VBA in Excel 2003)

    This sounds like your code is fouled up a bit.

    First thing to try: Run Rob Bovey's code cleaner .

    Next thing: Make sure your modules are not too large. When you reach Approximately 6400 lines things get flaky.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add in hangs (VBA in Excel 2003)

    Thanks for the reply Jefferson

    I am just back from a break.
    I tried exporting the Module then re-importing it and the problem still exists.
    I will try the Code Cleaner suggested by Jan Pieterse

    Thanks again for the Help

    Ralph
    Sydney
    Australia

  9. #9
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add in hangs (VBA in Excel 2003)

    Hi Jan

    Just got back from a break.
    thank you for your reply.
    The code cleaner looks good but I am receiving the following error message when I attempt to run it.

    Run-time error '1004'
    Programmatic access to Visual Basic Project is not trusted.

    I have looked in the help file for the Code Cleaner and cannot find anything about this error.

    Any ideas on how to get rid of this error ?

    Thanks again

  10. #10
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add in hangs (VBA in Excel 2003)

    Hi all

    I just discovered some new information about this problem and it is really, really weird.

    The program crashes AM time and runs PM time.
    I doubt that this gives any meaningful info but it is so strange I thought it might mean something ?

    Any ideas ?

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Add in hangs (VBA in Excel 2003)

    > The program crashes AM time and runs PM time.

    Are you manipulating date or date/time values?

  12. #12
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add in hangs (VBA in Excel 2003)

    Hi Jefferson
    Some date entries are involved of the form
    Cells(x,y).value=format(Now(),"d mmm yyyy")
    No time manipulation

  13. #13
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Add in hangs (VBA in Excel 2003)

    To allow programmatic access to the Visual Basic PRoject

    Tools > Macro > Security... > Trusted Sources > check the box marked "Trust access to Visual Basic Project"

    I would recommend that you clear this after running the cleaner, this protects you from viruses that try to create VBA.

    StuartR

  14. #14
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Add in hangs (VBA in Excel 2003)

    I was thinking more of casting a String to a Date, or doing Date math or comparisons with something that started as a string. However, since you're going from Excel to text, rather than the other way around, maybe you have no need to do this. (I hardly work with Excel or Excel VBA at all.)

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Add in hangs (VBA in Excel 2003)

    I don't have a clue about your locked-up breakpoints, and I may be out of my depth, but what object is "cells(x, y)" the child of? In other words is it fully qualified to something like:

    Thisworkbook.Worksheets("mysheet').Cells(x, y)

    or

    Activesheet.Cells(x, y)

    ... etc., and does coding it like this makes any difference?

    With <worksheetobject>.Cells(x, y)
    .Value = Date
    .NumberFormat = "d mmm yyyy"
    End With
    -John ... I float in liquid gardens
    UTC -7ąDS

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
  •