Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Question (2003 SP2)

    Be afraid, very afraid, I am trying my hand at VBA code

    I am copying from a lesson for a parts location database, quite often in the code I have to refer to 1Row for example in one part of the code it is Dim 1Row As Long, (no comma or ) shown in the lesson) in another .Cells(1Row, 1).Value=Me.cboPart.Value but each time I enter I get the compile error message below

    What can I be doing wrong?

    Cheers

    Steve
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: VBA Question (2003 SP2)

    The name of a variable must begin with a letter, not with a digit. You may use

    Dim Row1 As Long

    but not

    Dim 1Row As Long

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Question (2003 SP2)

    Hi Hans

    Thanks for the response, that did not help because there was actually a typo in the lesson notes, I found a similar exercise and it referred to iRow and not 1Row as my instructions stated. (although I am none the wiser as to what iRow means either)

    Trying to create a userform using txt boxes I did the following excercise, again I made sure I clearly followed the example given and using iRow instead of 1Row

    Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("PartsData")

    'find first empty row in database'
    iRow = ws.Cells(Rows.Count, 1)_
    .End(xlUp).Offset(1, 0).Row

    'check for a part number'
    If Trim(Me.txtPart.Value) = "" Then
    Me.txtPart.SetFocus
    MsgBox "Please enter a part number"
    Exit Sub
    End If

    'copy the data to the database'
    ws.Cells(iRow, 1).Value = Me.txtPart.Value
    ws.Cells(iRow, 2).Value = Me.txtLoc.Value
    ws.Cells(iRow, 3).Value = Me.txtDate.Value
    ws.Cells(iRow, 4).Value = Me.txtQty.Value

    'clear the data'
    Me.txtPart.Value = ""
    Me.txtLoc.Value = ""
    Me.txtDate.Value = ""
    Me.txtQty.Value = ""
    Me.txtPart.SetFocus

    End Sub

    Private Sub cmdClose_Click()
    Unload Me
    End Sub

    There are 2 buttons, 1 to add parts and one to close the form, when I test it however it queeries the . (period) before the following part of the code

    .End(xlUp).Offset(1, 0).Row

    If I remove the period it still will not run, having copied it verbatim I would have expected it to add the parts to the form I have created but there is obviousely an error/problem that I cannot because of my inexperience see

    Any idea please?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: VBA Question (2003 SP2)

    The underscore _ at the end of the line
    <code>
    iRow = ws.Cells(Rows.Count, 1)_
    </code>
    is a line continuation character. It signifies that the instruction isn't finished yet, but will continue on the last line (normally, an instruction occupies a single line).

    However, there should always be a space between the instruction and the line continuation character, in this example
    <code>
    iRow = ws.Cells(Rows.Count, 1) _
    </code>
    When you insert the space, VBA will interpret the line
    <code>
    .End(xlUp).Offset(1, 0).Row
    </code>
    as being a continuation of the previous line. The period . tells VBA that the End function is applied to the cell Cells(...)

    BTW, the name iRow has no intrinsic meaning. In fact, the name is unfortunate, since the prefix i is often used to indicate variables of type Integer, while this one is declared as Long. But you don;t need to bother with that for the moment.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Question (2003 SP2)

    Thanks Hans

    Perfect explaination and everything worked (unexpectedly for me) as it was supposed to, I experimented and deleted the _ and moved the line .End onto the same line and it still worked.

    Why do you think this was shown as 2 lines, is it perhaps easier (when you understand more) to see what is going on if the lines are split like this.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: VBA Question (2003 SP2)

    In this case, it wasn't really necessary to split the line (but there is nothing against it). Splitting is mainly used for long lines that don't fit on the screen or on paper when printed.

Posting Permissions

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