On 2 Jul, 15:28, "Chip Pearson" wrote:
I guess I just don't fully understand parameter passing in VB.

You can pass a variable either ByVal or ByRef. When you pass ByVal, the
called procedure can read the value and change the value, but that change is
not reflected in the calling procedure. With ByRef, when the called
procedure changes a parameter, that change is reflected in the calling
procedure.

If neither ByVal nor ByRef is specified, VBA uses ByRef. As a matter of
personal coding style, if I write a procedure that will modify one of the
passed in parameters, I include the ByRef specifier to emphasize that its
value will be changed. While this is not necessary, I find it beneficial for
documentation purposes. As an aside, while ByRef is the default in VB6 and
VBA, the default in VBNET is ByVal.

The following code illustrates the difference between passing parameters
ByVal and ByRef, for both value type variables (e.g., Longs, Strings) and
reference type variables (objects like Ranges).

Sub CallingProcedure()
* * Dim Y As Long
* * Y = 1
* * PassByVal Y
* * ' note that even though PassByVal modifies the parameter,
* * ' that change isn't made to the variable Y.
* * Debug.Print "After pass ByVal: " & Y

* * Y = 1
* * PassByRef Y
* * ' note that since the address of Y is passed to PassByRef,
* * ' the change to the variable in PassByRef is made to the
* * ' variable in this procedure.
* * Debug.Print "After pass ByRef: " & Y

* * ' objects are always passed by reference. the ByVal or ByRef
* * ' specifier indicates whether the address of the object is
* * ' passed by reference or value.

* * Dim RR As Range
* * Set RR = Range("A1")
* * PassObjByVal RR
* * ' since the range object RR is passed by value, the
* * ' PassObjByVal procedure can change the value of the
* * ' cell refered to by RR, but it cannot change which
* * ' cell RR refers to.7
* * Debug.Print "After PassObjByVal: " & RR.Address

* * Set RR = Range("A1")
* * PassObjByRef RR
* * ' since the range object RR is passed by reference, the
* * ' PassObjByRef procedure can change the cell to which RR
* * ' refers.

End Sub

Sub PassByVal(ByVal X As Long)
* * ' can change value of local X, but this change is not
* * ' reflected in calling procedure.
* * Debug.Print "ByVal Before Change: " & X
* * X = 2
* * Debug.Print "ByVal After Change: " & X
End Sub

Sub PassByRef(ByRef X As Long)
* * ' can change value of local X, and this change is
* * ' reflected in calling procedure.
* * Debug.Print "ByRef Before Change: " & X
* * X = 3
* * Debug.Print "ByRef After Change: " & X
End Sub

Sub PassObjByVal(ByVal R As Range)
* * ' can change value of Range R and can change
* * ' the cell to which R refers, but this change
* * ' is not reflected in the calling procedure.
* * Debug.Print "ByVal Range Befo " & R.Address
* * R.Value = 123
* * Set R = Range("Z1")
* * Debug.Print "ByVal Range After: " & R.Address
End Sub

Sub PassObjByRef(ByRef R As Range)
* * ' can change the value of Range R and can change
* * ' the cell to which R refers, and this chagne will
* * ' be reflected in the calling procedure.
* * Debug.Print "ByRef Range Befo " & R.Address
* * R.Value = 321
* * Set R = Range("Z1")
* * Debug.Print "ByRef Range After: " & R.Address
End Sub

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
* * Excel Product Group
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

"Tone" wrote in message

...
On 2 Jul, 14:10, "Bob Phillips" wrote:





Works fine for me. You may need to give us more code.

Or even don't use Formula as a variable name, better to use say mFormula.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Tone" wrote in message

...

I have some code that looks like:

Sub doToCells()
Dim formula As String
...
formula = InputBox(message, title, defString)
pr_doToCells formula
end sub

Private Sub pr_doToCells(formula As String)
If formula < "" Then
formula = Replace(formula, ..., ...)
...
End If
End Sub

But I get an error - ByRef Argument type mismatch when it compiles -
complaining about the "formula" used as an argument to pr_doToCells.
Cannot understand why, and the problem disappears if I put parentheses
around the "formula" argument when I call it.

Any ideas?

Thanks
Tone- Hide quoted text -

- Show quoted text -

I assign to "formula" in the second procedure & I think that's what's
upsetting the compiler. Adding a "ByVal" to the argument declaration
in pr_doToCells solves the problem. *I guess I just don't fully
understand parameter passing in VB.

Thanks for trying this out and helping me isolate the issue.

Cheers

Tone- Hide quoted text -

- Show quoted text -

Chip

Many thanks. One thing I don't understand is the difference between

mySub aString

and

mySub(aString)

where mySub is a procedure and aString is a string variable.

Cheers

Tone