Byref argument type mismatch vba excel ошибка

I’m working with VBA. I wrote a user define function that takes a string, process it and return a cleaned string. I am not sure what is wrong with it. I am not able to call it and ask it to process my string and return it. I am thinking there are a mistake in the way I am defining or returning it.

Public Function ProcessString(input_string As String) As String
    ' The temp string used throughout the function
    Dim temp_string As String

    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

And I use this function like this

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

Last name is a string variable, usually looks like this Lastname*****, and I am trying to remove all the stars behind it. Have it return Lastname without the stars.

I received Compile error: ByRef arugment type mismatch when I tried to run this. I am using Windows XP with Office 2003.

EDIT: I added the basic struction of the code I have, I have about 20 lines of the similar code. Doing the same thing for each field I need.

Private Sub CommandButton2_Click()
' In my original production code I have a chain of these
' Like this Dim last_name, first_name, street, apt, city, state, zip As String
Dim last_name As String

' I get the last name from a fixed position of my file. Because I am 
' processing it from another source which I copied and pasted into excel
last_name = Mid(Range("A4").Value, 20, 13)

' Insert the data into the corresponding fields in the database worksheet
Worksheets(data_sheet).Range("C2").Value = ProcessString(last_name)

In this article, we will look at the ByRef argument type mismatch error.  First, let us have a look at what is ByRef and the difference between ByRef and ByVal.

In Visual Basic, you can pass an argument to a procedure or function by value or by reference. This is known as the passing mechanism, and it determines whether the procedure or function can modify the variable that has been passed. The declaration of the function determines the passing mechanism for each parameter by specifying the ByVal or ByRef keyword.

The advantage of passing an argument ByRef is that the procedure can return a value to the calling code through that argument. The advantage of passing an argument ByVal is that it protects a variable from being changed by the procedure. The default in VBA is to pass arguments by reference. For more details, you can refer to the article here.

Whenever an argument is passed ByRef (by reference), the default, you must match the precise data type in the argument passed and the argument in the function definition. So, let us have a look at some of the most likely causes of the error and how to fix them:

Case 1: You passed an argument of one type that could not be forced to the type expected.

For example, this error occurs if you try to pass an Integer variable when a Long is expected.

Sub callByRef()

Dim amount As Integer

amount = 80
Call processAmt(amount)

'Rest of the code

End Sub

Public Function processAmt(amount As Long)
'Do your processing here
End Function


The error that you will get is:

Let us look at possible solutions to this:
Solution 1:
If possible, always try to match the data type. This is easiest and quickest solution. So, in our case, the amount variable should be integer (or long, based on the requirement) in both the places.
Dim amount As Long


Solution 2:
Force coercion to occur by passing the argument in its own set of parentheses (even if it causes information to be lost).
Sub callByRef()

Dim amount As Integer

amount = 80

Call processAmt((amount))

End Sub


Placing the argument in its own set of parentheses the fractional portion of the number is rounded to make it conform to the expected argument type. The result of the evaluation is placed in a temporary location and the original amount retains its value.
Solution 3:
Similar to the above solution, you can explicitly convert the data type of the variable while passing
Sub callByRef()

Dim amount As Integer

 amount = 80

 Call processAmt(CLng(amount))

 End Sub


Solution 4:
Pass the variable by value. For that, specify the keyword “ByVal” before the variable name in the function definition.
Public Function processAmt(ByVal amount As Long)
'Do your processing here
End Function


Case 2: You have not declared the variable in the calling sub.
Sub callByRef()

amount = 80

Call processAmt(amount)

End Sub

Public Function processAmt(amount As Long)
'Do your processing here
End Function


Here you need to make sure that the variable is defined along with the data type



  


                       
  




Dim amount As Long


As a standard practice, you should always have “Option Explicit” added to your code as the first line. This makes declaring variables mandatory and helps preventing many errors. Here is an example of where to place it
Option Explicit
Sub callByRef()

'Code here
End Sub


Case 3: Variable is declared but the data type is not specified
Dim firstName


In VB, if you declare a variable without specifying the data type, by default the variable is assigned the type “variant”. So, you need to explicitly specify the variable data type before passing it to a function.
Another common source of error here is when you declare multiple variables on a single line and specify the data type only for the last one. For example,
Dim firstName, lastName As String


Here, VBA sets lastName as a String. However, for firstName it is a variant data type and not String. This is because in VBA, you have to specify data type for each and every variable separately (even if the variables are declared on the same line).
So, the above statement is equivalent to:
Dim firstName As Variant, lastName As String


And the right way to declare the variable is
 Dim firstName As String, lastName As String 
So, to summarize, the argument data type should always be same while calling a function and in the function definition. If you get this error, you can fix it by using one of following methods

Make sure the variable is defined with correct data type
Use explicit conversion while passing the argument
Use an own set of parentheses while passing the argument
Pass the variable by value if you do not intend to change the value in the called function