[vba] ByRef argument type mismatch in Excel VBA

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)

It looks like ByRef needs to know the size of the parameter. A declaration of Dim last_name as string doesn't specify the size of the string so it takes it as an error. Before using Worksheets(data_sheet).Range("C2").Value = ProcessString(last_name) The last_name has to be declared as Dim last_name as string *10 ' size of string is up to you but must be a fix length

No need to change the function. Function doesn't take a fix length declaration.

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

Something is wrong with that string try like this:

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

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

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.