Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Ontario, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ByVal (VBA/Excel 2000)

    I can't seem to pass values to a subroutine without the value being changed in the original routine. What am I doing wrong?

    Call WriteDouble(DoubleUps, Row, Col, Cons, Half)

    Private Sub WriteDouble(ByVal DoubleUp As Integer, Row As Integer, Col As Integer, Cons As Integer, Half As Integer)
    Dim i As Integer
    Row = Row + 2
    Col = Col + 2
    For i = Cons To Half + 2 Step -2
    Sheets("Sewing").Cells(Row, Col).Select
    Selection.Value = DoubleUp
    DoubleUp = DoubleUp - 1
    Row = Row + 2
    Next i
    End Sub

  2. #2
    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: ByVal (VBA/Excel 2000)

    Aha! You might think that changing the names of the parameters in the second Sub would disassociate them the variables you are passing, but no, VBA always passes by reference* unless instructed otherwise. You have a couple of options: adding ByVal in front of each parameter declaration, or using parentheses to "de-reference" the variables. (The latter can easily bite you in other situations!) This demo shows how both methods work:

    Sub test1()
    Dim lngA As Long, strA As String
    lngA = 1000
    strA = "Hello"
    Call test2(lngA, strA)
    Debug.Print lngA, strA
    Call test3(lngA, strA)
    Debug.Print lngA, strA
    Call test2((lngA), (strA))
    Debug.Print lngA, strA
    End Sub

    Sub test2(lngB As Long, strB As String)
    lngB = lngB + 1000
    strB = strB & ", you sexy beast."
    End Sub

    Sub test3(ByVal lngB As Long, ByVal strB As String)
    lngB = lngB + 1000
    strB = strB & " Come over here and scratch my back."
    End Sub

    The Immediate window should show the following:

    2000 Hello, you sexy beast.
    2000 Hello, you sexy beast.
    2000 Hello, you sexy beast.

    Alternately, of course, you can treat the passed variables as "read-only" and make changes only to new variables that you Dim in the second Sub. This probably is the best programming practice, but unlike the purists, a few shortcuts don't bother me too much. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    * As described in online help, passing by reference passes the address, in memory, of the passed variable, rather than a separate copy of the value stored in that variable.

  3. #3
    New Lounger
    Join Date
    Aug 2002
    Location
    Ontario, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ByVal (VBA/Excel 2000)

    Hi Jefferson

    Thanks very much for the quick reply. I'll give it a try.

    Mary

  4. #4
    New Lounger
    Join Date
    Aug 2002
    Location
    Ontario, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ByVal (VBA/Excel 2000)

    Hey, it worked!
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Thanks

Posting Permissions

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