none
With statement issues RRS feed

  • Question

  • Excel 2013 64 bit

    So I have a program that I use a standard with statement to reference a form for cleaner code.  For some reason my program is crashing every time the with statement is hit in the program.  Nothing is out of the ordinary with the form and oddly enough if I put in a variable to catch the error number the number is 0.  So there is no legitimate error.  It is a full excel software crash where it asks if you would like excel to try and recover the document.  Does anyone have ANY clue as to why this is happening.  I can obviously bypass the issue by putting in a resume next with an error catch but I am just wondering why it is even crashing.

    Yes, this is all that is there, and it will crash each time.  

    Public sub testWithError

    With frmMainMenu .txtName = "Something" .txtAddress = "Something else" ... end with

    end sub



    • Edited by JDevsFan Thursday, November 6, 2014 3:42 PM
    Thursday, November 6, 2014 1:54 PM

All replies

  • Re:  userforms

    If you are using VBA in Excel then...
      A UserForm in Excel does not have .txtName or .txtAddress property.
      It does have a Caption property... UserForm1.Caption = "Sludge"

    If you are using VB with Excel then I can't help.
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Tuesday, November 1, 2016 3:04 AM
    Thursday, November 6, 2014 2:58 PM
  • I apologize, those were text fields on the form in the example.  It is a hypothetical form that contains two text fields, name and address.
    Thursday, November 6, 2014 3:21 PM
  • re:  Excel UserForms

    Up thru xl2010 (and probably xl2013), there are no Excel VBA Userform controls I can find that are described as or called "text fields".
    You can add a Textbox control or a Label control to an Excel Userform to contain/display text.  There is not much else that displays text.
    (of course, the userform.caption and control.captions can be used)
    If you have an Excel UserForm with some other type of control on it, that might be the reason for the crash.

    If you are programming in a non-VBA language, then you should specify it in order to get appropriate help.
    '---
    Jim Cone
    Thursday, November 6, 2014 5:05 PM
  • Ok, so obviously I have not posted much on here so I am not entirely used to this.  Yes, I am referring to text boxes.  And the above example shows text boxes with the names .txtName and .txtAddress being assigned values.  I was just using an example to show how I am using the with command in the code.  The code breaks ON the with statement, not before or after so it does not have anything to do with the actual fields, they are fine.  It is the with statement.
    • Edited by JDevsFan Thursday, November 6, 2014 5:17 PM
    Thursday, November 6, 2014 5:17 PM
  • Re:  Excel UserForm Textbox Controls

    Try changing the syntax...
    From:
      .txtName = "Something"
    To:

      .txtName.Text = "Something"
    '---
    Jim Cone

    Thursday, November 6, 2014 5:41 PM
  • But that is not the issue.  I appreciate what you are saying and that's what I thought at first, it has to be one of my form fields.  But it is not.  It errors out while stepping through the code right at the with statement.  If I put in

    Public sub testWithError

    On Error Resume Next

    With frmMainMenu 'POINT OF ERROR

    errNumTest = err.number 'returns 0, rest of the program runs flawlessly

    .txtName = "Something" .txtAddress = "Something else" ... end with

    end sub

    It goes through the whole process correct and errNumTest = 0. There is no error. So why does it crash without the resume next statement? It has nothing to

    do with the form items.

    Thursday, November 6, 2014 6:10 PM
  • re:  Excel UserForms

    The following works without interuption for me...
    (I don't have xl2013 - thank you)
    '---
    Sub TestThis()
    With frmMain
      .txtName.Text = "Sludge2"
      .txtAddress.Text = 12345
    End With
    frmMain.Show
    End Sub
    '---
    I have to assume the issue stems from xl2013 or 64 bit (both of which seem to have problems according to the posts I've read).
    You don't speed anything up, in your case, by using the With construct, so don't use it.
    There is the possibility of file/application corruption, but you will have to determine that.
    Also, I wonder if the SDI implementation may have something to do with it
    '---
    Jim Cone

    • Edited by James Cone Thursday, November 6, 2014 6:54 PM
    Thursday, November 6, 2014 6:50 PM
  • Thank you for your help though.  It is very odd as it is intermittent, we have had this version on the same computer for over 1 month and it just now does this.  I am going to toss it up to a funky oddity and go with what you said and just remove the with statements, as it is just for cosmetics that my eyes prefer while reading through code.
    Thursday, November 6, 2014 6:56 PM
  • Hi,

    What's the detail error message that you get?

    It seems that there are something error in excel.

    Please try it in word and check whether it has the same issue.

    On the other hand, please try it in another machine with some application.

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 7, 2014 10:03 AM
    Moderator
  • The screen goes to that white out color and the dialog pops up that says Microsoft Excel has stopped working, windows is checking for a solution to the problem...  This is an intermittent problem on that computer and another computer.
    Friday, November 7, 2014 1:19 PM
  • Hi JDevsFan,

    Please share the file on the OneDrive, we will check it.

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 10, 2014 9:49 AM
    Moderator
  • Hi,

    Clearly the problem is the fact that the Excel is 64 bits. I've tested in XL 2013 32 bits and no problems occured.

    A userform is like a class module and needs to be instantiated. One thing that might be happening is that you are calling a form without initiallizing it, so, i would instantiate the form before the with statement. Like this:

    Public Sub Test()
        Dim fMainMenu As frmMainMenu
        
        Set fMainMenu = New frmMainMenu
        With fMainMenu
            .txtName.Caption = "Something"
            .txtAddress.Caption = "Something else"
            .Show
        End With
        
        'clean up
        Unload fMainMenu
        Set fMainMenu = Nothing
    End Sub

    Hope it works!

    Best Regards,


    Marcelo Nogueira | CEO | Clarian Solutions | www.clarian.com.br | Excel Development & Trainning


    Thursday, November 13, 2014 2:32 AM