ByRef Argument Type mismatch

T

Tone

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
 
B

Bob Phillips

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)
 
T

Tone

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)











- Show quoted text -
Click to expand...

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
 
C

Chip Pearson

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 Before: " & 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 Before: " & 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, LLC
www.cpearson.com
(email on web site)




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)











- Show quoted text -
Click to expand...

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
 
T

Tone

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 changeis
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 Before: " & 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 Before: " & 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)






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 -
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Not getting "argument type mismatch" 4
Word macro that can call cells out of Excel for a find and replace 0
Pass "array of strings" into a function?? 1
AddPicturesToShapes 0
ByRef argument type mismatch when passing dictionary object 7
HELP "ByRef Argument Type Mismatch" 6
Optional argument as Variant not acting like number 4
Type mismatch: array or user-defined type expected 3

Top