Announcement

Collapse
No announcement yet.

VBA Macro Causes Crash When Debugged

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Macro Causes Crash When Debugged



    Hello,

    I'll pay 50$ to anyone that can state a solid code to overcome such situation.

    It's best to quote the problem itself first

    Please see for the explanation of the problem itself.
    http://www.excelforum.com/excel-prog...mpilation.html
    and also http://forums.microsoft.com/MSDN/Sho...21009&SiteID=1 and likely other places

    What I'm expecting from you is to find a solid solution to this and avoid excel crash while allowing a safe save with compiled VBA + granting that excel will not crash on next time reopened and ran the VB of the saved file.

    Posting my specific code won't help because it's actually caused by this problem (very sure).

    Hopefully I made myself clear.

  • #2
    Re: VBA Macro Causes Crash When Debugged

    Posting my specific code won't help because it's actually caused by this problem (very sure).
    I disagree that posting code "won't help".


    See Diagnose and repair crashing Office programs by using Office Diagnostics in your 2007 help.

    Comment


    • #3
      Re: VBA Macro Causes Crash When Debugged

      Tried diagnosis, didn't find anything problematic, and told me download some other file and in a week it "might" do something useful. SO for now I'm skipping that.

      You made me suspect myself, so I tried blanking out Userform_initialize(), and this time excel didn't crash but, if I add something like below (just 1 line) to Private Sub UserForm_Initialize()

      Code:
      ComboBox107.Value = ThisWorkbook.Worksheets("OEC").Range("CU32").Value
      just to update the value of a combobox, this thing again happened. But remeber, when it's not compiled it works perfectly well, but after compiled -> saved -> reopened -> run the compiled VB it fails to do it. Below is what happens when combobox107 value change. I can assure this is the end of the triggered actions of ran line of the initalizing action.

      Code:
      Private Sub ComboBox107_Change()
      If IsError(ComboBox107) = False Then
      ThisWorkbook.Worksheets("OEC").Range("CU32").Value = ComboBox107.Value
      End If
      End Sub

      Comment


      • #4
        Re: VBA Macro Causes Crash When Debugged

        and told me download some other file and in a week it "might" do something useful. SO for now I'm skipping that.
        Why?


        Why not Name the cell and use the ControlSource Property of the ComboBox to link the cell and the ComboBox?

        Failing that, use
        Code:
        Private Sub ComboBox107_Change()
            If ComboBox107.ListIndex > -1 Then
                Range("MyName").Value = ComboBox107.Value
            End If
        End Sub
        Where MyName is the Named Cell.

        Comment


        • #5
          Re: VBA Macro Causes Crash When Debugged

          Dave, tried using controlsource property instead of updating with initalize, it didn't crash.

          Another thing, I've cleared Userform_initialize() and instead used Userform Activate and placed a timer to wait 10 seconds to make sure every objet to be loaded, and at the end of the timer it runs the sub that contains the stuff initialise had. So I got this error:

          Runtime Error
          '-2147417848(80010108)':
          Automation Error
          The Object invoked has disconneted from its clients

          Then crashed.

          I'm having trouble understanding why this thing works flawlessly, when run from decompiled; on the other hand, it fails miserably when the working file compiled -> saved -> closed -> reopened and run. I would be glad if you can enlighten me about this.

          Regarding your solution, kindly, wait for sometime before I actually switch everything to mostly using controlsource property. If it works, I'll immediatly complete the transaction.

          Comment


          • #6
            Re: VBA Macro Causes Crash When Debugged

            UserForum_Activate will fire each and every time the form is activated. I don't think you want that. If you are using the ControlSource, any other code linking the cell and Control is superfluous.

            As for Compiling VBA code in Excel. When any code in any Module is run the entire Module is Compiled BEFORE the code is run. In other words, you code will always Compile BEFORE it's run.

            You might also consider Exporting ALL Modules and UserForms to you hard drive. Then Import them into a new Workbook in case of corruption.

            and placed a timer to wait 10 seconds to make sure every objet to be loaded
            Highly unlikely any timer or pause is required.

            Comment


            • #7
              Re: VBA Macro Causes Crash When Debugged

              How will control source property ensure for example :

              a textbox which must only have numbers that are > 0
              or
              another textbox which only must have things in a specific array.

              Also, is there another way from VB point of view to avoid source intake from a cell containing N/A or NAME and display an error message in it instead stop functioning?

              Comment


              • #8
                Re: VBA Macro Causes Crash When Debugged

                Let's get this payment out the way 1st and then I will help you on your new questions.

                Comment


                • #9
                  Re: VBA Macro Causes Crash When Debugged

                  Originally posted by Dave Hawley
                  Let's get this payment out the way 1st and then I will help you on your new questions.
                  Dear Mr.Dave,

                  Right now I can not use the control source property to replace the method I've been using on the Userform; so please, kindly, help me on this and so I can test it (replacing it will take a full business day or two ). The question I asked was to have an operational VBA in EXCEL,while finding a solution to the problem I've been facing. Sorry dave, I can not pay for a thing without getting a value or a guarantee out of it.



                  Message me your account details ( I use PayPaL ), and please remember the transaction will be completed when this works.

                  Comment


                  • #10
                    Re: VBA Macro Causes Crash When Debugged

                    Right now I can not use the control source property to replace the method I've been using on the Userform
                    Why not use the code I provided then?

                    To validate the TextBox Controls for only numbers, see Ensure Numbers Only In TextBox

                    Comment


                    • #11
                      Re: VBA Macro Causes Crash When Debugged

                      I had a very little sleep, and I thought you gave up. Tried to narrow to very specific problem but it seems there are alot others and there is no significant information of corruption of file from Microsoft.

                      Anyway, I already knew that when I run the userform from the visual basic editor design page, it work perfectly as intended. So I gave up fixing the problem and rather went for simulating the action I'm doing manually, with a macro.


                      Code:
                      Application.VBE.ActiveVBProject.VBComponents.Item("Userform1").codemodule.CodePane.Show
                      Application.VBE.MainWindow.Visible = False
                      UserForm1.Show
                      can also be done with .sendkeys but this's far better. Hopefully this helps other people that suffer from the same thing and can not just go back and start over a very big project.

                      Thanks for your effort Dave.

                      Comment


                      • #12


                        Re: VBA Macro Causes Crash When Debugged

                        Pay me at [email protected] please

                        Comment

                        Working...
                        X