ByRef argument type mismatch in Excel VBA

Solution 1:

I suspect you haven't set up last_name properly in the caller.

With the statement Worksheets(data_sheet).Range("C2").Value = ProcessString(last_name)

this will only work if last_name is a string, i.e.

Dim last_name as String

appears in the caller somewhere.

The reason for this is that VBA passes in variables by reference by default which means that the data types have to match exactly between caller and callee.

Two fixes:

1) Force ByVal -- Change your function to pass variable ByVal:
Public Function ProcessString(ByVal input_string As String) As String, or

2) Dim varname -- put Dim last_name As String in the caller before you use it.

(1) works because for ByVal, a copy of input_string is taken when passing to the function which will coerce it into the correct data type. It also leads to better program stability since the function cannot modify the variable in the caller.

Solution 2:

I don't know why, but it is very important to declare the variables separately if you want to pass variables (as variables) into other procedure or function.

For example there is a procedure which make some manipulation with data: based on ID returns Part Number and Quantity information. ID as constant value, other two arguments are variables.

Public Sub GetPNQty(ByVal ID As String, PartNumber As String, Quantity As Long)

the next main code gives me a "ByRef argument mismatch":

Sub KittingScan()  
Dim BoxPN As String
Dim BoxQty, BoxKitQty As Long

  Call GetPNQty(InputBox("Enter ID:"), BoxPN, BoxQty) 

End sub

and the next one is working as well:

Sub KittingScan()
Dim BoxPN As String
Dim BoxQty As Long
Dim BoxKitQty As Long

  Call GetPNQty(InputBox("Enter ID:"), BoxPN, BoxQty)

End sub

Solution 3:

I changed a few things to work with Option Explicit, and the code ran fine against a cell containing "abc.123", which returned "abc.12,". There were no compile errors.

Option Explicit ' This is new

Public Function ProcessString(input_string As String) As String
    ' The temp string used throughout the function
    Dim temp_string As String
    Dim i As Integer ' This is new
    Dim return_string As String ' This is new
    For i = 1 To Len(input_string)
        temp_string = Mid(input_string, i, 1)
        If temp_string Like "[A-Z, a-z, 0-9, :, -]" Then
            return_string = return_string & temp_string
        End If
    Next i
    return_string = Mid(return_string, 1, (Len(return_string) - 1))
    ProcessString = return_string & ", "
End Function

I'll suggest you post more of your relevant code (that calls this function). You've stated that last_name is a String, but it appears that may not be the case. Step through your code line by line and ensure that this is actually the case.

Solution 4:

While looping through your string one character at a time is a viable method, there's no need. VBA has built-in functions for this kind of thing:

Public Function ProcessString(input_string As String) As String
    ProcessString=Replace(input_string,"*","")
End Function