Solved Compile error: ByRef argument type mismatch with STRING INPUT

[LiziM]

Hi all I have a production tracking database that creates a new record every time a product is scanned into & out of a warehouse. I need to error-proof the system so a MsgBox pops up when an employee tries to scan a product into a warehouse when it hasn't been scanned out of the previous. I have a function ("CheckScanOut") that, when a product is scanned in, checks to see it was scanned out of the previous warehouse, here is the code:

Public Function CheckScanOut(varProductSerialNumber As String) As Boolean 'receive ProductSerial as input and output=true/false (true if OUT_employee exisits, false if not or
Dim maxID As Long
Dim varOUTEmployee As Variant
Dim TestVar As String

maxID = DMax("[Transaction_ID]", "tbl_Transaction_Master", "[ProductSerialNumber]='" & varProductSerialNumber & "'") ''" & varProductSerialNumber & "'"

varOUTEmployee = (DLookup("[OUT_Employee]", "tbl_Transaction_Master", "[Transaction_ID]=" & maxID))

    CheckScanOut = Not IsNull(varOUTEmployee)
    
End Function

There is an existing record for the serial number I am testing with, so the error can't be due to a Null value. Also, the value passed to this function (varProductSerialNumber) is Dim'ed as a string, but I get the following error: "Compile error: ByRef argument type mismatch" when the following line of code passes an input to CheckScanOut...

If CheckScanOut(varProductSerialNumber) = True Then 'if out_employee does exist in previous warehouse

Please let me know if you need more info!

 

[LiziM]

I changed the first line of the function to

Public Function CheckScanOut(ByVal varProductSerialNumber As String) As Boolean

after reading about it on a forum. It seems to be working now, though I am not 100% sure of the logic behind this change.

 

[The_Doc_Man]

If you are getting the result you want, then you at least have a way through the problem. I have to admit that logically, there should be very little difference between ByRef and ByVal for string comparison. HOWEVER, there IS one major difference that is visible at compile time.

ByVal is forced to be a string by the implied string-copy that occurs during a ByVal argument passage whereas ByRef variables can be anything. Given that it is a Compile error rather than a run-time error, I am thinking that the declaration of varProductSerialNumber in the CALLING routine is somehow different from the declaration in the CALLED routine.

 

[Isaac]

Why are you using a variable in the calling procedure named the same as the parameter name? that seems extremely confusing at best.

 

[The_Doc_Man]

@Isaac, interesting question. Offhand I know of no rule that you can't use the same name, and sometimes the name is totally obvious wherever it is used.

IF the name you use as a parameter is ALSO a Public variable that is in-scope to the called routine, that would lead to scoping conflict issues. I'm not sure that it would lead to compiler errors of the "Type Mismatch" variety. And the use of ByVal vs. ByRef shouldn't affect scoping issues if that were the real problem.

 

[Isaac]

I know of no rule either, it would just confuse the heck out of (me personally) if I did something like this and then had to troubleshoot/debug it:

Function Something(lngInput As Long) As Long
Something = lngInput + 2
End Function

Sub CallingProcedure()

Dim lngInput As Long
lngInput = 3
MsgBox Something(lngInput)

End Sub

I only think it relevant here because adding confusion into a problem doesn't help

 

[MajP]

IF the name you use as a parameter is ALSO a Public variable that is in-scope to the called routine, that would lead to scoping conflict issues.

No there is not scoping issue. An argument just like a local variable is protected within the procedure and can have the same name. Confusing and a bad choice, but no conflict.
Simple test

Public SomeX As Variant


Public Sub TestSomeX()
  SomeX = 123
  SomeProcedure "ABC"
  SomeProcedure SomeX
  SomeProcedure2
  SomeProcedure3 "ABC"
  SomeProcedure3 SomeX
End Sub

Public Sub SomeProcedure(SomeX As Variant)
  Debug.Print SomeX & " in SomeProcedure"
End Sub
Public Sub SomeProcedure2()
  Debug.Print SomeX & " in SomeProcedure2"
End Sub

Public Sub SomeProcedure3(SomeY As Variant)
  Dim SomeX As Variant
  SomeX = SomeY
   Debug.Print SomeX & " in SomeProcedure3"
End Sub

Results

ABC in SomeProcedure
123 in SomeProcedure
123 in SomeProcedure2
ABC in SomeProcedure3
123 in SomeProcedure3

Why are you using a variable in the calling procedure named the same as the parameter name? that seems extremely confusing at best

Funny though, I do this all the time and personally find it less confusing not more. But that is personal preference

Public sub CallingProcedure
  dim UserID a long
  UserID = me.txtUserID
 SomeProcedure UserID
end sub

Public Sub SomeProcedure (UserID as Long)
  do something
end Sub

I am not even sure what I would rename it for the sake of renaming.

 

[Isaac]

Funny though, I do this all the time and personally find it less confusing not more. But that is personal preference

Yeah as soon as I typed that ... I figured "well, some people may feel the exact opposite as I do". I guess I was thinking a lot of the immediate window, and how I'd differentiate it if I needed to. I seem to find myself in break mode half the time some days

 

[The_Doc_Man]

Some time after I wrote that, I realized that the scoping issue is that you CANNOT see the external case because the rule is "closest match wins" when resolving variable names. So if the OP meant to use an external reference (thus causing a "side effect") it would fail in that case.

 

[LiziM]

Why are you using a variable in the calling procedure named the same as the parameter name? that seems extremely confusing at best.

I only call the function once so far, so it's actually easier for me to keep track of variables that way for now. Different strokes for different folks, I guess

 

[LiziM]

If you are getting the result you want, then you at least have a way through the problem. I have to admit that logically, there should be very little difference between ByRef and ByVal for string comparison. HOWEVER, there IS one major difference that is visible at compile time.

ByVal is forced to be a string by the implied string-copy that occurs during a ByVal argument passage whereas ByRef variables can be anything. Given that it is a Compile error rather than a run-time error, I am thinking that the declaration of varProductSerialNumber in the CALLING routine is somehow different from the declaration in the CALLED routine.

Thank you for this response. This has helped my understanding a lot!