ByVal (VBA/Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

* 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.

Reply | Quote

Hi Jefferson

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

Mary

Reply | Quote

Hey, it worked!

Thanks

Reply | Quote